QCC | 5회차 코드 리뷰 & 피드백

☑️  문제1 . 조건별 연도별 매출 집계

[문제]

---

[문제 풀이]

내 코드

SELECT release_year
	,sum(revenue) AS revenue
FROM movies m 
WHERE release_year >=2012 
  AND VOTE_COUNT >=100 
  AND JSON_LENGTH(genres)>=2
 GROUP BY release_year 
 ORDER BY 1;

 

이슈 및 해결 과정

• 단순히 조건 필터링후 집계 하는 문제
• 장르 컬럼이 JSON 타입으로 저장 → 2개이상 조건 필터링시 json 타입의 길이 반환해주는 함수 적용
  
  • `JSON_LENGTH(genres)`

• `JSON_LENGTH(json_doc)`
  • json 타입의 길이 반환해주는 함수, 유효한 표현식이 아닐경우 에러남
  • 중첩된 배열 계산 안함

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
+---------------------------------+
| JSON_LENGTH('[1, 2, {"a": 3}]') |
+---------------------------------+
|                               3 |
+---------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
+------------------------------------------------+
| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+

 

한줄 포인트

• JSON 타입 길이 반환시 `JSON_LENGTH` 함수 활용할 것  
• 정답처리

☑️  고객 중 상위 5명의 순위 매기기 & 총 결제 금액 집계하기 

[문제]

---

[문제 풀이]

내 코드

SELECT customer_id AS CustomerID
        ,sum(total_due) AS TotalOrderAmount
        ,DENSE_RANK () OVER (ORDER BY sum(total_due) DESC) AS rn
FROM sales_order_header soh 
GROUP BY customer_id 
ORDER BY TotalOrderAmount DESC, customer_id 
LIMIT 5;

 

이슈 및 해결 과정

• 아이디어 : 고객 그룹바이 기준으로 총 매출 집계 & 순위 매기기 
• 순위는 동일 출력값은 동일한 순위로 매겨지며 비어있는 순위가 없으므로 → `Dense_rank` 사용

한줄 포인트

• 정답처리

☑️  SCD Type 2 형식의 데이터 테이블을 생성하기

[문제]

---

[문제 풀이]

내 코드

WITH customer_date AS ( -- 고객 상태별 일자 확인
SELECT customer_id
	  ,CASE WHEN event_type ='JOIN' then 'ACTIVE' ELSE 'INACTIVE' END AS status
	  ,event_date AS start_date
	  ,DATE_SUB(LEAD(event_date,1) OVER (PARTITION BY customer_id ORDER BY event_date),INTERVAL 1 DAY) AS end_date
FROM membership_history mh    
ORDER BY customer_id,event_date
)
SELECT customer_id
	  ,status
	  ,start_date
	  ,end_date
	  ,CASE WHEN status = 'ACTIVE' AND end_date IS NULL THEN 1 ELSE 0 END AS current_flag
FROM customer_date
ORDER BY 1,3;

 

이슈 및 해결 과정

• 아이디어 : 기존 컬럼 유지한 후 집계 컬럼 추가생성 → `윈도우 함수` 사용
  • status 컬럼 : event_type 에서 조건별값 변경 → `case ~ when` 조건문 적용
  • end_date 컬럼 : customer_id 기준 다음 event_date 의 하루 전날 → `lead` & `date_sub` 중첩 사용
  • current_flag 컬럼: status & end_date 컬럼 조건별 값 변경 → `case when` 사용 & 다중 중첩으로 가독성 떨어지므로 staus & end_date 컬럼 추가한 테이블 생성 후 current_flag 컬럼 추가 

한줄 포인트

• 오답 → `CASE WHEN status = 'ACTIVE' AND end_date IS NULL THEN '1' ELSE 0 END AS current_flag` 에서 1을 문자열 '1' 로 설정 함.. 
• 리마인드
  • date_sub( 컬럼, interval n day/week/등)
  • lead(출력할 컬럼) over (partiton by 그룹컬럼 order by 정렬순서) 

https://dev.mysql.com/doc/refman/8.4/en/json-attribute-functions.html

MySQL :: MySQL 8.4 Reference Manual :: 14.17.5 Functions That Return JSON Value Attributes