PostgreSQL中如何对列数据求和且避免重复统计

有一个每小时运行一次的任务，会插入过去24小时的交易总数量，表结构如下：

需要编写一条SQL查询来对datavalue求和，确保任何时间都能得到准确的交易总数，避免重复统计——因为每条记录的数值都包含了前一条记录的大部分数据。只能以初始交易计数（seeded data）作为计算基础，无法修改任务本身或其运行频率。

补充说明：

• ID为1的记录是初始数据（seeded data）。
• 每条"job data"类型的记录都包含过去24小时的交易数，每小时插入一次，新记录的大部分数据已包含在之前的记录中。
• 示例：
  • 记录ID2的150000中，23/24的数据已包含在初始值中，未统计的交易数为150000 - (150000 * (23/24)) = 6250，截至该记录的总交易数为1006250。
  • 记录ID3的143000需要扣除已包含在初始记录1和记录2中的部分，未统计交易数为143000 - (143000 * (11/12)) = 11917，截至该记录的总交易数为1011917（1000000 + 6250 + 5667）。
• 最初24小时的记录之后，每次新增记录时都需要扣除前23小时记录中已包含的部分，以得到累计的未重复交易数。

核心思路是计算每条job data记录贡献的新增交易数：用当前记录的datavalue减去与前一条记录重叠的部分，再加上初始的seeded data值，最终累加得到总数。

具体实现（以MySQL为例） #

WITH sorted_records AS (
    -- 按时间排序，获取每条记录的前一条记录的时间和数值
    SELECT 
        id,
        date,
        datavalue,
        record_type,
        LAG(date) OVER (ORDER BY date) AS prev_date,
        LAG(datavalue) OVER (ORDER BY date) AS prev_datavalue
    FROM your_table_name
),
calculated_additions AS (
    SELECT
        *,
        CASE
            -- 初始数据直接保留原值
            WHEN record_type = 'seeded data' THEN datavalue
            ELSE 
                -- 计算当前记录与前一条的时间间隔（小时）
                datavalue - prev_datavalue * (24 - TIMESTAMPDIFF(HOUR, prev_date, date))/24
        END AS new_transactions
    FROM sorted_records
)
-- 累加初始值和所有新增交易数
SELECT
    SUM(
        CASE
            WHEN record_type = 'seeded data' THEN datavalue
            ELSE new_transactions
        END
    ) AS total_unique_transactions
FROM calculated_additions;

关键逻辑说明 #

1. 窗口函数LAG()：用来获取当前记录的前一条记录的时间和datavalue，这是计算重叠比例的核心依据。
2. 重叠比例计算：两条记录的时间间隔为hour_diff，则当前记录与前一条记录的24小时窗口重叠时长为24 - hour_diff，重叠比例为(24 - hour_diff)/24。
3. 新增交易数：用当前记录的datavalue减去前一条记录中重叠部分的数值，得到这条记录真正贡献的新增交易数。
4. 跨数据库适配：如果使用PostgreSQL，时间差计算需改为EXTRACT(EPOCH FROM (date - prev_date))/3600；SQL Server则用DATEDIFF(HOUR, prev_date, date)。

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