Oracle 分組統計實戰：TRUNC 日期截取、DECODE/CASE 條件聚合與多種寫法對比

業務場景

統計 heart_active 欄位（0/1）在不同日期的筆數，按日期分組排序。初始 SQL 因 input_date 含時分秒（YYYY-MM-DD HH24:MI:SS）導致無法正確按「日」分組。

核心解法：TRUNC 截取日期部分

TRUNC(input_date, 'DD')  -- 或 TRUNC(input_date) 預設截至日

將 datetime 統一截斷為當日 00:00:00，實現正確分組。

四種 SQL 寫法演進與對比

寫法一：DECODE + GROUP BY 明細行

SELECT TRUNC(a.input_date, 'DD') AS dt,
       a.heart_active,
       SUM(DECODE(a.heart_active, 1, 1, 0, 1)) AS cnt
FROM rfid_fixed_heart a
GROUP BY TRUNC(a.input_date, 'DD'), a.heart_active
ORDER BY dt DESC;

輸出：每日每狀態一行（0 一行、1 一行）。

寫法二：DECODE 橫向轉欄（樞紐分析）

SELECT TRUNC(a.input_date, 'DD') AS dt,
       SUM(DECODE(a.heart_active, '0', 1)) AS heart_active_0,
       SUM(DECODE(a.heart_active, '1', 1)) AS heart_active_1
FROM rfid_fixed_heart a
GROUP BY TRUNC(a.input_date, 'DD')
ORDER BY dt DESC;

輸出：每日一行，兩狀態分列。報表友好，最常用。

寫法三：CASE 表達式（ANSI 標準，可讀性更佳）

SELECT TRUNC(input_date, 'DD') AS dt,
       SUM(CASE WHEN heart_active = '0' THEN 1 END) AS heart_active_0,
       SUM(CASE WHEN heart_active = '1' THEN 1 END) AS heart_active_1
FROM rfid_fixed_heart
GROUP BY TRUNC(input_date, 'DD')
ORDER BY dt DESC;

寫法四：子查詢預先轉換 + 外層聚合

SELECT op_date, heart_active,
       SUM(heart_active_0) AS heart_active_0,
       SUM(heart_active_1) AS heart_active_1
FROM (
  SELECT TO_CHAR(input_date, 'YYYY-MM-DD') AS op_date,
         heart_active,
         CASE WHEN heart_active = '0' THEN COUNT(heart_active) ELSE 0 END AS heart_active_0,
         CASE WHEN heart_active = '1' THEN COUNT(heart_active) ELSE 0 END AS heart_active_1
  FROM rfid_fixed_heart
  GROUP BY input_date, heart_active
) a
GROUP BY op_date, heart_active
ORDER BY op_date DESC;

邏輯較繞，不推薦生產使用，僅供理解轉換思路。

關鍵技術點總結

效能建議

• 大表情況下，確保 input_date 有索引，或建立 函數索引 CREATE INDEX idx_trunc_date ON rfid_fixed_heart(TRUNC(input_date))。
• 若頻繁按日/月統計，考慮分區表（RANGE PARTITION BY input_date）實現分區裁剪。
• 避免在 WHERE 條件對欄位做函數操作（如 WHERE TRUNC(dt) = ...），改用範圍查詢 dt >= TRUNC(SYSDATE) AND dt < TRUNC(SYSDATE)+1 走索引。