官方解答｜區域總和平均

官方解答｜區域總和平均

解題目標

先按 zone 計算有效 reading 總和（全缺值為 0），再對每組總和取不加權平均；固定一列，沒有區域為 NULL。

查詢步驟

WITH 將資料整理成命名階段；逐階段確認分組、去重與列數，最後再做篩選、連接或視窗。不可假定 CTE 會按撰寫順序實體執行。 依序輸出欄位 average_total。最終排序：average_total 升序，NULL 最後。

正確性

各階段分別實現指定篩列、數值或分組規則。每個保留項目只貢獻題意要求的次數；未配對、缺值與平手依明文規則處理。最後固定別名及排序，因而得到要求的列數、欄位順序與內容。

NULL 與邊界

NULL 表示缺值，不等於 0 或空字串。WHERE／HAVING 只保留真值，假或未知皆排除；一般等號不匹配 NULL。算術操作數缺值時結果為 NULL，除非題目指定備援規則。COUNT(欄位)、SUM、AVG、MIN、MAX 忽略該欄 NULL；COUNT(*) 計算列數。未要求分組或去重時保留所有重複紀錄。空結果仍須具有指定欄名。 先按 zone 計算有效 reading 總和（全缺值為 0），再對每組總和取不加權平均；固定一列，沒有區域為 NULL。

公開範例

fixture 為 {"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":[[21,"north",7,null,null,2,"a"],[41,"south",-2,0,1,4,null],[11,"west",null,4,0,2,"beta"],[31,"north",3,2,1,1,"alpha"]]},{"name":"stations","columns":[{"name":"station_id","type":"INTEGER","nullable":false},{"name":"zone","type":"TEXT","nullable":true},{"name":"target","type":"INTEGER","nullable":true},{"name":"enabled","type":"INTEGER","nullable":true}],"rows":[[9,"north",5,1],[7,"south",null,1],[12,"east",2,null],[3,"north",7,0]]},{"name":"left_samples","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":[[11,"west",null,4,0,2,"beta"],[21,"north",7,null,null,2,"a"],[51,null,5,1,0,8,"ab"],[41,"south",-2,0,1,4,null],[31,"north",3,2,1,1,"alpha"]]},{"name":"right_samples","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":[[32,"east",5,null,null,5,"a"],[42,"south",5,0,1,4,null],[22,"west",null,4,0,2,"beta"],[12,"north",7,1,1,2,"alpha"]]}]}。按題意處理並完成排序後得到 {"columns":["average_total"],"rows":[[2.6666666666666665]]}。即使沒有結果列，也必須保留指定欄名。

複雜度

設所有輸入列合計為 n、輸出／中間配對數為 k。沒有索引時，多表或相關子查詢的保守參考成本可達 O(n²)，再以比較排序處理 k 列需 O(k log(k+1))，暫存資料最多與輸入及中間結果同階。最佳化器可能使用雜湊、索引或重寫降低成本；CTE 的寫法本身不保證實體暫存。

三種方言

下方分別提供完整 SQLite、PostgreSQL、MySQL 查詢。文字排序明寫二進位／字元碼排序；本題 ASCII 域中 SQLite LENGTH 對應其他引擎 CHAR_LENGTH。SQL NULL 輸出 JSON null，不輸出文字 "NULL"。

WITH zone_totals AS (SELECT zone,COALESCE(SUM(reading),0) AS total FROM readings GROUP BY zone)
SELECT average_total AS average_total
FROM (
SELECT AVG(total) AS average_total
FROM zone_totals
) query_result
ORDER BY CASE WHEN average_total IS NULL THEN 1 ELSE 0 END ASC, average_total ASC;

WITH zone_totals AS (SELECT zone,COALESCE(SUM(reading),0) AS total FROM readings GROUP BY zone)
SELECT average_total AS average_total
FROM (
SELECT AVG(total) AS average_total
FROM zone_totals
) query_result
ORDER BY CASE WHEN average_total IS NULL THEN 1 ELSE 0 END ASC, average_total ASC;

WITH zone_totals AS (SELECT zone,COALESCE(SUM(reading),0) AS total FROM readings GROUP BY zone)
SELECT average_total AS average_total
FROM (
SELECT AVG(total) AS average_total
FROM zone_totals
) query_result
ORDER BY CASE WHEN average_total IS NULL THEN 1 ELSE 0 END ASC, average_total ASC;