当前位置:   article > 正文

SQL的窗口函数_sql窗口函数

sql窗口函数

SQL的窗口函数

1. 介绍

窗口函数分类:聚合函数、排序函数和值函数(偏移函数)。
SQL中的窗口函数是一种特殊的函数,它能够在查询结果中创建一个窗口或者窗口集合,然后对这个窗口进行计算。窗口函数可以用于分析和汇总数据,同时保留原始查询结果的行顺序。
窗口函数基于查询结果的行数据进行计算,窗口函数运行在HAVING子句之后、 ORDER BY子句之前。窗口函数需要特殊的关键字OVER子句来指定窗口即触发一个窗口函数。

2. 聚合函数
AVG:该函数用于计算平均值。
COUNT:该函数用于计算记录数。
MAX:该函数用于计算最大值。
MIN:该函数用于计算最小值。
STDDEV:返回数值的总体标准差。
STDDEV_SAMP:返回一组数值(整数、小数或浮点)的样本标准差。
SUM:该函数用于计算汇总值。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
0.数据准备
-- 数据准备
WITH t_sales_info AS (
    SELECT * FROM (
        VALUES('A', '2023-06-01', '2023', '1', 36.0)
            , ('A', '2023-09-01', '2023', '2', 48.0)
            , ('B', '2023-09-07', '2023', '2', 48.0)
            , ('C', '2023-10-10', '2023', '3', 23.0)
            , ('A', '2023-11-11', '2023', '3', 23.0)
            , ('C', '2023-12-11', '2023', '3', 23.0)
            , ('A', '2024-01-01', '2024', '2', 48.0)
            , ('B', '2024-02-02', '2024', '2', 48.0)
            , ('C', '2024-02-04', '2024', '1', 36.0)
            , ('C', '2024-02-11', '2024', '1', 36.0)
            , ('B', '2024-03-16', '2024', '3', 23.0)
            , ('B', '2024-04-01', '2024', '3', 23.0)
            , ('A', '2024-04-14', '2024', '3', 23.0)
            , ('C', '2024-04-21', '2024', '3', 23.0)
            , ('B', '2024-05-07', '2024', '3', 23.0)
    ) AS tbl_name(customer_id, order_date, year, product_id, product_price)
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
1. AVG
该函数用于计算平均值。
输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:DOUBLE。
  • 1
  • 2
  • 3
-- order by 可以省略
SELECT customer_id, order_date, `year`, product_id, product_price
    , AVG(product_price) OVER(PARTITION BY customer_id ORDER BY customer_id) AS price_avg 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_priceprice_avg
A2024-04-142024323.035.6
A2024-01-012024248.035.6
A2023-09-012023248.035.6
A2023-06-012023136.035.6
A2023-11-112023323.035.6
B2024-05-072024323.033.0
B2023-09-072023248.033.0
B2024-02-022024248.033.0
B2024-03-162024323.033.0
B2024-04-012024323.033.0
C2024-04-212024323.028.2
C2023-10-102023323.028.2
C2023-12-112023323.028.2
C2024-02-042024136.028.2
C2024-02-112024136.028.2
-- 分组字段和排序字段不一致时, 会先分组, 再根据排序字段分组累计求平均值; 
-- 如下: 先计算 A+2023 平均值, 到2024时计算 A+2023+2024 平均值
SELECT customer_id, order_date, `year`, product_id, product_price
    , AVG(product_price) OVER(PARTITION BY customer_id ORDER BY `year`) AS price_avg 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
  • 5
customer_idorder_dateyearproduct_idproduct_priceprice_avg
A2023-09-012023248.035.666666666666664
A2023-06-012023136.035.666666666666664
A2023-11-112023323.035.666666666666664
A2024-01-012024248.035.6
A2024-04-142024323.035.6
B2023-09-072023248.048.0
B2024-05-072024323.033.0
B2024-02-022024248.033.0
B2024-03-162024323.033.0
B2024-04-012024323.033.0
C2023-10-102023323.023.0
C2023-12-112023323.023.0
C2024-04-212024323.028.2
C2024-02-042024136.028.2
C2024-02-112024136.028.2
2. COUNT
该函数用于计算记录数。
输入值类型:数值、字符串类型或BOOLEAN类型。
返回值类型:BIGINT。
  • 1
  • 2
  • 3
-- 2. COUNT:该函数用于计算记录数。
SELECT customer_id, order_date, `year`, product_id, product_price
    , COUNT(product_id) OVER(PARTITION BY customer_id, `year` ORDER BY `year`) AS product_ct 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_priceproduct_ct
A2023-09-012023248.03
A2023-06-012023136.03
A2023-11-112023323.03
A2024-01-012024248.02
A2024-04-142024323.02
B2023-09-072023248.01
B2024-05-072024323.04
B2024-02-022024248.04
B2024-03-162024323.04
B2024-04-012024323.04
C2023-10-102023323.02
C2023-12-112023323.02
C2024-04-212024323.03
C2024-02-042024136.03
C2024-02-112024136.03
3. MAX
该函数用于计算最大值。
输入值类型:该函数支持输入任意类型的参数,但是BOOLEAN类型的数据不允许参与运算。
返回值类型:与该函数的输入值类型保持一致。

注意:当列中的值为NULL时,该行不参与计算。
  • 1
  • 2
  • 3
  • 4
  • 5
-- 3. MAX:该函数用于计算最大值。
SELECT customer_id, order_date, `year`, product_id, product_price
    , MAX(product_price) OVER(PARTITION BY customer_id ORDER BY customer_id) AS product_ct 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_priceproduct_ct
A2024-04-142024323.048.0
A2024-01-012024248.048.0
A2023-09-012023248.048.0
A2023-06-012023136.048.0
A2023-11-112023323.048.0
B2024-05-072024323.048.0
B2023-09-072023248.048.0
B2024-02-022024248.048.0
B2024-03-162024323.048.0
B2024-04-012024323.048.0
C2024-04-212024323.036.0
C2023-10-102023323.036.0
C2023-12-112023323.036.0
C2024-02-042024136.036.0
C2024-02-112024136.036.0
4. MIN
该函数用于计算最小值。
输入值类型:该函数支持输入任意类型的参数,但是BOOLEAN类型的数据不允许参与运算。
返回值类型:与该函数的输入值类型保持一致。

注意:当列中的值为NULL时,该行不参与计算。
  • 1
  • 2
  • 3
  • 4
  • 5
-- 4. MIN:该函数用于计算最小值。
SELECT customer_id, order_date, `year`, product_id, product_price
    , MIN(product_price) OVER(PARTITION BY customer_id ORDER BY customer_id) AS product_ct 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_priceproduct_ct
A2024-04-142024323.023.0
A2024-01-012024248.023.0
A2023-09-012023248.023.0
A2023-06-012023136.023.0
A2023-11-112023323.023.0
B2024-05-072024323.023.0
B2023-09-072023248.023.0
B2024-02-022024248.023.0
B2024-03-162024323.023.0
B2024-04-012024323.023.0
C2024-04-212024323.023.0
C2023-10-102023323.023.0
C2023-12-112023323.023.0
C2024-02-042024136.023.0
C2024-02-112024136.023.0
5. 标准差
STDDEV:返回数值的总体标准差。
STDDEV_SAMP:返回一组数值(整数、小数或浮点)的样本标准差。
样本标准差=方差的算术平方根=s=sqrt(((x1-x)^2 +(x2-x)^2 +......(xn-x)^2)/(n-1))
总体标准差=σ=sqrt(((x1-x)^2 +(x2-x)^2 +......(xn-x)^2)/n )
  • 1
  • 2
  • 3
  • 4
-- 5. 标准差。
SELECT customer_id, order_date, `year`, product_id, product_price
    , STDDEV(product_price) OVER(PARTITION BY customer_id ORDER BY customer_id) AS price_std
    , STDDEV_SAMP(product_price) OVER(PARTITION BY customer_id ORDER BY customer_id) AS price_std_samp 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
  • 5
customer_idorder_dateyearproduct_idproduct_priceprice_stdprice_std_samp
A2024-04-142024323.011.1821285987954912.501999840025595
A2024-01-012024248.011.1821285987954912.501999840025595
A2023-09-012023248.011.1821285987954912.501999840025595
A2023-06-012023136.011.1821285987954912.501999840025595
A2023-11-112023323.011.1821285987954912.501999840025595
B2024-05-072024323.012.2474487139158913.693063937629153
B2023-09-072023248.012.2474487139158913.693063937629153
B2024-02-022024248.012.2474487139158913.693063937629153
B2024-03-162024323.012.2474487139158913.693063937629153
B2024-04-012024323.012.2474487139158913.693063937629153
C2024-04-212024323.06.3686733312362647.12039324756716
C2023-10-102023323.06.3686733312362647.12039324756716
C2023-12-112023323.06.3686733312362647.12039324756716
C2024-02-042024136.06.3686733312362647.12039324756716
C2024-02-112024136.06.3686733312362647.12039324756716
6. SUM
该函数用于计算汇总值。
输入值类型:BIGINT、DOUBLE或FLOAT。
返回值类型:BIGINT。
  • 1
  • 2
  • 3
-- 6. SUM:该函数用于计算汇总值。
SELECT customer_id, order_date, `year`, product_id, product_price
    , SUM(product_price) OVER(PARTITION BY customer_id, `year` ORDER BY `year`) AS price_sum
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_priceprice_sum
A2023-09-012023248.0107.0
A2023-06-012023136.0107.0
A2023-11-112023323.0107.0
A2024-01-012024248.071.0
A2024-04-142024323.071.0
B2023-09-072023248.048.0
B2024-05-072024323.0117.0
B2024-02-022024248.0117.0
B2024-03-162024323.0117.0
B2024-04-012024323.0117.0
C2023-10-102023323.046.0
C2023-12-112023323.046.0
C2024-04-212024323.095.0
C2024-02-042024136.095.0
C2024-02-112024136.095.0
3. 排序函数
CUME_DIST:返回一组数值中每个值的累计分布。
RANK:返回数据集中每个值的排名。
DENSE_RANK:返回一组数值中每个数值的排名。
ROW_NUMBER:根据行在窗口分区内的顺序,为每行数据返回一个唯一的有序行号,行号从1开始。
PERCENT_RANK:返回数据集中每个数据的排名百分比,其结果由(r - 1) / (n - 1)计算得出。其中r为RANK()计算的当前行排名, n为当前窗口分区内总的行数。
NTILE:将每个窗口分区的数据分散到桶号从1到n的n个桶中。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

语法

function over (partition by a order by b RANGE|ROWS BETWEEN start AND end)       
  • 1
分区规范:用于将输入行分散到不同的分区中,过程和GROUP BY子句的分散过程相似。
排序规范:决定输入数据行在窗口函数中执行的顺序。
窗口区间:指定计算数据的窗口边界。
窗口区间支持RANGE、ROWS两种模式:
	RANGE按照计算列值的范围进行定义。
	ROWS按照计算列的行数进行范围定义。
	RANGE、ROWS中可以使用BETWEEN start AND end指定边界可取值。BETWEEN start AND end取值为:
		CURRENT ROW,当前行。
		N PRECEDING,前n行。
		UNBOUNDED PRECEDING,直到第1行。
		N FOLLOWING,后n行。
		UNBOUNDED FOLLOWING,直到最后1行。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
1. CUME_DIST
返回一组数值中每个值的累计分布。
返回结果:在窗口分区中对窗口进行排序后的数据集,包括当前行和当前行之前的数据行数。排序中任何关联值均会计算成相同的分布值。
返回值类型:DOUBLE。
  • 1
  • 2
  • 3
-- 1. CUME_DIST:返回一组数值中每个值的累计分布。
SELECT customer_id, order_date, `year`, product_id, product_price
    , CUME_DIST() OVER(PARTITION BY customer_id ORDER BY order_date) AS cume_dist 
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_pricecume_dist
A2023-06-012023136.00.2
A2023-09-012023248.00.4
A2023-11-112023323.00.6
A2024-01-012024248.00.8
A2024-04-142024323.01.0
B2023-09-072023248.00.2
B2024-02-022024248.00.4
B2024-03-162024323.00.6
B2024-04-012024323.00.8
B2024-05-072024323.01.0
C2023-10-102023323.00.2
C2023-12-112023323.00.4
C2024-02-042024136.00.6
C2024-02-112024136.00.8
C2024-04-212024323.01.0
2. RANK, DENSE_RANK, ROW_NUMBER
返回数据集中每个值的排名: RANK() 会跳过重复值; DENSE_RANK() 不会跳过重复值序号; ROW_NUMBER() 不包含重复值, 相同名次按顺序排列;
排名值是将当前行之前的行数加1,不包含当前行。因此,排序的关联值可能产生顺序上的空隙,而且这个排名会对每个窗口分区进行计算。
DENSE_RANK()与RANK()功能相似,但是DENSE_RANK()关联值不会产生顺序上的空隙。
返回值类型:BIGINT。
  • 1
  • 2
  • 3
  • 4
SELECT customer_id, product_price
    , RANK() OVER(PARTITION BY customer_id ORDER BY product_price) AS rk 
    , DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY product_price) AS d_rk 
    , ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY product_price) AS rn
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
  • 5
customer_idproduct_pricerkd_rkrn
A23.0111
A23.0112
A36.0323
A48.0434
A48.0435
B23.0111
B23.0112
B23.0113
B48.0424
B48.0425
C23.0111
C23.0112
C23.0113
C36.0424
C36.0425
3. PERCENT_RANK
命令说明:返回数据集中每个数据的排名百分比,其结果由(r - 1) / (n - 1)计算得出。其中,r为RANK()计算的当前行排名, n为当前窗口分区内总的行数。
返回值类型:DOUBLE。
  • 1
  • 2
SELECT customer_id, order_date, `year`, product_id, product_price
    , RANK() OVER(PARTITION BY customer_id ORDER BY product_price) AS rk 
    , PERCENT_RANK() OVER(PARTITION BY customer_id ORDER BY product_price) AS pr
FROM t_sales_info ;
  • 1
  • 2
  • 3
  • 4
customer_idorder_dateyearproduct_idproduct_pricerkpr
A2024-04-142024323.010.0
A2023-11-112023323.010.0
A2023-06-012023136.030.5
A2024-01-012024248.040.75
A2023-09-012023248.040.75
B2024-05-072024323.010.0
B2024-03-162024323.010.0
B2024-04-012024323.010.0
B2023-09-072023248.040.75
B2024-02-022024248.040.75
C2024-04-212024323.010.0
C2023-10-102023323.010.0
C2023-12-112023323.010.0
C2024-02-042024136.040.75
C2024-02-112024136.040.75
4. NTILE
命令说明:将每个窗口分区的数据分散到桶号从1到n的n个桶中。

桶号值最多间隔1,如果窗口分区中的数据行数不能均匀地分散到每一个桶中,则剩余值将从第1个桶开始,每1个桶分1行数据。例如,有6行数据和4个桶, 最终桶号值为1 1 2 2 3 4。

返回值类型:BIGINT。
  • 1
  • 2
  • 3
  • 4
  • 5
SELECT customer_id, order_date, `year`, product_id, product_price
    , NTILE(4) OVER(PARTITION BY `year` ORDER BY order_date) AS rk 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricerk
A2023-06-012023136.01
A2023-09-012023248.01
B2023-09-072023248.02
C2023-10-102023323.02
A2023-11-112023323.03
C2023-12-112023323.04
A2024-01-012024248.01
B2024-02-022024248.01
C2024-02-042024136.01
C2024-02-112024136.02
B2024-03-162024323.02
B2024-04-012024323.03
A2024-04-142024323.03
C2024-04-212024323.04
B2024-05-072024323.04
4. 值函数(偏移函数)
FIRST_VALUE:返回窗口分区第1行的值。
LAST_VALUE返回窗口分区最后1行的值。
LAG:返回窗口内距离当前行之前偏移offset后的值。
LEAD:返回窗口内距离当前行偏移offset后的值。
NTH_VALUE:返回窗口内偏移指定offset后的值,偏移量从1开始。
  • 1
  • 2
  • 3
  • 4
  • 5
1. FIRST_VALUE
命令说明:返回窗口分区第一行的值。

返回值类型:与输入参数类型相同。
  • 1
  • 2
  • 3
SELECT customer_id, order_date, `year`, product_id, product_price
    , FIRST_VALUE(product_id) OVER(PARTITION BY customer_id ORDER BY order_date) AS fv 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricefv
A2023-06-012023136.01
A2023-09-012023248.01
A2023-11-112023323.01
A2024-01-012024248.01
A2024-04-142024323.01
B2023-09-072023248.02
B2024-02-022024248.02
B2024-03-162024323.02
B2024-04-012024323.02
B2024-05-072024323.02
C2023-10-102023323.03
C2023-12-112023323.03
C2024-02-042024136.03
C2024-02-112024136.03
C2024-04-212024323.03
2. LAST_VALUE
命令说明:返回窗口分区最后一行的值。LAST_VALUE默认统计范围是 rows between unbounded preceding and current row,即取当前行数据与当前行之前的数据进行比较。如果像FIRST_VALUE那样直接在每行数据中显示最后一行数据,需要在 order by 条件的后面加上语句:rows between unbounded preceding and unbounded following。

返回值类型:与输入参数类型相同。
  • 1
  • 2
  • 3
SELECT customer_id, order_date, `year`, product_id, product_price
    , FIRST_VALUE(product_id) OVER(PARTITION BY customer_id ORDER BY order_date) AS lv 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricelv
A2023-06-012023136.01
A2023-09-012023248.02
A2023-11-112023323.03
A2024-01-012024248.02
A2024-04-142024323.03
B2023-09-072023248.02
B2024-02-022024248.02
B2024-03-162024323.03
B2024-04-012024323.03
B2024-05-072024323.03
C2023-10-102023323.03
C2023-12-112023323.03
C2024-02-042024136.01
C2024-02-112024136.01
C2024-04-212024323.03
SELECT customer_id, order_date, `year`, product_id, product_price
    , LAST_VALUE(product_id) OVER(PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN unbounded preceding AND unbounded following ) AS lv 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricelv
A2023-06-012023136.03
A2023-09-012023248.03
A2023-11-112023323.03
A2024-01-012024248.03
A2024-04-142024323.03
B2023-09-072023248.03
B2024-02-022024248.03
B2024-03-162024323.03
B2024-04-012024323.03
B2024-05-072024323.03
C2023-10-102023323.03
C2023-12-112023323.03
C2024-02-042024136.03
C2024-02-112024136.03
C2024-04-212024323.03
3. LAG
LAG(x[, offset[, default_value]])   

命令说明:返回窗口内距离当前行之前偏移offset后的值。
偏移量起始值是0,也就是当前数据行。偏移量可以是标量表达式,默认offset是1 。
如果偏移量的值是null或者大于窗口长度,则返回default_value;如果没有指定default_value,则返回null。
返回值类型:与输入参数类型相同。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
SELECT customer_id, order_date, `year`, product_id, product_price
    , LAG(product_id, 1, '无') OVER(PARTITION BY customer_id ORDER BY order_date) AS lag1 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricelag1
A2023-06-012023136.0
A2023-09-012023248.01
A2023-11-112023323.02
A2024-01-012024248.03
A2024-04-142024323.02
B2023-09-072023248.0
B2024-02-022024248.02
B2024-03-162024323.02
B2024-04-012024323.03
B2024-05-072024323.03
C2023-10-102023323.0
C2023-12-112023323.03
C2024-02-042024136.03
C2024-02-112024136.01
C2024-04-212024323.01
4. LEAD
LEAD(x[,offset[, default_value]])  

命令说明:返回窗口内距离当前行偏移offset后的值。

偏移量offset起始值是0,也就是当前数据行。偏移量可以是标量表达式,默认offset是1 。

如果偏移量的值是null或者大于窗口长度,则返回default_value;如果没有指定default_value,则返回null。

返回值类型:与输入参数类型相同。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
SELECT customer_id, order_date, `year`, product_id, product_price
    , LEAD(product_id, 1) OVER(PARTITION BY customer_id ORDER BY order_date) AS lead1 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricelead1
A2023-06-012023136.02
A2023-09-012023248.03
A2023-11-112023323.02
A2024-01-012024248.03
A2024-04-142024323.0\N
B2023-09-072023248.02
B2024-02-022024248.03
B2024-03-162024323.03
B2024-04-012024323.03
B2024-05-072024323.0\N
C2023-10-102023323.03
C2023-12-112023323.01
C2024-02-042024136.01
C2024-02-112024136.03
C2024-04-212024323.0\N
5. NTH_VALUE
NTH_VALUE(x, offset) 

命令说明:返回窗口内偏移指定offset后的值,偏移量从1开始。

如果偏移量offset是null或者大于窗口内值的个数,则返回null;如果偏移量offset为0或者负数,则系统提示报错。

返回值类型:与输入参数类型相同。
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
SELECT customer_id, order_date, `year`, product_id, product_price
    , NTH_VALUE(product_id, 2) OVER(PARTITION BY customer_id ORDER BY order_date) AS nv1 
FROM t_sales_info ;
  • 1
  • 2
  • 3
customer_idorder_dateyearproduct_idproduct_pricenv1
A2023-06-012023136.0\N
A2023-09-012023248.02
A2023-11-112023323.02
A2024-01-012024248.02
A2024-04-142024323.02
B2023-09-072023248.0\N
B2024-02-022024248.02
B2024-03-162024323.02
B2024-04-012024323.02
B2024-05-072024323.02
C2023-10-102023323.0\N
C2023-12-112023323.03
C2024-02-042024136.03
C2024-02-112024136.03
C2024-04-212024323.03
END
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/神奇cpp/article/detail/894268
推荐阅读
相关标签
  

闽ICP备14008679号