先说说,我们之前在mysql5.x喜欢用的方式,以下代码是不是很熟悉
select * from xxx where aa=xxx group by id order by create_time desc
有一天突然发现,我们平常用的,习惯的sql运行结果和我们记忆中的不一样,完蛋是不是我sql写错了,多少人开始怀疑自己了,哈哈,其实是大版本升级,语法进行优化,那么现在用什么方式得到之前想要的结果呢
函数名 | 参数 | 描述 |
cume_dist() | 否 | 累计分布值。即分组值小于等于当前值的行数与分组总行数的比值。取值范围为(0,1]。 |
dense_rank() | 否 | 不间断的组内排序。使用这个函数时,可以出现1,1,2,2这种形式的分组。 |
first_value(expr) | 是 | 返回分组内截止当前行的第一个值。 |
lag(expr,[N,[default]]) | 是 | 从当前行开始往前取第N行,如果N缺失默认为1。若没有没有,则默认返回default。default默认值为NULL |
last_value(expr) | 是 | 返回分组内截止当前行的最后一个值。 |
lead(expr,[N,[default]]) | 是 | 从当前行开始往后取第N行。函数功能与lag()相反,其余与lag()相同。 |
nth_value(expr,N) | 是 | 返回分组内截止当前行的第N行。first_value\last_value\nth_value函数功能相似,只是返回分组内截止当前行的不同行号的数据。 |
ntile(N) | 是 | 返回当前行在分组内的分桶号。在计算时要先将改分组内的所有数据划分成N个桶,之后返回每个记录所在的分桶号。返回范围从1到N |
percent_rank() | 否 | 累计百分比。该函数的计算结果为:小于该条记录值的所有记录的行数/该分组的总行数-1. 所以改记录的返回值为[0,1] |
rank() | 否 | 间断的组内排序。其排序结果可能出现如下结果:1,1,3,4,4,6 |
row_number() | 否 | 当前行在其分组内的序号。不管其排序结果中是否出现重复值,其排序结果都为:1,2,3,4,5 |
窗口函数的基本用法格式如下:
函数名 ([expr]) over子句
over关键字指定函数窗口的范围,如果省略后面括号中的内容,则窗口会包含满足WHERE条件的所有记录,窗口函数会基于所有满足WHERE条件的记录进行计算。如果over关键字后面的括号不为空,则可以使用如下语法设置窗口。
- window_name:为窗口设置一个别名,用来标识窗口。
- PARTITION BY子句:指定窗口函数按照哪些字段进行分组。分组后,窗口函数可以在每个分组中分别执行。
- ORDER BY子句:指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
- FRAME子句:为分区中的某个子集定义规则,可以用来作为滑动窗口使用。
创建t_goods数据表为例,向t_goods数据表中插入数据。
mysql> INSERT INTO t_goods (t_category_id, t_category, t_name, t_price, t_stock, t_upper_time) VALUES -> (1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00'), -> (1, '女装/女士精品', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00'), -> (1, '女装/女士精品', '卫衣', 89.90, 1500, '2020-11-10 00:00:00'), -> (1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00'), -> (1, '女装/女士精品', '百褶裙', 29.90, 500, '2020-11-10 00:00:00'), -> (1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00'), -> (2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00'), -> (2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00'), -> (2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00'), -> (2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00'), -> (2, '户外运动', '运动外套', 799.90, 500, '2020-11-10 00:00:00'), -> (2, '户外运动', '滑板', 499.90, 1200, '2020-11-10 00:00:00'); Query OK, 12 rows affected (0.01 sec) Records: 12 Duplicates: 0 Warnings: 0
下面针对t_goods表中的数据来验证每个窗口函数的功能。
序号函数
1.ROW_NUMBER()函数
ROW_NUMBER()函数能够对数据中的序号进行顺序显示。
例如,查询t_goods数据表中每个商品分类下价格最高的3种商品信息。
mysql> SELECT * FROM -> ( -> SELECT -> ROW_NUMBER() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num, -> id, t_category_id, t_category, t_name, t_price, t_stock -> FROM t_goods) t -> WHERE row_num <= 3; +---------+----+---------------+---------------------+-----------------+---------+---------+ | row_num | id | t_category_id | t_category | t_name | t_price | t_stock | +---------+----+---------------+---------------------+-----------------+---------+---------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | +---------+----+---------------+---------------------+-----------------+---------+---------+ 6 rows in set (0.00 sec)
在名称为“女装/女士精品”的商品类别中,有两款商品的价格为89.90元,分别是卫衣和牛仔裤。两款商品的序号都应该为2,而不是一个为2,另一个为3。此时,可以使用RANK()函数和DENSE_RANK()函数解决。
2.RANK()函数
使用RANK()函数能够对序号进行并列排序,并且会跳过重复的序号,比如序号为1、1、3。
例如,使用RANK()函数获取t_goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
mysql> SELECT * FROM -> ( -> SELECT -> RANK() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num, -> id, t_category_id, t_category, t_name, t_price, t_stock -> FROM t_goods) t -> WHERE t_category_id = 1 AND row_num <= 4; +---------+----+---------------+---------------------+--------------+---------+---------+ | row_num | id | t_category_id | t_category | t_name | t_price | t_stock | +---------+----+---------------+---------------------+--------------+---------+---------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | +---------+----+---------------+---------------------+--------------+---------+---------+ 4 rows in set (0.00 sec)
可以看到,使用RANK()函数得出的序号为1、2、2、4,相同价格的商品序号相同,后面的商品序号是不连续的,跳过了重复的序号。
3.DENSE_RANK()函数
DENSE_RANK()函数对序号进行并列排序,并且不会跳过重复的序号,比如序号为1、1、2。
例如,使用DENSE_RANK()函数获取t_goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息。
mysql> SELECT * FROM -> ( -> SELECT -> DENSE_RANK() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS row_num, -> id, t_category_id, t_category, t_name, t_price, t_stock -> FROM t_goods) t -> WHERE t_category_id = 1 AND row_num <= 3; +---------+----+---------------+---------------------+--------------+---------+---------+ | row_num | id | t_category_id | t_category | t_name | t_price | t_stock | +---------+----+---------------+---------------------+--------------+---------+---------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | +---------+----+---------------+---------------------+--------------+---------+---------+ 4 rows in set (0.00 sec)
可以看到,使用DENSE_RANK()函数得出的行号为1、2、2、3,相同价格的商品序号相同,后面的商品序号是连续的,并且没有跳过重复的序号。
2 分布函数
1.PERCENT_RANK()函数
PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。
(rank - 1) / (rows - 1)
其中,rank的值为使用RANK()函数产生的序号,rows的值为当前窗口的总记录数。
例如,计算t_goods数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值。
mysql> SELECT -> RANK() OVER w AS r, -> PERCENT_RANK() OVER w AS pr, -> id, t_category_id, t_category, t_name, t_price, t_stock -> FROM t_goods -> WHERE t_category_id = 1 -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price DESC); +---+-----+----+---------------+---------------------+--------------+---------+---------+ | r | pr | id | t_category_id | t_category | t_name | t_price | t_stock | +---+-----+----+---------------+---------------------+--------------+---------+---------+ | 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | +---+-----+----+---------------+---------------------+--------------+---------+---------+ 6 rows in set (0.00 sec)
2.CUME_DIST()函数
CUME_DIST()函数主要用于查询小于或等于某个值的比例。
例如,查询t_goods数据表中小于或等于当前价格的比例。
mysql> SELECT -> CUME_DIST() OVER(PARTITION BY t_category_id ORDER BY t_price DESC) AS cd, -> id, t_category, t_name, t_price -> FROM t_goods; +---------------------+----+---------------------+-----------------+---------+ | cd | id | t_category | t_name | t_price | +---------------------+----+---------------------+-----------------+---------+ | 0.16666666666666666 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | | 0.5 | 3 | 女装/女士精品 | 卫衣 | 89.90 | | 0.5 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | | 0.6666666666666666 | 2 | 女装/女士精品 | 连衣裙 | 79.90 | | 0.8333333333333334 | 1 | 女装/女士精品 | T恤 | 39.90 | | 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 | | 0.16666666666666666 | 8 | 户外运动 | 山地自行车 | 1399.90 | | 0.3333333333333333 | 11 | 户外运动 | 运动外套 | 799.90 | | 0.5 | 12 | 户外运动 | 滑板 | 499.90 | | 0.8333333333333334 | 7 | 户外运动 | 自行车 | 399.90 | | 0.8333333333333334 | 10 | 户外运动 | 骑行装备 | 399.90 | | 1 | 9 | 户外运动 | 登山杖 | 59.90 | +---------------------+----+---------------------+-----------------+---------+ 12 rows in set (0.01 sec)
前后函数
1.LAG(expr,n)函数
LAG(expr,n)函数返回当前行的前n行的expr的值。
例如,查询t_goods数据表中前一个商品价格与当前商品价格的差值。
mysql> SELECT id, t_category, t_name, t_price, pre_price, -> t_price - pre_price AS diff_price -> FROM ( -> SELECT id, t_category, t_name, t_price, -> LAG(t_price,1) OVER w AS pre_price -> FROM t_goods -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price)) t; +----+---------------------+-----------------+---------+-----------+------------+ | id | t_category | t_name | t_price | pre_price | diff_price | +----+---------------------+-----------------+---------+-----------+------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL | | 1 | 女装/女士精品 | T恤 | 39.90 | 29.90 | 10.00 | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 40.00 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 79.90 | 10.00 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 89.90 | 0.00 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 89.90 | 310.00 | | 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL | | 7 | 户外运动 | 自行车 | 399.90 | 59.90 | 340.00 | | 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 0.00 | | 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 100.00 | | 11 | 户外运动 | 运动外套 | 799.90 | 499.90 | 300.00 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 799.90 | 600.00 | +----+---------------------+-----------------+---------+-----------+------------+ 12 rows in set (0.00 sec)
2.LEAD(expr,n)函数
LEAD(expr,n)函数返回当前行的后n行的expr的值。
例如,查询t_goods数据表中后一个商品价格与当前商品价格的差值。
mysql> SELECT id, t_category, t_name, behind_price, t_price, -> behind_price - t_price AS diff_price -> FROM( -> SELECT id, t_category, t_name, t_price, -> LEAD(t_price, 1) OVER w AS behind_price -> FROM t_goods -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price)) t; +----+---------------------+-----------------+--------------+---------+------------+ | id | t_category | t_name | behind_price | t_price | diff_price | +----+---------------------+-----------------+--------------+---------+------------+ | 5 | 女装/女士精品 | 百褶裙 | 39.90 | 29.90 | 10.00 | | 1 | 女装/女士精品 | T恤 | 79.90 | 39.90 | 40.00 | | 2 | 女装/女士精品 | 连衣裙 | 89.90 | 79.90 | 10.00 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 89.90 | 0.00 | | 4 | 女装/女士精品 | 牛仔裤 | 399.90 | 89.90 | 310.00 | | 6 | 女装/女士精品 | 呢绒外套 | NULL | 399.90 | NULL | | 9 | 户外运动 | 登山杖 | 399.90 | 59.90 | 340.00 | | 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 0.00 | | 10 | 户外运动 | 骑行装备 | 499.90 | 399.90 | 100.00 | | 12 | 户外运动 | 滑板 | 799.90 | 499.90 | 300.00 | | 11 | 户外运动 | 运动外套 | 1399.90 | 799.90 | 600.00 | | 8 | 户外运动 | 山地自行车 | NULL | 1399.90 | NULL | +----+---------------------+-----------------+--------------+---------+------------+ 12 rows in set (0.00 sec)
首尾函数
1.FIRST_VALUE(expr)函数
FIRST_VALUE(expr)函数返回第一个expr的值。
例如,按照价格排序,查询第1个商品的价格信息。
mysql> SELECT id, t_category, t_name, t_price, t_stock, -> FIRST_VALUE(t_price) OVER w AS first_price -> FROM t_goods -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price); +----+---------------------+-----------------+---------+---------+-------------+ | id | t_category | t_name | t_price | t_stock | first_price | +----+---------------------+-----------------+---------+---------+-------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 | | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 29.90 | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 29.90 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 29.90 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 29.90 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 29.90 | | 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 | | 7 | 户外运动 | 自行车 | 399.90 | 1000 | 59.90 | | 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 59.90 | | 12 | 户外运动 | 滑板 | 499.90 | 1200 | 59.90 | | 11 | 户外运动 | 运动外套 | 799.90 | 500 | 59.90 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 59.90 | +----+---------------------+-----------------+---------+---------+-------------+ 12 rows in set (0.00 sec)
2.LAST_VALUE(expr)函数
LAST_VALUE(expr)函数返回最后一个expr的值。
例如,按照价格排序,查询最后一个商品的价格信息。
mysql> SELECT id, t_category, t_name, t_price, t_stock, -> LAST_VALUE(t_price) OVER w AS last_price -> FROM t_goods -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price); +----+---------------------+-----------------+---------+---------+------------+ | id | t_category | t_name | t_price | t_stock | last_price | +----+---------------------+-----------------+---------+---------+------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 | | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 39.90 | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 79.90 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 89.90 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 89.90 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 399.90 | | 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 | | 7 | 户外运动 | 自行车 | 399.90 | 1000 | 399.90 | | 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 399.90 | | 12 | 户外运动 | 滑板 | 499.90 | 1200 | 499.90 | | 11 | 户外运动 | 运动外套 | 799.90 | 500 | 799.90 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 1399.90 | +----+---------------------+-----------------+---------+---------+------------+ 12 rows in set (0.00 sec)
其他函数
1.NTH_VALUE(expr,n)函数
NTH_VALUE(expr,n)函数返回第n个expr的值。
例如,查询t_goods数据表中排名第3和第4的价格信息。
mysql> SELECT id, t_category, t_name, t_price, -> NTH_VALUE(t_price,2) OVER w AS second_price, -> NTH_VALUE(t_price,3) OVER w AS third_price -> FROM t_goods -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price); +----+---------------------+-----------------+---------+--------------+-------------+ | id | t_category | t_name | t_price | second_price | third_price | +----+---------------------+-----------------+---------+--------------+-------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL | | 1 | 女装/女士精品 | T恤 | 39.90 | 39.90 | NULL | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 79.90 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 39.90 | 79.90 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 39.90 | 79.90 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 39.90 | 79.90 | | 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL | | 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 399.90 | | 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 399.90 | | 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 399.90 | | 11 | 户外运动 | 运动外套 | 799.90 | 399.90 | 399.90 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 399.90 | 399.90 | +----+---------------------+-----------------+---------+--------------+-------------+ 12 rows in set (0.00 sec)
2.NTILE(n)函数
NTILE(n)函数将分区中的有序数据分为n个桶,记录桶编号。
例如,将t_goods表中的商品按照价格分为3组。
mysql> SELECT -> NTILE(3) OVER w AS nt, -> id, t_category, t_name, t_price -> FROM t_goods -> WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price); +------+----+---------------------+-----------------+---------+ | nt | id | t_category | t_name | t_price | +------+----+---------------------+-----------------+---------+ | 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 | | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | | 2 | 2 | 女装/女士精品 | 连衣裙 | 79.90 | | 2 | 3 | 女装/女士精品 | 卫衣 | 89.90 | | 3 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | | 3 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | | 1 | 9 | 户外运动 | 登山杖 | 59.90 | | 1 | 7 | 户外运动 | 自行车 | 399.90 | | 2 | 10 | 户外运动 | 骑行装备 | 399.90 | | 2 | 12 | 户外运动 | 滑板 | 499.90 | | 3 | 11 | 户外运动 | 运动外套 | 799.90 | | 3 | 8 | 户外运动 | 山地自行车 | 1399.90 | +------+----+---------------------+-----------------+---------+ 12 rows in set (0.00 sec)
来点自己其他的一下
计算一段时间内头尾数据进行差值计算
SELECT bds_id, nbq_id, fetch_time, q_cumulative_output, (nbq_data.last_output - nbq_data.first_output) AS q_output_daily, nbq_data.last_output, nbq_data.first_output FROM ( SELECT bds_id, nbq_id, q_cumulative_output, fetch_time, FIRST_VALUE( q_cumulative_output ) OVER ( PARTITION BY nbq_id ORDER BY `fetch_time` ASC ) AS first_output, LAST_VALUE( q_cumulative_output ) OVER ( PARTITION BY nbq_id ORDER BY `fetch_time` ASC ) AS last_output, ROW_NUMBER() over ( PARTITION BY nbq_id ORDER BY `fetch_time` DESC ) AS q_order FROM gffp_2022.znyw_data_nbq_series_9_2 WHERE bds_id = '46432F6736384B078C12AE515AAF0F48' AND fetch_time BETWEEN '2022-09-02 00:00:00' AND '2022-09-02 23:59:00' ) nbq_data WHERE nbq_data.q_order =1