如何在PostgreSQL中使用GROUP BY。

Question

本次查询需要使用两个不同的表：country和city。Country表包含国家名称(name)，国家代码(country_code)(主键)；city表包含城市名称(name)，人口数量(population)，以及所属国家的国家代码(country_code)(主键)。

我想使用聚合函数GROUP BY，但是下面的查询不起作用。

对于每个国家，列出其任何城市中最大的人口数及该城市的名称。因此，需要列出每个国家人口最多的城市。

所以应该显示的内容为：国家名称、该国家人口最多的城市名称，以及该城市的人口数量。每个城市只能对应一个国家。

$query6 = "SELECT c.name AS country, ci.name AS city,
GREATEST(ci.population) AS max_pop
FROM lab6.country c INNER JOIN lab6.city ci
ON(c.country_code = ci.country_code)
GROUP BY c.name
ORDER BY country ASC";

我也尝试了按国家分组，DISTINCT c.name。

我对聚合函数不熟悉，如果有特定的情况需要使用GROUP BY，而这并不是其中之一，请告诉我。

我正在使用PHP运行以下查询：

$result = pg_query($connection, $query);
if(!$result)
{
       die("Failed to connect to database");
}

错误：列 "ci.name" 必须出现在 GROUP BY 子句中或在聚合函数中使用 LINE 1: SELECT DISTINCT c.name AS country, ci.name AS city, 是这个错误的原因。

我们得到了表格，而不是制作它们，我无法包含制作的表格的屏幕截图，因为我没有任何声望。

- Paigentry

---

最好发布您的表的SQL DDL，而不是描述它们。 - Mike Sherrill 'Cat Recall'

为什么它不起作用，它返回了什么错误？ - Farlan

对不起，什么是SQL DDL？这是我在数据库课程实验室遇到的问题。如果您让我知道它是什么，我会尝试发布那个问题。 - Paigentry

DDL 意为数据定义语言。它是用于创建表的CREATE TABLE语句。想要帮助找到它更容易且速度更快的方法是将DDL粘贴到自己的查询窗口中，而不是尝试通过描述来进行表的前向工程。 - Mike Sherrill 'Cat Recall'

可能是查询返回太多结果？的重复问题。 - Erwin Brandstetter

Answer 1

一些可供操作的DDL语句。

create table country (
  country_code char(2) primary key, -- ISO country code
  country_name varchar(35) not null unique
);

insert into country values 
('US', 'United States of America'),
('IT', 'Italy'),
('IN', 'India');

-- The full name of a city is more than city name plus country name.
-- In the US, there are a couple of dozen cities named Springfield,
-- each in a different state. I'd be surprised if this weren't true
-- in most countries.
create table city (
  country_code char(2) not null references country (country_code),
  name varchar(35) not null,
  population integer not null check (population > 0),
  primary key (country_code, name)
);

insert into city values 
('US', 'Rome, GA', 36303),
('US', 'Washington, DC', 632323),
('US', 'Springfield, VA', 30484),
('IT', 'Rome', 277979),
('IT', 'Milan', 1324110),
('IT', 'Bari', 320475),
('IN', 'Mumbai', 12478447),
('IN', 'Patna', 1683200),
('IN', 'Cuttack', 606007);

一个国家的人口最多。

select country.country_code, max(city.population) as max_population
from country
inner join city on country.country_code = city.country_code
group by country.country_code;

有几种方法可以使用，以便获得您想要的结果。其中一种方法是在公共表达式上使用内部连接。

with max_population as (
  select country.country_code, max(city.population) as max_population
  from country
  inner join city on country.country_code = city.country_code
  group by country.country_code
)
select city.country_code, city.name, city.population
from city
inner join max_population 
        on max_population.country_code = city.country_code
       and max_population.max_population = city.population;

另一种方法是在子查询中使用内连接。（公共表达式的文本进入主查询。 使用别名“max_population”，查询不需要进一步更改即可运行。）

select city.country_code, city.name, city.population
from city
inner join (select country.country_code, max(city.population) as max_population
            from country
            inner join city on country.country_code = city.country_code
            group by country.country_code
           ) max_population 
        on max_population.country_code = city.country_code
       and max_population.max_population = city.population;

另一种方法是在子查询中使用窗口函数。你需要从子查询中进行选择，因为你不能直接在WHERE子句中使用rank()的结果。也就是说，这个方法可以实现。

select country_code, name, population
from (select country_code, name, population,
      rank() over (partition by country_code 
                   order by population desc) as city_population_rank
      from city
     ) city_population_rankings
where city_population_rank = 1;

但这种方法并不起作用，即使乍一看更有道理。

select country_code, name, population,
       rank() over (partition by country_code 
                    order by population desc) as city_population_rank
from city
where city_population_rank = 1;

ERROR:  column "city_population_rank" does not exist

- Mike Sherrill 'Cat Recall'

---

我是否错误地使用了GROUP BY？如果我按照错误提示进行操作，并将其设置为GROUP BY c.name、ci.name、ci.population，那么如果我不添加GROUP BY，它会给我相同的表格，这是错误的。 - Paigentry

这个答案是正确的，但我不能使用它，因为它对我所知道的来说太高级了。有没有更简单的方法来做到这一点？ - Paigentry

@Paigentry：更新了答案。我认为你可能对 GROUP BY 的理解有误。GROUP BY 不是一个聚合函数，但 MAX() 是。GREATEST() 不是聚合函数，而是一个条件函数。GROUP BY 需要一个聚合函数。 - Mike Sherrill 'Cat Recall'

@MikeSherrill'Catcall' 如果将 WHERE 替换为 HAVING，那最后一个版本是否可行？ - Andrew Lazarus

@AndrewLazarus：不会的。标准SQL语句必须表现得好像它们在评估SELECT子句中的派生或别名列之前评估WHERE子句和HAVING子句一样。在此SQL Server参考中搜索“逻辑处理顺序”。逻辑处理顺序在SQL标准中有规定；它适用于PostgreSQL以及SQL Server。 - Mike Sherrill 'Cat Recall'

Answer 2

在最近版本的PostgreSQL中，最好的方法是使用窗口函数（文档）。在此之前，当您想要将某些特殊行的其他列带入最终输出时，例如具有最大人口的行，您需要做一些丑陋的事情。

WITH preliminary AS 
     (SELECT country_code, city_name, population,
      rank() OVER (PARTITION BY country_code ORDER BY population DESC) AS r
      FROM country
      NATURAL JOIN city) -- NATURAL JOIN collapses 2 country_code columns into 1
SELECT * FROM preliminary WHERE r=1;

这也会在一个国家的两个或更多最大城市有完全相同人口的情况下做出一些智能的处理。

[根据评论进行编辑]

在窗口化之前，我的常规方法是

SELECT country_code, city_name, population
FROM country co1 NATURAL JOIN city ci1
WHERE ROW(co1.country_code, ci1.population) =
    (SELECT co2.country_code, ci2.population 
     FROM country co2 NATURAL JOIN city ci2
     WHERE co1.country_code = co2.country_code 
     ORDER BY population DESC LIMIT 1) 
     AS subquery;
-- note for lurkers, some other DBs use TOP 1 instead of LIMIT

如果数据库被智能索引，Postgres会优化子查询，因此这个性能还不错。与Mike Sherrill的答案中使用子查询内连接相比较。

请告诉我们导师的答案吧？根据你目前拥有的设备，可能会效率低下，在并列情况下不完整，或两者兼而有之。

- Andrew Lazarus

---

一遍又一遍地说，这也许是正确的答案，但对于我所知道的来说太高级了。因为这是我的第一门数据库课程，而且我第一次接触聚合函数，所以并不期望我做得这么多。有没有类似于我的格式的方法来完成这个任务？不用WITH，因为那还没有被介绍过。 - Paigentry