如何筛选每个月中timestamp为最大值的对应行？

以下方案基于标准SQL语法编写，可根据你使用的数据库类型调整对应日期处理函数即可使用。 #

需求1：单独查询每个月对应的最大timestamp值 #

这个需求直接用分组聚合即可实现，核心逻辑是按年月维度分组后取timestamp字段的最大值：

-- 通用模板，<年月提取函数>替换为对应数据库的函数，your_table替换为实际表名
SELECT
  <年月提取函数> AS stat_month,
  MAX(`timestamp`) AS max_timestamp_of_month
FROM your_table
GROUP BY stat_month
ORDER BY stat_month;

不同数据库常用的年月提取函数参考：

• MySQL：DATE_FORMAT(timestamp, '%Y-%m')
• PostgreSQL：TO_CHAR(DATE_TRUNC('month', "timestamp"), 'YYYY-MM')
• SQL Server：FORMAT([timestamp], 'yyyy-MM')

以MySQL为例的可运行示例：

SELECT
  DATE_FORMAT(`timestamp`, '%Y-%m') AS stat_month,
  MAX(`timestamp`) AS max_timestamp_of_month
FROM your_table
GROUP BY stat_month
ORDER BY stat_month;

需求2：筛选出每个月内timestamp为最大值的所有对应行 #

如果存在同一个月内有多行数据的timestamp都等于当月最大值的情况，推荐用下面两种方案实现：

方案A：窗口函数（推荐，支持所有主流新版本数据库） #

用RANK()窗口函数做分组排名，所有当月timestamp最大的行排名都会是1，不会漏数据：

WITH timestamp_rank_per_month AS (
  SELECT
    *,
    RANK() OVER(
      PARTITION BY <年月提取函数>
      ORDER BY `timestamp` DESC
    ) AS rk
  FROM your_table
)
-- 过滤出排名为1的就是当月timestamp最大的所有行
SELECT * FROM timestamp_rank_per_month WHERE rk = 1;

注意不要用ROW_NUMBER()替代RANK()，前者在遇到多个相同最大值时只会随机返回1行，不符合需求。

方案B：子查询关联（兼容不支持窗口函数的低版本数据库） #

先通过分组聚合拿到每个月的最大timestamp，再和原表做关联匹配即可：

SELECT t1.*
FROM your_table t1
INNER JOIN (
  -- 先查每个月的最大timestamp
  SELECT
    <年月提取函数> AS stat_month,
    MAX(`timestamp`) AS max_ts
  FROM your_table
  GROUP BY stat_month
) t2
ON <年月提取函数(t1.`timestamp`)> = t2.stat_month
AND t1.`timestamp` = t2.max_ts;

通用注意事项 #

• 如果timestamp字段存在空值，建议提前加WHERE timestamp IS NOT NULL过滤，避免结果异常
• 可根据实际需求在最终查询结果里去掉不需要的辅助列（比如窗口函数生成的rk列）

内容的提问来源于stack exchange，提问作者hiro