▲

GN⁺: SQL 팁과 요령

 (github.com/ben-n93)
7P by neo 2달전 | favorite | 댓글 2개

목차

포맷팅/가독성

  • 필드를 구분할 때 선행 쉼표 사용
  • WHERE 절에 더미 값 사용
  • 적절한 코드 들여쓰기
  • 복잡한 쿼리를 작성할 때 CTE 고려

유용한 기능

  • :: 연산자를 사용하여 데이터 타입 변환
  • 안티 조인 활용
  • 윈도우 함수 필터링에 QUALIFY 사용
  • 열 위치로 GROUP BY 사용 가능

피해야 할 함정

  • NULL 값과 함께 NOT IN 사용 시 주의
  • 계산된 필드 이름 변경하여 모호성 방지
  • 각 열이 어느 테이블에 속하는지 명시
  • 실행 순서 이해
  • 코드에 주석 달기
  • 문서 전체 읽기

포맷팅/가독성

필드를 구분할 때 선행 쉼표 사용

  • SELECT 절에서 필드를 구분할 때 선행 쉼표를 사용하여 새로운 열을 명확히 구분할 수 있음
  • 선행 쉼표는 쉼표가 누락되었는지 쉽게 확인할 수 있는 시각적 단서 제공
SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

WHERE 절에 더미 값 사용

  • WHERE 절에 더미 값을 사용하여 조건을 동적으로 추가 및 제거할 수 있음
SELECT *
FROM employees
WHERE 1=1 -- 더미 값
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

적절한 코드 들여쓰기

  • 코드를 들여써서 가독성을 높이고 동료 및 미래의 자신을 위해 코드 이해를 쉽게 함
-- 나쁜 예:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- 좋은 예:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

복잡한 쿼리를 작성할 때 CTE 고려

  • 인라인 뷰를 중첩하는 대신 공통 테이블 표현식(CTE)을 사용하여 코드의 가독성과 조직성을 높일 수 있음
-- 인라인 뷰 사용:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- CTE 사용:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

유용한 기능

:: 연산자를 사용하여 데이터 타입 변환

  • 일부 RDBMS에서는 :: 연산자를 사용하여 값을 다른 데이터 타입으로 변환할 수 있음
SELECT CAST('5' AS INTEGER); -- CAST 함수 사용
SELECT '5'::INTEGER; -- :: 구문 사용

안티 조인 활용

  • 안티 조인은 한 테이블에만 존재하는 행을 반환할 때 매우 유용함
  • 서브쿼리를 사용할 수도 있지만, 일반적으로 안티 조인이 더 빠름
-- 안티 조인:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- 서브쿼리:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- 상관 서브쿼리:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

윈도우 함수 필터링에 QUALIFY 사용

  • QUALIFY를 사용하여 윈도우 함수의 결과를 필터링할 수 있음
  • 코드 라인을 줄이는 데 유용함
-- QUALIFY 사용:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- QUALIFY 없이:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

열 위치로 GROUP BY 사용 가능

  • 열 이름 대신 열 위치를 사용하여 GROUP BY 또는 ORDER BY 할 수 있음
  • 임시 쿼리에는 유용하지만, 프로덕션 코드에서는 항상 열 이름을 참조해야 함
SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no는 SELECT 절의 첫 번째 열
ORDER BY 2 DESC;

피해야 할 함정

NULL 값과 함께 NOT IN 사용 시 주의

  • NOT IN은 NULL 값이 있는 경우 작동하지 않음
  • 대신 NOT EXISTS를 사용해야 함
INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- NULL 값 때문에 작동하지 않음
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- 해결 방법
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

계산된 필드 이름 변경하여 모호성 방지

  • 계산된 필드를 기존 열 이름으로 변경하면 예기치 않은 동작이 발생할 수 있음
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- 윈도우 함수가 'Robot' 제품을 1위로 랭크함
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

각 열이 어느 테이블에 속하는지 명시

  • 복잡한 쿼리에서 각 열이 어느 테이블에 속하는지 명시하면 문제를 추적하기 쉬움
SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

실행 순서 이해

  • SQL을 배우는 사람에게 가장 중요한 조언은 실행 순서를 이해하는 것임
  • 실행 순서를 이해하면 쿼리 작성 방식이 완전히 바뀜

코드에 주석 달기

  • 코드 작성 시 이유를 설명하는 주석을 달아야 함
  • 동료와 미래의 자신이 감사할 것임
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- 새로운 CMS는 아카이브 비디오 형식을 처리할 수 없음
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

문서 전체 읽기

  • 문서를 전체적으로 읽으면 예상치 못한 문제를 피할 수 있음
  • 문서를 읽는 데 몇 분이 걸리지 않으며, 예상치 못한 문제를 해결하는 데 도움이 됨
-- 문서를 더 읽었다면 NULL 문제를 해결할 수 있었음
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- GREATEST_IGNORE_NULLS 함수 사용 가능
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

GN⁺의 정리

  • 이 글은 SQL을 더 효율적이고 가독성 있게 작성하는 다양한 팁과 트릭을 제공함
  • SQL을 처음 배우는 사람이나 경험이 있는 데이터 분석가 모두에게 유용한 정보가 많음
  • 특히 복잡한 쿼리를 작성할 때 CTE 사용, 안티 조인 활용, QUALIFY 사용 등은 실무에서 큰 도움이 될 것임
  • SQL의 실행 순서를 이해하고, 코드에 주석을 달며, 문서를 꼼꼼히 읽는 습관을 들이는 것이 중요함
  • 비슷한 기능을 가진 다른 도구로는 PostgreSQL, MySQL, Oracle 등이 있음
▲
hiyama 2달전  [-]

선행 쉼표가 이 게시물에는 전부 후행 쉼표로 작성되어있네요. 원문에는 선행으로 입력되어있습니다.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
답변달기
▲
neo 2달전  [-]
Hacker News 의견

  • DB 서버를 잘 이해하고 쿼리 계획을 자주 확인할 필요성

    • EXISTS가 IN보다 빠른 경우가 많음
    • NOT EXISTS와 EXCEPT가 NULL 값 처리에서 다르게 동작함
    • 테이블 조인 대신 서브쿼리 컬럼 사용 권장
    • 테이블 스캔을 피하고 인덱스를 추가해야 함
    • 표현식 필터링 시 계산된 컬럼과 인덱스 사용 가능
    • UNION ALL이 OR보다 빠를 수 있음
    • 서브쿼리 JOIN을 통해 필터링 순서 강제 가능

  • 복잡한 저장 프로시저 작업 팁

    • 영구 테이블을 임시 테이블로 복사하고 필요한 행만 필터링
    • 임시 테이블을 조작
    • 트랜잭션 내에서 영구 테이블 업데이트, 오류 시 롤백
    • 원격 테이블 작업 시 주의 필요, 임시 테이블로 복사 후 작업 권장
    • 쿼리 계획이 혼란스러울 수 있어 작은 단계로 나누어 작업
    • 쿼리 계획을 항상 확인

  • 코드 가독성 관련 의견

    • 첫 두 예시는 가독성을 희생하고 작성 용이성을 높임
    • 마지막 예시는 들여쓰기가 큰 효과를 주지 못함

  • SQL의 FROM-first와 파이핑 문법 사용 제안

    • Kusto query language 사용 경험이 큰 발전임

  • Anti Join 관련 팁

    • EXISTS 사용 권장, 조건 기반 서브쿼리에서 행 존재 여부 확인 시 유리함

  • SELECT 문에서 선행 쉼표 사용 장점

    • 개별 라인 주석 처리 가능
    • 코드 들여쓰기로 가독성 향상

  • MSSQL에서 주석 사용 시 -- 대신 /* */ 사용 권장

    • 쿼리 저장소가 줄 바꿈 없이 쿼리를 저장하기 때문

  • 윈도우 함수 사용 권장

  • WHERE 절에서 1=1 사용에 대한 논란

  • AI2sql 소개

    • 평문 영어 프롬프트로 SQL 쿼리 생성 가능
    • 복잡한 쿼리 작성 시 유용함
답변달기