PostgreSQL中按指定ID与日期范围获取num列出现最频繁值

嘿，针对你这个需求——在指定日期范围和id下找出num列出现次数最多的值，我给你整理了一套实用的PostgreSQL解决方案，完全适配你的场景：

先理清楚需求核心 #

咱们要做的就是针对指定id+日期区间，找到num列的众数（也就是出现次数最多的值），如果有多个值次数相同，还能灵活处理（返回全部或其中一个）。

第一步：先统计每个num的出现次数 #

首先过滤出符合条件的数据，然后分组统计每个num的出现次数，这样能直观看到哪个num出现得最多：

SELECT 
    id,
    num,
    COUNT(*) AS occurrence_count
FROM your_table_name
WHERE 
    id = 'a' -- 指定你要查询的id
    AND date BETWEEN '2011-08-12T00:00:00.000Z' AND '2011-08-12T23:59:00.000Z' -- 日期范围
GROUP BY id, num
ORDER BY occurrence_count DESC;

这段代码会返回指定范围内每个num的出现次数，按次数从高到低排，一眼就能看到众数。

第二步：精准提取出现次数最多的num #

如果只需要拿到那个出现次数最多的num，用窗口函数RANK()或者ROW_NUMBER()来给结果排名，然后取第一名就好：

WITH num_occurrences AS (
    -- 先统计每个num的出现次数
    SELECT 
        id,
        num,
        COUNT(*) AS occurrence_count
    FROM your_table_name
    WHERE 
        id = 'a'
        AND date BETWEEN '2011-08-12T00:00:00.000Z' AND '2011-08-12T23:59:00.000Z'
    GROUP BY id, num
)
SELECT id, num
FROM (
    -- 给每个num按出现次数排名
    SELECT 
        id,
        num,
        RANK() OVER (PARTITION BY id ORDER BY occurrence_count DESC) AS rank_num
    FROM num_occurrences
) ranked_results
WHERE rank_num = 1;

• 用RANK()的话，如果有多个num出现次数相同（比如两个num都出现2次），会返回所有这些众数；
• 如果只需要返回其中一个，把RANK()换成ROW_NUMBER()就行，它会给每个记录一个唯一排名，即使次数相同也会选其中一个（顺序由数据库默认排序决定）。

适配你的示例结果 #

你的示例结果里还有个090518字段，看起来像是自定义的固定值？直接在SELECT里加上就行：

WITH num_occurrences AS (
    SELECT 
        id,
        num,
        COUNT(*) AS occurrence_count
    FROM your_table_name
    WHERE 
        id = 'a'
        AND date BETWEEN '2011-08-12T00:00:00.000Z' AND '2011-08-12T23:59:00.000Z'
    GROUP BY id, num
)
SELECT id, num, '090518' AS custom_code
FROM (
    SELECT 
        id,
        num,
        RANK() OVER (PARTITION BY id ORDER BY occurrence_count DESC) AS rank_num
    FROM num_occurrences
) ranked_results
WHERE rank_num = 1;

这样运行出来的结果就和你预期的完全一致啦。

额外小贴士 #

• 记得把your_table_name换成你实际的表名；
• 如果要同时处理多个id，删掉WHERE里的id = 'a'就行，窗口函数的PARTITION BY id会自动按每个id分组计算众数；
• 日期筛选也可以写成date >= '起始日期' AND date <= '结束日期'，和BETWEEN效果一样，看你习惯用哪种。

内容的提问来源于stack exchange，提问作者R. Junah