2025-01-10 TIL (QCC 리뷰)

👀 아니!!? 언제 등재 되었어..!

대화 분석을 통한 사회정서역량 측정 애플리케이션 개발 - ChatGPT API를 이용하여 -

대화 분석을 통한 사회정서역량 측정 애플리케이션 개발 - ChatGPT API를 이용하여 -

 

살다보니 이런일이 생기는구나, 애플리케이션 프로토타입 부분 중 총점 매핑, 시각화 부분을 담당 했는데 논문에 등재되다니..

정말 신기한 경험인 것 같다. 아무튼 감개무량합니다!!!

 

QCC 리뷰

문제 1 : 첫 주문 고객 연도별 매출 조회

1. 2011년 12월에 첫 주문을 한 고객들의 연도별 매출

2. 고객 ID 오름차순, 연도 오름차순

 

문제 1 풀이

-- 1.2011년 12월에 첫 주문한 고객
SELECT customerid
FROM sales_order
GROUP BY customerid
HAVING year(min(CAST(orderdate AS date))) = '2011'
	AND month(min(CAST(orderdate AS date))) = '12'

 

customerid을 그룹화 해서 가장 작은 날짜인이 2011년 12월인 고객 가져오기

 

-- 2.2011년 12월에 첫 주문한 고객 주문 내역
SELECT s.customerid, unitprice * orderqty AS totalprice, left(s.orderdate, 4) AS year
FROM sales_order s
WHERE customerid IN 
	(
		SELECT customerid
		FROM sales_order
		GROUP BY customerid
		HAVING year(min(CAST(orderdate AS date))) = '2011'
			AND month(min(CAST(orderdate AS date))) = '12'
	)

 

해당 고객들의 주문가져오기 totalprice로 총금액 계산

 

-- 3. 연도별 계산
SELECT s.customerid, left(s.orderdate, 4) AS year, sum(unitprice * orderqty) AS gmv
FROM sales_order s
WHERE customerid IN 
	(
		SELECT customerid
		FROM sales_order
		GROUP BY customerid
		HAVING year(min(CAST(orderdate AS date))) = '2011'
			AND month(min(CAST(orderdate AS date))) = '12'
	)
GROUP BY 1, 2

 

해당 고객들의 연도별로 집계 gmv로 계산

 

-- 4. 이름 가져오기, 정렬
SELECT s.customerid
	,(SELECT concat(c.firstname, ' ', c.lastname) 
		FROM customer c 
		WHERE s.customerid = c.customerid) AS customer_name
	, CAST(left(s.orderdate, 4) AS UNSIGNED) AS year
	, sum(unitprice * orderqty) AS gmv
FROM sales_order s
WHERE customerid IN 
	(
		SELECT customerid
		FROM sales_order
		GROUP BY customerid
		HAVING year(min(CAST(orderdate AS date))) = '2011'
			AND month(min(CAST(orderdate AS date))) = '12'
	)
GROUP BY s.customerid, CAST(left(s.orderdate, 4) AS UNSIGNED)
ORDER BY s.customerid, CAST(left(s.orderdate, 4) AS UNSIGNED);

 

상관 서브쿼리로 해당 이름 가져오고 정렬

 

 

문제 2

문제 2 풀이

1. 날짜 구간을 사용한 계산

-- 1. 2011-12 첫 구매 사용자 테이블 가져오기
SELECT s.customerid,
    CAST(left(s.orderdate, 4) AS UNSIGNED) AS year, 
    sum(s.orderqty * s.unitprice) AS gmv
FROM sales_order s
WHERE customerid IN 
(
    SELECT customerid
    FROM sales_order
    GROUP BY customerid 
    HAVING min(orderdate) like '2011-12%'
)
GROUP BY s.customerid, left(s.orderdate, 4)
ORDER BY 1, 2

 

문제 1에 사용했던 테이블 사용

 

-- 2 집계 계산
temp_02 AS (
	SELECT customerid, round(sum(gmv) / (max(year) - min(year) + 1),2) AS avg_yearly_gmv
	FROM temp_01 
	GROUP BY customerid
)

 

구간을 이용한 연 평균 계산

 

-- 3. 이름 붙이기, 정렬
SELECT customerid
	, (SELECT concat(c.firstname, ' ', c.lastname) 
		FROM customer c 
		WHERE t2.customerid = c.customerid) AS customer_name
	, avg_yearly_gmv
FROM temp_02 t2
ORDER BY customerid ASC;

출력에 맞는 이름, 정렬 진행

 

-- 전체코드
WITH temp_01 AS (
	SELECT s.customerid,
		CAST(left(s.orderdate, 4) AS UNSIGNED) AS year, 
		sum(s.orderqty * s.unitprice) AS gmv
	FROM sales_order s
	WHERE customerid IN 
	(
		SELECT customerid
		FROM sales_order
		GROUP BY customerid 
		HAVING min(orderdate) like '2011-12%'
	)
	GROUP BY s.customerid, left(s.orderdate, 4)
	ORDER BY 1, 2
),
temp_02 AS (
	SELECT customerid, round(sum(gmv) / (max(year) - min(year) + 1),2) AS avg_yearly_gmv
	FROM temp_01 
	GROUP BY customerid
)
SELECT customerid
	, (SELECT concat(c.firstname, ' ', c.lastname) 
		FROM customer c 
		WHERE t2.customerid = c.customerid) AS customer_name
	, avg_yearly_gmv
FROM temp_02 t2
ORDER BY customerid ASC;

 

2. dim_years 사용

-- 1. 2011-12 고객중 첫번째 주문 연도, 마지막 주문 연도 가져오기
SELECT 
    c.customerid,
    CONCAT(c.firstname, ' ', c.lastname) customer_name,
    MIN(YEAR(s.orderdate)) first_order_year,
    MAX(YEAR(s.orderdate)) last_order_year
FROM 
    customer c
INNER JOIN sales_order s ON c.customerid = s.customerid 
GROUP BY c.customerid, customer_name
HAVING DATE_FORMAT(MIN(s.orderdate), '%Y-%m') = '2011-12'

 

customer_filtered Table

customerid	custumer_name	first_order_year	last_order_year
11387	Megan Ramirez	2011	2013
11388	Joseph Martin	2011	2013

 

-- 2. 최초 주문 연도, 마지막 주문 연도 사이 채우기
select 
    cf.customerid,
    cf.customer_name,
    d.year
from customer_filtered cf
	left join dim_years d on d.year between cf.first_order_year and cf.last_order_year

 

dim Table

year

2000

2001...

 

Between Join

customer_years Table

cf.customerid	cf.custumer_name	d.year
11387	Megan Ramirez	2011
11387	Megan Ramirez	2012
11387	Megan Ramirez	2013
11388	Joseph Martin	2011
11388	Joseph Martin	2012
11388	Joseph Martin	2013

 

-- 3. 고객별 연도별 합계 
SELECT
    cy.customerid,
    cy.customer_name,
    cy.year,
    COALESCE(SUM(s.unitprice * s.orderqty),0) gmv  
FROM
    customer_years cy  
LEFT JOIN sales_order s ON cy.customerid=s.customerid AND cy.year = YEAR(s.orderdate)
GROUP BY cy.customerid, cy.customer_name, cy.year

 

customer_years 테이블에 각 연도, 고객번호 결합

null 값 0으로

 

-- 고객별 연평균 계산, 정렬
SELECT
	customerid,
	customer_name,
	ROUND(AVG(gmv), 2) avg_yearly_gmv
FROM 
	customer_gmv
GROUP BY customerid, customer_name
ORDER BY customerid;

 

만들어진 행으로 계산

 

문제 3 : 고객별 최대 주문 수량 연도와 주문 수량 집계

1. 2011년 12월에 첫 주문을 한 고객 대상, 각 연도에서 주문 수량 집계, 최대 주문 수량과 그 최대 주문 수량 발생시킨 연도

2. 최대 주문 수량이 같은 연도가 있으면, 가장 최근 연도 출력

3. 고객 ID 기준으로 오름차순 정렬

 

문제 3 풀이

-- 1. 2011년 12월 첫 주문을 한 고객 가져오기
SELECT *
FROM sales_order s
WHERE customerid IN 
	(
		SELECT customerid
		FROM sales_order
		GROUP BY customerid
		HAVING year(min(CAST(orderdate AS date))) = '2011'
			AND month(min(CAST(orderdate AS date))) = '12'
	)

 

문제 1번와 같이 첫 주문을 한 고객 주문 테이블 가져오기

 

-- 2. 연도별로 주문 수량 집계
SELECT s.customerid,
		CAST(left(s.orderdate, 4) AS UNSIGNED) AS year_order,
		sum(s.orderqty) AS total_qty
FROM sales_order s
WHERE customerid IN 
	(
		SELECT customerid
		FROM sales_order
		GROUP BY customerid
		HAVING year(min(CAST(orderdate AS date))) = '2011'
			AND month(min(CAST(orderdate AS date))) = '12'
	)
GROUP BY s.customerid, CAST(left(s.orderdate, 4) AS UNSIGNED)

 

고객 아이별 연도별 주문 수량 집계

 

-- 3. 연도별로 주문 수량 집계 순서 매기기
WITH temp_01 AS (
	SELECT s.customerid,
			CAST(left(s.orderdate, 4) AS UNSIGNED) AS year_order,
			sum(s.orderqty) AS total_qty
	FROM sales_order s
	WHERE customerid IN 
		(
			SELECT customerid
			FROM sales_order
			GROUP BY customerid
			HAVING year(min(CAST(orderdate AS date))) = '2011'
				AND month(min(CAST(orderdate AS date))) = '12'
		)
	GROUP BY s.customerid, CAST(left(s.orderdate, 4) AS UNSIGNED)
)
SELECT *, 
	RANK() OVER (PARTITION BY customerid ORDER BY total_qty desc, year_order desc) AS rk
FROM temp_01

 

고객아이디별 그룹, 주문건수 내림차순 같으면 연도 내림차순 -- 조건 2

 

-- 4. 연도별로 주문 수량 집계 순서 매기기
WITH temp_01 AS (
	SELECT s.customerid,
			CAST(left(s.orderdate, 4) AS UNSIGNED) AS year_order,
			sum(s.orderqty) AS total_qty
	FROM sales_order s
	WHERE customerid IN 
		(
			SELECT customerid
			FROM sales_order
			GROUP BY customerid
			HAVING year(min(CAST(orderdate AS date))) = '2011'
				AND month(min(CAST(orderdate AS date))) = '12'
		)
	GROUP BY s.customerid, CAST(left(s.orderdate, 4) AS UNSIGNED)
),
temp_02 AS (
	SELECT *, 
		RANK() OVER (PARTITION BY customerid ORDER BY total_qty desc, year_order desc) AS rk
	FROM temp_01
)
SELECT t2.customerid, 
	(SELECT concat(firstname, ' ', lastname) 
	FROM customer c 
	WHERE t2.customerid = c.customerid) AS customer_name,
	t2.year_order AS max_qty_year,
	t2.total_qty AS max_total_qty
FROM temp_02 t2
WHERE rk = 1
ORDER by t2.customerid

 

가장 높은 rk = 1만 가져오기, 상관서브쿼리로 이름 가져오기, 고객아이디로 정렬