[강의 수강 14일차] 데이터 결과 검증과 가독성 있는 쿼리 작성

이번에 분석을 하며 쿼리를 참 많이 활용했는 데, 

어떤 것을 보기 위해 어떤 함수와 단계 로직을 활용했는 지를 파악하는 게 중요하다고 느꼈다. 

 

이에 대한 부분이 남들도 봤을 때 이해할 수 있어야 하고

특히 정합성 검증 / 데이터에 대한 이해도를 높이기 위해서 꼭 필요한 절차라는 생각이 들었다.

 

☑️ 실무에서 SQL로 분석하면서 느낀 건 WHERE 조건 & JOIN 조건 설정의 중요성과

쿼리 로직 및 단계에 따라 이러한 조건들을 잘 활용해야 원하는 데이터 추출이 된다는 점을 몸소 깨달았다.

 

쿼리를 실수하는 CASE는

문법을 잘 못 알고 있는 경우, 쿼리가 복잡한 경우, 데이터를 파악하지 않고 쿼리를 작성하는 경우가 있다. 

 

✏️ 가독성 있는 쿼리가 중요한 이유

✅ 다른 사람의 쿼리를 봐야하는 경우 혹은 내 쿼리를 다른 사람이 보는 경우, 내가 재활용할 때  

- 쿼리를 가독성 있게 잘 작성했다면 별도의 설명 없이도 이해할 가능성이 존재한다.

- 매번 쿼리에 대해서 설명을 해야 한다면 쿼리를 재활용하고자 하는 사람들에게 시간 투자를 해야 하는 상황

- 만약 쿼리가 달라져 변경해야 할 경우, 특정 부분만 바꿨는 지 or 전체를 바꿨는 지 AS-IS TO-BE를 잘 작성해두는 것이 좋다.

➡️ 이번에 하면서도 느꼈지만 이후 실수를 방지하기 위해서 & 업무의 효율성을 높이기 위해서 해당 작업은 미래를 위한 필수 작업이라고 볼 수 있다. 

 

✏️ 내가 경험한 바로는 이후에 정리하고자 한다면 헷갈리는 경우가 많아서 추출 회고 때 1번 진행,

분석 회고 시 최종 진행하는 식으로 그때그때 정리해두는 게 큰 리소스를 들이지 않고 할 수 있는 방법인 듯 하다. 

 

📌 가독성 있는 쿼리의 원칙 

참고 : 회사 내의 규칙이 있다면 따르면 되지만 없다면 아래의 제공되는 SQL 스타일 가이드 활용해도 좋다.

여기서 POINT는 일관성을 유지하여 서로 소통의 리소스를 줄이는 게 포인트이다. 

 

🔗 SQL 스타일 가이드: https://www.sqlstyle.guide/

🔗 Mozilla(Firefox)의 SQL 스타일 가이드: https://docs.telemetry.mozilla.org/concepts/sql_style.html

 

1️⃣ 예약어는 대문자로 작성하기 

SQL에서 문법적인 용도로 사용하고 있는 문자들은 대문자로 작성

예약어의 대표적인 예시: SELECT, FROM, WHERE, 각종 함수

 

2️⃣ 컬럼 이름은 snake_case로 작성

컬럼 이름은 CamelCase가 아닌 snake_case로 작성

ex. 첫구매 관련 > FirstPurchase or first_purchase 

 

3️⃣ 명시적 vs 암시적인 이름 

Alias로 별칭을 지을 때는 명시적인 이름을 적용 > 직관성을 주는 것이 중요하다. 

AS a,AS b등 컬럼의 의미를 한번 더 생각하게 하는 이름이 아닌 직관적으로 의미 해석이 가능한 명시적인 것 사용해주면 좋다. 

➕ JOIN할 때 테이블의 이름도 명시적으로 할 수 있다면 명시적으로 진행하기

➕ AS를 생략해서 별칭을 설정할 수도 있는 데, AS를 쓰는 것도 명시적인 표현

 

4️⃣ 왼쪽 정렬

컬럼명도 한줄 띄워서 들여쓰기로 맞춰서 사용하도록 한다.

SELECT, FROM, WHERE은 같은 위계이므로 왼쪽 정렬로 작성하기 

 

5️⃣ 예약어나 컬럼은 한 줄에 하나씩 권장 

특히 컬럼은 바로 주석 처리 할 수 있는 장점이 있기에 한 줄에 하나씩 작성하도록 한다. 

 

6️⃣ 쉼표는 컬럼 바로 뒤에 

이는 스타일에 따라서 일관성있게 유지만 하면 된다.

마지막 컬럼에 쉼표가 있어도 BigQuery에서는 정상 작동하기 때문!

 

📌 WITH 문을 통해 가독성 있는 쿼리 만들기

WITH문 : CTE (Common Table Expression)라고 표현

- SELECT 구문에 이름을 정해주는 것이라고 보면 된다.

- 쿼리 내에서 FROM 절에 서브쿼리로 쓰는 것이 아닌 별도 테이블로 만드는 것

- 쿼리 내에서 반복적으로 사용할 수 있는 장점이 있다. 

✏️ TIP. 가장 기초가 되는 테이블을 가장 먼저 명시 base로 명시 / 이후 용도에 따라 명시하는 것이 가독성 측면에서 좋다. 

 

PARTITION

특정 시기에 필요한 데이터를 찾고 싶을 때 PARTITION을 통해 구분할 수 있다.

 

☑️ PARTITION을 사용하면 좋은 점

1) 쿼리 성능 향상

- 전체 데이터를 스캔하는 것보다 파티션을 설정한 곳만 스캔하는 것이 더 빠르다.

2) 데이터 관리 용이성

- 특정 일자의 데이터를 모두 변경하거나 삭제해야 하면 파티션을 설정해서 삭제할 수 있다.

3) 비용

- 파티션에 해당되는 데이터만 스캔해서 비용을 줄일 수 있다. (빅쿼리는 쿼리 용량에 비례해 과금되기 때문) 

 

 

이와 같이 파티션 컬럼의 조건을 확인해서 추출해야 하는 경우도 있다.

이때 DATETIME 값을 이전 값으로 늘려가며 실행해보면 된다. 

 

✏️ 처리량의 경우, 초반에 데이터를 조회하는 양에 따라서 좌우되는 경향이 있다.

따라서 필요 컬럼을 잘 지정해서 추출해주면 처리량을 낮추는 데에 도움이 될 수 있으니 참고하기!

 

데이터 결과 검증의 정의

SQL 쿼리 후 얻은 결과가 예상과 일치하는 지 확인하는 과정

✔️ 목적 : 분석 결과의 정확성, 신뢰성 확보

 

방법 : 내가 기대하는 예상 결과 정의 > 쿼리 작성 > 두개가 일치하는 지 비교

 

여기서 제일 중요한 부분은

- 문제를 잘 정의하고, 미리 작성해보기 

- 도메인 특수성 잘 파악하기(회사 내에서 사전에 정해놓은 정의가 있다면 그 수식을 따른다) 

 

✏️ 문제와 지표를 처음부터 잘 정의하고 미리 작성해 예측하는 것이 중요하다. 

실제로 그렇게 하고 있는 데도 데이터 결과 검증과 정합성 검증은 어려운 것 같다..! 

 

데이터 결과를 검증하는 흐름

 

문제 정의하는 게 가장 첫 시작이므로 중요한 데, 어떤 상황에서 어떤 것을 확인하기 위해 추출하는 지

그 추출 조건은 무엇인 지를 확인하는 것이 우선시 되어야 한다. 

 

그러고 input <> output 사이에서 중간 결과 테이블 일부 혹은 절차 일부를 확인해가며 쿼리 작성하면 된다.

 

이때 POINT는 예상 쿼리를 손 or 엑셀로 예상해보고 결과랑 비교하는 게 필요하다.

✔️ 데이터 결과 검증할 때 자주 활용하는 SQL 쿼리 

1) COUNT(*): 행 수를 확인, 의도한 데이터의 행 개수가 맞는가?

2) NOT NULL: 특정 컬럼에 NULL이 존재하는가? 필수 필드가 비어있지 않는가? 

✔️ NOT NULL의 경우는 저장이 잘 안되어 있는 경우도 있으니 확인하도록 하자.

3) DISTINCT: 데이터의 고유값을 확인해 중복 여부 확인

4) IF문, CASE WHEN: 의도와 같다면 TRUE, 아니면 FALSE

1) + 3) => SELECT COUNT(DISTINCT col), COUNT(col) 두 컬럼을 보고 개수를 비교

 

✏️ 데이터의 특징을 파악하고 그 결과를 예측해보는 것이 방법이다. 

예를 들어 비회원일 경우 회원 id 값에 null 이 존재할 수 있는 데, 이를 제외하고 볼 것인 지 / 포함하고 볼 것인 지

그리고 null 값이면 무조건 비회원으로 볼 것인지까지 고려하는 것이 필요하다.

 

✔️ 데이터 결과 검증할 때 활용하는 방식 TIP 

1) 특정 user_id 로 필터링 걸어 확인

- 1명의 데이터 확인 (예. WHERE user_id=402) 

- 결과를 예상할 때 RAW 데이터에서 하나씩 확인하고 예상 결과 적어두기

ex. 그룹별 구매 횟수와 그 날짜 구할 때 

➡️ 샘플 데이터를 가지고 예상하는 것이 핵심 > 이 경우 그룹에 대한 정의를 넣고 3~4개 샘플 데이터로 결과 확인하기

> 결과적으로 샘플 데이터를 group 으로 묶어 확인할 때 그 샘플 데이터가 각 그룹에 맞게 나와야 하는 것이 포인트! 

- 1명의 데이터의 예상 결과와 쿼리 결과가 동일한 지 확인

- 다른 user_id 3~4건 추가로 확인 (여러 케이스가 존재할 수 있기 때문이다) 

✏️ 여기서 user_id의 특징이 있는 값들로 비교해주는 것도 방법이 될 듯 하다. 

ex. 구매 회차에 따라 구하는 경우, 1회차 / 2회차 등에 해당하는 user_id로 비교해보기 

- 3~4개에서 동일한 결과가 나오면 user_id 조건 삭제하고 이후 단계 진행

 

2) WITH 문으로 샘플 데이터 생성해 확인 ➡️ 문법이 익숙하지 않을 때 활용하면 좋다) 

- WITH 문 사용해 예시 데이터 생성한 후 결과 예쌍하고 쿼리 작성

- 복잡한 데이터에서 바로 쿼리 작성하는 것보다 단계단계 확인하고 쿼리 자체 올바른 지 확인할 때 주로 사용한다.

✔️ 특히 UNION ALL(row 단위, 테이블 아래로 데이터 붙이는 함수)을 활용해 샘플 데이터를 만들고 활용하는 방법도 있다. 

➡️ WITH 문의 경우 중간 단계에서 활용해 데이터 확인하면 좋다. 

 

📌 데이터 결과 검증을 할 때 : 추출하는 단계에 대한 로직과 단계에 대해서 적고 생각해보기 

실제 정의 > 어떤 로직으로 추출할 것인 지 > 데이터 조건은 뭐가 있지? 등을 정리하는 작업이 필요하다.

 

결과 예상된 3단계와 5단계를 비교하는 작업이 필요하다. 

 

✔️ 여기서 쿼리 작성의 point 

트레이너가 승리한 비율이라는 지표를 구하려면 어떤 데이터가 필요한 지 생각하는 단계가 필요하다.

✏️ 지표를 구할 때는 이 지표를 정의 > 데이터 구성 > 데이터의 특징을 파악해야 한다.

 

✏️ 또한, 데이터 추출할 때 테이블이 독특한 경우가 있는 데, 

어떻게 데이터를 조합해서 내가 원하는 지표를 볼 수 있을 지 / 그룹 지을 수 있을 지 고민해보는 단계도 필요하다.

 

✏️이번 강의와 학습을 통해 느낀점과 배운점 
✅ 데이터 검증하는 과정에서 특정 CASE 기준 데이터 3~4개 기반으로 데이터 검증을 해보면 좋다. 
✅ 이 단계에서 시나리오와 로직 단계 절차를 확인하고 정리 및 기록으로 남겨두도록 하자.
➡️ 현업에서 실무하면서 느꼈던 건 중간 로직이 꼬여버리면 복잡해지는 데 이 단계를 거치면 정합성 측면에서도 효율적일 것 같다.