如何查询每月单日Count字段的最大值记录

直接按月份分组只能得到每月的最大Count值，但无法关联到对应的日期，这里提供两种常用的窗口函数解决方案：

方法1：使用ROW_NUMBER()获取唯一最大值记录 #

该方法会在同月有多个相同最大值时，按指定规则（比如日期倒序）选取其中一条记录：

SELECT "Date", "Count"
FROM (
    SELECT 
        "Date", 
        "Count",
        -- 按年月分区，Count降序、日期降序分配行号
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', "Date") 
            ORDER BY "Count" DESC, "Date" DESC
        ) AS rn
    FROM your_table
) t
WHERE rn = 1;

方法2：使用RANK()保留所有最大值记录 #

如果同月存在多条Count等于最大值的记录，该方法会返回全部符合条件的记录：

SELECT "Date", "Count"
FROM (
    SELECT 
        "Date", 
        "Count",
        -- 按年月分区，Count降序排名
        RANK() OVER (
            PARTITION BY DATE_TRUNC('month', "Date") 
            ORDER BY "Count" DESC
        ) AS rnk
    FROM your_table
) t
WHERE rnk = 1;

补充说明 #

• DATE_TRUNC('month', "Date") 用于将日期截断到当月第一天，以此作为分组依据，避免跨年同月份的混淆；也可以用 EXTRACT(YEAR_MONTH FROM "Date") 替代，直接生成如202201这样的年月标识。
• 窗口函数的PARTITION BY负责按月份分组，ORDER BY决定组内的排序规则，确保最大值排在最前面。

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