PostgreSQL如何实现JSONB字段按月分组sum()汇总利润

问题根因 #

你写的SQL只返回全量总利润、无法输出按月分组结果，由3个常见问题导致：

• SELECT子句未包含分组使用的月份维度字段：即使数据库完成了按月分组，返回结果中没有月份标识，无法区分各月统计值
• 未做JSONB提取值的合法性校验：如果部分行的installmentBaseDate为空、或格式不符合日期规范，类型转换后会被统一归为null分组，和有效数据混算得到异常聚合值
• 金额字段使用float类型计算存在浮点精度误差，不适合财务类统计场景

修正后SQL #

SELECT
  count(row_data->>'bankMovementAmount') AS "COUNTER",
  to_char((row_data->>'installmentBaseDate')::date, 'yyyy-mm') AS "MONTH",
  COALESCE(SUM((row_data->>'bankMovementAmount')::numeric) FILTER (WHERE row_data->>'bankMovementOperationType' = 'E'), 0)
  - COALESCE(SUM((row_data->>'bankMovementAmount')::numeric) FILTER (WHERE row_data->>'bankMovementOperationType' = 'S'), 0)
  AS "VALUE"
FROM public.teste
WHERE
  abbreviation = 'BMO'
  AND row_data->>'companyName' = 'Nec Plus Ultra Gestão e Tecnologia LTDA'
  AND row_data->>'installmentBaseDate' IS NOT NULL
GROUP BY 2
ORDER BY 2;

注：这里用GROUP BY 2、ORDER BY 2是简写，对应SELECT列表中第2个字段即月份字段，避免重复写日期转换表达式，减少出错概率；加COALESCE是为了避免某类操作类型没有数据时，SUM返回null导致整个利润计算结果为null。

前置校验方法 #

如果执行后还是异常，先跑下面的语句确认日期提取逻辑是否正常，能返回多个不同月份值就说明维度逻辑没问题：

SELECT
  to_char((row_data->>'installmentBaseDate')::date, 'yyyy-mm') AS check_month,
  count(*) AS row_cnt
FROM public.teste
WHERE
  abbreviation = 'BMO'
  AND row_data->>'companyName' = 'Nec Plus Ultra Gestão e Tecnologia LTDA'
  AND row_data->>'installmentBaseDate' IS NOT NULL
GROUP BY check_month
ORDER BY check_month;

如果这条语句只返回1行结果，说明你筛选条件下的所有数据本身就属于同一个月份，和SQL逻辑无关。

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