数据分组

AI 助力的 SQL 查询进阶

Jasmin Ludolf

Senior Data Science Content Developer

数据分组

针对特定分组获取洞见

合并相同取值的行

汇总以揭示模式

一叠语言类书籍

去重 vs. 分组

SELECT DISTINCT language
FROM films;

|language|        
|--------|
|Danish  |
|Greek   |
|Dzongkha|
|...     |

唯一值
有哪些语言？

SELECT language
FROM films
GROUP BY language;

|language|        
|--------|
|Danish  |
|Greek   |
|Dzongkha|
|...     |

创建分组
每种语言有多少部电影？

分组的提示语

提示： 按语言分组电影

SELECT language, COUNT(*) AS lang_count
FROM films
GROUP BY language;

|language|lang_count|
|--------|----------|
|        |11        |
|Arabic  |5         |
|Kannada |1         |
|Czech   |1         |
...

分组常与汇总统计一起用：
- 如计数、平均值

未指定请求 -> 用 COUNT()
- 聚合函数
- 将多行汇总为一个值

常见分组错误

提示： 按语言分组显示所有电影标题

SELECT title
FROM films
GROUP BY language;

column "films.title" must appear in the
GROUP BY clause or be used in an 
aggregate function LINE 1...

AI 修正：

添加 COUNT() 函数

SELECT language, COUNT(title) AS count
FROM films
GROUP BY language;

选择正确的字段

SELECT title, language
FROM films
GROUP BY title, language;

分组提示

提示： 按国家和语言统计电影数量

SELECT country, language, COUNT(*) AS film_count
FROM films
GROUP BY country, language;

|country           |language|film_count|
|------------------|--------|----------|
|                  |        |1         |
|Pakistan          |Urdu    |1         |
|Mexico            |English |3         |
|Dominican Republic|Spanish |1         |
...

分组与排序

提示： 按电影数量对语言排名，显示前 3 名

SELECT language, COUNT(*) AS film_count
FROM films
GROUP BY language
ORDER BY film_count DESC
LIMIT 3;

|language|film_count|
|--------|----------|
|English |4635      |
|French  |72        |
|Spanish |40        |

Vamos praticar!

AI 助力的 SQL 查询进阶