如何实现单日按小时统计销量Top5商品的SQL查询？

我需要生成一份报表，统计单日里每个小时时段的销量Top5商品，但之前写的SQL只能算出当天整体销量Top5的商品，没法按小时拆分出各时段的Top5。以下是我的具体情况：

原SQL语句（仅能统计单日总销量Top5） #

Select count(*) as SALES_COUNT, PT.name, EXTRACT(HOUR FROM T.created) as hour 
FROM PRODUCT_TBL P 
INNER JOIN TRANSACTION_TBL T on T.PID = P.ID 
WHERE T.created <= '2018-05-22 23:50:58' AND T.created >= '2018-05-22 00:00:00' 
AND P.ID IN (
    Select P.PID 
    FROM PRODUCT_TBL P 
    INNER JOIN TRANSACTION_TBL T on T.PID = P.ID 
    WHERE T.created <= '2018-05-22 23:50:58' AND T.created >= '2018-05-22 00:00:00' 
    GROUP BY PT.ID, order by COUNT(*) desc LIMIT 5
) 
GROUP BY PT.name, hour 
ORDER BY hour asc, TRANSACTION_COUNT

示例数据 #

TRANSACTION_TBL #

PRODUCT_TBL #

预期输出 #

解决方案：用窗口函数实现按小时分组取Top5 #

原来的SQL问题在于先取了单日总销量的Top5，再按小时统计这些商品的销量，这和需求完全相反。我们需要的是先按小时统计每个商品的销量，再在每个小时组里取Top5，这里用窗口函数就能完美解决。

优化后的SQL #

WITH hourly_sales AS (
    -- 第一步：统计当天每个小时、每个商品的销量
    SELECT 
        p.name AS Product,
        COUNT(*) AS SALES_COUNT,
        EXTRACT(HOUR FROM t.created) AS HOUR
    FROM TRANSACTION_TBL t
    JOIN PRODUCT_TBL p ON t.PID = p.ID
    -- 限定日期为2018-05-22当天
    WHERE t.created >= '2018-05-22 00:00:00' 
      AND t.created < '2018-05-23 00:00:00'
    GROUP BY p.name, EXTRACT(HOUR FROM t.created)
),
ranked_sales AS (
    -- 第二步：按小时分组，给每个商品的销量排名
    SELECT 
        *,
        -- 按小时分区，销量降序排名，取前5
        ROW_NUMBER() OVER (PARTITION BY HOUR ORDER BY SALES_COUNT DESC) AS sales_rank
    FROM hourly_sales
)
-- 第三步：筛选每个小时排名前5的商品
SELECT Product, SALES_COUNT, HOUR
FROM ranked_sales
WHERE sales_rank <= 5
ORDER BY HOUR ASC, SALES_COUNT DESC;

关键细节说明 #

1. CTE hourly_sales：先把当天所有交易按小时和商品分组，算出每个商品在对应小时的销量，这是我们的基础统计数据。
2. 窗口函数ROW_NUMBER()：用PARTITION BY HOUR把数据按小时分成不同的组，然后在每个组内按销量从高到低排序，给每个商品分配一个排名。
   • 如果需要处理销量相同的商品并列排名的情况，可以替换成：
     • RANK()：相同销量的商品排名相同，后续排名会跳过（比如1,1,3）
     • DENSE_RANK()：相同销量的商品排名相同，后续排名不跳过（比如1,1,2）
3. 最后筛选出每个小时排名≤5的记录，按小时和销量排序，就得到了我们想要的按小时Top5报表。

适配示例数据的结果 #

运行这段SQL后，会和你的预期输出完全一致：

• 16点时段：P1（4单）、P2（3单）、P3（1单）都进入Top5（该时段只有这三个商品有交易）
• 18点时段：P5（2单）、P6（1单）、P7（1单）进入Top5（该时段只有这三个商品有交易）

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