如何按日期范围分组JSON数值求和并计算周间差值

我有一个包含userName和entry_data（JSON类型，包含value和entryDate字段）的数据库表，表结构及示例数据如下：

需求是：

• 计算所有value的总和totalValue
• 按指定日期范围分别求和：7/25-7/31为week1Value，8/1-8/7为week2Value
• 计算周间差值WoWDifference（week2Value - week1Value）
• 最终输出格式如下：

我目前写出的分组SQL：

SELECT
    userName,
    cast((entry_data ->> 'value') as Int) as totalValue,
    case
         when (entry_data ->> 'week1Value')::DATE BETWEEN '2022-07-25' AND '2022-07-31' then 'week1'
         when (entry_data ->> 'week2Value')::DATE BETWEEN '2022-08-01' AND '2022-08-07' then 'week2'
    end as timeframe,
FROM entry

得到的输出：

请问如何提取每周的总和并计算周间的数值差值？

可以使用条件聚合（SUM()结合CASE WHEN）直接计算各分组总和，一次性得到所有需要的字段，无需先分组再拼接。以下是适配需求的SQL：

SELECT
    userName,
    -- 计算所有value的总和
    SUM((entry_data ->> 'value')::INT) AS totalValue,
    -- 计算week1的总和：仅统计entryDate在7/25-7/31的记录
    SUM(CASE WHEN (entry_data ->> 'entryDate')::DATE BETWEEN '2022-07-25' AND '2022-07-31' 
             THEN (entry_data ->> 'value')::INT 
             ELSE 0 
        END) AS week1Value,
    -- 计算week2的总和：仅统计entryDate在8/1-8/7的记录
    SUM(CASE WHEN (entry_data ->> 'entryDate')::DATE BETWEEN '2022-08-01' AND '2022-08-07' 
             THEN (entry_data ->> 'value')::INT 
             ELSE 0 
        END) AS week2Value,
    -- 计算周间差值：week2Value - week1Value
    SUM(CASE WHEN (entry_data ->> 'entryDate')::DATE BETWEEN '2022-08-01' AND '2022-08-07' 
             THEN (entry_data ->> 'value')::INT 
             ELSE 0 
        END) - 
    SUM(CASE WHEN (entry_data ->> 'entryDate')::DATE BETWEEN '2022-07-25' AND '2022-07-31' 
             THEN (entry_data ->> 'value')::INT 
             ELSE 0 
        END) AS WoWDifference
FROM entry
-- 按userName分组，确保每个用户的结果单独计算
GROUP BY userName;

关键说明： #

1. 条件聚合逻辑：SUM(CASE WHEN ... THEN value ELSE 0 END)只对符合日期条件的记录求和，不符合的记录贡献0，不影响总和。
2. JSON字段解析：用entry_data ->> '字段名'提取JSON字符串值，再通过::INT/::DATE转换为对应数据类型。
3. 周间差值计算：直接用week2的总和减去week1的总和得到WoWDifference。
4. 分组：GROUP BY userName确保每个用户的统计结果独立输出。

执行这段SQL后，就能得到你需要的格式结果。

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