如何在mysql中使用最后一個值視窗分析函式來結轉時間序列中缺失日期的值

如何結轉缺失日期 postcode/indicator_category 的值以創建完整的月度時間序列。我試圖使用 last_value 來繼承值，但無法實作。我的方法正確嗎？任何幫助都會受到高度贊賞。

給定表格的示例：

    create table test.indicator_data(
      postcode text, 
      month_ts date, 
      indicator_cat integer,
      measure double precision);

INSERT INTO 值到indicator_data 表

    INSERT INTO test.indicator_data 
    VALUES
    ('sw5', '2017-07-01', 2, 99212.231),
    ('sw5', '2018-02-01', 2, 232.215),
    ('sw5', '2017-11-01', 3, 1523.2576),
    ('sw5', '2017-12-01', 3, 152.16),
    ('sw5', '2018-02-01', 3, 142.981),
    ('sw5', '2018-07-01', 3 , 142.1361),
    ('sw5 9', '2018-03-01', 2, 821.21),
    ('sw5 9', '2018-02-01', 2, 1182.19);

輸入：

預期產出：

嘗試解決方案：

使用以下程式創建日歷表：

    DELIMITER |
    CREATE PROCEDURE test.fill_calendar(`start_date` DATE, `end_date` DATE)
    BEGIN
      DECLARE `crt_date` DATE;
      SET `crt_date`=start_date;
      WHILE `crt_date` < `end_date` DO
        INSERT INTO calendar VALUES(`crt_date`);
        SET `crt_date` = ADDDATE(`crt_date`, INTERVAL 1 MONTH);
      END WHILE;
    END |
    DELIMITER ;

呼叫程式

CALL test.fill_calendar('2017-07-01', '2018-07-01');

查詢試圖丟失日期，但未能獲得 last_values

    select postcode,last_value(postcode) over (order by datefield) from (
    SELECT indicator_data.postcode,calendar.datefield AS date,indicator_data.indicator_cat,indicator_data.measure
    FROM indicator_data RIGHT join calendar ON (DATE(indicator_data.month_ts) = calendar.datefield)
    WHERE (calendar.datefield BETWEEN (SELECT MIN(DATE(month_ts)) FROM indicator_data) AND (SELECT MAX(DATE(month_ts)) FROM indicator_data))
    GROUP BY date) as test;

uj5u.com熱心網友回復：

您可以使用遞回 CTE 創建日歷表的結果集。
然后LEFT將日歷連接到表格并使用SUM()視窗函式創建帶有nullin的行組measure。
最后使用MAX()視窗函式選擇最后一個非 null measure：

WITH  
  RECURSIVE dates AS (
    SELECT postcode, indicator_cat, MIN(month_ts) month_ts, MAX(month_ts) max_month_ts 
    FROM indicator_data
    GROUP BY postcode, indicator_cat
    UNION ALL
    SELECT postcode, indicator_cat, month_ts   INTERVAL 1 MONTH, max_month_ts 
    FROM dates 
    WHERE month_ts   INTERVAL 1 MONTH <= max_month_ts
  ),
  cte AS (
    SELECT d.postcode, d.month_ts, d.indicator_cat, i.measure,
           SUM(i.measure IS NOT NULL) OVER (PARTITION BY d.postcode, d.indicator_cat ORDER BY d.month_ts) grp
    FROM dates d LEFT JOIN indicator_data i
    ON (i.postcode, i.indicator_cat, i.month_ts) = (d.postcode, d.indicator_cat, d.month_ts)
  )
SELECT postcode, month_ts, indicator_cat,  
       MAX(measure) OVER (PARTITION BY postcode, indicator_cat, grp) measure   
FROM cte
ORDER BY postcode, indicator_cat, month_ts;

請參閱演示。

uj5u.com熱心網友回復：

遞回 CTE 可以獲得預期的結果：

WITH RECURSIVE 
max_info AS (
  SELECT postcode, indicator_cat, MAX(month_ts) month_ts
  FROM indicator_data
  GROUP BY postcode, indicator_cat
),
fill_info AS (
  SELECT postcode, indicator_cat, month_ts, measure
  FROM indicator_data
  UNION ALL
  SELECT postcode, indicator_cat, month_ts   INTERVAL 1 MONTH month_ts, measure
  FROM fill_info i
  WHERE NOT EXISTS (
    SELECT 1
    FROM indicator_data d
    WHERE d.postcode = i.postcode
    AND d.indicator_cat = i.indicator_cat
    AND d.month_ts = i.month_ts   INTERVAL 1 MONTH
  )
  AND EXISTS (
    SELECT 1
    FROM max_info m
    WHERE m.postcode = i.postcode
    AND m.indicator_cat = i.indicator_cat
    AND m.month_ts > i.month_ts   INTERVAL 1 MONTH
  )
)
SELECT postcode, month_ts, indicator_cat, measure
FROM fill_info
ORDER BY postcode, indicator_cat, month_ts

標籤：mysql sql

上一篇：JavaSqlite如何列印a.rowname

下一篇：如何計算每個月的收入？