문제
제출
WITH max_id_value AS (
SELECT MAX(id) AS max_id FROM ecoli_data
)
SELECT
id,CASE
WHEN a.num <= max_id * 0.25 THEN 'CRITICAL'
WHEN a.num <= max_id * 0.5 THEN 'HIGH'
WHEN a.num <= max_id * 0.75 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY desc) num
FROM ecoli_data
) a
CROSS JOIN max_id_value
order by id asc📌 새로 배운 내용
✅ ROW_NUMBER() OVER (ORDER BY 컬럼)
컬럼 기준 오름차순/내림차순으로 번호를 매긴다.
처음에 크기 내림차순 정렬에 맞춰 순번을 부여하고, 순번에 따른 비율 별로 분류 이름을 부여하려고 했다. 그에 따라 기준에 맞춰 순번 부여을 부여하는 함수인 ROW_NUMBER() OVER 를 사용했다.
+) row_number() over (partition by 컬럼)
컬럼 기준으로 그룹핑 한 뒤 그룹 별로 번호를 매긴다.
+) row_number() over (partition by 컬럼 order by 컬럼)
컬럼 기준으로 그룹핑 한 뒤 특정 컬럼 기준으로 번호를 매긴다.
🚨 error
WITH max_id_value AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY desc) num, max(id) max FROM ecoli_data
)max(id) 때문에 계속 sql_mode=only_full_group_by 에러가 났다.
집계 함수는 기본적으로 GROUP BY 없이 다른 컬럼과 함께 사용할 수 없기 때문에 select문에 다른 컬럼과 같이 넣어 조회할 수 없다.
집계함수 하나만 조회할 때는 가능하지만, 다른 컬럼들과 조회할 때는 group by 없이 집계함수를 쓰면 에러가 난다.
집계함수를 다른 컬럼들과 같이 조회하려고 할 때
1. group by 사용
2. 윈도우 함수 사용
윈도우 함수 vs 집계 함수
• 윈도우 함수는 OVER()를 사용해 그룹을 나누면서도 개별 행을 유지하는 함수.
• 일반 집계 함수는 GROUP BY로 그룹을 묶어 개별 행이 사라짐.
윈도우 함수
SQL에서 집계 함수와 비슷하지만, 데이터를 그룹화하지 않고 전체 결과 집합에 대해 계산을 수행하는 함수
• 그룹화 없이 계산
• 결과 집합에 영향을 주지 않음
• 범위 정의
<윈도우 함수> OVER (PARTITION BY <열> ORDER BY <열>)• PARTITION BY <열> : 데이터를 그룹화(파티션)하여 각 그룹에 대해 계산을 수행합니다. 이 부분은 선택적입니다.
• ORDER BY <열> : 데이터의 순서를 지정합니다. 순서는 윈도우 함수가 어떻게 계산될지를 결정합니다.
✅ NTILE()
결과 집합을 지정된 개수의 그룹(버킷)으로 나눈다.
NTILE(number_of_buckets) OVER (ORDER BY column_name)• number_of_buckets: 데이터를 몇 개의 그룹(버킷)으로 나눌지 지정하는 값
• column_name: 데이터를 정렬할 기준이 되는 열
NTILE() 사용한 쿼리
WITH max_id_value AS (
SELECT id, ntile(4) OVER (ORDER BY SIZE_OF_COLONY desc) num
FROM ecoli_data
)
SELECT
id,CASE
WHEN num = 1 THEN 'CRITICAL'
WHEN num = 2 THEN 'HIGH'
WHEN num = 3 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM max_id_value
order by id asc✅ PERCENT_RANK()
주어진 값이 결과 집합 내에서 차지하는 백분위(rank)를 계산
결과 집합 내에서 각 행의 상대적인 순위를 계산하여 0과 1 사이의 값으로 반환
PERCENT_RANK() OVER (ORDER BY column_name)• PERCENT_RANK()는 내림차순으로 정렬한 후, 각 백분위를 계산
• 집합 내에서 차지하는 상대적인 순위를 나타냄
percent_rank() 이용한 쿼리
WITH max_id_value AS (
SELECT id, percent_rank() OVER (ORDER BY SIZE_OF_COLONY desc) num
FROM ecoli_data
)
SELECT
id,CASE
WHEN num <= 0.25 THEN 'CRITICAL'
WHEN num <= 0.5 THEN 'HIGH'
WHEN num <= 0.75 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM max_id_value
order by id asc✅ COUNT(*) OVER()
결과 집합의 전체 행 수를 반환
GROUP BY 없이 전체 데이터의 개수를 계산하며, 각 행에 대해 동일한 전체 행 수 값을 반환
COUNT(*) OVER() 사용한 쿼리
count(*) 사용시 group by 해야한다고 에러가 뜨지만, 윈도우 함수 COUNT(*) OVER()를 사용하면 에러가 뜨지 않는다.
WITH max_id_value AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY desc) num, count(*) over() total FROM ecoli_data
)
SELECT
id,CASE
WHEN num <= total * 0.25 THEN 'CRITICAL'
WHEN num <= total * 0.5 THEN 'HIGH'
WHEN num <= total * 0.75 THEN 'MEDIUM'
ELSE 'LOW'
END AS COLONY_NAME
FROM max_id_value
order by id asc