管理人Kのひとりごと

デジモノレビューやプログラミングや写真など

相関サブクエリで直近3日間の通信量を求める(sql)

日ごとの通信量が記録されたテーブルを使って、直近3日間(当日より前の3日間)の通信量の合計を求めたかったのでメモ。

やりたいこと

usage_date usage_vol
2019/4/2 2.83
2019/4/3 1.51
2019/4/4 1.67
2019/4/5 1.01
2019/4/6 2.14
2019/4/7 1.9
... ...

日ごとの通信料が記録されたテーブルを使って、直近3日間(当日より前の3日間)の通信量の合計を求めたい。こんな感じで↓

usage_date usage_vol 直近3日間の通信量
2019/4/2 2.83 -
2019/4/3 1.51 2.83
2019/4/4 1.67 4.34
2019/4/5 1.01 6.01
2019/4/6 2.14 4.19
2019/4/7 1.9 4.82

確認環境

SQL> select version_full from v$instance;

VERSION_FULL
-----------------
18.3.0.0.0

作成したSQL

SELECT
    t1.d_usage_date as usage_date
   ,t1.num_usage_vol as usage_vol
   ,sum(t2.num_usage_vol) as usage_vol_3days
FROM
    wimax_usage_vol t1 left outer JOIN wimax_usage_vol t2
    ON  t1.d_usage_date-3 <= t2.d_usage_date
    AND t2.d_usage_date < t1.d_usage_date
GROUP BY
    t1.d_usage_date
   ,t1.num_usage_vol
ORDER BY
    t1.d_usage_date
USAGE_D USAGE_VOL USAGE_VOL_3DAYS
19-04-02 2.83 -
19-04-03 1.51 2.83
19-04-04 1.67 4.34
19-04-05 1.01 6.01
19-04-06 2.14 4.19
19-04-07 1.9 4.82

上の表のSUMをほどくと、usage_dateごとに直近3日の使用量が抽出されています。

USAGE_D USAGE_VOL USAGE_VOL_3DAYS
19-04-02 2.83 -
19-04-03 1.51 2.83
19-04-04 1.67 1.51
19-04-04 1.67 2.83
19-04-05 1.01 1.51
19-04-05 1.01 1.67
19-04-05 1.01 2.83
19-04-06 2.14 1.01
19-04-06 2.14 1.51
19-04-06 2.14 1.67
19-04-07 1.9 1.01
19-04-07 1.9 1.67
19-04-07 1.9 2.14
SELECT
    t1.d_usage_date as usage_date
   ,t1.num_usage_vol as usage_vol
   ,t2.num_usage_vol as usage_vol_3days
FROM
    wimax_usage_vol t1 left outer JOIN wimax_usage_vol t2
    ON  t1.d_usage_date-3 <= t2.d_usage_date
    AND t2.d_usage_date < t1.d_usage_date
GROUP BY
    t1.d_usage_date
   ,t1.num_usage_vol
   ,t2.num_usage_vol
ORDER BY
    t1.d_usage_date

参考にしました