PostgreSQL按时间查询客户维度下商品的最大聚合库存数量

要实现按客户维度统计各商品在对应时间点的最大聚合数量（即该时间点客户所有账户下该商品的最新数量之和），可以通过以下步骤的SQL查询完成：

标准SQL实现（兼容多数数据库） #

WITH account_item_history AS (
    -- 为每个客户的账户-商品组合按时间倒序标记记录
    SELECT
        client_code,
        account,
        item,
        quantity,
        timestamp,
        ROW_NUMBER() OVER (PARTITION BY client_code, account, item ORDER BY timestamp DESC) AS rn
    FROM your_table_name
),
all_time_points AS (
    -- 获取所有发生过数量更新的时间点
    SELECT DISTINCT timestamp AS event_time FROM your_table_name
),
latest_account_quantity AS (
    -- 计算每个时间点，各账户-商品的最新数量
    SELECT
        h.client_code,
        h.item,
        tp.event_time,
        h.account,
        FIRST_VALUE(h.quantity) OVER (
            PARTITION BY h.client_code, h.account, h.item, tp.event_time
            ORDER BY h.timestamp DESC
        ) AS latest_qty
    FROM account_item_history h
    CROSS JOIN all_time_points tp
    WHERE h.timestamp <= tp.event_time
),
client_item_totals AS (
    -- 按客户、商品、时间点聚合总数量
    SELECT
        client_code,
        item,
        event_time AS timestamp,
        SUM(latest_qty) AS total_quantity
    FROM latest_account_quantity
    GROUP BY client_code, item, event_time
),
max_aggregate_records AS (
    -- 筛选每个客户-商品组合的最大聚合数量记录
    SELECT
        client_code,
        item,
        timestamp,
        total_quantity,
        ROW_NUMBER() OVER (
            PARTITION BY client_code, item 
            ORDER BY total_quantity DESC, timestamp DESC
        ) AS rn
    FROM client_item_totals
)
-- 最终结果：每个客户-商品的最大聚合数量及对应时间点
SELECT
    client_code,
    item,
    timestamp,
    total_quantity
FROM max_aggregate_records
WHERE rn = 1;

逻辑说明 #

1. account_item_history：为每个客户的「账户-商品」组合的记录按时间倒序编号，方便后续筛选最新记录。
2. all_time_points：提取表中所有发生过数量更新的时间点，作为统计的时间维度。
3. latest_account_quantity：通过交叉连接所有时间点，对每个时间点筛选出该时间点及之前，每个「客户-账户-商品」的最新数量。
4. client_item_totals：按客户、商品、时间点聚合，得到每个时间点的商品总数量。
5. max_aggregate_records：对每个「客户-商品」组合，按总数量降序、时间降序排序，取第一条即为最大聚合数量的对应记录。

针对示例数据的验证 #

用你提供的示例数据（假设client_code统一为c1），查询会返回：

若存在多个时间点总数量相同的情况，会取最新的时间点。

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