찾다

집 >데이터 베이스 >SQL >SQL 창 기능에 대한 자세한 설명: 순위 창 기능의 사용

SQL 창 기능에 대한 자세한 설명: 순위 창 기능의 사용

WBOY
WBOY앞으로
2022-09-08 17:44:472180검색

이 문서에서는 주로 SQL Server 기본 키 제약 조건(PRIMARY KEY)을 소개하는 SQL 서버에 대한 관련 지식을 제공합니다. 기본 키는 테이블의 각 행을 고유하게 식별하는 열 또는 열 그룹입니다. 아래의 세부 내용을 살펴보겠습니다. 모두에게 도움이 되기를 바랍니다.

SQL 창 기능에 대한 자세한 설명: 순위 창 기능의 사용

추천 학습: "SQL Tutorial"

창 함수의 기본 사항은 SQL 창 함수

문서를 참조하세요. 값 창 함수는 창 내의 지정된 위치에 데이터 행을 반환하는 데 사용할 수 있습니다. 일반적인 값 창 함수는 다음과 같습니다.

LAG 함수는 창에서 현재 행 이전의 데이터 N번째 행을 반환할 수 있습니다. LEAD 함수는 창의 현재 행 다음의 N번째 데이터 행을 반환할 수 있습니다. FIRST_VALUE 함수는 창에 있는 데이터의 첫 번째 행을 반환할 수 있습니다. LAST_VALUE 함수는 창에 있는 데이터의 마지막 행을 반환할 수 있습니다. NTH_VALUE 함수는 창에서 N번째 데이터 행을 반환할 수 있습니다.

그 중 LAG 함수와 LEAD 함수는 동적 창 크기를 지원하지 않으며 전체 파티션을 분석 창으로 사용합니다.

사례 분석

사례에 사용된 테이블 예

다음 쿼리는 테이블을 사용합니다. sales_monthly 테이블은 제품 판매 정보를 저장하고, product는 제품 이름을 나타내고, ym은 연도와 월을 나타내고, amount는 판매량을 나타냅니다( 위안) .

다음은 테이블의 일부 데이터입니다.

이 테이블의 초기화 스크립트는 기사 하단에서 얻을 수 있습니다.

1. 월별 분석

월별 증가율은 이전 기간의 데이터 대비 현재 기간의 데이터 증가를 나타냅니다. 예를 들어 2019년 6월 매출 대비 제품 판매량이 증가한 것입니다. 2019년 5월.

다음 문은 다양한 상품의 월간 성장률을 계산한 것입니다. 

SELECT s.product AS "产品", s.ym AS "年月", s.amount AS "销售额",
 ( 
    (s.amount - LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym))/
    LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS "环比增长率(%)"
FROM sales_monthly s
ORDER BY s.product,s.ym

그 중 LAG(amount, 1)는 이전 기간의 판매량을 구한다는 뜻이고, PARTITION BY 옵션은 상품별로 파티션을 나눈다는 의미입니다. , ORDER BY 옵션은 제품별로 파티션을 나누어 월별로 정렬한다는 의미입니다.

당월 매출액에서 이전 기간 매출액을 뺀 값을 이전 기간 매출액으로 나눈 값이 월간 성장률입니다.

쿼리는 다음 결과를 반환합니다.

2018년 1월이 첫 번째 기간이므로 월별 증가율이 비어 있습니다.

2018년 2월 "오렌지"의 월간 성장률은 약 0.2856%((10183-10154)/10154×100) 등이었습니다.

2. 전년 대비 분석

전년 대비 성장은 전년도 또는 과거 같은 기간 대비 현재 기간의 데이터 증가를 나타냅니다. 예를 들어 2019년 6월의 제품 판매입니다. 2018년 6월 매출액 대비 증가하였습니다.

다음 문은 매월 각종 상품의 전년 대비 성장률을 계산한 것입니다. 

SELECT s.product AS "产品", s.ym AS "年月", s.amount AS "销售额",
 ( 
    (s.amount - LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym))/
    LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS "同比增长率(%)"
FROM sales_monthly s
ORDER BY s.product,s.ym

그 중 LAG(amount, 12)는 이번 달 전 12기의 판매량, 즉 판매량을 나타냅니다. 작년 같은 달의 것입니다.

PARTITION BY 옵션은 제품별로 파티션을 나누는 것을 의미하고, ORDER BY 옵션은 월별로 정렬하는 것을 의미합니다.

당월 매출액에서 작년 같은 기간 매출액을 뺀 값을 작년 같은 기간 매출액으로 나눈 것이 전년 대비 증가율입니다.

이 쿼리에서 반환된 결과는 다음과 같습니다.

2018년 12개 기간의 데이터에 해당하는 전년 대비 성장률이 없습니다. 2019년 1월은 약 9.3067%((11099-10154)/ 10154×100) 등이었습니다.

팁: LEAD 함수는 LAG 함수와 유사하지만 반환 결과는 현재 행 다음의 N번째 데이터 행입니다.

3. 복합성장률

복합성장률은 N번째 기간의 데이터를 첫 번째 기간의 기준 데이터로 나눈 후 N-1승으로 올리고 1을 뺀 값입니다.

2018년 제품 판매량이 10,000개, 2019년 제품 판매량이 12,500개, 2020년 제품 판매량이 15,000개라고 가정해 보겠습니다. 그런 다음 이 2년의 복합 성장률을 다음과 같이 계산합니다.

연간 기준으로 계산한 복합 성장률을 연평균 복합 성장률, 월 단위로 계산한 복합 성장률을 월 평균 복합 성장률.

다음 쿼리는 2018년 1월 이후 다양한 제품의 월 평균 매출 복합 성장률을 계산합니다. 

WITH s (product,ym,amount,first_amount,num) AS (
  SELECT m.product, m.ym, m.amount,
  FIRST_VALUE(m.amount) OVER (PARTITION BY m.product ORDER BY m.ym),
  ROW_NUMBER() OVER (PARTITION BY m.product ORDER BY m.ym)
  FROM sales_monthly m
)
 
SELECT product AS "产品", ym AS "年月",amount AS "销售额",
       (POWER( amount/first_amount, 1.0/NULLIF(num-1,0)) -1)*100 AS "月均复合增长率(%)"
FROM s
ORDER BY product, ym

먼저 FIRST_VALUE(amount)가 첫 번째 기간(201801)의 매출을 반환하는 일반 테이블 표현식을 정의합니다. 함수는 각 기간의 수를 반환합니다.

메인 쿼리의 POWER 함수는 제곱근 연산을 수행하는 데 사용되며, NULLIF 함수는 데이터의 첫 번째 기간의 0으로 나누기 오류를 처리하는 데 사용되며, 상수 1.0은 다음과 같은 결과로 인한 정밀도 손실을 방지하는 데 사용됩니다. 정수 나누기.

이 쿼리로 반환된 결과는 다음과 같습니다.

2018년 1월이 첫 번째 기간이므로 해당 제품의 월평균 매출의 복합 성장률은 비어 있습니다.

“桔子”2018年2月的月均销售额复合增长率等于它的环比增长率，2018年3月的月均销售额复合增长率等于0.4471%，依此类推。 

4.不同产品最高和最低销售额

以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份：

  SELECT product AS "产品", ym AS "年月",amount AS "销售额",
  
         FIRST_VALUE(m.ym) OVER (
           PARTITION BY m.product ORDER BY m.amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS "最高销售额月份",
         
         LAST_VALUE(m.ym) OVER (
           PARTITION BY m.product ORDER BY m.amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS "最低销售额月份",
         
         NTH_VALUE(m.ym,3) OVER (
           PARTITION BY m.product ORDER BY m.amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
         ) AS "第三高销售额月份"
 
  FROM sales_monthly m
  ORDER BY product, ym;

三个窗口函数的OVER子句相同，PARTITION BY选项表示按照产品进行分区，ORDER BY选项表示按照销售额从高到低排序。

以上三个函数的默认窗口都是从分区的第一行到当前行，因此我们将窗口扩展到了整个分区。

该查询返回的结果如下： 

“桔子”的最高销售额出现在2019年6月，最低销售额出现在2018年1月，第三高销售额出现在2019年4月。

示例表和脚本

-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);

推荐学习：《SQL教程》

위 내용은 SQL 창 기능에 대한 자세한 설명: 순위 창 기능의 사용의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：
이 기사는 jb51.net에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제
이전 기사：SQL 쿼리에서 테이블 별칭 지정의 핵심 사항(요약 공유)다음 기사：SQL 쿼리에서 테이블 별칭 지정의 핵심 사항(요약 공유)

관련 기사

더보기