[TIL] camp_day26_240522

프로젝트 결론이 대략적으로 나왔는데, 뭔가 조금 아쉽다.
계속 파이썬 과제와 프로젝트 때문에 밀려서 코테를 못 풀고 있었는데 SQL 문제 막혔던 두 개를 비롯해서 오랜만에 문제를 많이 풀었다.

학습 내용

---

1. JOIN 시 조건을 범위로 지정할 수 있다.

   • 문제 : 리트코드_평균 판매 금액 구하기

   • 상품별로, 기간에 따라 가격이 변경될 때 각각의 상품들의 평균 판매 금액을 구해서 출력하는 문제였다.

   • 지금까지 JOIN 을 할 때 정확히 일치하는 값만 Key로 설정했는데, 처음으로 범위를 지정해도 된다는 것을 알게된 문제.

   • 두 개의 테이블을 조인해야 하는데,
     (1) product_id 끼리 일치하고, (2) 상품 판매일이 특정 가격의 시작일과 종료일 기간 내에 포함되는 값끼리 조인해야 했다.

   • 작성한 쿼리문

       SQL
       # 처음 작성한 쿼리문 : 틀렸다.
       # 판매된 상품이 아예 없는 경우 0을 출력해야 하는데, 아래 쿼리문은 제대로 작동하지 않았다.
       # ROUND 함수와 COALESCE 가 사용된 순서 때문에 그런 것 같아서 두 번째 쿼리로 수정함
       SELECT p.product_id
       ,round(COALESCE(sum(u.units*p.price)/sum(u.units),0),2) as average_price
       FROM Prices as p
       INNER JOIN UnitsSold as u
           on u.product_id = p.product_id
           and purchase_date between start_date and end_date
       GROUP BY p.product_id

       SQL
       # SQL Style Guide를 참고한 다음이라 쿼리가 좀 더 깔끔해졌다...
       SELECT 
         p.product_id
         ,IFNULL(ROUND((SUM(units * price)/SUM(units)),2),0) AS average_price
       FROM 
         Prices AS p
       LEFT JOIN UnitsSold AS us
           ON us.product_id = p.product_id
           AND us.purchase_date BETWEEN p.start_date AND p.end_date
               # 이렇게 범위를 JOIN 조건으로 설정할 수 있다!
       GROUP BY 1

2. 화요일에 배운 WINDOW 함수 활용해서 푼 문제들!

   1. LAST_VALUE 활용 : 문제_리트코드_마지막으로 버스에 승차할 수 있는 사람

       SQL
       SELECT 
         DISTINCT LAST_VALUE(person_name) OVER() AS person_name
       FROM
       (
        SELECT
         person_name
        FROM
           (
           SELECT 
             person_name
             ,SUM(weight) OVER (ORDER BY turn) as cum_sum
           FROM
               Queue
           ) AS q
        WHERE cum_sum <= 1000
       ) AS f

   2. LEAD 활용 : 문제_리트코드_세 번 연속해서 등장하는 숫자 출력하기

      • 처음에 JOIN 써서 풀어보려고 했는데, 테스트 케이스도 통과하지 못해서 제쳐뒀던 문제다
      • WINDOW 함수 배우자마자 써봤는데 바로 문제가 풀려서 너무 좋았다.

        SQL
        # id 가 1, 2, 4 처럼 연속적이지 않을 때 쿼리가 값을 출력하여 오답이 되므로 해당 조건 추가
        SELECT DISTINCT num AS ConsecutiveNums
        FROM
        (
          SELECT 
          id
          ,LEAD(id,2) OVER (ORDER BY id) AS id_plus_two
          ,num
          ,LEAD(num,1) OVER (ORDER BY id) AS second_num
          ,LEAD(num,2) OVER (ORDER BY id) AS third_num
          FROM Logs
        ) AS l
        WHERE num = second_num 
            AND num = third_num
            AND id + 2 = id_plus_two