如何编写SQL查询实现按商品统计每日每秒最大订单量并转置展示

没问题，咱们一步步来实现你要的查询效果。核心需求是从原始的时间-商品表中，按商品维度统计每日每秒的最大订单量，并将日期作为列展示成宽表格式。

分步拆解与SQL实现 #

我们可以分三个步骤来完成，用CTE（公共表表达式）让逻辑更清晰：

1. 先统计每个商品每秒的订单量

首先把原始数据按「商品」、「日期（天）」、「秒级时间」分组，计算每一秒的订单数：

WITH per_second_counts AS (
    SELECT
        Items,
        CAST(date AS DATE) AS order_date, -- 提取日期部分（天）
        COUNT(*) AS orders_per_second
    FROM your_table_name -- 这里替换成你的实际表名
    GROUP BY Items, CAST(date AS DATE), DATE_TRUNC('second', date) -- 按秒分组统计
),

2. 计算每个商品每日的每秒最大订单量

基于上面的结果，按「商品」和「日期」分组，取当日每秒订单量的最大值：

daily_max AS (
    SELECT
        Items,
        order_date,
        MAX(orders_per_second) AS max_orders_per_second
    FROM per_second_counts
    GROUP BY Items, order_date
)

3. 转成指定的宽表格式

用条件聚合把日期转成列（兼容性更好，适配大多数数据库）：

SELECT
    Items,
    -- 针对每个日期，提取对应商品的最大订单量
    MAX(CASE WHEN order_date = '2021-07-07' THEN max_orders_per_second END) AS "2021-07-07",
    MAX(CASE WHEN order_date = '2021-07-08' THEN max_orders_per_second END) AS "2021-07-08"
FROM daily_max
GROUP BY Items
ORDER BY Items;

完整SQL代码 #

把上面的片段整合起来，最终完整的SQL是：

WITH per_second_counts AS (
    SELECT
        Items,
        CAST(date AS DATE) AS order_date,
        COUNT(*) AS orders_per_second
    FROM your_table_name
    GROUP BY Items, CAST(date AS DATE), DATE_TRUNC('second', date)
),
daily_max AS (
    SELECT
        Items,
        order_date,
        MAX(orders_per_second) AS max_orders_per_second
    FROM per_second_counts
    GROUP BY Items, order_date
)
SELECT
    Items,
    MAX(CASE WHEN order_date = '2021-07-07' THEN max_orders_per_second END) AS "2021-07-07",
    MAX(CASE WHEN order_date = '2021-07-08' THEN max_orders_per_second END) AS "2021-07-08"
FROM daily_max
GROUP BY Items
ORDER BY Items;

可选：用PIVOT语法（部分数据库支持） #

如果你的数据库支持PIVOT（比如SQL Server、BigQuery），也可以用更简洁的PIVOT写法：

WITH per_second_counts AS (
    SELECT
        Items,
        CAST(date AS DATE) AS order_date,
        COUNT(*) AS orders_per_second
    FROM your_table_name
    GROUP BY Items, CAST(date AS DATE), DATE_TRUNC('second', date)
),
daily_max AS (
    SELECT
        Items,
        order_date,
        MAX(orders_per_second) AS max_orders_per_second
    FROM per_second_counts
    GROUP BY Items, order_date
)
SELECT Items, [2021-07-07], [2021-07-08]
FROM daily_max
PIVOT (
    MAX(max_orders_per_second)
    FOR order_date IN ([2021-07-07], [2021-07-08])
) AS pivot_table
ORDER BY Items;

注意事项 #

• 记得把your_table_name替换成你实际的表名。
• 如果你的日期是动态的（比如不止这两天），动态转列需要用到数据库的动态SQL功能（比如PostgreSQL的crosstab、MySQL的预处理语句），固定日期的话上面的静态写法就足够了。

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