AVG([DISTINCT | ALL] expr)
-- ALL(預設) 計算所有值,DISTINCT 去重後計算
SELECT AVG(DISTINCT sal), AVG(ALL sal), AVG(sal) FROM table3;
-- 範例資料: 1111.11, 1111.11, 5555.55
-- 結果: 3333.33, 2592.59, 2592.59
SUM([DISTINCT | ALL] expr)
SELECT SUM(DISTINCT sal), SUM(ALL sal), SUM(sal) FROM table3;
-- 結果: 6666.66, 7777.77, 7777.77
VARIANCE([DISTINCT | ALL] expr)
SELECT VARIANCE(DISTINCT sal), VARIANCE(ALL sal), VARIANCE(sal) FROM table3;
-- 結果: 9876523.4568, 6584348.9712, 6584348.9712
COUNT(*) | COUNT([DISTINCT | ALL] expr)
-- COUNT(*) 計算所有行(含 NULL)
-- COUNT(expr) 忽略 NULL
-- COUNT(DISTINCT expr) 去重計數
SELECT COUNT(*), COUNT(xm), COUNT(ALL xm), COUNT(DISTINCT sal), COUNT(ALL sal), COUNT(sal), SUM(1)
FROM table3;
-- 資料: ('gao',1111.11), ('gao',1111.11), ('zhu',5555.55), ('',1111.11), ('zhu',0)
-- 結果: 5, 4, 4, 3, 5, 5, 5
等價關係:COUNT(*) = SUM(1)。
MIN([DISTINCT | ALL] expr)
MAX([DISTINCT | ALL] expr)
-- 字串型忽略 NULL,數值/日期同理
SELECT MIN(DISTINCT sal), MIN(xm), MIN(DISTINCT xm), MIN(ALL xm) FROM table3;
-- 結果: 0, 'gao', 'gao', 'gao'
DISTINCT / ALL 關鍵字與運算元之間**必須有空格**。COUNT(*))。GROUP BY 分組統計,HAVING 過濾分組結果。VARIANCE、STDDEV 需至少 2 筆非 NULL 值,否則回傳 NULL。AVG(sal) OVER (PARTITION BY deptno) -- 部門平均薪資不分組顯示
SUM(sal) OVER (ORDER BY hiredate ROWS UNBOUNDED PRECEDING) -- 累積薪資
聚合函數是 SQL 統計分析的基石,掌握 DISTINCT 與 NULL 處理語義差異,能避免報表數據偏差。