資料分組

使用 AI 進行 SQL 查詢進階

Jasmin Ludolf

Senior Data Science Content Developer

資料分組

特定群組的洞察

合併相同值的列

建立摘要以看出模式

一疊語言書籍

Distinct 與群組

SELECT DISTINCT language
FROM films;

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

唯一值
有哪些語言？

SELECT language
FROM films
GROUP BY language;

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

建立群組
每種語言各有多少部片？

分組型提示

Prompt: 依語言分組影片

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

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

分組常配合摘要統計：
- 例如計數、平均

無特定需求 → 用 COUNT()
- 聚合函式
- 將多列彙整為單一值

常見分組錯誤

Prompt: 顯示依語言分組的所有片名

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 corrections:

加上 COUNT() 函式

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

選擇正確欄位

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

分組提示範例

Prompt: 依國家與語言計算片數

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         |
...

分組與排序

Prompt: 依片數排名語言，僅顯示前 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        |

一起來練習吧！

使用 AI 進行 SQL 查詢進階