SQL문을 작성하면서, SQL은 마치 무법지대 같다는 생각이 들었다.
여러 요소를 리스팅할 때 사용하는 콤마(,) 위치도 제각각이고,
무엇보다 SQL문의 가독성을 위해 암묵적 룰로 사용해왔다는 대/소문자 규칙도 너무나 임의적이었다.
(SQL은 대/소문자를 구분하지 않는다.)
SQLD 시험에서 쿼리 키워드고 필드명이고 싹다 대문자로 썼으니 말 다했지.
덕분에 가독성이 매우 안좋아서, 문제를 풀 때 키워드에 열심히 동그라미를 그렸던 기억이 난다.
최근 컨벤션의 중요성을 많이 느꼈기에 SQL의 스타일가이드도 궁금해졌다.
그런데 SQL은 스타일가이드 자체도 많지 않았고,
한국어로 번역된 스타일가이드는 거의 없었다. (있어야 1~2개 블로그 포스팅 정도)
그런데 개인적으로 콤마를 앞에 붙이는 형식은 별로 마음에 들지 않아서,
-- 예를 들어, 다음과 같은 쿼리문
SELECT account_id
, account_name
, account_type
FROM account
몇몇 스타일 가이드를 둘러보다가
Gitlab handbook의 스타일가이드를 번역했다. (사실 번역은 구글 번역기와 파파고가 다 했다.)
이유는 다음과 같았다.
- 키워드 및 함수는 대문자, 컬럼명은 소문자로 작성하므로 구분하기 쉽다.
- 키워드 아래에 컬럼명을 같은 레벨로 나열한다.
(가끔 콤마를 앞에 붙이는 스타일가이드는 두번째 컬럼부터 한~두 칸씩 밀려있다.) - 내가 어떤 키워드를 쓰는지에 상관없이 일정한 형식으로 작성할 수 있다.
-- 이 형식의 쿼리문은 분명 아름답지만, 내가 어떤 키워드를 쓰느냐에 따라 indent가 달라질 수 있다.
SELECT a.title, a.release_date, a.recording_date
FROM albums AS a
WHERE a.title = 'Charcoal Lane'
OR a.title = 'The New Danger';
-- 예를 들어, 서브쿼리를 쓰는 경우 괄호때문에 한 칸 더 들여써야 하고,
-- LEFT JOIN, RIGHT JOIN, ORDER BY 등이 있는 경우 훨씬 더 밀려날 수 있다.
-- 쿼리문 출처 : https://www.sqlstyle.guide/
따라서 내가 앞으로 개인적으로 공부할 때 사용할 SQL 스타일 가이드는
아래 Gitlab Handbook의 가이드를 따르기로 하고,
입사 후 회사 내 컨벤션이 있다면 그것으로 따라가려고 한다.
복사 & 붙여넣기 과정에서 코드블록의 모든 줄에 indent가 두 칸씩 생겼습니다. 의도한 것은 아닙니다!
SQL 스타일 가이드
(출처 : Gitlab handbook)
필드 이름 지정 및 참조 규칙
- 필드 이름은 모두 소문자여야 합니다.
id,name, 또는type과 같이 일반적으로 모호한 값에는 항상 식별 또는 명명 대상이 접두사로 붙어야 합니다.
-- Good
SELECT
id AS account_id,
name AS account_name,
type AS account_type,
...
-- Bad
SELECT
id,
name,
type,
...
- 다른 소스의 데이터에 결합할 때 모호성을 방지하기 위해 필드 앞에 데이터 소스(예:
sfdc_account_id)를 붙여야 합니다.
-- Good
SELECT
sfdc_account.account_id AS sfdc_account_id,
zuora_account.account_id AS zuora_account_id
FROM sfdc_account
LEFT JOIN zuora_account ON ...
-- Bad
SELECT
sfdc_account.account_id,
zuora_account.account_id AS zuora_id
FROM sfdc_account
LEFT JOIN zuora_account ON ...
- 테이블을 조인하고 둘 다의 열을 참조할 때는 별칭 대신 전체 테이블 이름을 참조하는 것이 좋습니다. 테이블 이름이 긴 경우(~20) 가능하면 CTE의 이름을 변경하고 마지막으로 설명이 포함된 별칭을 고려하십시오.
-- Good
SELECT
budget_forecast_cogs_opex.account_id,
-- 15 more columns
date_details.fiscal_year,
date_details.fiscal_quarter,
date_details.fiscal_quarter_name,
cost_category.cost_category_level_1,
cost_category.cost_category_level_2
FROM budget_forecast_cogs_opex
LEFT JOIN date_details
ON date_details.first_day_of_month = budget_forecast_cogs_opex.accounting_period
LEFT JOIN cost_category
ON budget_forecast_cogs_opex.unique_account_name = cost_category.unique_account_name
-- Ok, but not preferred. Consider renaming the CTE in lieu of aliasing
SELECT
bfcopex.account_id,
-- 15 more columns
date_details.fiscal_year,
date_details.fiscal_quarter,
date_details.fiscal_quarter_name,
cost_category.cost_category_level_1,
cost_category.cost_category_level_2
FROM budget_forecast_cogs_opex bfcopex
LEFT JOIN date_details
ON date_details.first_day_of_month = bfcopex.accounting_period
LEFT JOIN cost_category
ON bfcopex.unique_account_name = cost_category.unique_account_name
-- Bad
SELECT
a.*,
-- 15 more columns
b.fiscal_year,
b.fiscal_quarter,
b.fiscal_quarter_name,
c.cost_category_level_1,
c.cost_category_level_2
FROM budget_forecast_cogs_opex a
LEFT JOIN date_details b
ON b.first_day_of_month = a.accounting_period
LEFT JOIN cost_category c
ON b.unique_account_name = c.unique_account_name
- 모든 필드 이름은 Snake case를 사용해야 합니다.
-- Good
SELECT
dvcecreatedtstamp AS device_created_timestamp
FROM table
-- Bad
SELECT
dvcecreatedtstamp AS DeviceCreatedTimestamp
FROM table
- 부울 필드 이름은
has_,is_또는does_로 시작해야 합니다.
-- Good
SELECT
deleted AS is_deleted,
sla AS has_sla
FROM table
-- Bad
SELECT
deleted,
sla
FROM table
- 소스 데이터를 변환할 때 대소문자를 구분하는 열 또는 "$" 또는 "_"와 다른 특수 문자가 포함된 열을 식별하려면 큰따옴표를 사용하십시오. 대문자로 된 필드 이름에는 큰따옴표가 필요하지 않습니다. 이는 Snowflake 식별자가 내부적으로 처리되는 방식이기 때문입니다.
-- Good
SELECT "First_Name_&_" AS first_name,
-- Bad
SELECT "FIRST_NAME" AS first_name,
날짜
- 타임스탬프는
_at(예:deal_closed_at)으로 끝나야 하며 항상 UTC로 표시되어야 합니다. - 날짜는
_date(예:deal_closed_date)로 끝나야 합니다. - 월은 그대로 표시되어야 하며 항상 날짜 형식으로 잘려야 합니다. (예:
deal_closed_month) - 열 이름으로
date또는month과 같은 키워드를 사용하지 마십시오. date_part보다 명시적 날짜 함수를 선호하지만,extract보다date_part를 선호합니다. (예:DAYOFWEEK(created_at)>DATE_PART(dayofweek, 'created_at')>EXTRACT(dow FROM created_at))- 날짜의 일부를 선택하는 것은 날짜를 자르는 것과 다릅니다.
date_trunc('month', created_at)는 달력 월('2019-01-25'의 경우 '2019-01-01')을 생성하는 반면SELECT date_part('month', '2019-01-25'::date)는 숫자 1을 생성합니다.
- 날짜의 일부를 선택하는 것은 날짜를 자르는 것과 다릅니다.
- DATEDIFF 사용 시 종종 직관적이지 않은 결과가 나오므로 주의하십시오.
- 예를 들어
SELECT DATEDIFF('days', '2001-12-01 23:59:59.999', '2001-12-02 00:00:00.000')는 타임스탬프가 1밀리초 차이가 나는 경우에도1을 반환합니다. - 마찬가지로
SELECT DATEDIFF('days', '2001-12-01 00:00:00.001', '2001-12-01 23:59:59.999')도 타임스탬프가 거의 하루 차이가 나더라도0을 반환합니다. DATEDIFF함수와 적절한 간격을 사용하면 올바른 결과를 얻을 수 있습니다. 예를 들어,DATEDIFF('days', '2001-12-01 23:59:59.999', '2001-12-02 00:00:00.000')는 1일 간격을 제공하고,DATEDIFF('ms', '2001-12-01 23:59:59.999', '2001-12-02 00:00:00.000')는 1밀리초를 제공합니다.
- 예를 들어
서브쿼리가 아닌 CTE(Common Table Expressions) 사용
- CTE는 SQL을 더 읽기 쉽게 만들고 성능을 향상시킵니다.
- CTE를 사용하여 다른 테이블을 참조하십시오. 이것을 import 문으로 생각하십시오.
- CTE는 쿼리의 맨 위에 배치해야 합니다.
- 성능이 허용하는 경우 CTE는 하나의 논리적 작업 단위를 수행해야 합니다.
- CTE 이름은 명확하면서도 최대한 간결해야 합니다.
replace_sfdc_account_id_with_master_record_id와 같은 긴 이름은 피하고, CTE에 주석이 있는 짧은 이름을 선호합니다. 이렇게 하면 조인에서 테이블 앨리어싱을 방지하는 데 도움이 됩니다.
- 혼란스럽거나 주목할만한 논리가 있는 CTE는 파일에 주석을 달고 dbt 문서에 문서화해야 합니다.
- 여러 모델에 걸쳐 중복되는 CTE는 자체 모델로 가져와야 합니다.
- 쿼리문 위와 아래에 빈 행을 두십시오.
- CTE는 다음과 같은 형식이어야 합니다.
WITH events AS ( -- think of these select statements as your import statements.
...
), filtered_events AS ( -- CTE comments go here
...
)
SELECT * -- you should always aim to "select * from final" for your last model
FROM filtered_events
일반
- CTE 내에서 전체 SQL 문은 4칸 들여써야 합니다.
-- Good
WITH my_data AS (
SELECT *
FROM prod.my_data
WHERE filter = 'my_filter'
)
-- Bad
WITH my_data AS (
SELECT *
FROM prod.my_data
WHERE filter = 'my_filter'
)
- 쿼리 내의 들여쓰기(예: 열,
JOIN절, 여러 줄GROUP BY등)는 2칸으로 들여써야 합니다.
-- Good
SELECT
column_name1,
column_name2,
column_name3
FROM table_1
JOIN table_2
ON table_1.id = table_2.id
WHERE clouds = true
AND gem = true
GROUP BY 1,2,3
HAVING column_name1 > 0
AND column_name2 > 0
-- Bad
SELECT
column_name1,
column_name2,
column_name3
FROM table_1
JOIN table_2
ON table_1.id = table_2.id
WHERE clouds = true
AND gem = true
GROUP BY 1,2,3
HAVING column_name1 > 0
AND column_name2 > 0
- 탭은 사용하지 않고 공백만 사용합니다. 탭을 공백으로 변환하도록 편집기를 설정해야 합니다. 자세한 내용은 [온보딩 템플릿](https://gitlab.com/gitlab-data/analytics/-/blob/master/.gitlab/issue_templates/Data Onboarding.md#data-grip-configuration) 을 참조하세요.
- SQL 줄은 80자 이하여야 합니다.
- 특정 값에 대한
WHERE절의 임시 필터를 제외하고 쉼표는 오른쪽 쉼표로 줄 끝(EOL, End-of-Line)에 있어야 합니다.
-- Good
SELECT
deleted AS is_deleted, -- EOL right comma
accountId AS account_id
FROM table
WHERE is_deleted = false
AND account_id NOT IN (
'232'
, '234' -- left comma
, '425'
)
-- Bad
SELECT
deleted AS is_deleted, -- EOL right comma
accountId AS account_id
FROM table
WHERE is_deleted = false
AND account_id NOT IN ('232', '234', '425')
SELECTing 할 때 항상 각 열에 자체 행을 지정합니다. 단, 단일 행에 있을 수 있는SELECT *는 예외입니다.DISTINCT는SELECT와 동일한 행에 포함되어야 합니다.- 필드 또는 테이블 이름을 프로젝팅할 때
AS키워드를 사용해야합니다 - 별칭을 사용할 때 (
AS) 원래 열 이름을 단일 세로줄에 정렬하고AS키워드를 별도의 세로줄에 정렬합니다. - 집계/창 함수 앞에 필드를 지정해야 합니다.
- 정렬 및 그룹화는 번호로(예: GROUP BY 1, 2)하는 것이 좋습니다.
- dbt 모델에서 3개 이상의 열을 기준으로 그룹화하는 경우 dbt-utils의 group_by 매크로를 사용하세요.
- 둘 중 하나로 충분할 때,
HAVING보다WHERE를 선호합니다. - 대괄호 구문을 사용하여 JSON에 액세스하는 것을 선호합니다. (예:
data_by_row['id']::bigint as id_value) USING은 Snowflake에서 부정확한 결과를 생성하므로 조인에서 사용하지 마십시오 .UNION보다UNION ALL을 선호합니다 이는UNION이 다른 곳에서 더 잘 해결되는 업스트림 데이터 무결성 문제를 나타낼 수 있기 때문 입니다.<>보다!=을 선호합니다. 왜냐하면!=는 다른 프로그래밍 언어에서 더 일반적이고 "같지 않음"으로 읽혀 우리가 더 잘 말할 수 있기 때문입니다.- 성능을 고려하십시오.
LIKE와ILIKE,IS와=, 그리고NOT,!,<>의 차이를 이해하고, 적절하게 사용하십시오. column ILIKE '%Match%'보다LOWER(column) LIKE '%match%'를 선호합니다 . 이렇게 하면 예기치 않은 결과를 초래할 수 있는 엉뚱한 대문자의 가능성이 낮아집니다.- DRY 원칙을 숙지하십시오. dbt의 CTE, jinja 및 매크로, Sisense의 스니펫을 활용합니다. 같은 줄을 두 번 입력하면 두 군데에서 유지되어야 합니다.
- 짧은 코드를 굳이 최적화하지 마세요.
- DO NOT OPTIMIZE FOR A SMALLER NUMBER OF LINES OF CODE. NEWLINES ARE CHEAP. BRAIN TIME IS EXPENSIVE.
데이터 타입
- 별칭이 아닌 기본 데이터 유형을 사용합니다. 자세한 내용은 데이터 타입에 대한 Snowflake 요약을 검토 하십시오. 기본값은 다음과 같습니다.
DECIMAL,NUMERIC,INTEGER,BIGINT대신NUMBERDOUBLE,REAL대신FLOATSTRING,TEXT대신VARCHARDATETIME대신TIMESTAMP
타임스탬프의 경우는 예외입니다. TIME보다 TIMESTAMP를 선호합니다. TIMESTAMP의 기본값은 표준 시간대가 포함되지 않은 TIMESTAMP_NTZ입니다.
함수
- 함수 이름과 키워드는 모두 대문자여야 합니다.
NVL보다IFNULL을 선호합니다.- 단일 행
CASE문 보다IFF를 선호합니다. - bool문
(amount < 10) AS is_less_than_ten보다IFF를 선호합니다. - 가능한 경우
CASE반복문을 단순화하는 것을 고려해보십시오.
-- OK
CASE
WHEN field_id = 1 THEN 'date'
WHEN field_id = 2 THEN 'integer'
WHEN field_id = 3 THEN 'currency'
WHEN field_id = 4 THEN 'boolean'
WHEN field_id = 5 THEN 'variant'
WHEN field_id = 6 THEN 'text'
END AS field_type
-- Better
CASE field_id
WHEN 1 THEN 'date'
WHEN 2 THEN 'integer'
WHEN 3 THEN 'currency'
WHEN 4 THEN 'boolean'
WHEN 5 THEN 'variant'
WHEN 6 THEN 'text'
END AS field_type
조인
- 조인할 때 명시적이어야 합니다(예:
JOIN대신LEFT JOIN. (기본 조인은INNER)) - 조인할 때 테이블 이름을 열에 접두사로 붙이고 그렇지 않으면 생략합니다.
- FROM 테이블을 먼저 사용하고 JOIN 테이블을 두 번째로 사용하여 조인 순서를 지정합니다.
-- Good
FROM source
LEFT JOIN other_source
ON source.id = other_source.id
WHERE ...
-- Bad
FROM source
LEFT JOIN other_source
ON other_source.id = source.id
WHERE ...
예제 코드
- 모든 것을 종합해보면:
WITH my_data AS (
SELECT *
FROM prod.my_data
WHERE filter = 'my_filter'
), some_cte AS (
SELECT DISTINCT
id,
other_field_1,
other_field_2
FROM prod.my_other_data
), final AS (
SELECT
data_by_row['id']::NUMBER AS id_field,
field_1 AS detailed_field_1,
field_2 AS detailed_field_2,
detailed_field_3,
CASE
WHEN cancellation_date IS NULL AND expiration_date IS NOT NULL
THEN expiration_date
WHEN cancellation_date IS NULL
THEN start_date + 7
ELSE cancellation_date
END AS cancellation_date,
LAG(detailed_field_3) OVER (
PARTITION BY
id_field,
detailed_field_1
ORDER BY cancellation_date
) AS previous_detailed_field_3,
SUM(field_4) AS field_4_sum,
MAX(field_5) AS field_5_max
FROM my_data
LEFT JOIN some_cte
ON my_data.id = some_cte.id
WHERE field_1 = 'abc'
AND (field_2 = 'def' OR field_2 = 'ghi')
GROUP BY 1, 2, 3, 4, 5
HAVING COUNT(*) > 1
ORDER BY 4 DESC
)
SELECT *
FROM final
주석
- 한 줄 주석을 작성할 때
--구문을 사용합니다. - 여러 줄 주석을 작성할 때
/* */구문을 사용합니다. - 주석을 작성할 때 글자 수 제한을 준수하세요. 주석이 너무 길면 새 줄로 이동하거나 documentation으로 이동합니다.
- dbt 모델 주석은 모델 documentation에 있어야 합니다.
- SQL에서 수행되는 계산에는 진행 상황에 대한 간략한 설명과 메트릭(및 계산 방법)을 정의하는 핸드북에 대한 링크가 있어야 합니다.
TODO의견을 남기는 대신 개선을 위한 새로운 이슈를 만들어보세요.