TIL - 250111

SQL challenge 1강

문법 정리

window 함수

정의 : 행과 행 간의 관계를 정의하기 위해 제공
역할 : 순위, 합계, 평균, 행 위치 등 조작 가능
특징 :
1. GROUP BY 구문과 병행하여 사용할 수 없다.
2. 결과 건수가 줄어들지 않는다. (집계 제외)
3. GROUP BY 구문은 둘 다 파티션을 분할한다는 의미에서 유사
4. 다른 함수와 달리 중첩해서 사용은 못함. 서브쿼리는 가능
종류 :
1. 순위 - RANK, DENSERANK, ROW_NUMBER
2. 집계 - SUM, MAX, MIN, AVG, COUNT
3. 순서 - FIRST_VALUE, LAST_VALUE, LAG, LEAD
4. 비율 - RATIO_TO_REPORT, CUME_DIST

# 윈도우 함수 기본문법 
SELECT WINDOW_FUNCTION () OVER([PARTITION BY 컬럼] [ORDER BY 컬럼])
FROM 테이블명

ROW_NUMBER() : 어떻게든 순위를 정해준다, 중복 순위 X
이 때 동점의 경우 ORDER BY 기준으로 순위가 매겨진다.
LAG() : 지연이라는 뜻, n번째 행을 가져와서 지연시키는 것

LAG(column_name, offset, default_value) OVER ([PARTITION BY column_name] ORDER BY column_name)

offset : 몇 행 전의 값을 가져올지 지정 (기본값: 1).
default_value : 이전 값이 없을 경우 반환할 값.
LEAD() : LAG()의 반대
CUME_DIST() : 누적백분율 출력

그 외 중요한 함수

• string 함수
  

• math 함수
  

• 날짜 함수
  

쿼리 스타일 가이드

정해지진 않았지만 따르면 좋은 것들.

예약어는 대문자. (ex) SELECT, FROM, WHERE, GROUP BY
컬럼 이름은 snake_name, NOT CamelName
WITH 절을 사용할 때 컬럼 이름을 명시적으로 기입 (가시성을 위해)
예약어는 하나씩
SEELCT
a
,b
,c
FROM aa
WHERE aa
GROUP BY aa
ORDER BY aa;

SQL challenge 2강

이동평균

지금까지 7일간의 평균은
= '6일전 부터 현재까지의 평균'과 같고, 이를 SQL WINDOW 함수로 표현하면
= ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

과제

• 문제
  링크 : https://datalemur.com/questions/rolling-average-tweets
  

• 풀이
  문제 해석을 간단하게 하면 3일 간 평균을 구하라는 내용이다.

SELECT 
  user_id
  ,tweet_date
  ,ROUND(AVG(tweet_count) OVER(
                              PARTITION BY user_id ORDER BY tweet_date
                              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) rolling_avg_3d
FROM tweets;

• 문제 2
  링크 : https://datalemur.com/questions/signup-confirmation-rate
  

• 풀이
  문제 해석 : 이메일 활성 유저 백분율로 구하기

SELECT 
  ROUND(
    SUM(
      CASE WHEN signup_action = 'Confirmed' THEN 1 ELSE 0 END
    ) * 1.0
    / count(email_id)
  ,2)confirm_rate
FROM emails e 
JOIN texts t 
USING (email_id)

• 문제 3
  링크 : https://datalemur.com/questions/international-call-percentage
  

• 풀이
  문제 해석 : 전체 전화 중 국제 전화 비중을 %로

WITH a AS (
          SELECT 
            a.caller_id
            ,a.receiver_id
            ,a.caller_country
            ,i.country_id receiver_country
          FROM (SELECT
                  c.caller_id
                  ,c.receiver_id
                  ,country_id caller_country
                FROM phone_calls c
                JOIN phone_info i
                ON c.caller_id = i.caller_id) a
          JOIN phone_info i 
          ON a.receiver_id = i.caller_id
)
SELECT 
  ROUND(
  SUM(CASE WHEN caller_country != receiver_country THEN 1 ELSE 0 END) * 1.0
  / count(caller_id) * 100
  ,1) international_calls_pct
FROM a

맞췄을 때 희열이 있었다.
좋은 하루였다.