모든 초보자가 알아야 할 16가지 SQL 기술

1에서 10까지의 척도에서 귀하의 데이터 웨어하우징 기술은 얼마나 좋습니까?

7/10 이상을 원하시나요? 그렇다면 이 글은 당신을 위한 것입니다.

당신의 SQL은 얼마나 좋은가요? 최대한 빨리 취업 면접을 준비하고 싶으신가요?

이 블로그 게시물에서는 가장 복잡한 데이터 웨어하우스 SQL 기술을 자세히 설명합니다. BigQuery 표준 SQL 언어를 사용하여 이 주제에 대한 몇 가지 생각을 적어 보겠습니다.

1. 증분 테이블 및 MERGE

테이블 업데이트가 중요합니다. 참으로 중요합니다. 이상적인 상황은 PRIMARY 키, 고유 정수 및 자동 증분인 트랜잭션이 있는 경우입니다. 이 경우 테이블 업데이트는 간단합니다.

최신 데이터 웨어하우스에서 비정규화된 스타 스키마 데이터 세트로 작업할 때 항상 그런 것은 아닙니다. SQL로 세션을 생성하거나 데이터의 일부만으로 데이터 세트를 점진적으로 업데이트해야 할 수도 있습니다. transaction_id 존재하지 않을 수도 있지만 대신 고유 키가 알려진 최신 transaction_id (또는 타임스탬프)에 따라 달라지는 데이터 모델을 처리해야 합니다. 예를 들어 last_online 데이터세트의 user_id 알려진 최신 연결 타임스탬프에 따라 달라집니다. 이 경우 기존 사용자를 update 하고 새 사용자를 insert 할 수 있습니다.

MERGE 및 증분 업데이트

MERGE를 사용하거나 작업을 두 가지 작업으로 분할할 수 있습니다. 하나는 기존 레코드를 새 레코드로 업데이트하고 다른 하나는 종료되지 않는 완전히 새로운 레코드를 삽입하는 것입니다(LEFT JOIN 상황).

MERGE 는 관계형 데이터베이스에서 일반적으로 사용되는 문입니다. Google BigQuery MERGE 명령은 DML(데이터 조작 언어) 문 중 하나입니다. 하나의 명령문에서 세 가지 주요 기능을 원자적으로 수행하는 데 자주 사용됩니다. 이러한 함수는 UPDATE, INSERT 및 DELETE입니다.

• 두 개 이상의 데이터가 일치하는 경우 UPDATE 또는 DELETE 절을 사용할 수 있습니다.
• INSERT 절은 두 개 이상의 데이터가 다르거나 일치하지 않는 경우 사용할 수 있습니다.
• UPDATE 또는 DELETE 절은 주어진 데이터가 소스와 일치하지 않는 경우에도 사용할 수 있습니다.

즉, Google BigQuery MERGE 명령을 사용하면 Google BigQuery 테이블에서 데이터를 업데이트, 삽입 및 삭제하여 Google BigQuery 데이터를 병합할 수 있습니다.

다음 SQL을 고려해보세요.

2. 단어 세기

UNNEST()를 수행하고 필요한 단어가 필요한 목록에 있는지 확인하면 데이터 웨어하우스 감정 분석과 같은 여러 상황에서 유용할 수 있습니다.

3. SELECT 문 외부에서 IF() 문 사용

이를 통해 일부 코드 줄을 절약하고 코드 측면에서 더욱 설득력 있게 작업할 수 있는 기회를 얻을 수 있습니다. 일반적으로 이것을 하위 쿼리에 넣고 where 절에 필터를 추가하고 싶지만 대신 다음 과 같이 할 수 있습니다.

분할된 테이블에서 이를 사용 하지 않는 방법의 또 다른 예입니다. 이러지 마세요 . 이는 일치하는 테이블 접미사가 아마도 동적으로 결정되기 때문에(테이블의 내용을 기반으로) 전체 테이블 스캔에 대한 비용이 청구되기 때문에 나쁜 예입니다.

HAVING 절과 AGGREGATE 함수에서도 사용할 수 있습니다.

4. GROUP BY ROLLUP 사용

ROLLUP 함수는 여러 수준에서 집계를 수행하는 데 사용됩니다. 이는 차원 그래프로 작업해야 할 때 유용합니다.

다음 쿼리는 where 절에 지정된 거래 유형(is_gift)별로 일일 총 크레딧 지출을 반환하고, 각 날짜의 총 지출과 사용 가능한 모든 날짜의 총 지출도 표시합니다.

5. 테이블을 JSON으로 변환

테이블을 각 레코드가 중첩 배열의 요소인 JSON 개체로 변환해야 한다고 상상해 보세요. to_json_string() 함수가 유용해지는 곳은 다음과 같습니다.

그런 다음 날짜, 마케팅 퍼널, 지수, 히스토그램 그래프 등 어디에서나 사용할 수 있습니다.

6. PARTITION BY 사용

user_id , date 및 total_cost 열이 지정되었습니다. 각 날짜에 대해 모든 행을 유지하면서 각 고객의 총 수익 가치를 어떻게 표시합니까? 다음과 같이 이를 달성할 수 있습니다.

7. 이동 평균

BI 개발자는 보고서와 환상적인 대시보드에 이동 평균을 추가하는 임무를 맡는 경우가 많습니다. 이는 7, 14, 30일/월 또는 연도 MA 선 그래프일 수 있습니다. 그럼 어떻게 해야 할까요?

8. 날짜 배열

사용자 유지 작업을 하거나 일부 데이터세트에서 누락된 값(예: 날짜)을 확인하려는 경우 매우 유용합니다. BigQuery에는 GENERATE_DATE_ARRAY 라는 함수가 있습니다.

9. 행_번호()

이는 데이터에서 최신 정보(예: 최근 업데이트된 기록 등)를 가져오거나 중복 항목을 제거하는 데 유용합니다.

10. 엔타일()

또 다른 번호 매기기 기능. 모바일 앱이 있는 경우 Login duration in seconds 모니터링하는 데 매우 유용합니다. 예를 들어 내 앱이 Firebase에 연결되어 있고 사용자가 login 하면 시간이 얼마나 걸리는지 확인할 수 있습니다.

이 함수는 행 순서에 따라 행을 constant_integer_expression 버킷으로 나누고 각 행에 할당된 1부터 시작하는 버킷 번호를 반환합니다. 버킷의 행 수는 최대 1만큼 다를 수 있습니다. 나머지 값(버킷으로 나눈 나머지 행 수)은 버킷 1부터 시작하여 각 버킷에 하나씩 배포됩니다. constant_integer_expression NULL, 0 또는 음수로 평가되는 경우 오류가 제공됩니다.

11. 순위 / 밀도_순위

넘버링 기능이라고도 합니다. 나는 DENSE_RANK 기본 순위 함수로 사용하는 경향이 있습니다. RANK 다음으로 사용 가능한 순위를 건너뛰지 않기 때문입니다. 연속된 순위 값을 반환합니다. 결과를 별개의 버킷으로 나누는 파티션과 함께 사용할 수 있습니다. 각 파티션의 행은 동일한 값을 갖는 경우 동일한 순위를 받습니다. 예:

제품 가격의 또 다른 예:

12. 피벗 / 피벗 해제

피벗은 행을 열로 변경합니다. 그게 전부입니다. Unpivot은 그 반대 입니다.

13. 첫 번째_값 / 마지막_값

이는 특정 파티션의 첫 번째/마지막 값에 대해 각 행의 델타를 얻는 데 도움이 되는 또 다른 유용한 기능입니다.

14. 테이블을 구조체 배열로 변환하고 UDF에 전달합니다.

이는 각 행이나 테이블에 복잡한 논리가 포함된 사용자 정의 함수(UDF)를 적용해야 할 때 유용합니다. 언제든지 테이블을 TYPE STRUCT 객체의 배열로 간주한 다음 각 객체를 UDF에 전달할 수 있습니다. 그것은 당신의 논리에 달려 있습니다. 예를 들어 구매 만료 시간을 계산하는 데 사용합니다.

비슷한 방법으로 UNION ALL을 사용할 필요 없이 테이블을 만들 수 있습니다. 예를 들어, 단위 테스트를 위한 일부 테스트 데이터를 모의하는 데 사용합니다. 이렇게 하면 편집기에서 Alt + Shift + Down 사용하기만 하면 매우 빠르게 작업을 수행할 수 있습니다.

15. FOLLOWING 및 UNBOUNDED FOLLOWING을 사용하여 이벤트 퍼널 만들기

좋은 예는 마케팅 퍼널일 수 있습니다. 데이터 세트에는 동일한 유형의 지속적으로 반복되는 이벤트가 포함될 수 있지만 이상적으로는 각 이벤트를 다른 유형의 다음 이벤트와 연결하는 것이 좋습니다. 이는 퍼널 데이터 세트를 구축하기 위해 이벤트, 구매 등의 목록을 가져와야 할 때 유용할 수 있습니다. PARTITION BY를 사용하면 각 파티션에 존재하는 이벤트 수에 관계없이 다음 이벤트를 모두 그룹화할 수 있습니다.

16. 정규식

구조화되지 않은 데이터(예: 환율, 사용자 정의 그룹화 등)에서 무언가를 추출해야 하는 경우 이 기능을 사용합니다.

정규 표현식을 사용하여 환율 작업

환율 데이터가 포함된 다음 예를 살펴보세요.

정규 표현식을 사용하여 앱 버전 작업

때로는 regexp 사용하여 앱의 주요 버전 , 릴리스 또는 모드 버전을 가져오고 사용자 정의 보고서를 만들고 싶을 수도 있습니다.

결론

SQL은 데이터를 조작하는 데 도움이 되는 강력한 도구입니다. 디지털 마케팅의 이러한 SQL 사용 사례가 귀하에게 도움이 되기를 바랍니다. 이는 실제로 편리한 기술이며 많은 프로젝트에 도움이 될 수 있습니다. 이 SQL 조각은 내 삶을 훨씬 쉽게 만들어 주었고 거의 매일 직장에서 사용합니다. 또한 SQL 및 최신 데이터 웨어하우스는 데이터 과학을 위한 필수 도구입니다. 강력한 방언 기능을 통해 데이터를 쉽게 모델링하고 시각화할 수 있습니다. SQL은 데이터 웨어하우스 및 비즈니스 인텔리전스 전문가가 사용하는 언어이므로 이들과 데이터를 공유하려는 경우 탁월한 선택입니다. 이는 시장에 있는 거의 모든 데이터 웨어하우스/레이크 솔루션과 통신하는 가장 일반적인 방법입니다.

원본은 datamike 가 mydataschool.com 에 게시했습니다.

Mike는 열정적이고 디지털에 초점을 맞춘 개인으로, 풍부한 추진력과 열정을 갖고 있으며 디지털 마케팅의 전체 조합이 던지는 도전을 좋아합니다. 영국에 거주하며 2015년 뉴캐슬 대학교에서 MBA를 마쳤습니다.