如何基于PostgreSQL租户订单变更数据生成最新订单求和时间序列

我有一个PostgreSQL表，当每个租户（tenant）的订单数（orders）发生变化时，会插入相应记录。表结构及数据如下：

注：tenant3的第一条记录来自前一天，且租户数量是动态的。

能否基于以上数据生成小时级时间序列结果，计算每个时间点所有租户的最新订单数之和？期望结果如下：

可以通过以下SQL语句实现需求，自动适配动态租户数量：

WITH hourly_times AS (
    -- 生成目标日期的所有整点时间序列
    SELECT generate_series(
        '2023-09-16 00:00:00'::timestamp,
        '2023-09-16 23:00:00'::timestamp,
        '1 hour'::interval
    ) AS hour_time
),
tenant_order_changes AS (
    -- 标记每个租户订单数的生效区间
    SELECT
        tenant_id,
        datetime,
        orders,
        -- 获取当前记录的下一次变化时间，确定生效截止点
        LEAD(datetime) OVER (PARTITION BY tenant_id ORDER BY datetime) AS next_change_time
    FROM your_table_name -- 替换为你的实际表名
),
tenant_hourly_data AS (
    -- 匹配每个时间点对应的租户最新订单数
    SELECT
        ht.hour_time,
        toc.tenant_id,
        toc.orders
    FROM hourly_times ht
    LEFT JOIN tenant_order_changes toc
        ON ht.hour_time >= toc.datetime
        AND (ht.hour_time < toc.next_change_time OR toc.next_change_time IS NULL)
)
-- 按时间点聚合求和
SELECT
    hour_time AS datetime,
    COALESCE(SUM(orders), 0) AS sum
FROM tenant_hourly_data
GROUP BY hour_time
ORDER BY hour_time;

逻辑说明 #

1. hourly_times：生成目标日期的24个整点时间，确保覆盖当天所有需要统计的时间点。
2. tenant_order_changes：用LEAD窗口函数获取每个租户下一次订单数变化的时间，从而确定当前订单数的生效区间为[datetime, next_change_time)。
3. tenant_hourly_data：将时间序列与租户的订单生效区间关联，找到每个时间点下每个租户的有效订单数。
4. 最后按时间分组求和，COALESCE用于处理无数据场景（确保求和结果不为NULL）。

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