여기서 시작 하시겠습니까? 이 강의는 데이터 분석을위한 SQL 사용에 대한 전체 길이 자습서의 일부입니다. 시작 부분을 확인하세요.
이 강의에서는 다음 내용을 다룹니다.
- 창 함수 소개
- 기본 창 구문
- 일반적인 용의자 : SUM, COUNT, AVG
- ROW_NUMBER ()
- RANK () 및 DENSE_RANK ()
- NTILE
- LAG 및 LEAD
- 창 별칭 정의
- 고급 창 기술
이 강의에서는 다음을 게시하는 Washington DC의 Capital Bikeshare Program의 데이터를 사용합니다. 웹 사이트의 자세한 여행 수준의 과거 데이터. 데이터는 2014 년 2 월에 다운로드되었지만 2012 년 1 분기 동안 수집 된 데이터로 제한됩니다. 각 행은 1 회 탑승을 나타냅니다. rider_type를 제외하고 대부분의 필드는 설명이 필요하지 않습니다. “등록됨”은 라이드 쉐어 프로그램의 월간 멤버십을 나타내고 “캐주얼”은 라이더가 3 일 패스를 구입했음을 나타냅니다. start_time 및 end_time 필드는 SQL 날짜 형식에 맞게 원래 양식에서 정리되었으며이 테이블에 타임 스탬프로 저장됩니다.
창 함수 소개
PostgreSQL의 문서는 창 함수의 개념을 소개하는 훌륭한 작업을 수행합니다.
윈도우 함수는 현재 행과 어떤 식 으로든 관련된 테이블 행 세트에서 계산을 수행합니다. 이는 집계 함수로 수행 할 수있는 계산 유형과 비슷합니다. 그러나 일반 집계 함수와 달리 윈도우 사용 함수는 행을 단일 출력 행으로 그룹화하지 않습니다. 행은 별도의 ID를 유지합니다. 배후에서 window 함수는 쿼리 결과의 현재 행 이상에 액세스 할 수 있습니다.
가장 실용적인 예는 누적 합계입니다.
위 쿼리가 집계 (< GROUP BY를 사용하지 않고 div id = “cb894ca5a9″>
). 구문을 분석하고 작동 방식을 살펴 보겠습니다.
기본 윈도우 구문
위 집계의 첫 번째 부분 인 SUM(duration_seconds) , 다른 집계와 매우 유사합니다. OVER를 추가하면 창 함수로 지정됩니다. 위의 집계를 “take the sum of duration_seconds 전체 결과 집합에 대해 start_time 순서대로. “
전체 데이터 세트에서 개별 그룹으로 창을 좁히려면 데이터 세트에서 PARTITION BY를 사용하여 수행 할 수 있습니다.
위의 쿼리는 start_terminal별로 쿼리를 그룹화하고 정렬합니다. . start_terminal의 각 값 내에서 start_time에 따라 정렬되며 현재 행과
. start_terminal 값이 변경 될 때까지 아래로 스크롤하면 running_total가 처음부터 다시 시작됩니다. 이것이 PARTITION BY를 사용하여 그룹화 할 때 발생합니다. 여전히 ORDER BY에 의해 당황하는 경우 지정된 열을 기준으로 정렬합니다. (s) ORDER BY 절과 동일한 방식으로 모든 파티션을 개별적으로 취급한다는 점만 다릅니다. 또한 누적 합계를 생성합니다. ORDER BY없이 각 값은 해당 iv id의 모든 duration_seconds 값의 합계가됩니다. = “8ba480cb82″>
. 아이디어를 얻으려면 ORDER BY없이 위 쿼리를 실행 해보세요.
ORDER 및 PARTITION는 계산이 수행되는 데이터의 정렬 된 하위 집합 인”창 “이라고하는 것을 정의합니다.
참고 : 창 함수와 표준 집계를 같은 곳에 사용할 수 없습니다. 더 구체적으로 말하자면 GROUP BY 절에 창 함수를 포함 할 수 없습니다.
연습 문제
각 시작 터미널에서 탑승자가 누적 한 총 시간의 백분율로 각 탑승 시간을 표시하는 위 예제 쿼리의 쿼리 수정을 작성하십시오.
사용해보기 답변보기
일반적인 의심 : SUM, COUNT 및 AVG
윈도우 함수를 사용할 때 일반적인 상황에서와 동일한 집계를 적용 할 수 있습니다. div id = “891e999974″>
, COUNT 및 AVG. 이를 이해하는 가장 쉬운 방법은 몇 가지 추가 기능을 사용하여 이전 예제를 다시 실행하는 것입니다.
또는 ORDER BY와 동일한 기능을 확인합니다.
이전 두 쿼리를 Mode에 연결하고 실행해야합니다. 이 다음 연습 문제는 예제와 매우 유사하므로 처음부터 시작하지 말고 위 코드를 수정 해보십시오.
문제 연습
자전거 타기 시간의 누계를 표시하는 쿼리를 작성하십시오 ( 마지막 예), end_terminal별로 그룹화되고 승차 시간이 내림차순으로 정렬됩니다.
사용해보기 답변보기
ROW_NUMBER ()
ROW_NUMBER()는 주어진 행의 번호를 표시합니다. 시작은 1이고 window 문의 ORDER BY 부분에 따라 행 번호를 매 깁니다. ROW_NUMBER()에서는 괄호 안에 변수를 지정할 필요가 없습니다.
PARTITION BY 절을 사용하면 다음을 수행 할 수 있습니다. 각 파티션에서 다시 1을 세기 시작합니다. 다음 쿼리는 각 터미널에 대해 카운트를 다시 시작합니다.
RANK () 및 DENSE_RANK ()
RANK()는 ROW_NUMBER()와 약간 다릅니다. 예를 들어 start_time로 주문하는 경우 일부 터미널에는 시작 시간이 동일한 라이드가있을 수 있습니다. 이 경우 동일한 순위가 부여되는 반면 ROW_NUMBER()는 서로 다른 번호를 부여합니다. 다음 쿼리에서 start_terminal 31000에 대한 4 번째 및 5 번째 관찰을 확인할 수 있습니다. 둘 다 4 등급이 부여되고 다음 결과는 6 등급을받습니다.
애플리케이션에 따라 RANK() 대신 DENSE_RANK()를 사용할 수도 있습니다. 세 항목이 동일한 값을 갖는 상황을 상상해보십시오. 두 명령 중 하나를 사용하면 모두 동일한 순위를 얻습니다. 이 예에서는 “2”라고합시다. 두 명령이 다음 결과를 다르게 평가하는 방법은 다음과 같습니다.
-
RANK()는 동일한 행에 순위 2를 부여한 다음 순위 3을 건너 뜁니다. 그리고 4이므로 다음 결과는 5가됩니다. -
DENSE_RANK()는 여전히 모든 동일한 행에 2의 순위를 부여하지만 다음 행은 3이됩니다. 순위는 건너 뜁니다.
연습 문제
5 개의 가장 긴 라이드를 표시하는 쿼리 작성 각 출발 터미널에서, 터미널별로 주문하고 각 터미널 내에서 최장 거리에서 최단 거리까지 운행합니다. 2012 년 1 월 8 일 이전에 발생한 차량으로 제한됩니다.
사용해보기 답변보기
NTILE
윈도우 함수를 사용하여 주어진 행이 속하는 백분위 수 (또는 사 분위수 또는 기타 세분 위)를 식별 할 수 있습니다. 구문은 NTILE(*# of buckets*).이 경우 ORDER BY는 사 분위수를 결정하는 데 사용할 열을 결정합니다. 지정). 예 :
위 쿼리의 결과를 보면 percentile 열이 예상대로 정확하게 계산되지 않음을 알 수 있습니다. 두 개의 레코드가 있고 백분위 수를 측정하고 있었다면 한 레코드는 1 번째 백분위 수를 정의하고 다른 레코드는 100 번째 백분위 수를 정의 할 것으로 예상합니다. NTILE 함수를 사용하면 실제로 표시되는 것은 1 번째 백분위 수에 하나의 레코드이고 2 번째 백분위 수에 하나입니다. 31000-percentile 열은 숫자 순위처럼 보입니다. start_terminal 31007까지 아래로 스크롤하면 해당 start_terminal에 대해 100 개가 넘는 레코드가 있기 때문에 백분위 수를 제대로 계산할 수 있습니다. 매우 작은 창으로 작업하는 경우이 점을 염두에두고 사 분위수 또는 이와 유사한 작은 밴드.
연습 문제
여행 기간과 해당 지역의 백분위 수만 표시하는 쿼리를 작성하세요. 해당 기간은 (터미널별로 분할되지 않은 전체 데이터 세트에 걸쳐) 감소합니다.
사용해보기 답변보기
LAG 및 LEAD
특히 이전 또는 다음 행과 행을 비교하는 것이 유용 할 수 있습니다. 의미있는 순서대로 데이터가있는 경우. LAG 또는 LEAD를 사용하여 다른 항목에서 값을 가져 오는 열을 만들 수 있습니다. 행-가져올 열과 끌어 오기를 수행 할 행 수를 입력하기 만하면됩니다. LAG는 이전 행에서 가져오고 LEAD는 다음 행에서 가져옵니다.
계산하려는 경우 특히 유용합니다. 행 간 차이 :
difference 열의 첫 번째 행은 가져올 이전 행이 없기 때문에 null입니다. 마찬가지로 LEAD를 사용하면 데이터 세트 끝에 null이 생성됩니다. 결과를 좀 더 깔끔하게 만들고 싶다면 외부 쿼리로 래핑하여 null을 제거 할 수 있습니다.
창 별칭 정의
작성하려는 경우 여러 창 기능을 같은 쿼리에 동일한 창을 사용하여 별칭을 만들 수 있습니다.위의 NTILE 예를 살펴보세요.
다음과 같이 다시 작성할 수 있습니다.
WINDOW 절 (포함 된 경우)은 항상 WHERE 절 뒤에 와야합니다.
고급 창 기술
창의 전체 목록을 확인할 수 있습니다. Postgres 설명서의 Postgres (Mode에서 사용하는 구문)의 함수 연결된 데이터베이스에서 창 함수를 사용하는 경우 시스템에 적합한 구문 가이드를 참조해야합니다.