PostgreSQL报错：summed_hits列不存在——Rails中Hstore求和查询问题

这个问题我之前也碰到过，其实是PostgreSQL的查询执行顺序在搞鬼！让我给你拆解一下原因和解决办法。

你的问题场景 #

你在Rails应用里尝试对Hstore类型字段analytics中的多个日期的hits值求和，写了下面这段SQL查询：

SELECT *, 
  COALESCE(NULLIF(analytics->'2018.5.17.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.18.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.19.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.20.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.21.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.22.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.23.hits', '')::INT, 0) + 
  COALESCE(NULLIF(analytics->'2018.5.24.hits', '')::INT, 0) as summed_hits 
FROM "searched_words" 
WHERE "searched_words"."account_id" = 2 
  AND (name ILIKE '%%') 
  AND (analytics ?| ARRAY['2018.5.17.hits','2018.5.18.hits','2018.5.19.hits','2018.5.20.hits','2018.5.21.hits','2018.5.22.hits','2018.5.23.hits','2018.5.24.hits']) 
  AND (summed_hits > 1) 
  AND (analytics ?| ARRAY['2018.5.17.hits','2018.5.18.hits','2018.5.19.hits','2018.5.20.hits','2018.5.21.hits','2018.5.22.hits','2018.5.23.hits','2018.5.24.hits']);

结果PostgreSQL报错：

ERROR: column "summed_hits" does not exist
LINE 1: ...22.hits','2018.5.23.hits','2018.5.24.hits']) AND (summed_hit...

为什么会报错？ #

核心原因是PostgreSQL的SQL执行顺序：
数据库会先执行WHERE子句过滤数据，这时候还没处理SELECT子句里的列定义和别名。也就是说，当数据库检查summed_hits > 1这个条件时，summed_hits这个别名还没被创建出来，自然就会提示“列不存在”。

解决办法 #

有两种常用的方式可以解决这个问题，都是让summed_hits先被计算出来，再用它做过滤：

方法1：使用子查询 #

把原来的查询包装成一个子查询，先计算出summed_hits，然后在外层查询里用这个别名过滤：

SELECT *
FROM (
  SELECT *, 
    COALESCE(NULLIF(analytics->'2018.5.17.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.18.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.19.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.20.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.21.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.22.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.23.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.24.hits', '')::INT, 0) as summed_hits 
  FROM "searched_words" 
  WHERE "searched_words"."account_id" = 2 
    AND (name ILIKE '%%') 
    AND (analytics ?| ARRAY['2018.5.17.hits','2018.5.18.hits','2018.5.19.hits','2018.5.20.hits','2018.5.21.hits','2018.5.22.hits','2018.5.23.hits','2018.5.24.hits'])
) AS subquery
WHERE summed_hits > 1;

方法2：使用CTE（公共表表达式） #

CTE和子查询类似，但可读性更好，适合复杂查询：

WITH calculated_hits AS (
  SELECT *, 
    COALESCE(NULLIF(analytics->'2018.5.17.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.18.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.19.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.20.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.21.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.22.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.23.hits', '')::INT, 0) + 
    COALESCE(NULLIF(analytics->'2018.5.24.hits', '')::INT, 0) as summed_hits 
  FROM "searched_words" 
  WHERE "searched_words"."account_id" = 2 
    AND (name ILIKE '%%') 
    AND (analytics ?| ARRAY['2018.5.17.hits','2018.5.18.hits','2018.5.19.hits','2018.5.20.hits','2018.5.21.hits','2018.5.22.hits','2018.5.23.hits','2018.5.24.hits'])
)
SELECT * FROM calculated_hits WHERE summed_hits > 1;

不推荐的方法：重复计算逻辑 #

你也可以把summed_hits的计算逻辑直接重复写在WHERE子句里，但这样代码冗余，后续修改日期范围时要改两处，容易出错，所以不建议这么做。

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