Analytic Functions: Databases for Developers #13

각 색상별로 내가 가지고있는 벽돌의 수를 계산하고 싶습니다 너는에 이것을 할 수있다

그룹화 기준을 사용하는 SQL 그러나 이것은 문제가 있습니다 각 색상에 대해 하나의 행만 가져올 수 있으며 유일한 열은 색상과 카운트 벽돌과 같은 다른 속성을보고 싶다면 무게, 치수 및 기타 모든 벽돌과 함께 포장됩니다! 스칼라 서브 쿼리를 사용하여이 문제를 해결할 수 있습니다 그러면 테이블을 쿼리합니다

다시 각 벽돌에 대해 동일한 색상의 다른 모든 것들을 찾아서 카운트 그러나 이것은 낭비되는 많은 노력입니다 다행히도 훨씬 좋은 방법이 있습니다 분석 기능! 분석 또는 창 함수를 사용하면 그룹을 사용하지 않고 합계를 집계하십시오 그룹 별과는 달리 당신은 여전히 ​​모든 행을 얻는다

함수 다음에 over 절에서이를 수행하십시오 이 숫자를 카운트 한 후에 우리가 가지고있는 벽돌의 총 개수를 구하십시오 좋아,하지만 그건 우리가 원하는 것이 아니야? 얼마나 많은 벽돌을 쌓고 싶었는지 우리는 각 색깔이 있습니다 이렇게하려면 partition by 절을 사용해야합니다 이것은 group by와 비슷하게 데이터를 열의 각 값에 대한 집합으로 조각합니다

그래서 각 색상에 대한 카운트를 얻으려면 색상별로 파티션을 만드십시오 이것은 이미 시원하고 강력합니다 그러나 분석 함수를 사용하여 누적 합계를 계산할 수도 있습니다 가장 가벼운 물건부터 가장 무거운 물건까지 무게별로 장난감을 분류했다고 가정 해보십시오 누적 합계를 결과에 추가하려고합니다

그래서 각 장난감마다 당신은 그것보다 가벼운 모든 것들의 합계에 그 무게를 더할 필요가 있습니다 이를 수행하려면 초과 절에서 가중치를 더한 후 가중치로 순서를 추가하십시오 멋지고 쉬운! 하지만 거기에 잡았다 기본적으로이 값은 또는보다 작은 값을 갖는 모든 행을 포함합니다 현재 행의 값과 같습니다

이로 인해 예상치 못한 결과가 발생할 수 있습니다 같은 무게의 장난감 두 개를 가지고 있다고 가정 해보십시오 누적 합계는 동일합니다 두 장난감 다 분류에는 암시 적 윈도우 잉 절 언 바운드 선행 및 현재 행 사이의 범위 이것은 모든 값을 덜 포함한다는 것을 의미합니다

현재 값과 가중치 열에 같은 값을 가진 값보다 아마 당신이 원하는 것이 아닙니다 일반적으로 누적 합계를 증가합니다 이렇게하려면 범위를 행으로 변경해야합니다 그러나 이것은 결과를 비 결정적으로 만듭니다 따라서 SQL을 실행할 때마다 다른 결과를 얻을 수 있습니다

이것을 피하려면 순서에 열을 추가하여 더 이상 관계가 없게하십시오 어느 쪽이든 창은 항상 세트의 첫 번째 행으로 시작합니다 그러나 회전하는 창을 원한다면? 현재 장난감에 무게를 더한 무게를 더한다고합시다 이렇게하려면 windowing 절을 약간 조정하면됩니다 변화 하나에 제한이 없으며 이전 장난감의 가치 만 포함합니다

계산 또한 이것을 사용하여 결과 세트를 검색 할 수 있습니다 변화 현재 행을 다음 값으로 변환합니다 원하는 행 또는 값의 수를 나타냅니다 당신의 결과를 미리 보아라

이것은 당신이 창조적 인 일을 할 수있게 해줍니다 현재 장난감의 평균 무게와 어느 쪽이든 찾고 싶다고합시다 그것의 이 작업은 다음을 사용하여 수행 할 수 있습니다 행 1과 행 1 사이의 행 또는 체중 측정기의 평균을 구할 수 있습니다

현재 장난감보다 많거나 적은 2 단위 : 전후 2 ~ 후 2의 범위 현재 행을 포함하지 않도록 창을 오프셋 할 수도 있습니다 앞선 3 개에서 1 개 사이의 행 포함할지 여부를 명확하게 지정하십시오 정확한 행 수 또는 windowing 절의 특정 값 범위 행 또는 범위를 적절하게 사용하십시오 따라서 우리는 모두에 걸쳐 누적 합계를 얻었습니다 장난감

그러나 이것을 색상으로 계산할 수도 있습니다 이 열을 파티션 나누기 및 별도의 창 가져 오기 각 색조마다 개수와 합계뿐만 아니라 over 절을 모든 좋아하는 집계 함수를 만들고 필요에 따라 파티션 및 순서를 지정하십시오 하지만 거기 또한 over 절이 필요한 다른 함수의 전체 호스트입니다 일반적인 것들은 다음과 같습니다 : 래그 (lag) 및 리드 (lead)를 사용하여 결과 세트에서 앞뒤로 돌아볼 수 있습니다

행 번호 지정 함수 row_number, rank 및 dense rank 그리고 첫 번째와 마지막 값을 사용하면 정렬 된 결과 집합 이들 각각은 고유 한 단점을 가지고 있습니다 자세한 내용 및 이러한 Connor의 분석 시리즈를 시청하거나 해당 내용을 문서에서 읽으십시오 알고 있어야 할 마지막 사항은 분석 함수를 사용할 수 없다는 것입니다

where 절 선택 만 따라서 결과를 필터링하려면 다음과 같이하십시오 롤링 평균이 3보다 큰 행을 찾으려면 하위 쿼리에서 호출하십시오 그런 다음 그 결과를 필터링하십시오

보고 주셔서 감사합니다! SQL에 대해 가장 알고 싶은 것은 무엇입니까? 의견을 통해 알려주십시오 향후 업데이트 구독