SQL 코딩테스트 01: WHERE절, HAVING절, 윈도우 함수, CTE, LEFT JOIN

WHERE 절

• 목적: WHERE 절은 행 수준에서 필터링을 수행합니다.
• 사용 시기: 그룹화(GROUP BY)가 발생하기 전에 데이터를 필터링할 때 사용됩니다.
• 적용 대상: 집계 함수(SUM, AVG, COUNT 등)가 아닌 개별 행의 조건을 지정할 때 사용됩니다.
• 예시:

SELECT * 
FROM employees 
WHERE salary > 50000;

HAVING 절

• 목적: HAVING 절은 그룹 수준에서 필터링을 수행합니다.
• 사용 시기: 그룹화(GROUP BY)가 발생한 후, 그룹화된 데이터에 대한 조건을 지정할 때 사용됩니다.
• 적용 대상: 집계 함수가 포함된 조건을 지정할 때 사용됩니다.
• 예시:

SELECT department, SUM(salary) as total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 200000;

차이점 요약

1. 적용 시기:
   • WHERE: 그룹화되기 전에 각 행에 대해 조건을 적용합니다.
   • HAVING: 그룹화된 후 각 그룹에 대해 조건을 적용합니다.
2. 적용 대상:
   • WHERE: 개별 행의 조건을 지정할 때 사용됩니다.
   • HAVING: 집계 함수 결과를 포함한 조건을 지정할 때 사용됩니다.

예시를 통한 차이점 설명

WHERE 절 예시

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;

이 쿼리는 employees 테이블에서 각 직원의 salary가 50000보다 큰 행들을 필터링합니다.

HAVING 절 예시

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

이 쿼리는 employees 테이블에서 각 부서(department_id)별로 직원들의 평균 salary를 계산하고, 평균 salary가 60000보다 큰 부서들만 필터링합니다.

요약하면, WHERE 절은 그룹화되기 전의 행을 필터링하는 데 사용되고, HAVING 절은 그룹화된 후의 그룹을 필터링하는 데 사용됩니다.

 

https://solvesql.com/problems/daily-revenue/

https://solvesql.com/problems/daily-revenue/

select day, SUM(total_bill) as revenue_daily
from tips
group by day
having sum(total_bill) >= 1000
order by revenue_daily desc;

WHERE 절
GROUP BY 발생 전 필터링
SUM, AVG, COUNT 아닌 개별 행 조건 지정시 사용

HAVING 절
GROUP BY 발생 후 그룹화 된 데이터에 조건 지정
집계 함수 포함된 조건 지정시 사용

 

---

WHERE 절에서 윈도우 함수(예: LEAD)를 사용할 수 없기 때문입니다. 윈도우 함수는 SELECT 절, ORDER BY 절 또는 HAVING 절에서만 사용할 수 있으며, WHERE 절에서는 사용할 수 없습니다.

대신에, 윈도우 함수를 사용한 결과를 WHERE 절에서 필터링하려면 서브쿼리 또는 CTE(Common Table Expressions)를 사용해야 합니다

 

윈도우 함수 (Window Functions)

윈도우 함수는 행 집합 내에서 쿼리 결과를 계산하는 함수입니다. 일반적으로 집계 함수와 비슷하지만, 집계 함수와 달리 결과를 여러 행에 걸쳐 계산할 수 있습니다. 윈도우 함수는 OVER 절을 사용하여 윈도우(범위)를 정의합니다.

주요 윈도우 함수 예시

• ROW_NUMBER(): 각 행에 고유한 번호를 부여합니다.
• RANK(): 값에 따라 순위를 부여합니다. 동점일 경우 순위가 건너뜁니다.
• DENSE_RANK(): 값에 따라 순위를 부여합니다. 동점일 경우 순위가 건너뛰지 않습니다.
• SUM(), AVG(), MAX(), MIN() 등 집계 함수도 윈도우 함수로 사용할 수 있습니다.

예시: ROW_NUMBER() 윈도우 함수

SELECT 
	employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id 
ORDER BY salary DESC) AS rank 
FROM employees;​

 

위 쿼리는 각 부서 내에서 급여 순위에 따라 직원을 순위 매깁니다. PARTITION BY는 부서별로 그룹을 나누고, ORDER BY는 급여를 기준으로 정렬합니다.

공통 테이블 표현식 (CTE)

CTE는 쿼리의 가독성을 높이고 재사용 가능한 임시 결과 집합을 정의하기 위해 사용됩니다. CTE는 WITH 절을 사용하여 정의하며, 이후 메인 쿼리에서 이를 참조할 수 있습니다.

CTE 문법 

WITH cte_name AS ( 
	-- CTE의 정의 
	SELECT column1, column2 FROM table_name WHERE condition 
) 

-- 메인 쿼리 
SELECT * FROM cte_name WHERE another_condition;

예시: CTE 사용

WITH DepartmentSalaries AS ( 
	SELECT department_id, SUM(salary) AS total_salary 
    FROM employees 
    GROUP BY department_id 
) 

SELECT department_id, total_salary 
FROM DepartmentSalaries 
WHERE total_salary > 100000;

위 예시에서, DepartmentSalaries라는 CTE는 각 부서의 총 급여를 계산합니다. 메인 쿼리는 이 CTE를 사용하여 총 급여가 100,000을 초과하는 부서를 선택합니다.

윈도우 함수와 CTE를 함께 사용

윈도우 함수와 CTE를 함께 사용하여 복잡한 쿼리를 보다 간결하고 효율적으로 작성할 수 있습니다.

예시: 윈도우 함수와 CTE 함께 사용

WITH RankedEmployees AS ( 
	SELECT 
    	employee_id, department_id, salary, 
        ROW_NUMBER() OVER (
        	PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS rank 
    FROM employees 
) 
SELECT * FROM RankedEmployees WHERE rank = 1;

위 예시는 각 부서에서 가장 높은 급여를 받는 직원을 선택합니다. RankedEmployees CTE는 각 부서별로 직원의 급여 순위를 매기고, 메인 쿼리는 순위가 1인 직원을 선택합니다.

요약

• 윈도우 함수는 행 집합 내에서 계산을 수행하며, OVER 절을 사용하여 범위를 정의합니다.
• CTE는 WITH 절을 사용하여 임시 결과 집합을 정의하고 재사용할 수 있도록 합니다.
• 윈도우 함수와 CTE를 함께 사용하면 복잡한 쿼리를 간결하고 효율적으로 작성할 수 있습니다

---

LEFT JOIN 설명

LEFT JOIN은 SQL에서 두 테이블을 조인할 때 사용되는 조인의 한 종류입니다. LEFT JOIN을 사용하면, 왼쪽 테이블(이 경우 measurements AS m1)의 모든 행과, 오른쪽 테이블(이 경우 measurements AS m2)의 일치하는 행들을 조인합니다. 만약 오른쪽 테이블에 일치하는 행이 없으면, NULL 값이 반환됩니다.

m2.measured_at = DATE_ADD(m1.measured_at, INTERVAL 1 DAY) 설명

이 조건은 조인 조건으로, m1 테이블과 m2 테이블을 조인할 때 사용하는 기준을 지정합니다. 구체적으로, m1 테이블의 measured_at 값에 하루를 더한 날짜와 m2 테이블의 measured_at 값이 같은지 여부를 확인합니다. 여기서 DATE_ADD(m1.measured_at, INTERVAL 1 DAY)는 m1 테이블의 measured_at 값에 하루를 더하는 함수입니다.

예시를 통해 설명

measurements 테이블 (예시 데이터)

measured_atpm10

2024-06-01	50
2024-06-02	55
2024-06-03	45
2024-06-04	60

쿼리의 실행 단계

1. LEFT JOIN 수행:
   • m1 테이블의 각 행에 대해 m1.measured_at에 하루를 더한 날짜와 m2.measured_at이 같은 행을 m2 테이블에서 찾습니다.
   • 예를 들어, 2024-06-01의 다음 날은 2024-06-02이므로, 이 조건을 만족하는 행을 찾습니다.
2. WHERE 조건 적용:
   • 조인된 결과에서 m2.pm10이 m1.pm10보다 큰 행만 선택합니다.

쿼리의 작동 방식

SELECT m1.measured_at AS 'today',
       m2.measured_at AS 'next_day',
       m1.pm10 AS 'pm10',
       m2.pm10 AS 'next_pm10'
FROM measurements AS m1 
     LEFT JOIN measurements AS m2 ON m2.measured_at = DATE_ADD(m1.measured_at, INTERVAL 1 DAY)
WHERE m2.pm10 > m1.pm10​

위 쿼리는 measurements 테이블을 두 번 참조합니다(m1과 m2). m1의 각 행에 대해, m2의 measured_at이 m1.measured_at의 하루 후 날짜인 행을 찾아 조인합니다. 그 후, m2.pm10이 m1.pm10보다 큰 행만을 선택하여 결과를 반환합니다.

결과 예시 (예시 데이터 기반)

todaynext_daypm10next_pm10

2024-06-01	2024-06-02	50	55
2024-06-03	2024-06-04	45	60

• 2024-06-01의 pm10 값은 50이고, 그 다음 날인 2024-06-02의 pm10 값은 55로 더 큽니다.
• 2024-06-03의 pm10 값은 45이고, 그 다음 날인 2024-06-04의 pm10 값은 60으로 더 큽니다.

이렇게 LEFT JOIN과 DATE_ADD를 사용하여 특정 조건에 맞는 데이터를 조인하고 필터링하는 방법을 이해할 수 있습니다.

 

 

https://solvesql.com/problems/bad-finedust-measure/

https://solvesql.com/problems/bad-finedust-measure/

SELECT *
FROM (
  select measured_at AS today, LEAD(measured_at,1) OVER (ORDER BY measured_at) AS next_day,
          pm10, LEAD(pm10,1) OVER (ORDER BY measured_at) AS next_pm10
  from measurements
) subquery
where pm10 < next_pm10;