曲沃网站建设,wordpress手动裁剪,wordpress 文章美化,网站建设的步骤过程文库窗口函数概念常用窗口函数聚合窗口函数专用窗口函数语法OVER子句window_specwindow_name (命名窗口)partition_clause 分区order_clause 排序frame_clause 范围 #xff08;指定窗口大小#xff09;使用限制练习准备概念 窗口函数对一组查询执行类似于聚合的操作。然而#…
窗口函数概念常用窗口函数聚合窗口函数专用窗口函数语法OVER子句window_specwindow_name (命名窗口)partition_clause 分区order_clause 排序frame_clause 范围 指定窗口大小使用限制练习准备概念 窗口函数对一组查询执行类似于聚合的操作。然而聚合操作将查询行分组为单个结果行而窗口函数为每个查询行生成一个结果 查询中的每个窗口操作都通过包含一个OVER子句来表示该子句指定如何将查询行划分为组以供窗口函数处理 第一个OVER子句为空它将整个查询行集视为一个分区。窗口函数因此产生一个全局计算结果但对每一行都这样做。第二个OVER子句按对应的分区生成每个组的计算结果。该函数为每个分组行生成次计算结果 窗口函数只允许在选择列表(类似于需要显示的字段)和ORDER BY子句中使用。查询结果行由FROM 子句确定在WHERE、GROUP BY和HAVING处理之后窗口执行发生在ORDER BY、LIMIT和SELECT DISTINCT之前。 常用窗口函数
聚合窗口函数
AVG() – 窗口内平均数COUNT() – 窗口内数据MAX() – 窗口内最大值MIN() – 窗口内最小值SUM() – 窗口内求和 当聚合函数带有OVER()子句是则该函数为聚合窗口函数 专用窗口函数
ROW_NUMBER() ---- 行号RANK() · ---- 分区内的排名有间隙间隙指同排名会继续往后排DENSE_RANK() ---- 分区内的排名没有间隙PERCENT_RANK() ---- 分区内的百分比排名没有间隙LAG() ---- 分区内向上平移LEAD() ---- 分区内向下平移NTILE() ---- 分区平均的划分 得到的当前行是第几桶FIRST_VALUE() ---- 窗口内的第一行LAST_VALUE() ---- 窗口内的最后一行NTH_VALUE() ---- 窗口内第几行的数据 专用窗口函数必须要有OVER()子句 分区与窗口的区别 分区为 partition by 执行过后的所以行 称之为分区 如果没有partition by 则所有行称之为分区 当我们的分区加上了 rows|range 他们指定的范围内的数据称之为窗口 语法
OVER子句
# 有两种形式定义OVER 子句
over_clause:{OVER (window_spec) | OVER window_name}window_spec
# 以下几个部分都是可选的
window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]如果OVER() 为空则分区由所有查询行组成窗口函数使用所有行计算结果。否则括号内的子句确认哪些查询行用于计算函数结果以及他们如何分区、窗口和排序 window_name (命名窗口) 可以定义窗口并为他指定名称以便在OVER子句中引用它们使用WINDOW关键字定义WINDOW子句位于 HAVING 和ORDER BY之间 WINDOW window_name as(window_spec)[,window_name as (window_spec)]...SELECTDISTINCT year, country,# 可以继续添加 window_name 中没有的子句FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first, FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);window_name可以互相引用但是不能循环引用
partition_clause 分区 一个PARTITION BY子句 指示如何将查询行分区。给定行的窗口函数结果基于包含该行的分区的行查询。 如果PARTITION BY忽略则存在所有查询行组成的单个分区 PARTITION BY 语法
PARTITION BY expr [,expr1] ...order_clause 排序
order_clause:ORDER BY expr [ASC|DESC] [,expr1 [ASC|DESC]] ...frame_clause 范围 指定窗口大小
frame_clause:frame_units frame_extentframe_units:{ROWS | RANGE}frame_extent:{frame_start | frame_between}frame_between:BETWEEN frame_start AND frame_end
FOLLOWING
frame_start, frame_end: {CURRENT ROW -- 当前行| UNBOUNDED PRECEDING -- 之前所有含当前行| UNBOUNDED FOLLOWING -- 之后所有含当前行| expr PRECEDING -- 当前行 之前的expr行 不包含当前行| expr FOLLOWING -- 当前行 之后的expr行 不包含当前行
}如果没有 frame_clause 则会取决于order_clause是否存在 存在 则当前分区的开始到当前行 包含当前行 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 不存在 则默认为当前分区的所有行 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 使用限制
不支持在UPDATE orDELETE中使用不允许嵌套窗口
练习
准备
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (1, 可比克, 食品, a, 10, 2022-01-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (2, 乐事, 食品, b, 15, 2022-01-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (3, 布洛芬, 药品, a, 50, 2022-02-11);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (4, 999感冒灵, 药品, b, 20, 2022-03-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (5, 连花清瘟胶囊, 药品, c, 100, 2022-04-03);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (6, 扫把, 日用品, b, 10, 2022-05-05);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (7, 拖把, 日用品, b, 20, 2022-06-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (8, 口罩, 日用品, c, 50, 2022-07-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (9, 德芙, 食品, c, 120, 2022-08-05);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (10, 镜子, 日用品, c, 55, 2022-09-04);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (11, 卫龙, 食品, a, 8, 2022-10-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (12, 方便面, 食品, b, 5, 2022-11-01);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (13, 阿莫西林, 药品, b, 20, 2022-12-11);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (14, 青霉素, 药品, b, 20, 2022-03-11);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (15, 眼药水, 药品, c, 15, 2022-04-23);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (16, 桌子, 日用品, a, 150, 2022-05-15);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (17, 凳子, 日用品, b, 30, 2022-06-21);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (18, 笔, 日用品, c, 5, 2022-07-11);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (19, 热干面, 食品, a, 5, 2022-08-24);
INSERT INTO test.pay_record (id, goods_name, goods_type, user_name, pay_money, pay_time) VALUES (20, 水壶, 日用品, b, 50, 2022-09-27);----------------------------------------------------------------------
| id | goods_name | goods_type | user_name | pay_money | pay_time |
----------------------------------------------------------------------
| 1 | 可比克 | 食品 | a | 10 | 2022-01-01 |
| 2 | 乐事 | 食品 | b | 15 | 2022-01-01 |
| 3 | 布洛芬 | 药品 | a | 50 | 2022-02-11 |
| 4 | 999感冒灵 | 药品 | b | 20 | 2022-03-01 |
| 5 | 连花清瘟胶囊 | 药品 | c | 100 | 2022-04-03 |
| 6 | 扫把 | 日用品 | b | 10 | 2022-05-05 |
| 7 | 拖把 | 日用品 | b | 20 | 2022-06-01 |
| 8 | 口罩 | 日用品 | c | 50 | 2022-07-01 |
| 9 | 德芙 | 食品 | c | 120 | 2022-08-05 |
| 10 | 镜子 | 日用品 | c | 55 | 2022-09-04 |
| 11 | 卫龙 | 食品 | a | 8 | 2022-10-01 |
| 12 | 方便面 | 食品 | b | 5 | 2022-11-01 |
| 13 | 阿莫西林 | 药品 | b | 20 | 2022-12-11 |
| 14 | 青霉素 | 药品 | b | 20 | 2022-03-11 |
| 15 | 眼药水 | 药品 | c | 15 | 2022-04-23 |
| 16 | 桌子 | 日用品 | a | 150 | 2022-05-15 |
| 17 | 凳子 | 日用品 | b | 30 | 2022-06-21 |
| 18 | 笔 | 日用品 | c | 5 | 2022-07-11 |
| 19 | 热干面 | 食品 | a | 5 | 2022-08-24 |
| 20 | 水壶 | 日用品 | b | 50 | 2022-09-27 |
----------------------------------------------------------------------
20 rows in set (0.02 sec)1.计算每个用户在各类商品的支付金额和所有支付金额
聚合窗口函数我们使用sum()来说明如何使用
SELECT# 利用OVER(partition by) 子句按照用户名分区 计算总金额* , SUM(sum_type_money) over(partition by user_name) as sum_money
FROM# 首先查询出每个用户在各类商品的支付金额 ( SELECT user_name, goods_type, sum( pay_money ) sum_type_money FROM pay_record GROUP BY user_name, goods_type ORDER BY user_name ) a;--------------------------------------------------
| user_name | goods_type | sum_type_money | sum_money |
--------------------------------------------------
| a | 日用品 | 150 | 223 |
| a | 药品 | 50 | 223 |
| a | 食品 | 23 | 223 |
| b | 日用品 | 110 | 190 |
| b | 药品 | 60 | 190 |
| b | 食品 | 20 | 190 |
| c | 日用品 | 110 | 345 |
| c | 药品 | 115 | 345 |
| c | 食品 | 120 | 345 |
--------------------------------------------------
9 rows in set (0.01 sec)2.计算每个用户购买日用品花费的金额及排名(升序排列)
SELECTuser_name,sum( pay_money ),# 行号ROW_NUMBER() over ( group_goods_type_order_sum_type_money ) AS money_row_number,# 排名 有相同名次的情况 会跳数字RANK() over ( group_goods_type_order_sum_type_money ) AS money_rank,# 排名 有相同名次的情况 不会跳数字DENSE_RANK() over ( group_goods_type_order_sum_type_money ) AS money_dense_rank,# 排名 百分比排名 返回分区内小于当前行值的百分比 (rank - 1) / (rows - 1)PERCENT_RANK() over ( group_goods_type_order_sum_type_money ) AS money_percent_rank
FROMpay_record
WHEREgoods_type 日用品 GROUP BY# WINDOW 定义 命名窗口 上面有重复使用的时候 可以直接引用user_name WINDOW group_goods_type_order_sum_type_money AS (PARTITION BY goods_type ORDER BYsum( pay_money ));-------------------------------------------------------------------------------------------------
| user_name | sum( pay_money ) | money_row_number | money_rank | money_dense_rank | money_percent_rank |
-------------------------------------------------------------------------------------------------
| b | 110 | 1 | 1 | 1 | 0 |
| c | 110 | 2 | 1 | 1 | 0 |
| a | 150 | 3 | 3 | 2 | 1 |
-------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)3.查询支付金额排名 占前 30% 的购买记录
SELECT*
FROM# 把整个分区当做一个组去分成 10 份( SELECT *, NTILE( 10 ) over ( ORDER BY pay_money DESC ) LEVEL FROM pay_record ) a
WHERELEVEL 3-----------------------------------------------------------------------------
| id | goods_name | goods_type | user_name | pay_money | pay_time | LEVEL |
-----------------------------------------------------------------------------
| 16 | 桌子 | 日用品 | a | 150 | 2022-05-15 | 1 |
| 9 | 德芙 | 食品 | c | 120 | 2022-08-05 | 1 |
| 5 | 连花清瘟胶囊 | 药品 | c | 100 | 2022-04-03 | 2 |
| 10 | 镜子 | 日用品 | c | 55 | 2022-09-04 | 2 |
| 3 | 布洛芬 | 药品 | a | 50 | 2022-02-11 | 3 |
| 8 | 口罩 | 日用品 | c | 50 | 2022-07-01 | 3 |
-----------------------------------------------------------------------------
6 rows in set (0.00 sec)4.查询a用户 第二次后面的商品次数、第一次购买商品的时间及最后一次购买商品的时间
SELECTpay_time,# 窗口内的第2行数据NTH_VALUE( pay_time, 2 ) over ( ORDER BY pay_time rows BETWEEN unbounded preceding AND unbounded following ) twoRow,# 窗口内的第一行数据FIRST_VALUE( pay_time ) over ( ORDER BY pay_time rows BETWEEN unbounded preceding AND unbounded following ) firstRow,# 窗口内的最后一行数据LAST_VALUE( pay_time ) over ( ORDER BY pay_time rows BETWEEN unbounded preceding AND unbounded following ) lastRow
FROMpay_record
WHEREuser_name a------------------------------------------------
| pay_time | twoRow | firstRow | lastRow |
------------------------------------------------
| 2022-01-01 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-02-11 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-05-15 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-08-24 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
| 2022-10-01 | 2022-02-11 | 2022-01-01 | 2022-10-01 |
------------------------------------------------# 5.查询两次购买时间间隔最长的天数用户
SELECT*,DATEDIFF( pay_time, following_pay_time ) paytime_diff,RANK() over ( ORDER BY DATEDIFF( pay_time, following_pay_time ) DESC ) paytime_diff_rank
FROM(SELECTuser_name,pay_time,# 分区内当前行的下一行数据lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time desc ) following_pay_time, # 分区内当前行的上一行数据lag( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time desc) preceding_pay_time FROMpay_record ) a limit 1------------------------------------------------------------------------------------------------
| user_name | pay_time | following_pay_time | preceding_pay_time | paytime_diff | paytime_diff_rank |
------------------------------------------------------------------------------------------------
| a | 2022-08-24 | 2022-05-15 | 2022-10-01 | 101 | 1 |
------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
