Postgres如何忽略缺失租户数据的分组并填充历史值

有一张Postgres表orders，结构及数据如下：

注意：12:00时段tenant2的orders_today数据尚未生成。

使用以下查询汇总当日订单数：

SELECT datetime, SUM(orders_today)
FROM orders
GROUP BY datetime

得到结果包含12:00的分组：

需要解决两个问题：

1. 如何让查询忽略存在租户数据缺失的12:00分组？
2. 能否用tenant2在11:00的orders_today值填充缺失项后再进行求和？

一、忽略存在租户缺失的分组 #

核心思路是：先确定所有租户的总数，再筛选出每个时段下租户数量等于总数的分组。

通用方案（适配租户数量变化的场景） #

WITH all_tenants AS (
    -- 获取所有唯一租户
    SELECT DISTINCT tenant_id FROM orders
)
SELECT o.datetime, SUM(o.orders_today)
FROM orders o
GROUP BY o.datetime
-- 只保留租户数量和总租户数一致的时段
HAVING COUNT(DISTINCT o.tenant_id) = (SELECT COUNT(*) FROM all_tenants);

简化方案（如果租户固定已知，比如只有tenant1和tenant2） #

SELECT datetime, SUM(orders_today)
FROM orders
GROUP BY datetime
-- 直接指定需要的租户数量
HAVING COUNT(DISTINCT tenant_id) = 2;

以上两种方案都会过滤掉12:00的分组，最终只返回10:00和11:00的求和结果。

二、用前一时段数值填充缺失项后求和 #

要实现填充，需要先生成所有时段与租户的完整组合，再用窗口函数补全缺失值，最后求和。

完整SQL实现 #

WITH all_datetimes AS (
    -- 获取所有唯一的时段
    SELECT DISTINCT datetime FROM orders
),
all_tenants AS (
    -- 获取所有唯一租户
    SELECT DISTINCT tenant_id FROM orders
),
full_combinations AS (
    -- 生成所有时段+租户的完整组合，确保每个时段每个租户都有记录
    SELECT ad.datetime, at.tenant_id
    FROM all_datetimes ad
    CROSS JOIN all_tenants at
),
filled_data AS (
    SELECT 
        fc.datetime,
        fc.tenant_id,
        -- 取当前租户最近的非空订单数填充缺失值
        LAST_VALUE(o.orders_today) OVER (
            PARTITION BY fc.tenant_id 
            ORDER BY fc.datetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS filled_orders
    FROM full_combinations fc
    LEFT JOIN orders o ON fc.datetime = o.datetime AND fc.tenant_id = o.tenant_id
)
-- 分组求和填充后的数据
SELECT datetime, SUM(filled_orders)
FROM filled_data
GROUP BY datetime;

结果说明 #

执行后12:00的求和结果会是5+2=7（tenant1的5加上tenant2从11:00继承的2），最终返回：

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