如何获取GROUP BY后包含所有category取值的分组数据？

现有一张数据表，已对name和category列执行GROUP BY分组，并计算了number列的平均值。需要仅保留那些包含category列所有可能取值（此处为1和2）的分组数据。

示例数据表 #

| name  | category | number |
|-------|----------|--------|
| jack  | 1        | 12.30  |
| jack  | 1        | 12.50  |
| jack  | 2        | 13.35  |
| jack  | 2        | 13.35  |
| jack  | 2        | 13.35  |
| james | 1        | 18.76  |
| james | 1        | 20.38  |
| kate  | 1        | 22.14  |
| kate  | 1        | 22.18  |
| kate  | 2        | 21.80  |
| kate  | 2        | 22.00  |

当前查询及结果 #

当前使用的查询语句：

SELECT name, category, AVG(number) AS average_number
FROM dummy_table
GROUP BY name, category

查询结果：

| name  | category | average_number |
|-------|----------|----------------|
| jack  | 1        | 12.40          |
| jack  | 2        | 13.35          |
| james | 1        | 19.57          |
| kate  | 1        | 22.16          |
| kate  | 2        | 21.90          |

期望结果 #

移除仅包含部分category值的数据（比如james只有category=1），最终结果如下：

| name  | category | average_number |
|-------|----------|----------------|
| jack  | 1        | 12.40          |
| jack  | 2        | 13.35          |
| kate  | 1        | 22.16          |
| kate  | 2        | 21.90          |

解决方案 #

方法一：子查询+分组筛选 #

先找出拥有所有category取值的name，再关联原分组结果过滤数据：

SELECT t.name, t.category, t.average_number
FROM (
    -- 先按name和category分组计算平均值
    SELECT name, category, AVG(number) AS average_number
    FROM dummy_table
    GROUP BY name, category
) t
-- 关联筛选出的有效name
INNER JOIN (
    SELECT name
    FROM dummy_table
    GROUP BY name
    -- 这里的计数要等于category的总唯一值数量，示例中是2
    HAVING COUNT(DISTINCT category) = (SELECT COUNT(DISTINCT category) FROM dummy_table)
) valid_names ON t.name = valid_names.name

方法二：窗口函数筛选 #

通过窗口函数计算每个name对应的category数量，再过滤符合条件的记录：

SELECT name, category, average_number
FROM (
    SELECT 
        name, 
        category, 
        AVG(number) AS average_number,
        -- 统计每个name拥有的不同category数量
        COUNT(DISTINCT category) OVER (PARTITION BY name) AS cat_count,
        -- 获取全局的category总唯一值数量
        (SELECT COUNT(DISTINCT category) FROM dummy_table) AS total_cat
    FROM dummy_table
    GROUP BY name, category
) t
-- 只保留拥有所有category的记录
WHERE cat_count = total_cat

测试用DDL语句 #

Create Table Emps(person_name VARCHAR(50), category INT, number DECIMAL);

Insert Into Emps Values
('steve',1,15.30),
('steve',1,14.30),
('steve',2,14.30),
('steve',2,13.30),
('john',1,18.76),
('john',1,17.36),
('john',1,18.42),
('angela',1,23.84),
('angela',1,24.48),
('angela',2,22.00),
('angela',2,22.94);

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