[MYSQL] SolveSql Window Function

✔ Window Function

레스토랑 요일 별 구매금액 Top 3 영수증

[SolveSql top-3-bill]

• NTILE() OVER() : 지정된 수만큼의 파티션으로 등급을 나누어 각 등급 번호를 반환
•        RANK() OVER() : 순위 값 중 동등 순위 번호를 같게 나오고 그 다음 순위를 다음 번호를 뺀 그 다음 값을 반환 (1 1 3)
• DENSE_RANK() OVER() : 순위 값 중 동등 순위 번호는 같게 나오고 그 다음 순위를 다음 번호로 반환 (1 1 2)
• ROW_NUMBER() OVER() : 동등 순위를 인식하지 않고 매번 증가되는 번호를 반환 (1 2 3)
  • OVER(PARTITION BY) : 이때 파티션을 적용해 파티션 안의 순위를 찾을 수 있음
• LEAD() OVER() : 지정된 칼럼 이전의 행 값을 반환 
• LAG() OVER() : 지정된 칼럼 이후의 행 값을 반환
• FIRST_VALUE() OVER() : 각 그룹별 첫 번째 값 하나만 반환
• LAST_VALUE() OVER() : 각 그룹별 마지막 값 하나만 반환
• CUME_DIST() OVER() : 주어진 그룹에 대한 상대적인 누적분포도 값을 반환하며 반환 값의 범위는 0 초과 1 이하 사이의 값

/*
 SELECT : 요일, 시간대, 결제자 성별, 식사 금액, 요일별 결제 금액 중복 순위
 FROM : tips 테이블
*/
WITH
  bill_rank AS (
    SELECT
      day,
      time,
      sex,
      total_bill,
      DENSE_RANK() OVER (PARTITION BY day ORDER BY total_bill DESC) AS 'rank'
    FROM
      tips
  )

/*
 SELECT : 요일, 시간대, 결제자 성별, 식사 금액
 FROM : bill_rank 테이블
 WHERE : 요일 별로 상위 결제 금액이 3위 안에 드는 경우
*/
SELECT
  day,
  time,
  sex,
  total_bill
FROM
  bill_rank
WHERE
  rank <= 3

카테고리 별 매출 비율

[SolveSql revenue-pct-per-category]

• SUM() OVER() : SUM()된 값에서 누적 합계를 구하여 반환
  • OVER(PARTITION BY) : 이때 파티션을 적용해 파티션 안의 누적 합을 찾을 수 있음

/*
 SELECT : 카테고리, 서브 카테고리, 카테고리-서브 카테고리 별 매출액의 합계
 FROM : records 테이블
 GROUP BY : category, sub_category
*/
WITH
  sub_records AS (
    SELECT
      category, sub_category, SUM(sales) AS sum_sales
    FROM
      records
    GROUP BY
      category, sub_category
  )
  
/*
 SELECT : 카테고리 이름, 서브 카테고리 이름, 서브 카테고리 별 매출액의 합계, 카테고리 별 매출액의 합계, 전체 매출액, 
          카테고리 매출 중 해당 서브 카테고리 매출의 비율, 전체 매출 중 해당 서브 카테고리 매출의 비율
 FROM : sub_records 테이블
*/
SELECT
  category,
  sub_category,
  ROUND(SUM(sum_sales) OVER (PARTITION BY sub_category), 2) AS 'sales_sub_category',
  ROUND(SUM(sum_sales) OVER (PARTITION BY category), 2) AS 'sales_category',
  ROUND(SUM(sum_sales) OVER(), 2) AS 'sales_total',
  ROUND(SUM(sum_sales) OVER (PARTITION BY sub_category) / SUM(sum_sales) OVER (PARTITION BY category) * 100, 2) AS 'pct_in_category',
  ROUND(SUM(sum_sales) OVER (PARTITION BY sub_category) / SUM(sum_sales) OVER () * 100, 2) AS 'pct_in_total'
FROM
  sub_records;

세션 재정의하기

[SolveSql redefine-session]

/*
 SELECT : 사용자 아이디, 이벤트의 종류, 이벤트 시각, 이전 이벤트 시각, 다음 이벤트 시각, 아이디
 FROM : ga 테이블
 WHERE : 사용자 아이디가 S3WDQCqLpK

 SELECT : 사용자 아이디, 이벤트 시각, 아이디, 이전 이벤트와 이벤트 사이의 시간 차이, 이벤트와 다음 이벤트 사이의 시간 차이
 FROM : event_data 테이블

 SELECT : 전체, 1시간 이상 행동하지 않았을 경우 새로운 세션으로 재정의하고 그렇지 않다면 기존과 동일한 세션으로 설정
 FROM : event_diff 테이블
 
 SELECT : 사용자 아이디, 세션 시작 시각, 세션 종료 시각
 FROM : session_data 테이블
 GROUP BY : 사용자 아이디, 세션 별
 ORDER BY : 세션 시작 시각 기준 정렬
*/

# SQLite
WITH event_data AS (
  SELECT user_pseudo_id
        ,event_name
        ,event_timestamp_kst
        ,LAG(event_timestamp_kst,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS 'last_event'
        ,LEAD(event_timestamp_kst,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS 'next_event'
        ,ROW_NUMBER() OVER () AS 'id'
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
event_diff AS (
  SELECT user_pseudo_id
        ,event_timestamp_kst
        ,id
        ,(JULIANDAY(event_timestamp_kst) - JULIANDAY(last_event)) * 86400 AS 'last_diff'
        ,(JULIANDAY(next_event) - JULIANDAY(event_timestamp_kst)) * 86400 AS 'next_diff'
  FROM event_data
),
session_data AS (
  SELECT * 
        ,CASE 
           WHEN last_diff IS NULL THEN id 
           WHEN last_diff >= 3600 THEN id 
           ELSE LAG(id, 1) OVER (PARTITION BY user_pseudo_id ORDER BY id) 
         END AS 'session'
  FROM event_diff
  WHERE last_diff IS NULL 
     OR next_diff IS NULL 
     OR last_diff >= 3600 
     OR next_diff >= 3600
)

SELECT user_pseudo_id
      ,session
      ,MIN(event_timestamp_kst) AS 'session_start'
      ,MAX(event_timestamp_kst) AS 'session_end'
FROM session_data
GROUP BY user_pseudo_id, session
ORDER BY session_start;

# MySQL
WITH event_data AS (
  SELECT user_pseudo_id
        ,event_name
        ,event_timestamp_kst
        ,LAG(event_timestamp_kst,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS 'last_event'
        ,LEAD(event_timestamp_kst,1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS 'next_event'
        ,ROW_NUMBER() OVER () AS 'id'
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
event_diff AS (
  SELECT user_pseudo_id
        ,event_timestamp_kst
        ,id
        ,TIMESTAMPDIFF(SECOND, last_event, event_timestamp_kst) AS 'last_diff'
        ,TIMESTAMPDIFF(SECOND, event_timestamp_kst, next_event) AS 'next_diff'
  FROM event_data
),
session_data AS (
  SELECT * 
        ,CASE 
           WHEN last_diff IS NULL THEN id 
           WHEN last_diff >= 3600 THEN id 
           ELSE LAG(id, 1) OVER (PARTITION BY user_pseudo_id ORDER BY id) 
         END AS 'session'
  FROM event_diff
  WHERE last_diff IS NULL 
     OR next_diff IS NULL 
     OR last_diff >= 3600 
     OR next_diff >= 3600
)

SELECT user_pseudo_id
      ,session
      ,MIN(event_timestamp_kst) AS 'session_start'
      ,MAX(event_timestamp_kst) AS 'session_end'
FROM session_data
GROUP BY user_pseudo_id, session
ORDER BY session_start;