変化値と合計値をもつテーブルの最新分から、各行逆算する SQL

環境

• Oracle 11g

サンプルデータ

テーブル作成

各IDごとに、 1 日分の移動距離と総移動距離をもつテーブル。

create_table

CREATE TABLE TRAVEL_LOG (
  ID              VARCHAR2(1)
 ,DAY             DATE        NOT NULL   -- 日付
 ,DISTANCE        INTEGER     NOT NULL   -- その日の移動距離
 ,DISTANCE_TOTAL  INTEGER     NOT NULL   -- その日の移動距離を含めた合計値
 ,PRIMARY KEY(ID, DAY)
);

データ流し込み

• 最新分の総移動距離は 1000 だが、それ以前の総移動距離はわからない。総移動距離の列が後から足されたという想定。
  • あくまで想定。このテーブル構成がちゃんとしてるとは思えない。
• 各 ID の移動距離には少しだけ差をもたせている。

insert_and_update_samples

INSERT INTO TRAVEL_LOG
    SELECT
      CHR(ASCII('A') + MOD(ROWNUM - 1, 3))   -- [A-C]
     ,TO_DATE('20180101', 'YYYYMMDD') + FLOOR((ROWNUM - 1) / 3)
     ,ROWNUM
     ,0
    FROM
      (SELECT 0 FROM ALL_CATALOG WHERE ROWNUM <= (3 * 5))   -- 5 日分
;

-- 日付の最新分にだけ値を設定する
UPDATE TRAVEL_LOG
SET
  DISTANCE_TOTAL = 1000
WHERE
  (ID, DAY) IN (
      SELECT ID, MAX(DAY) FROM TRAVEL_LOG
      GROUP BY ID
  )
;

内容

select

SELECT * FROM TRAVEL_LOG
ORDER BY ID, DAY;

ID	DAY	DISTANCE	DISTANCE_TOTAL
A	2018-01-01	1	0
A	2018-01-02	4	0
A	2018-01-03	7	0
A	2018-01-04	10	0
A	2018-01-05	13	1000
B	2018-01-01	2	0
B	2018-01-02	5	0
B	2018-01-03	8	0
B	2018-01-04	11	0
B	2018-01-05	14	1000
C	2018-01-01	3	0
C	2018-01-02	6	0
C	2018-01-03	9	0
C	2018-01-04	12	0
C	2018-01-05	15	1000

実施

SQL (SELECT)

fill_distance_history

SELECT
  ID
 ,DAY      
 ,DISTANCE
 ,FIRST_VALUE(DISTANCE_TOTAL)
      OVER (
        PARTITION BY ID
        ORDER BY DAY DESC
      )
      + DISTANCE
      - SUM(DISTANCE)
            OVER (
              PARTITION BY ID
              ORDER BY DAY DESC
            )
FROM
  TRAVEL_LOG
ORDER BY ID, DAY
;

結果

ID	DAY	DISTANCE	DISTANCE_TOTAL
A	2018-01-01	1	966
A	2018-01-02	4	970
A	2018-01-03	7	977
A	2018-01-04	10	987
A	2018-01-05	13	1000
B	2018-01-01	2	962
B	2018-01-02	5	967
B	2018-01-03	8	975
B	2018-01-04	11	986
B	2018-01-05	14	1000
C	2018-01-01	3	958
C	2018-01-02	6	964
C	2018-01-03	9	973
C	2018-01-04	12	985
C	2018-01-05	15	1000

SQL (UPDATE)

(未作成)