1에서 10까지의 척도에서 귀하의 데이터 웨어하우징 기술은 얼마나 좋습니까? 7/10 이상을 원하시나요? 그렇다면 이 글은 당신을 위한 것입니다. 당신의 SQL은 얼마나 좋은가요? 최대한 빨리 취업 면접을 준비하고 싶으신가요? 이 블로그 게시물에서는 가장 복잡한 데이터 웨어하우스 SQL 기술을 자세히 설명합니다. BigQuery 표준 SQL 언어를 사용하여 이 주제에 대한 몇 가지 생각을 적어 보겠습니다. 1. 증분 테이블 및 MERGE 테이블 업데이트가 중요합니다. 참으로 중요합니다. 이상적인 상황은 PRIMARY 키, 고유 정수 및 자동 증분인 트랜잭션이 있는 경우입니다. 이 경우 테이블 업데이트는 간단합니다. https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql 최신 데이터 웨어하우스에서 비정규화된 스타 스키마 데이터 세트로 작업할 때 항상 그런 것은 아닙니다. SQL로 생성하거나 데이터의 일부만으로 데이터 세트를 점진적으로 업데이트해야 할 수도 있습니다. 존재하지 않을 수도 있지만 대신 고유 키가 알려진 최신 (또는 타임스탬프)에 따라 달라지는 데이터 모델을 처리해야 합니다. 예를 들어 데이터세트의 알려진 최신 연결 타임스탬프에 따라 달라집니다. 이 경우 기존 사용자를 하고 새 사용자를 할 수 있습니다. 세션을 transaction_id transaction_id last_online user_id update insert MERGE 및 증분 업데이트 사용하거나 작업을 두 가지 작업으로 분할할 수 있습니다. 하나는 기존 레코드를 새 레코드로 업데이트하고 다른 하나는 종료되지 않는 완전히 새로운 레코드를 삽입하는 것입니다(LEFT JOIN 상황). MERGE를 는 관계형 데이터베이스에서 일반적으로 사용되는 문입니다. Google BigQuery MERGE 명령은 DML(데이터 조작 언어) 문 중 하나입니다. 하나의 명령문에서 세 가지 주요 기능을 원자적으로 수행하는 데 자주 사용됩니다. 이러한 함수는 UPDATE, INSERT 및 DELETE입니다. MERGE 두 개 이상의 데이터가 일치하는 경우 UPDATE 또는 DELETE 절을 사용할 수 있습니다. INSERT 절은 두 개 이상의 데이터가 다르거나 일치하지 않는 경우 사용할 수 있습니다. UPDATE 또는 DELETE 절은 주어진 데이터가 소스와 일치하지 않는 경우에도 사용할 수 있습니다. 즉, Google BigQuery MERGE 명령을 사용하면 Google BigQuery 테이블에서 데이터를 업데이트, 삽입 및 삭제하여 Google BigQuery 데이터를 병합할 수 있습니다. 다음 SQL을 고려해보세요. https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-sql 2. 단어 세기 UNNEST()를 수행하고 필요한 단어가 필요한 목록에 있는지 확인하면 데이터 웨어하우스 감정 분석과 같은 여러 상황에서 유용할 수 있습니다. https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql 3. SELECT 문 외부에서 IF() 문 사용 이를 통해 일부 코드 줄을 절약하고 코드 측면에서 더욱 설득력 있게 작업할 수 있는 기회를 얻을 수 있습니다. 일반적으로 이것을 하위 쿼리에 넣고 절에 필터를 추가하고 싶지만 대신 과 같이 할 수 있습니다. where 다음 https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql 테이블에서 이를 사용 방법의 또 다른 예입니다. . 이는 일치하는 테이블 접미사가 아마도 동적으로 결정되기 때문에(테이블의 내용을 기반으로) 분할된 하지 않는 이러지 마세요 전체 테이블 스캔에 대한 비용이 청구되기 때문에 나쁜 예입니다. https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql 절과 함수에서도 사용할 수 있습니다. HAVING AGGREGATE 4. GROUP BY ROLLUP 사용 ROLLUP 함수는 여러 수준에서 집계를 수행하는 데 사용됩니다. 이는 차원 그래프로 작업해야 할 때 유용합니다. 다음 쿼리는 절에 지정된 거래 유형(is_gift)별로 일일 총 크레딧 지출을 반환하고, 각 날짜의 총 지출과 사용 가능한 모든 날짜의 총 지출도 표시합니다. where https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql 5. 테이블을 JSON으로 변환 테이블을 각 레코드가 중첩 배열의 요소인 JSON 개체로 변환해야 한다고 상상해 보세요. 함수가 유용해지는 곳은 다음과 같습니다. to_json_string() https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql 그런 다음 날짜, 마케팅 퍼널, 지수, 히스토그램 그래프 등 어디에서나 사용할 수 있습니다. 6. PARTITION BY 사용 , 및 열이 지정되었습니다. 각 날짜에 대해 모든 행을 유지하면서 각 고객의 총 수익 가치를 어떻게 표시합니까? 다음과 같이 이를 달성할 수 있습니다. user_id date total_cost https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql 7. 이동 평균 BI 개발자는 보고서와 환상적인 대시보드에 이동 평균을 추가하는 임무를 맡는 경우가 많습니다. 이는 7, 14, 30일/월 또는 연도 MA 선 그래프일 수 있습니다. 그럼 어떻게 해야 할까요? https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql 8. 날짜 배열 작업을 하거나 일부 데이터세트에서 누락된 값(예: 날짜)을 확인하려는 경우 매우 유용합니다. BigQuery에는 라는 함수가 있습니다. 사용자 유지 GENERATE_DATE_ARRAY https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql 9. 행_번호() 이는 데이터에서 최신 정보(예: 최근 업데이트된 기록 등)를 가져오거나 중복 항목을 제거하는 데 유용합니다. https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql 10. 엔타일() 또 다른 번호 매기기 기능. 모바일 앱이 있는 경우 모니터링하는 데 매우 유용합니다. 예를 들어 내 앱이 Firebase에 연결되어 있고 사용자가 하면 시간이 얼마나 걸리는지 확인할 수 있습니다. Login duration in seconds login 이 함수는 행 순서에 따라 행을 버킷으로 나누고 각 행에 할당된 1부터 시작하는 버킷 번호를 반환합니다. 버킷의 행 수는 최대 1만큼 다를 수 있습니다. 나머지 값(버킷으로 나눈 나머지 행 수)은 버킷 1부터 시작하여 각 버킷에 하나씩 배포됩니다. NULL, 0 또는 음수로 평가되는 경우 오류가 제공됩니다. constant_integer_expression constant_integer_expression https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql 11. 순위 / 밀도_순위 기능이라고도 합니다. 나는 사용하는 경향이 있습니다. 다음으로 사용 가능한 순위를 건너뛰지 않기 때문입니다. 연속된 순위 값을 반환합니다. 결과를 별개의 버킷으로 나누는 파티션과 함께 사용할 수 있습니다. 각 파티션의 행은 동일한 값을 갖는 경우 동일한 순위를 받습니다. 넘버링 DENSE_RANK 기본 순위 함수로 RANK 예: https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql 제품 가격의 또 다른 예: https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql 12. 피벗 / 피벗 해제 피벗은 행을 열로 변경합니다. 그게 전부입니다. Unpivot은 그 입니다. 반대 https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql 13. 첫 번째_값 / 마지막_값 이는 특정 파티션의 첫 번째/마지막 값에 대해 각 행의 델타를 얻는 데 도움이 되는 또 다른 유용한 기능입니다. https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql 14. 테이블을 구조체 배열로 변환하고 UDF에 전달합니다. 이는 각 행이나 테이블에 복잡한 논리가 포함된 사용자 정의 함수(UDF)를 적용해야 할 때 유용합니다. 언제든지 테이블을 TYPE STRUCT 객체의 배열로 간주한 다음 각 객체를 UDF에 전달할 수 있습니다. 그것은 당신의 논리에 달려 있습니다. 예를 들어 구매 만료 시간을 계산하는 데 사용합니다. https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql 비슷한 방법으로 사용할 필요 없이 테이블을 만들 수 있습니다. 예를 들어, 단위 테스트를 위한 일부 테스트 데이터를 모의하는 데 사용합니다. 이렇게 하면 편집기에서 + + 사용하기만 하면 매우 빠르게 작업을 수행할 수 있습니다. UNION ALL을 Alt Shift Down https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql 15. FOLLOWING 및 UNBOUNDED FOLLOWING을 사용하여 이벤트 퍼널 만들기 좋은 예는 마케팅 퍼널일 수 있습니다. 데이터 세트에는 동일한 유형의 지속적으로 반복되는 이벤트가 포함될 수 있지만 이상적으로는 각 이벤트를 다른 유형의 다음 이벤트와 연결하는 것이 좋습니다. 이는 퍼널 데이터 세트를 구축하기 위해 이벤트, 구매 등의 목록을 가져와야 할 때 유용할 수 있습니다. PARTITION BY를 사용하면 각 파티션에 존재하는 이벤트 수에 관계없이 다음 이벤트를 모두 그룹화할 수 있습니다. https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql 16. 정규식 구조화되지 않은 데이터(예: 환율, 사용자 정의 그룹화 등)에서 무언가를 추출해야 하는 경우 이 기능을 사용합니다. 정규 표현식을 사용하여 환율 작업 환율 데이터가 포함된 다음 예를 살펴보세요. https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql 정규 표현식을 사용하여 앱 버전 작업 때로는 사용하여 앱의 , 또는 버전을 가져오고 사용자 정의 보고서를 만들고 싶을 수도 있습니다. regexp 주요 버전 릴리스 모드 https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql 결론 SQL은 데이터를 조작하는 데 도움이 되는 강력한 도구입니다. 디지털 마케팅의 이러한 SQL 사용 사례가 귀하에게 도움이 되기를 바랍니다. 이는 실제로 편리한 기술이며 많은 프로젝트에 도움이 될 수 있습니다. 이 SQL 조각은 내 삶을 훨씬 쉽게 만들어 주었고 거의 매일 직장에서 사용합니다. 또한 SQL 및 최신 데이터 웨어하우스는 데이터 과학을 위한 필수 도구입니다. 강력한 방언 기능을 통해 데이터를 쉽게 모델링하고 시각화할 수 있습니다. SQL은 데이터 웨어하우스 및 비즈니스 인텔리전스 전문가가 사용하는 언어이므로 이들과 데이터를 공유하려는 경우 탁월한 선택입니다. 이는 시장에 있는 거의 모든 데이터 웨어하우스/레이크 솔루션과 통신하는 가장 일반적인 방법입니다. 원본은 가 에 게시했습니다. datamike mydataschool.com Mike는 열정적이고 디지털에 초점을 맞춘 개인으로, 풍부한 추진력과 열정을 갖고 있으며 디지털 마케팅의 전체 조합이 던지는 도전을 좋아합니다. 영국에 거주하며 2015년 뉴캐슬 대학교에서 MBA를 마쳤습니다.

Read me on Medium.com

Read My Stories

이 오디오는 이야기의 원래 언어로 제작되었습니다!

너무 오래; 읽다

Making security fun one episode at a time

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

너무 오래; 읽다

Mike Shakhomirov

About Author

태그 걸기

Languages

이 기사는 다음에서 발표되었습니다....

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

너무 오래; 읽다

Mike Shakhomirov

About Author

태그 걸기

Languages

이 기사는 다음에서 발표되었습니다....

관련 기사