PostgreSQL同表双快照下OFFERING_ID价格变动计数问题

问题出现的原因 #

• COUNT函数使用逻辑错误
  COUNT()统计的是所有非NULL值的数量，原SQL中CASE语句设置了ELSE 0，不管条件是否满足，CASE都会返回非NULL的数值，所以三个COUNT统计出来的结果都等于关联后的总行数，自然数值完全一致。
• SQL语法结构错误
  JOIN关联应该写在FROM子句中，而不是WHERE子句下，语法本身不符合规范，即便部分PostgreSQL版本兼容执行，也可能出现关联逻辑异常。
• 临时表存在重复数据
  如果BEFORE或者AFTER临时表中，同一个OFFERING_ID存在多条重复记录，INNER JOIN会产生笛卡尔积，导致关联后的总行数远大于实际两个快照都存在的OFFERING_ID数量，这也是统计值超过实际OFFERING总数的核心原因。

正确SQL写法 #

首先确认BEFORE和AFTER临时表中每个OFFERING_ID仅有一条记录（OFFERING_ID是主键，单快照日理论上唯一），如果存在重复先做去重处理，再执行如下统计语句即可：

SELECT
    -- 不写ELSE的情况下，不满足条件的CASE返回NULL，COUNT不会统计
    COUNT(CASE WHEN b.PRICE_BEFORE = a.PRICE_AFTER THEN 1 END) AS SAME_PRICE,
    COUNT(CASE WHEN b.PRICE_BEFORE > a.PRICE_AFTER THEN 1 END) AS LOWER_PRICE,
    COUNT(CASE WHEN b.PRICE_BEFORE < a.PRICE_AFTER THEN 1 END) AS HIGHER_PRICE
FROM AFTER a
INNER JOIN BEFORE b 
ON a.OFFERING_ID = b.OFFERING_ID 
AND a.PRODUCT_ID = b.PRODUCT_ID
-- 单PRODUCT_ID查询可打开下方过滤条件
-- WHERE a.PRODUCT_ID = '目标PRODUCT_ID'

如果无法确认临时表是否有重复数据，可在关联时先做去重处理：

SELECT
    COUNT(CASE WHEN b.PRICE_BEFORE = a.PRICE_AFTER THEN 1 END) AS SAME_PRICE,
    COUNT(CASE WHEN b.PRICE_BEFORE > a.PRICE_AFTER THEN 1 END) AS LOWER_PRICE,
    COUNT(CASE WHEN b.PRICE_BEFORE < a.PRICE_AFTER THEN 1 END) AS HIGHER_PRICE
FROM (SELECT DISTINCT OFFERING_ID, PRODUCT_ID, PRICE_AFTER FROM AFTER) a
INNER JOIN (SELECT DISTINCT OFFERING_ID, PRODUCT_ID, PRICE_BEFORE FROM BEFORE) b
ON a.OFFERING_ID = b.OFFERING_ID 
AND a.PRODUCT_ID = b.PRODUCT_ID

INNER JOIN本身已经过滤了仅在单个快照日存在的OFFERING_ID，关联结果中两边的价格字段均为非NULL，不需要额外在CASE语句中增加非空判断。

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