SQL은 데이터 세계의 만국 공통어이며 데이터 전문가가 되기 위한 가장 중요한 기술입니다.
SQL이 중요한 이유는 데이터 랭글링 단계에서 요구되는 주요 스킬이기 때문입니다. 많은 데이터 탐색, 데이터 조작, 파이프라인 개발 및 대시보드 생성이 SQL을 통해 수행됩니다.
훌륭한 데이터 과학자와 훌륭한 데이터 과학자를 구분하는 것은 훌륭한 데이터 과학자가 SQL의 기능이 허용하는 한 많은 데이터를 랭글링할 수 있다는 것입니다. SQL이 제공하는 모든 것을 완전히 사용할 수 있는 큰 부분은 창 기능을 사용하는 방법을 아는 것입니다.
그 말로, 그것에 뛰어 들자!
1. LEAD() 및 LAG()가 있는 델타
LEAD() 및 LAG()는 주어진 메트릭에 대해 한 기간을 이전 기간과 비교할 때 주로 사용됩니다. 몇 가지 예를 들자면…
- 매년 매출과 전년도 매출 사이의 델타를 얻을 수 있습니다.
- 월 단위로 가입/전환/웹사이트 방문 수의 델타를 얻을 수 있습니다.
- 사용자 이탈을 월 단위로 비교할 수 있습니다.
예:
다음 쿼리는 월별 비용 변화율을 쿼리하는 방법을 보여줍니다.
with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs2. SUM() 또는 COUNT()를 사용한 누적 합계
누계 계산은 SUM() 또는 COUNT()로 시작하는 Windows 함수를 통해 간단히 수행할 수 있습니다. 이는 시간 경과에 따른 특정 메트릭의 성장을 표시하려는 경우에 강력한 도구입니다. 보다 구체적으로 다음과 같은 상황에서 유용합니다.
- 시간 경과에 따른 수익 및 비용의 누계를 구합니다.
- 사용자당 총 앱 사용 시간 가져오기
- 시간 경과에 따른 총 전환수 얻기
예:
다음 예에서는 월별 비용의 누적 합계 열을 포함하는 방법을 보여줍니다.
SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table3. AVG()를 사용한 이동 평균
AVG()는 다음을 계산할 수 있으므로 Windows 기능에서 정말 강력합니다. 이동 평균 시간이 지남에.
이동 평균은 단기적으로 값을 예측하는 간단하면서도 효과적인 방법입니다. 또한 그래프의 변동성 곡선을 부드럽게 만드는 데 매우 유용합니다. 일반적으로 이동 평균은 사물이 이동하는 일반적인 방향을 측정하는 데 사용됩니다.
더 구체적으로…
- 주간 판매의 일반적인 추세를 파악하는 데 사용할 수 있습니다(시간이 지남에 따라 평균이 올라가나요?). 이는 회사로서의 성장을 의미합니다.
- 마찬가지로 주간 전환 또는 웹사이트 방문의 일반적인 추세를 파악하는 데 사용할 수 있습니다.
예:
다음 쿼리는 전환에 대한 10일 이동 평균을 구하는 예입니다.
SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions4. ROW_NUMBER()
ROW_NUMBER()는 첫 번째 또는 마지막 레코드를 가져올 때 특히 유용합니다. 예를 들어, 체육관 회원이 언제 체육관에 왔는지에 대한 테이블이 있고 그들이 체육관에 처음 온 날짜를 알고 싶다면 PARTITION BY customer(name/id) 및 ORDER BY 구매 날짜를 사용할 수 있습니다. 그런 다음 첫 번째 행을 얻기 위해 rowNumber가 XNUMX인 행을 간단히 필터링할 수 있습니다.
예:
이 예는 ROW_NUMBER()를 사용하여 각 구성원(사용자)이 방문한 첫 번째 날짜를 가져오는 방법을 보여줍니다.
with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1요약하자면, 첫 번째 또는 마지막 레코드를 가져와야 하는 경우 ROW_NUMBER()가 이를 달성하는 좋은 방법입니다.
5. DENSE_RANK()로 순위 기록
DENSE_RANK()는 동일한 값에 대해 동일한 순위를 반환한다는 점을 제외하고 ROW_NUMBER()와 유사합니다. 조밀한 순위는 다음과 같이 상위 레코드를 검색할 때 매우 유용합니다.
- 이번 주 가장 많이 본 Netflix 프로그램 상위 10개를 뽑고 싶다면
- 지출한 금액을 기준으로 상위 100명의 사용자를 얻으려면
- 1000명의 최소 활성 사용자의 행동을 보고 싶다면
예:
총 판매액을 기준으로 상위 고객의 순위를 지정하려면 DENSE_RANK()를 사용하는 것이 좋습니다.
SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers읽어 주셔서 감사합니다!
그리고 그게 다야! 이것이 면접 준비에 도움이 되기를 바랍니다. 이 5가지 개념을 완전히 알고 있다면 대부분의 SQL 창 기능 문제와 관련하여 큰 도움이 될 것입니다.
항상 그렇듯이, 나는 당신의 학습 노력에 최선을 다하기를 바랍니다!
테렌스 신 SQL에서 3년 이상의 경험과 Python에서 2년 이상의 경험을 가진 데이터 애호가이자 데이터 과학 및 KDnuggets를 향한 블로거입니다.
실물. 허가를 받아 다시 게시했습니다.
