🚫[실무]solvesql SQLite 연습문제 난이도5

■ solvesql   

□ 코딩테스트 : SQLite / 연습문제 / 난이도5

https://solvesql.com/problems/?page=1

https://solvesql.com/problems/?page=1

 

 

□ 연습문제

1. 폐쇄할 따릉이 정류소 찾기 1

COS() 함수 : 파라미터 n(라디안으로 표현되는 각도)의 코사인 값을 반환하는 함수
sin() 함수 : 인수 n의 사인(sine)값을 반환하는 함수
acos() 함수 : n의 역사인(arc cosine) 값을 반환하는 함수(인수 n은 -1 ~ 1범위,  반환 값은 0 ~ pi(라디안)의 범위 값 반환)
radians() 함수 : 도 단위 각도를 라디안 단위로 등가 변환하는 함수

하버사인 공식(haversine fornula) : 지구 곡률의 영향으로 발생하는 오차를 예방하는 위경도 간 좌표 사이 거리를 측정하는 공식

SELECT 
  station_id, 
  name
FROM
  (SELECT 
    s.station_id, 
    s.name, 
    COUNT(s_near.station_id) AS cnt
  FROM 
    station AS s 
    LEFT JOIN station AS s_near 
    ON s.station_id != s_near.station_id 
       AND s_near.updated_at > s.updated_at
       AND (6371 * acos(cos(radians(s.lat))
                 * cos(radians(s_near.lat)) 
                 * cos(radians(s_near.lng) - radians(s.lng))
                 + sin(radians(s.lat)) * sin( radians(s_near.lat)))) <= 0.3 
  GROUP BY 
    s.station_id
  HAVING 
    cnt >= 5) AS t

 

 

2. 카테고리 별 매출 비율

SUM() 함수 : 특정 컬럼의 전체 합계를 계산하는 함수
OVER() 함수 : 특정 기준에 맞춰 컬럼 결과를 조회하고 싶을 때 사용하는 함수
- 집합 기준을 설정하는 방법 : 

  → ORDER BY : 카테고리에 따른 누적합을 구할 때 자기 자신과 상위에 위치한 행들을 집합에 포함시킴

  → PARTITION BY : 컬럼을 기준으로 행 집합을 나눌지를 직접 정의함

-  예시, ROUND(SUM(Sub_sales) OVER(PARTITION BY category), 2) AS sales_category

WITH Sub_table AS(
  SELECT category, sub_category, SUM(sales) AS Sub_sales
  FROM records 
  GROUP BY category, sub_category)

SELECT
  category, 
  sub_category,
  -- 서브 카테고리별/카테고리별 매출액 합계
  ROUND(SUM(Sub_sales) OVER(PARTITION BY sub_category), 2) AS sales_sub_category, 
  ROUND(SUM(Sub_sales) OVER(PARTITION BY category), 2) AS sales_category,
  -- 전체 매출액 합계
  ROUND(SUM(Sub_sales) OVER(), 2) AS sales_total,
  -- 카테고리 매출/전체 매출 중, 서브 카테고리 매출의 비율(%)
  ROUND(SUM(Sub_sales) OVER(PARTITION BY sub_category) / SUM(Sub_sales) OVER(PARTITION BY category) *100, 2) AS pct_in_category,
  ROUND(SUM(Sub_sales) OVER(PARTITION BY sub_category) / SUM(Sub_sales) OVER() *100, 2) AS pct_in_total

FROM
  Sub_table

 

 

3. 세션 재정의하기

TIMESTAMPDIFF() 함수 : 두 날짜간 차이를 가져올 때 사용하는 함수
- SECOND(초),  MINUTE(분), HOUR(시), DAY(일), WEEK(주), MONTH(월), QUARTER(분기), YEAR(연)

- 예시, SELECT TIMESTAMPDIFF(SECOND, '2017-03-01', '2018-03-28')
LAG() 함수 : 이전 행의 값을 리턴하는 함수
LEAD() 함수 : 다음 행의 값을 리턴하는 함수
ROW_NUMBER() 함수 : PARTITION 내  ORDER BY절에 의해 정렬되는 순서를 기준으로 고유 값을 반환하는 함수

SELECT 
  user_pseudo_id, 
  MIN(event_timestamp_kst) AS session_start,
  MAX(event_timestamp_kst) AS session_end
FROM 
  (SELECT 
    step2.*, 
    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 
    (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 
      (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'
      ) AS step1
    ) AS step2
  WHERE 
    last_diff IS NULL OR next_diff IS NULL OR last_diff >= 3600 OR next_diff >= 3600
  ) AS step3

GROUP BY 
  user_pseudo_id, session
ORDER BY 
  session_start