当前位置：首页 > news >正文

Mysql窗口函数

news 2025/7/27 21:04:04

WITH RankedData AS ( # WITH RankedData AS (...)：这是一个CTE，它创建了一个临时的结果集，名为RankedData。在这个CTE中，对fabalarm表执行了一个SELECT查询SELECT *,ROW_NUMBER() OVER (ORDER BY `time` DESC) AS rn,  # 按照时间降序 并给每条记录返回一个序号 该序号命名为rnCEIL(ROW_NUMBER() OVER (ORDER BY   `time` DESC) / 10.0) AS grp  # 再降序排列并分配序号 然后每个序号对10向上取整 得到的值作为组序号 命名为grpFROM fabalarm
), # 此时每条记录将增加两列，rn与grp，rn为按照time降序排列产生的序号，grp为按照time降序排列后每10个一组的组编号
FilteredData AS ( # 再次创建一个临时结果集SELECT *,ROW_NUMBER() OVER (PARTITION BY grp ORDER BY `time` DESC) AS rn_in_grp  FROM RankedData# 从临时结果集RankedData里查询，以grp分组，组内按照时间降序排序，得到相同的grp内的编号rn_in_grp
)
SELECT *
FROM FilteredDataWHERE rn_in_grp = 1;

第一个Select单独执行时得到的查询结果（RankedData ）如下：

SELECT *,ROW_NUMBER() OVER (ORDER BY `time` DESC) AS rn, CEIL(ROW_NUMBER() OVER (ORDER BY   `time` DESC) / 10.0) AS grp  FROM fabalarm

在这里插入图片描述
第一个和第二个select查询结果（FilteredData ）如下：

WITH RankedData AS (SELECT *,ROW_NUMBER() OVER (ORDER BY `time` DESC) AS rn, CEIL(ROW_NUMBER() OVER (ORDER BY   `time` DESC) / 10.0) AS grp  FROM fabalarm
),
FilteredData AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY grp ORDER BY `time` DESC) AS rn_in_grp  FROM RankedData
)
SELECT *
FROM FilteredData

在这里插入图片描述

SELECT *,
ROW_NUMBER() OVER (ORDER BY time DESC) AS rn,
CEIL(ROW_NUMBER() OVER (ORDER BY time DESC) / 10.0) AS grp
FROM fabalarm

ROW_NUMBER() OVER为窗口函数
用法为
ROW_NUMBER() OVER(PARTITION BY column 1 RDER BY column2)

表示按照column1来分组，即column1的值相同的行为一组，组内通过column2 来排序
例如表：
在这里插入图片描述
执行SELECT *, ROW_NUMBER() OVER (PARTITION BY sales.category ORDER BY sales.quantity ASC) FROM sales
查询结果

可以发现查询结果有ROW_NUMNER() OVER列，该列对同一组内根据quantity 升序分配了序号