고급 데이터 분석을 위해 SQL에서 창 함수를 어떻게 사용합니까?

이 기사에서는 SQL 창 함수, 고급 데이터 분석을위한 강력한 도구를 설명합니다. 조항에 의한 분할 및 주문을 포함하여 구문을 자세히 설명하고 총계, 순위, 지연/선도 및 이동 평균에 대한 사용을 보여줍니다.

고급 데이터 분석을 위해 SQL에서 창 함수 사용 방법

분석 기능이라고도하는 창 함수는 SQL의 강력한 도구입니다.이 도구는 현재 행과 관련된 일련의 테이블 행에서 계산을 수행 할 수 있습니다. 각 그룹에 대해 단일 값을 그룹화하고 단일 값을 반환하는 집계 함수 (Sum, Avg, Count)와 달리 창 함수는 그룹을 그룹화하지 않고 행 ( "창")에서 작동합니다. 즉, 결과 세트에서 모든 원래 행을 유지하지만 창에 따라 계산 된 열이 추가되었습니다.

기본 구문에는 함수 후 OVER 절을 지정하는 것이 포함됩니다. 이 조항은 창을 정의합니다. OVER 절 내의 주요 구성 요소는 다음과 같습니다.

• 파티션에 의해 : 이 조항은 결과를 파티션으로 나눕니다. 창 함수는 각 파티션에 별도로 적용됩니다. 데이터 내에서 하위 그룹을 만드는 것으로 생각하십시오. 생략하면 전체 결과 세트는 단일 파티션을 형성합니다.
• 주문 : 이 조항은 각 파티션 내의 행 순서를 지정합니다. 이것은 ROW 순서에 민감한 RANK , ROW_NUMBER 및 LAG/LEAD 와 같은 함수에 중요합니다.
• 행/범위 : 이 조항은 현재 행과 관련하여 계산에 포함되어야하는 행을 지정하여 창을 더 세분화합니다. 예를 들어, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 현재 행, 선행 행 및 다음 행이 포함됩니다. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 파티션의 시작부터 현재 행까지 모든 행이 포함됩니다.

예를 들어, 런닝 총 판매량을 계산합니다.

 <code class="sql">SELECT order_date, sales, SUM(sales) OVER (ORDER BY order_date) as running_total FROM sales_table;</code>

이 쿼리는 각 주문 날짜까지 누적 매출을 계산합니다. 절차 ORDER BY 여기에서 필수적입니다. 그것 없이는 달리기 총계는 예측할 수 없습니다.

SQL의 창 함수에 대한 일반적인 사용 사례

창 함수는 놀랍도록 다재다능하며 데이터 분석에 많은 응용 프로그램이 있습니다. 일부 일반적인 사용 사례는 다음과 같습니다.

• 총 실행/평균 실행 : 이전 예에서 알 수 있듯이 일련의 행에서 누적 합계, 평균 또는 기타 집계 계산. 이것은 추세 분석에 유용합니다.
• 순위 및 주문 : 파티션 내 행에 순위 또는 행 번호를 할당합니다. 이는 최고 성능, 특이 치 또는 데이터 우선 순위를 식별하는 데 도움이됩니다. RANK() , ROW_NUMBER() , DENSE_RANK() 및 NTILE() 과 같은 함수는 여기에서 사용됩니다.
• 지연 및 선두 : 동일한 파티션 내에서 이전 또는 후속 행의 값에 액세스합니다. 이것은 시간이 지남에 따라 변화를 비교하거나 추세를 식별하는 데 유용합니다. LAG() 및 LEAD() 함수가 사용됩니다.
• 이동 평균 계산 : 줄의 슬라이딩 창에 대한 평균 계산. 이것은 데이터의 변동을 부드럽게하고 기본 추세를 강조합니다.
• 데이터 파티셔닝 및 집계 : 파티셔닝을 집계 기능을 결합하면 정교한 분석이 가능합니다. 예를 들어, 지역 당 최고 N 판매를 찾습니다.

기존 SQL 쿼리에 비해 창 함수가 성능을 향상시키는 방법

창 함수는 종종 자체 요인 또는 하위 쿼리를 사용하여 유사한 결과를 달성하는 전통적인 SQL 쿼리를 능가합니다. 이것은 다음과 같습니다.

• 데이터 처리 감소 : 창 함수는 일반적으로 데이터를 한 번만 처리하는 반면,자가 요인 또는 하위 쿼리는 데이터를 통해 여러 패스를 포함하여 I/O 운영 및 처리 시간을 증가시킬 수 있습니다.
• 최적화 된 실행 계획 : 데이터베이스 최적화기는 종종 창 함수를 사용하여 쿼리를 최적화하는 데 더 나은 것이므로보다 효율적인 실행 계획을 세웁니다.
• 단순화 된 쿼리 로직 : 창 함수는 일반적으로 더 간결하고 읽기 쉬운 SQL 코드로 이어져 쿼리의 복잡성을 줄이고 이해하고 유지하기가 더 쉬워집니다.

그러나 성능 이득은 데이터 세트의 크기, 쿼리의 복잡성 및 사용중인 특정 데이터베이스 시스템을 포함한 여러 요인에 따라 달라집니다. 경우에 따라 잘 최적화 된 기존 쿼리가 여전히 창 함수 쿼리보다 성능이 우수 할 수 있습니다.

창 함수 사용으로 이익이되는 복잡한 SQL 쿼리의 예

윈도우 기능이 복잡한 쿼리를 크게 단순화하는 이러한 시나리오를 고려하십시오.

시나리오 1 : 판매를 기반으로 카테고리 당 상위 3 개 제품 찾기.

창 함수가 없으면 각 카테고리마다 자체 교육 또는 하위 퀘스트가 필요합니다. 창 함수 :

 <code class="sql">WITH RankedSales AS ( SELECT product_name, category, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) as sales_rank FROM products ) SELECT product_name, category, sales FROM RankedSales WHERE sales_rank </code>

시나리오 2 : 전달과 비교하여 판매 백분율 변화를 계산합니다.

LAG() 사용하면 이것을 크게 단순화합니다.

 <code class="sql">SELECT order_date, sales, (sales - LAG(sales, 1, 0) OVER (ORDER BY order_date)) * 100.0 / LAG(sales, 1, 1) OVER (ORDER BY order_date) as percentage_change FROM sales_table;</code>

이 예는 창 함수가 복잡성을 크게 줄이고 복잡한 SQL 쿼리의 가독성과 성능을 향상시키는 방법을 보여줍니다. 이들은 고급 데이터 분석을위한 강력한 도구이며 SQL 개발자의 툴킷의 핵심 부분이어야합니다.

위 내용은 고급 데이터 분석을 위해 SQL에서 창 함수를 어떻게 사용합니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!