2025年sql窗口函数（SQL窗口函数有哪些）

大家好，我是讯享网，很高兴认识大家。

 <svg xmlns="http://www.w3.org/2000/svg" style="display: none;"> <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path> </svg>

讯享网

1. 介绍

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

2. 聚合函数

讯享网

0.数据准备

1. AVG

讯享网

customer_id order_date year product_id product_price price_avg A 2024-04-14 2024 3 23.0 35.6 A 2024-01-01 2024 2 48.0 35.6 A 2023-09-01 2023 2 48.0 35.6 A 2023-06-01 2023 1 36.0 35.6 A 2023-11-11 2023 3 23.0 35.6 B 2024-05-07 2024 3 23.0 33.0 B 2023-09-07 2023 2 48.0 33.0 B 2024-02-02 2024 2 48.0 33.0 B 2024-03-16 2024 3 23.0 33.0 B 2024-04-01 2024 3 23.0 33.0 C 2024-04-21 2024 3 23.0 28.2 C 2023-10-10 2023 3 23.0 28.2 C 2023-12-11 2023 3 23.0 28.2 C 2024-02-04 2024 1 36.0 28.2 C 2024-02-11 2024 1 36.0 28.2

讯享网

customer_id order_date year product_id product_price price_avg A 2023-09-01 2023 2 48.0 35.6664 A 2023-06-01 2023 1 36.0 35.6664 A 2023-11-11 2023 3 23.0 35.6664 A 2024-01-01 2024 2 48.0 35.6 A 2024-04-14 2024 3 23.0 35.6 B 2023-09-07 2023 2 48.0 48.0 B 2024-05-07 2024 3 23.0 33.0 B 2024-02-02 2024 2 48.0 33.0 B 2024-03-16 2024 3 23.0 33.0 B 2024-04-01 2024 3 23.0 33.0 C 2023-10-10 2023 3 23.0 23.0 C 2023-12-11 2023 3 23.0 23.0 C 2024-04-21 2024 3 23.0 28.2 C 2024-02-04 2024 1 36.0 28.2 C 2024-02-11 2024 1 36.0 28.2

2. COUNT

讯享网

customer_id order_date year product_id product_price product_ct A 2023-09-01 2023 2 48.0 3 A 2023-06-01 2023 1 36.0 3 A 2023-11-11 2023 3 23.0 3 A 2024-01-01 2024 2 48.0 2 A 2024-04-14 2024 3 23.0 2 B 2023-09-07 2023 2 48.0 1 B 2024-05-07 2024 3 23.0 4 B 2024-02-02 2024 2 48.0 4 B 2024-03-16 2024 3 23.0 4 B 2024-04-01 2024 3 23.0 4 C 2023-10-10 2023 3 23.0 2 C 2023-12-11 2023 3 23.0 2 C 2024-04-21 2024 3 23.0 3 C 2024-02-04 2024 1 36.0 3 C 2024-02-11 2024 1 36.0 3

3. MAX

讯享网

customer_id order_date year product_id product_price product_ct A 2024-04-14 2024 3 23.0 48.0 A 2024-01-01 2024 2 48.0 48.0 A 2023-09-01 2023 2 48.0 48.0 A 2023-06-01 2023 1 36.0 48.0 A 2023-11-11 2023 3 23.0 48.0 B 2024-05-07 2024 3 23.0 48.0 B 2023-09-07 2023 2 48.0 48.0 B 2024-02-02 2024 2 48.0 48.0 B 2024-03-16 2024 3 23.0 48.0 B 2024-04-01 2024 3 23.0 48.0 C 2024-04-21 2024 3 23.0 36.0 C 2023-10-10 2023 3 23.0 36.0 C 2023-12-11 2023 3 23.0 36.0 C 2024-02-04 2024 1 36.0 36.0 C 2024-02-11 2024 1 36.0 36.0

4. MIN

讯享网

customer_id order_date year product_id product_price product_ct A 2024-04-14 2024 3 23.0 23.0 A 2024-01-01 2024 2 48.0 23.0 A 2023-09-01 2023 2 48.0 23.0 A 2023-06-01 2023 1 36.0 23.0 A 2023-11-11 2023 3 23.0 23.0 B 2024-05-07 2024 3 23.0 23.0 B 2023-09-07 2023 2 48.0 23.0 B 2024-02-02 2024 2 48.0 23.0 B 2024-03-16 2024 3 23.0 23.0 B 2024-04-01 2024 3 23.0 23.0 C 2024-04-21 2024 3 23.0 23.0 C 2023-10-10 2023 3 23.0 23.0 C 2023-12-11 2023 3 23.0 23.0 C 2024-02-04 2024 1 36.0 23.0 C 2024-02-11 2024 1 36.0 23.0

5. 标准差

讯享网

customer_id order_date year product_id product_price price_std price_std_samp A 2024-04-14 2024 3 23.0 11.549 12.5595 A 2024-01-01 2024 2 48.0 11.549 12.5595 A 2023-09-01 2023 2 48.0 11.549 12.5595 A 2023-06-01 2023 1 36.0 11.549 12.5595 A 2023-11-11 2023 3 23.0 11.549 12.5595 B 2024-05-07 2024 3 23.0 12.589 13.9153 B 2023-09-07 2023 2 48.0 12.589 13.9153 B 2024-02-02 2024 2 48.0 12.589 13.9153 B 2024-03-16 2024 3 23.0 12.589 13.9153 B 2024-04-01 2024 3 23.0 12.589 13.9153 C 2024-04-21 2024 3 23.0 6.6264 7.716 C 2023-10-10 2023 3 23.0 6.6264 7.716 C 2023-12-11 2023 3 23.0 6.6264 7.716 C 2024-02-04 2024 1 36.0 6.6264 7.716 C 2024-02-11 2024 1 36.0 6.6264 7.716

6. SUM

讯享网

customer_id order_date year product_id product_price price_sum A 2023-09-01 2023 2 48.0 107.0 A 2023-06-01 2023 1 36.0 107.0 A 2023-11-11 2023 3 23.0 107.0 A 2024-01-01 2024 2 48.0 71.0 A 2024-04-14 2024 3 23.0 71.0 B 2023-09-07 2023 2 48.0 48.0 B 2024-05-07 2024 3 23.0 117.0 B 2024-02-02 2024 2 48.0 117.0 B 2024-03-16 2024 3 23.0 117.0 B 2024-04-01 2024 3 23.0 117.0 C 2023-10-10 2023 3 23.0 46.0 C 2023-12-11 2023 3 23.0 46.0 C 2024-04-21 2024 3 23.0 95.0 C 2024-02-04 2024 1 36.0 95.0 C 2024-02-11 2024 1 36.0 95.0

3. 排序函数

语法

讯享网

1. CUME_DIST

讯享网

customer_id order_date year product_id product_price cume_dist A 2023-06-01 2023 1 36.0 0.2 A 2023-09-01 2023 2 48.0 0.4 A 2023-11-11 2023 3 23.0 0.6 A 2024-01-01 2024 2 48.0 0.8 A 2024-04-14 2024 3 23.0 1.0 B 2023-09-07 2023 2 48.0 0.2 B 2024-02-02 2024 2 48.0 0.4 B 2024-03-16 2024 3 23.0 0.6 B 2024-04-01 2024 3 23.0 0.8 B 2024-05-07 2024 3 23.0 1.0 C 2023-10-10 2023 3 23.0 0.2 C 2023-12-11 2023 3 23.0 0.4 C 2024-02-04 2024 1 36.0 0.6 C 2024-02-11 2024 1 36.0 0.8 C 2024-04-21 2024 3 23.0 1.0

2. RANK, DENSE_RANK, ROW_NUMBER

讯享网

customer_id product_price rk d_rk rn A 23.0 1 1 1 A 23.0 1 1 2 A 36.0 3 2 3 A 48.0 4 3 4 A 48.0 4 3 5 B 23.0 1 1 1 B 23.0 1 1 2 B 23.0 1 1 3 B 48.0 4 2 4 B 48.0 4 2 5 C 23.0 1 1 1 C 23.0 1 1 2 C 23.0 1 1 3 C 36.0 4 2 4 C 36.0 4 2 5

3. PERCENT_RANK

讯享网

customer_id order_date year product_id product_price rk pr A 2024-04-14 2024 3 23.0 1 0.0 A 2023-11-11 2023 3 23.0 1 0.0 A 2023-06-01 2023 1 36.0 3 0.5 A 2024-01-01 2024 2 48.0 4 0.75 A 2023-09-01 2023 2 48.0 4 0.75 B 2024-05-07 2024 3 23.0 1 0.0 B 2024-03-16 2024 3 23.0 1 0.0 B 2024-04-01 2024 3 23.0 1 0.0 B 2023-09-07 2023 2 48.0 4 0.75 B 2024-02-02 2024 2 48.0 4 0.75 C 2024-04-21 2024 3 23.0 1 0.0 C 2023-10-10 2023 3 23.0 1 0.0 C 2023-12-11 2023 3 23.0 1 0.0 C 2024-02-04 2024 1 36.0 4 0.75 C 2024-02-11 2024 1 36.0 4 0.75

4. NTILE

讯享网

customer_id order_date year product_id product_price rk A 2023-06-01 2023 1 36.0 1 A 2023-09-01 2023 2 48.0 1 B 2023-09-07 2023 2 48.0 2 C 2023-10-10 2023 3 23.0 2 A 2023-11-11 2023 3 23.0 3 C 2023-12-11 2023 3 23.0 4 A 2024-01-01 2024 2 48.0 1 B 2024-02-02 2024 2 48.0 1 C 2024-02-04 2024 1 36.0 1 C 2024-02-11 2024 1 36.0 2 B 2024-03-16 2024 3 23.0 2 B 2024-04-01 2024 3 23.0 3 A 2024-04-14 2024 3 23.0 3 C 2024-04-21 2024 3 23.0 4 B 2024-05-07 2024 3 23.0 4

4. 值函数(偏移函数)

讯享网

1. FIRST_VALUE

讯享网

customer_id order_date year product_id product_price fv A 2023-06-01 2023 1 36.0 1 A 2023-09-01 2023 2 48.0 1 A 2023-11-11 2023 3 23.0 1 A 2024-01-01 2024 2 48.0 1 A 2024-04-14 2024 3 23.0 1 B 2023-09-07 2023 2 48.0 2 B 2024-02-02 2024 2 48.0 2 B 2024-03-16 2024 3 23.0 2 B 2024-04-01 2024 3 23.0 2 B 2024-05-07 2024 3 23.0 2 C 2023-10-10 2023 3 23.0 3 C 2023-12-11 2023 3 23.0 3 C 2024-02-04 2024 1 36.0 3 C 2024-02-11 2024 1 36.0 3 C 2024-04-21 2024 3 23.0 3

2. LAST_VALUE

讯享网

customer_id order_date year product_id product_price lv A 2023-06-01 2023 1 36.0 1 A 2023-09-01 2023 2 48.0 2 A 2023-11-11 2023 3 23.0 3 A 2024-01-01 2024 2 48.0 2 A 2024-04-14 2024 3 23.0 3 B 2023-09-07 2023 2 48.0 2 B 2024-02-02 2024 2 48.0 2 B 2024-03-16 2024 3 23.0 3 B 2024-04-01 2024 3 23.0 3 B 2024-05-07 2024 3 23.0 3 C 2023-10-10 2023 3 23.0 3 C 2023-12-11 2023 3 23.0 3 C 2024-02-04 2024 1 36.0 1 C 2024-02-11 2024 1 36.0 1 C 2024-04-21 2024 3 23.0 3

customer_id order_date year product_id product_price lv A 2023-06-01 2023 1 36.0 3 A 2023-09-01 2023 2 48.0 3 A 2023-11-11 2023 3 23.0 3 A 2024-01-01 2024 2 48.0 3 A 2024-04-14 2024 3 23.0 3 B 2023-09-07 2023 2 48.0 3 B 2024-02-02 2024 2 48.0 3 B 2024-03-16 2024 3 23.0 3 B 2024-04-01 2024 3 23.0 3 B 2024-05-07 2024 3 23.0 3 C 2023-10-10 2023 3 23.0 3 C 2023-12-11 2023 3 23.0 3 C 2024-02-04 2024 1 36.0 3 C 2024-02-11 2024 1 36.0 3 C 2024-04-21 2024 3 23.0 3

3. LAG

讯享网

customer_id order_date year product_id product_price lag1 A 2023-06-01 2023 1 36.0 无 A 2023-09-01 2023 2 48.0 1 A 2023-11-11 2023 3 23.0 2 A 2024-01-01 2024 2 48.0 3 A 2024-04-14 2024 3 23.0 2 B 2023-09-07 2023 2 48.0 无 B 2024-02-02 2024 2 48.0 2 B 2024-03-16 2024 3 23.0 2 B 2024-04-01 2024 3 23.0 3 B 2024-05-07 2024 3 23.0 3 C 2023-10-10 2023 3 23.0 无 C 2023-12-11 2023 3 23.0 3 C 2024-02-04 2024 1 36.0 3 C 2024-02-11 2024 1 36.0 1 C 2024-04-21 2024 3 23.0 1

4. LEAD

讯享网

customer_id order_date year product_id product_price lead1 A 2023-06-01 2023 1 36.0 2 A 2023-09-01 2023 2 48.0 3 A 2023-11-11 2023 3 23.0 2 A 2024-01-01 2024 2 48.0 3 A 2024-04-14 2024 3 23.0 N B 2023-09-07 2023 2 48.0 2 B 2024-02-02 2024 2 48.0 3 B 2024-03-16 2024 3 23.0 3 B 2024-04-01 2024 3 23.0 3 B 2024-05-07 2024 3 23.0 N C 2023-10-10 2023 3 23.0 3 C 2023-12-11 2023 3 23.0 1 C 2024-02-04 2024 1 36.0 1 C 2024-02-11 2024 1 36.0 3 C 2024-04-21 2024 3 23.0 N

5. NTH_VALUE

讯享网

customer_id order_date year product_id product_price nv1 A 2023-06-01 2023 1 36.0 N A 2023-09-01 2023 2 48.0 2 A 2023-11-11 2023 3 23.0 2 A 2024-01-01 2024 2 48.0 2 A 2024-04-14 2024 3 23.0 2 B 2023-09-07 2023 2 48.0 N B 2024-02-02 2024 2 48.0 2 B 2024-03-16 2024 3 23.0 2 B 2024-04-01 2024 3 23.0 2 B 2024-05-07 2024 3 23.0 2 C 2023-10-10 2023 3 23.0 N C 2023-12-11 2023 3 23.0 3 C 2024-02-04 2024 1 36.0 3 C 2024-02-11 2024 1 36.0 3 C 2024-04-21 2024 3 23.0 3