SQL 聚合函数别名

我是 SQL 初学者，这是我被要求解决的问题：

假设大城市被定义为place类型的city人口为 至少100,000。编写返回方案的 SQL 查询(state_name,no_big_city,big_city_population)订购state_name，列出拥有 (a) 至少五个大城市或 (b) 至少有一百万人口居住在大城市的州。专栏state_name is the name of the state, no_big_city是该州大城市的数量，并且big_city_population是居住在该州大城市的人数。

现在，据我所知，以下查询返回正确的结果：

SELECT state.name AS state_name
     , COUNT(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN 1 ELSE NULL END) AS no_big_city
     , SUM(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN place.population ELSE NULL END) AS big_city_population
FROM state
JOIN place
ON state.code = place.state_code
GROUP BY state_name
    HAVING
        COUNT(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN 1 ELSE NULL END) >= 5 OR
        SUM(CASE WHEN place.type = 'city' AND place.population >= 100000 THEN place.population ELSE NULL END) >= 1000000
ORDER BY state_name;

然而，代码中使用的两个聚合函数出现了两次。我的问题：有什么办法可以让代码重复消失并保留功能吗？

需要明确的是，我已经尝试使用别名，但我只是收到“列不存在”错误。

手册明确： https://www.postgresql.org/docs/current/static/sql-select.html#SQL-SELECT-LIST

输出列的名称可用于引用该列的值ORDER BY and GROUP BY条款，但不在WHERE or HAVING条款；在那里你必须写出表达式。

大胆强调我的。

您可以避免使用子查询或 CTE 重复输入长表达式：

SELECT state_name, no_big_city, big_city_population
FROM  (
   SELECT s.name AS state_name
        , COUNT(*)        FILTER (WHERE p.type = 'city' AND p.population >= 100000) AS no_big_city
        , SUM(population) FILTER (WHERE p.type = 'city' AND p.population >= 100000) AS big_city_population
   FROM   state s
   JOIN   place p ON s.code = p.state_code
   GROUP  BY s.name -- can be input column name as well, best schema-qualified to avoid ambiguity
   ) sub
WHERE  no_big_city >= 5
   OR  big_city_population >= 1000000
ORDER  BY state_name;

在做这件事时，我简化了总体FILTER子句（Postgres 9.4+）：

• 如何简化这个游戏统计查询？ https://stackoverflow.com/questions/27136251/how-can-i-simplify-this-game-statistics-query/27141193#27141193

不过，我建议从这个更简单、更快的查询开始：

SELECT s.state_name, p.no_big_city, p.big_city_population
FROM   state s
JOIN  (
   SELECT state_code      AS code  -- alias just to simplify join
        , count(*)        AS no_big_city
        , sum(population) AS big_city_population
   FROM   place
   WHERE  type = 'city'
   AND    population >= 100000
   GROUP  BY 1  -- can be ordinal number referencing position in SELECT list
   HAVING count(*) >= 5 OR sum(population) >= 1000000  -- simple expressions now
   ) p USING (code)
ORDER  BY 1;    -- can also be ordinal number

我正在演示另一个引用表达式的选项GROUP BY and ORDER BY。仅在不损害可读性和可维护性的情况下才使用它。