SQL 수준별학습

---

SQL

• Window Function
  • 행과 행의 관계를 정의
  • 순위, 합계, 평균, 행 위치 조작
  • GROUP BY 구문과 병행하여 사용할 수 없음
  • 결과 건수가 줄어들지 않음 (집계 제외)
  • 파티션을 분할
  • 서브쿼리에는 사용 가능
  • 종류
    • 순위 : RANK, DENSE_RANK, ROW_NUMBER
    • 집계 : SUM, MAX, MIN, AVG, COUNT
    • 순서 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
    • 비율 : RATIO_TO_REPORT, PERCENT_RANK, CUME_DIST, NTILE

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

 

• 순위
  • RANK : 특정 컬럼의 순위를 구하는 함수, 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위를 비운 값 출력
  • DENSE_RANK : 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않음
  • ROW_NUMBER : 동일한 값이어도 고유한 순위를 부여
• 순서 
  • FIRST_VALUE : 가장 먼저 나온 값을 구하여 출력, 공동 등수를 인정하지 않고, 처음 나온 행만 가져오며 MIN함수를 쓰는 것과 결과가 동일
  • LAST_VALUE : 가장 마지막에 나온 값을 구하여 출력, 공동 등수를 인정하지 않고, 나중에 나온 행만 가져오며 MAX함수를 쓰는 것과 결과가 동일
  • LAG : 이전 N번째의 행을 가져오는 함수
  • LEAD : 이후 N행의 값을 가져오는 함수
• 비율
  • RATIO_TO_REPORT : 파티션 내 전체 SUM값에 대한 행별 백분율을 소수점으로 출력
  • PERCENT_RANK : 파티션별로 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값을 1로 정하여 행 순서별 백분율을 출력, 구간을 나누어 백분율로 출력
  • CUME_DIST : 파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 출력
  • NTILE : 파티션별 전체 건수를 계산한 값을 N등분한 결과를 출력
• STRING 함수
  • CONCAT : 문자열을 병합할 때 사용
  • SUBSTRING : 문자열을 자를 때 사용
  • SUBSTRING_INDEX : 문자열을 특정 구분기호를 통해 출력할 떄 사용
  • REVERSE : 문자열을 뒤집을 때 사용, 특정 문자를 찾을 때도 사용
  • LEFT, RIGHT :  문자열을 기준으로 N개 출력
• MATH함수
  • ABS : 절댓값을 출력 
  • ROUND : 숫자를 소수점 이하 자릿수에서 올림하여 출력
  • CEILING : 소수점을 올림하여 출력
  • FLOOR : 소수점을 내림하여 출력
  • TRUNCATE : 숫자를 소수점 이하 자릿수에서 버림하여 출력
  • RAND : 지정 숫자 범위 중 하나를 랜덤하게 출력
• 날짜함수
  • NOW / SYSDATE / CURRENT_TIMESTAMP : 현재시간과 날짜를 출력 
  • DATE_ADD : 날짜에서 기준값만큼 덧셈하여 출력
  • DATE_SUB : 날짜에서 기준값만큼 뺄셈하여 출력
  • DATEDIFF :  두 날짜를 뺄셈하여 출력
  • DATE_FORMAT : 날짜를 형식에 맞게 출력
  • UNIX_TIMESTAMP : 현재 시간을 UNIXTIME으로 구함
  • CURDATE / CURRENT_DATE : 현재 날짜 출력
  • CURTIME / CURRENT_TIME : 현재 시간 출력
  • YEAR : 날짜의 연도 출력
  • MONTH : 날짜의 월 출력
  • DAY : 날짜의 일을 출력

# RANK
RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

# DENSE_RANK
DENSE_RANK() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

# ROW_NUMBER
ROW_NUMBER() OVER(PARTITION BY 컬럼1 ORDER BY 컬럼2)

# FIRST_VALUE
FIRST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

# LAST_VALUE
LAST_VALUE(컬럼1) OVER(PARTITION BY 컬럼2 ORDER BY 컬럼3)

# LAG
LAG(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3) 
LAG(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

# LEAD
LEAD(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)
LEAD(컬럼1, 숫자) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

# RATIO_TO_REPORT
RATIO_TO_REPORT(컬럼1) OVER (PARTITION BY 컬럼2 ORDER BY 컬럼3)

# PERCENT_RANK
PERCENT_RANK() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

# CUME_DIST
CUME_DIST() OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

# NTILE
NTILE(숫자) OVER (PARTITION BY 컬럼1 ORDER BY 컬럼2)

 

---

SQL 과제 

row_number 윈도우 함수를 사용하여, 2023-01-01 이후 서버별로 경험치가 가장 많은 1위 유저의 serverno, game_account_id, exp, 순위(rown컬럼)를 출력

• 2023-01-01 이후 :where >= 2023-01-01
• row_number 윈도우 함수 : row_number() over(partition by order by) as rown
• 서버별 :  row_number() over(partition by serverno order by) as rown 
• 경험치가 가장 많은 1위 : where >= 1

SELECT 
  serverno,
  game_account_id,
  exp,
  rown
FROM (
  SELECT 
    serverno,
    game_account_id,
    exp,
    ROW_NUMBER() OVER(PARTITION BY serverno ORDER BY exp DESC) AS rown
  FROM basic.users
  WHERE date >= '2023-01-01'
) AS ranked_users
WHERE rown = 1;

 

1️⃣우리는 IP 주소를 통해 이상접속 유저를 감지하려고 합니다. ip 주소는 x1.x2.x3.x4 형태처럼, ’.’ 을 기준으로
4개의 숫자조합으로 구성되어 있습니다. ip주소를 x1.x2 기준으로 자르고 컬럼이름을 ip2로 지정해주세요.
그리고 그 값을 count 해 주세요. count 한 값은 컬럼이름을 ipcnt 로 지정해주세요.
2️⃣ 이 ip 주소를 기반으로, count 수가 4개 이상인 경우를 추출해주세요.
3️⃣ with 문을 사용하여, 해당 유저의 기존ip주소, ipcnt, ip2, game_account_id 를 추출

• ip주소를 x1.x2 기준으로 자르 : substring_index(,'.',2)
•  그 값을 count : count
• 이 ip 주소를 기반 : join
• count 수가 4개 이상 : where >= 4
•  with 문 : with ip_counts

WITH ip_counts AS (
  SELECT 
    SUBSTRING_INDEX(ip_addr, '.', 2) AS ip2,
    COUNT(*) AS ipcnt
  FROM basic.users
  GROUP BY ip2
)
SELECT 
  basic.users.ip_addr AS existing_ip,
  ipcnt,
  ip2,
  basic.users.game_account_id
FROM basic.users
JOIN ip_counts ON SUBSTRING_INDEX(basic.users.ip_addr, '.', 2) = ip_counts.ip2
WHERE ipcnt >=4;

 

1️⃣ basic.users 테이블에서, game_account_id 별로 level을 오름차순 정렬하여 이전 경험치를 lagexp 라는 컬럼
2️⃣ 현재 가지고 있는 경험치와, 이전 경험치를 모두 표시해주시고, 현재경험치-이전경험치를 getexp(획득한 경험치) 라는 컬럼
3️⃣ payment 테이블과 join하여(어떤 조인 방식이 좋을지 고민해주세요), 결제를 하지 않은 유저와 결제한 유저를 paygb 라는 컬럼
4️⃣ 단 이때, 획득한 경험치는 10만 이상인 경우만 필터링해주세요.
5️⃣ paygb를 기준으로 획득한 경험치가 많은 유저를 그룹별 3개씩 출력

•  game_account_id 별 : partiton by game_account_id
• level을 오름차순 정렬 : order by level
• 이전 경험치 : LAG
• 현재경험치-이전경험치 :  exp - lagexp
• payment 테이블과 join : left join
• 결제를 하지 않은 유저와 결제한 유저를 paygb 라는 컬럼 : case when
• 획득한 경험치는 10만 이상인 경우 : where >= 100000
•  paygb를 기준으로 획득한 경험치가 많은 유저 : row_number() over(partiton by paygb)
• 경험치가 많은 유저 :  row_number() over(partiton by paygb order by getexp desc)
• 그룹별 3개씩 : where rown >= 3

WITH lag_exp AS (
  SELECT
    game_account_id,
    exp,
    LAG(exp) OVER(PARTITION BY game_account_id ORDER BY level) AS lagexp
  FROM basic.users
),
pay_status AS (
  SELECT
    u.game_account_id,
    CASE WHEN p.game_account_id IS NOT NULL THEN '결제함' ELSE '결제안함' END AS paygb
  FROM basic.users u
  LEFT JOIN basic.payment p ON u.game_account_id = p.game_account_id
),
filtered_exp AS (
  SELECT DISTINCT
    l.game_account_id,
    l.exp,
    l.lagexp,
    l.exp - l.lagexp AS getexp,
    p.paygb
  FROM lag_exp l
  JOIN pay_status p ON l.game_account_id = p.game_account_id
  WHERE l.exp - l.lagexp >= 100000
),
ranked_exp AS (
  SELECT
    f.*,
    ROW_NUMBER() OVER(PARTITION BY f.paygb ORDER BY f.getexp DESC) AS rown
  FROM filtered_exp f
)
SELECT *
FROM ranked_exp
WHERE rown <= 3;