如何计算两个PostgreSQL查询结果中COUNTER1列的差值？

方法一：条件聚合（推荐，单表扫描效率更高） #

直接在一个查询中通过条件判断分别统计两天的记录数，再计算差值，无需多次扫描表：

SELECT
    'TWEM' AS MIC,
    'Equity' AS INSTRUMENT_TYPE,
    COUNT(CASE WHEN reference_date = CURRENT_DATE - INTERVAL '1 day' THEN 1 END) -
    COUNT(CASE WHEN reference_date = CURRENT_DATE - INTERVAL '2 days' THEN 1 END) AS counter_diff
FROM reference
WHERE
    reference_date IN (CURRENT_DATE - INTERVAL '1 day', CURRENT_DATE - INTERVAL '2 days')
    AND MIC = 'TWEM'
    AND INSTRUMENT_TYPE = 'Equity';

说明：通过CASE语句分别匹配两天的数据，COUNT会忽略NULL值，从而得到每天的统计数，最后直接相减得到差值。

方法二：子查询直接计算差值 #

将两天的统计结果作为独立子查询，直接计算差值：

SELECT
    (SELECT COUNT(*)
     FROM reference
     WHERE reference_date = CURRENT_DATE - INTERVAL '1 day'
       AND MIC = 'TWEM'
       AND INSTRUMENT_TYPE = 'Equity') -
    (SELECT COUNT(*)
     FROM reference
     WHERE reference_date = CURRENT_DATE - INTERVAL '2 days'
       AND MIC = 'TWEM'
       AND INSTRUMENT_TYPE = 'Equity') AS counter_diff;

说明：这种方式逻辑直观，适合快速理解，但会扫描两次表，数据量大时效率不如条件聚合。

方法三：CTE（公共表表达式） #

用CTE拆分两天的统计逻辑，可读性更强，适合后续扩展复杂逻辑：

WITH yesterday_stats AS (
    SELECT COUNT(*) AS counter1
    FROM reference
    WHERE reference_date = CURRENT_DATE - INTERVAL '1 day'
      AND MIC = 'TWEM'
      AND INSTRUMENT_TYPE = 'Equity'
), day_before_yesterday_stats AS (
    SELECT COUNT(*) AS counter1
    FROM reference
    WHERE reference_date = CURRENT_DATE - INTERVAL '2 days'
      AND MIC = 'TWEM'
      AND INSTRUMENT_TYPE = 'Equity'
)
SELECT y.counter1 - d.counter1 AS counter_diff
FROM yesterday_stats y, day_before_yesterday_stats d;

说明：CTE将两天的统计结果分别命名，最后在主查询中计算差值，逻辑清晰，便于维护。

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