潤乾報表缺失日期數據填補方案：SQL 與報表工具雙軌實現

業務場景

需求：報表展示某月 (如 11 月) 每一天的數據。若某天無數據，自動取「最近一天有數據的記錄」填補 (如 11/30 無數據，取 11/28 數據)。

方案一：SQL 層面生成完整日期序列 + 最近值填充 (推薦)

核心思路：

1. 生成目標月份的所有日期序列 (11/1 ~ 11/30)。
2. Left Join 業務數據表。
3. 使用窗口函數 LAST_VALUE 或相關邏輯向前填充最近非空值。

標準 SQL 實現 (以 PostgreSQL/Oracle/SQL Server 為例)

WITH date_series AS (
    -- 生成 2023-11 全月日期
    SELECT generate_series('2023-11-01'::date, '2023-11-30'::date, '1 day')::date AS dt
),
raw_data AS (
    -- 原始業務數據
    SELECT record_date, value FROM business_table 
    WHERE record_date BETWEEN '2023-11-01' AND '2023-11-30'
),
joined AS (
    -- 左連接：保留所有日期
    SELECT ds.dt, rd.value
    FROM date_series ds
    LEFT JOIN raw_data rd ON ds.dt = rd.record_date
)
SELECT 
    dt,
    -- 向前填充：取當前行及之前最近的非空 value
    LAST_VALUE(value) IGNORE NULLS OVER (ORDER BY dt 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS filled_value
FROM joined
ORDER BY dt;

MySQL 8.0+ 實現

WITH RECURSIVE date_series AS (
    SELECT '2023-11-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM date_series WHERE dt < '2023-11-30'
),
joined AS (
    SELECT ds.dt, bt.value
    FROM date_series ds
    LEFT JOIN business_table bt ON ds.dt = bt.record_date
)
SELECT 
    dt,
    -- MySQL 8.0 不支持 IGNORE NULLS，需用變量或子查詢模擬
    (SELECT value FROM joined j2 
     WHERE j2.dt <= j1.dt AND j2.value IS NOT NULL 
     ORDER BY j2.dt DESC LIMIT 1) AS filled_value
FROM joined j1
ORDER BY dt;

方案二：報表工具內置功能 (潤乾報表)

若不願改動 SQL，可在報表設計器中實現：

1. **數據集準備**：確保數據集包含完整日期字段 (或通過參數傳入起止日期)。
2. **分組與排序**：按日期分組，升序排列。
3. **使用「上一條記錄」函數**：在單元格表達式中編寫邏輯：
   =IF(ISBLANK(value), PREVIOUS(value), value)
   (具體函數名請查閱潤乾版本文檔，如 Prev()、LastNonBlank())。
4. **跨分組填充**：若按月分組，需確保填充邏輯不跨月 (11 月末不取 10 月數據)。

方案對比與選型建議

| 維度 | SQL 方案 | 報表工具方案 | |------|----------|--------------| | **性能** | 數據庫優化，一次查詢返回結果 | 報表引擎逐行計算，大數據量較慢 | | **維護性** | 邏輯集中在視圖/存儲過程，易複用 | 邏輯分散在報表模板，遷移困難 | | **靈活性** | 支持複雜填充規則 (如跨表取值) | 受限於報表表達式能力 | | **適用場景** | **推薦用於生產環境、大數據、需複用** | 適用於簡單報表、快速原型、無數據庫改動權限 |

關鍵提示

• **日期序列生成**是核心，數據庫無 generate_series 時可用遞歸 CTE、數字表、或應用層生成列表傳入。
• **最近非空值定義**需業務確認：是「最近一天」還是「最近一條記錄」？是否跨月？
• **索引優化**：業務表 record_date 必須建立索引。

採用 **SQL 方案 + 視圖封裝**，將填充邏輯下沉到數據層，報表只負責展示，是架構最清晰、效能最穩健的選擇。