SQL 彙總統計

先預測結果，再看輸出。

SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。

01｜先理解資料與結果

沒有 GROUP BY 的聚合查詢會回傳一列，即使來源空表也是如此。COUNT(*) 計列；COUNT(reading) 只計非 NULL reading。SUM、AVG、MIN、MAX 忽略 NULL，沒有有效值時為 NULL。把 reading 先補成 0 再平均會改變分母，與忽略缺值平均不同。

02｜建立正確查詢規則

COUNT(DISTINCT reading) 計算不同且非 NULL 的值數，與 SELECT DISTINCT reading 的列數不一定相同，後者可能包含一列 NULL。SUM(CASE WHEN reading>0 THEN 1 ELSE 0 END) 可統計正讀值，但空表 SUM 仍為 NULL，需要題意指定的 COALESCE(...,0)。

簡單範例｜測量筆數

計算所有列數，包含缺值列；空表為 0。固定回傳一列，即使來源空表也一樣。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}

SQLite

SELECT value AS value
FROM (
SELECT COUNT(*) AS value
FROM readings
) query_result
ORDER BY value ASC;

PostgreSQL

SELECT value AS value
FROM (
SELECT COUNT(*) AS value
FROM readings
) query_result
ORDER BY value ASC;

MySQL

SELECT value AS value
FROM (
SELECT COUNT(*) AS value
FROM readings
) query_result
ORDER BY value ASC;

預期查詢結果

value
12

中等範例｜有效值種類

計算不同非 NULL reading 的數量，重複只算一次；空表為 0。固定回傳一列，即使來源空表也一樣。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}

SQLite

SELECT value AS value
FROM (
SELECT COUNT(DISTINCT reading) AS value
FROM readings
) query_result
ORDER BY value ASC;

PostgreSQL

SELECT value AS value
FROM (
SELECT COUNT(DISTINCT reading) AS value
FROM readings
) query_result
ORDER BY value ASC;

MySQL

SELECT value AS value
FROM (
SELECT COUNT(DISTINCT reading) AS value
FROM readings
) query_result
ORDER BY value ASC;

預期查詢結果

value
7

困難範例｜標記總數

計算 flag=1 的列數；NULL 不計入，空表為 0。固定回傳一列，即使來源空表也一樣。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}

SQLite

SELECT value AS value
FROM (
SELECT COALESCE(SUM(CASE WHEN flag=1 THEN 1 ELSE 0 END),0) AS value
FROM readings
) query_result
ORDER BY value ASC;

PostgreSQL

SELECT value AS value
FROM (
SELECT COALESCE(SUM(CASE WHEN flag=1 THEN 1 ELSE 0 END),0) AS value
FROM readings
) query_result
ORDER BY value ASC;

MySQL

SELECT value AS value
FROM (
SELECT COALESCE(SUM(CASE WHEN flag=1 THEN 1 ELSE 0 END),0) AS value
FROM readings
) query_result
ORDER BY value ASC;

預期查詢結果

value
5

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

把 AVG 的缺值當成 0

先確認要求忽略缺值還是補零；兩者平均不同。

空表時少回傳一列

整體聚合仍有一列，COUNT 為 0、其他聚合依規則處理。

相異計數忘記排除 NULL

COUNT(DISTINCT column) 不把 NULL 算一種有效值。

PAUSE AND TRY

先不要急著看別人的寫法

對 [NULL,0,4] 比較 COUNT(*)、COUNT(reading)、AVG(reading)。
把所有 reading 改成 NULL，MIN 與 COALESCE(SUM,0) 各為何？
新增重複正讀值，COUNT(DISTINCT) 與正值總和如何改變？

先改一列資料，預測查詢結果，再切換引擎確認語法與排序。

把這幾件事帶走

正確選擇 COUNT(*)／COUNT(column)
處理空表與全缺值聚合
用條件聚合計算特定項目

FROM READING TO PRACTICE

用三道免費題，練習本章觀念

從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。

簡單

延伸閱讀：資料庫官方文件

需要查閱更完整的規則時，可參考以下官方文件。

01｜先理解資料與結果

02｜建立正確查詢規則

簡單範例｜測量筆數

中等範例｜有效值種類

困難範例｜標記總數

遇到錯誤，先檢查這裡

先不要急著看別人的寫法

把這幾件事帶走

用三道免費題，練習本章觀念

101. 測量筆數

109. 有效值種類

118. 標記總數

延伸閱讀：資料庫官方文件