250110 TIL 데이터 분석과정 (QCC만 하다가 하루가 끝난...)

1. 오늘의 목표🎯

 
이제 슬슬 포트폴리오에 자료들을 붙여넣으려고 하는데, 할 게 많아서 정신이 없다. 아직 모르는 게 너무 많은 느낌이다. 
그래도 통계학 기초 강의는 어제부로 완강하였고, 이제 실습을 다시 한 번 풀어보면서 파이썬에 적용하는 방법을 연습하려고 한다. 남는 시간은 틈틈히 파이썬이랑 sql복습하기! 

---

2. 회고📝

오늘 하루는 QCC문제만 풀다가 하루가 다 간 것 같다.
문제를 푸는데 concat 함수 적용하는 방법이 생각이 안나는 것이다..
코드를 돌려봐도 계속 first name과 last name만 결과에 출력되어서 진심 당황했다. 그래서 한 문제 겨우 제출하고 도망갔더니 매니저님이 따라오심ㅋㅋㅋ
 
왜 출력이 안되었나 하니 컬럼에 따옴표을 씌워서 출력했기 때문.. 그냥 문자열만 붙이려면 따옴표를 붙여야 하지만 컬럼을 연결할 때는 필요없다.

CONCAT ( argument1 , argument2 [ , argumentN ] ... )

그래도 한 번 틀리고 나니 기억에는 아주 잘 남는다. 절대 잊지않겠다. concat 따옴표
 
문제 복습하다가 하루가 다 지났는데...세 문제 풀다가 하루가 갈 수도 있구나😂😂 그래도 여전히 이해가 안가서 그냥 받아들이는 중이다. 마치 고등학교 수학 시간에 이해를 포기하고 공식을 우겨넣은 거 마냥 겁나 외워대고 있다. 
 
 
한 구절 한 구절 이해하면서 코드를 따라치다보니 시간이 생각보다 많이 걸린다. 그래도 대충 보고 넘기는 것보다는 천천히하더래도 머리에 꾹꾹 넣는 게 훨씬 효율적일 거라 믿는 즁 
 
오늘 만난 sql은 초면 같았다. 파이썬 하느라 디비버를 잊고 산 내 잘못이오..에디터 키는 것도 어딨는 지 헷갈려서 뒤적뒤적 거림. 뭔가 0개 국어가 되어가는 기분인데....아 그것과 더불어 요새 자꾸 사람이름이 잘 기억이 안나서 기억력이 퇴화되어 가는 중이라 더 공부하기 힘드뤄

 

퇴근 전 유일한 웃음..

---

3. QCC 복습 

문제 1. 

첫 주문 고객 연도별 매출 조회 “2011년 12월”에 첫 주문을 한 고객들의 연도별 매출을 조회하는 SQL 문을 작성하세요. 고객 이름은 이름과 성을 결합하여 생성합니다. 결과는 고객 ID(`customerid`)기준 오름차순, 연도(`year`) 기준 오름차순으로 정렬합니다. 출력: | customerid | customer_name | year | gmv | 

 
브레인스토밍 
1. 모든 값들을 한 번에 구할 수 없기에 2011년 12월에 첫 주문을 한 고객들 with절로 구해준다. 이 과정에서 분리되어 있는 이름과 성을 concat 함수로 붙여준다. 
 
2. 메인 쿼리에서 고객id, 고객 이름, 첫 주문 이후의 연도별 거래 금액, 각 주문의 거래금액 합계를 구해준다. 
 
3. left join로 테이블을 연결해서 문제에서 요구하는 값들을 출력해준다. 
left join을 사용하는 이유는 A고객이 2011년에는 구매내역이 있지만, 2012년에는 구매내역이 없을 수도 있기 때문에 해당 연도의 매출합계를 0으로 반환하기 위함이다. 
 
4. 또한 with절에 group by를 사용하였으나, select절에는 집계함수를 사용하지 않았는데, 이는 단순히 그룹화한 후에 having으로 필터링만 해주고자 해서 집계함수를 넣지 않았다. 나는 무조건 집계함수를 넣어주어야 하는 줄 알았는데, 상황에 따라 달라지는 것이었다. 

#2011년 12월에 첫 주문을 한 고객, 이름 결합
WITH first_order as (select c.customerid,
                            CONCAT(c.firstname, ' ', c.lastname) as customer_name
                     from customer c
                     inner join sales_order as so on c.customerid = so.customerid
                     group by so.orderdate #주문 월별로 집계를 해줌 group by를 사용하고 select에 반드시 집계함수가 필요한 것은 아님
                     having DATE_FORMAT(min(so.orderdate), '%Y-%m') = '2011-12' # having절로 집계한 그룹에서 2011년 12월에 해당하는 값들만 찾아준다.
) 
select fo.customerid, #고객 ID
       fo.customer_name, #고객 이름 
       year(so.orderdate) as year, #첫 주문 이후의 연도별 거래 금액을 집계해야 하기 때문
       sum(so.unitprice * so.orderQty) as gmv #각 주문의 거래 금액, 이를 연도별로 합산함
from first_order as fo 
left join sales_order so on so.customerid = fo.customerid #first_order에는 고객의 정보가 포함될 수 있지만, 그 다음 해에는 구매내역이 없을 수도 있음
group by fo.customerid, fo.customer_name, year(so.orderdate) #select절에 포함된 모든 컬럼이 group by에 포함되어야 함  
order by fo.customerid, year;

 

---

문제2. 고객별 연평균 총 거래액 집계

2011년 12월에 첫 주문을 한 각 고객의 첫 주문과 마지막 주문 사이의 기간을 기준으로, 해당 기간 동안의 연평균 총거래액(GMV)을 계산합니다. 소수점 2째자리까지 계산해주세요.
결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.

• 총거래액, GMV(Gross Merchandise Value)는 1번 문제에서 집계한 값을 사용합니다.
• 특정 연도에 주문을 하지 않았다면, 그 연도의 GMV와 주문 수는 0으로 처리하고 계산합니다. 예:
  • 고객 A: 첫주문 2011년 ($100), 2012년 ($250), 마지막 주문 2013년 ($400)
    • 연평균 GMV = (100 + 250 + 400) / 3 = $250.00
  • 고객 B: 첫주문 2011년 ($100), 마지막 주문 2013년 ($200)
    • 연평균 GMV = (100 + 0 + 200) / 3 = $100.00
    • (2012년에는 주문이 없으므로 그 해의 총거래액(GMV)은 0으로 처리하여 평균 계산)
  • 고객 C: 첫주문 & 마지막 주문 2011년 ($100)
    • 연평균 GMV = 100 / 1 = $100.00

 

 

1. 2011년 12월에 첫 주문한 고객들의 첫 주문과 마지막 주문 연도를 추출한다. 

2. filtered_customer의 각 고객에 대해 첫 주문 연도부터 마지막 주문 연도까지의 모든 연도를 생성

3. 최종적으로 고객별로 연도별 GMV(Gross Merchandise Value)를 계산한 다음, 고객의 평균 연간 GMV를 반환해준다. 

#2011년 12월에 첫 주문을 한 고객의 첫 주문 연도와 마지막 주문 연도를 계산
with filtered_customer as (select c.customerid,
	    				 concat(c.firstname, ' ', c.lastname) as customer_name, 
	    				 MIN(year(so.orderdate)) first_order_year,
	    				 MAX(year(so.orderdate)) last_order_year
	    			from customer c
	    			inner join sales_order so on c.customerid = so.customerid
	    			group by c.customerid, customer_name
	    			having date_format(min(so.orderdate), '%Y-%m') = '2011-12'
), #filtered customer의 모든 주문 연도를 추출 
customer_years as (
select 
fc.customerid,
fc.customer_name,
dy.year
from filtered_customer fc 
      left join dim_years dy on dy.year between fc.first_order_year and fc.last_order_year
),
customer_gmv as ( #gmv를 계산한 다음, 고객의 평균 연간 GMV를 반환 
	 select 
	 cy.customerid,
	 cy.customer_name, 
	 cy.year,
	 COALESCE(SUM(so.unitprice * so.orderqty), 0) gmv
	 from customer_years cy
	 left join sales_order so on cy.customerid = so.customerid and cy.year = year(so.orderdate)
)
select 
 	customerid,
 	customer_name, 
 	round(avg(gmv),2) agv_yearly_gmv
from 
	 customer_gmv
group by customerid, customer_name 
order by customerid;

 

 

✅COALESCE

인수를 순서대로 평가하고 처음으로 NULL이 아닌 첫 번째 식의 현재 값을 반환합니다. 예를 들어 SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');는 세 번째 값이 Null이 아닌 첫 값이기 때문에 세 번째 값을 반환합니다.

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;

 

 

---

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

2011년 12월에 첫 주문을 한 고객 대상으로, 고객별 각 연도에서의 주문 수량을 집계하고, 최대 주문 수량과 그 최대 주문 수량을 발생시킨 연도를 찾습니다.
최대 주문 수량이 같은 연도가 있다면, 가장 최근 연도를 출력합니다.
결과는 고객 ID(customerid) 기준으로 오름차순으로 정렬합니다.
만약 최대 주문 수량이 여러 연도에서 발생하면, 더 최근의 연도를 선택합니다.
예: 고객 A: 2011년 (주문 5개), 2012년 (주문 10개), 2013년 (주문 11개) 최대 주문 수량 : 11개 최대 주문 수량이 발생한 연도 : 2013년 고객 B: 2011년 (주문 10개), 2012년 (주문 5개), 2013년 (주문 10개) 최대 주문 수량 : 10개 최대 주문 수량이 발생한 연도: 2013년 (2011년과 주문 수량이 같지만 더 최근의 연도)

 

 

#2011년 12월에 첫 주문을 한 고객
WITH first_order as (select c.customerid,
				 CONCAT(c.firstname, ' ', c.lastname) as customer_name  
				 from customer c
				 inner join sales_order so on c.customerid = so.customerid
				 group by c.customerid, customer_name 
				 having DATE_FORMAT(min(so.orderdate), '%Y-%m') = '2011-12' 
),#연도 별 주문 수 집계 
customer_qty_rank as (select fo.customerid, 
						fo.customer_name, 
						year(so.orderdate) year, 
						sum(so.orderqty) total_qty,
						row_number() over (PARTITION by fo.customerid
							 	                  order by sum(so.orderqty) desc,
							 	                  year(so.orderdate)desc) as qty_rank
				      from first_order fo 
				      			left join sales_order so on fo.customerid = so.customerid
				      			group by fo.customerid, fo.customer_name, year(so.orderdate)
)
SELECT customerid,
	   customer_name, 
	   year as max_qty_year,
	   total_qty as max_total_qty
from customer_qty_rank
where qty_rank = 1
order by customerid

 

 

✅ 윈도우 함수 - ROW NUMBER() 

결과 집합의 파티션 내 각 행에 순차적인 정수를 할당하는 함수이다. 윈도우 함수에는 OVER절이 항상 함께 사용되어야 한다. 

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

PARTITION BY 
윈도우 함수를 적용할 데이터를 파티션으로 나눈다.  ROW_NUMBER() 윈도우 함수는 각 파티션에 별도로 정수를 할당하며, 각 파티션 별로 1번부터 할당한다.

PARTITION BY 절은 옵션 값이며, PARTITION BY를 무시하고 사용한다면 결과 집합의 전체를 한 파티션으로 취급한다.

 

ORDER BY

결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의한다. ROW_NUMBER()함수는 정수를 순차적으로 할당하는 윈도우 함수이기 때문에 ORDER BY절은 필수적으로 필요하다.

 

 

OVER 절(Transact-SQL) - SQL Server

 

ROW_NUMBER(Transact-SQL) - SQL Server