日ごとに変動する値の上昇期間を求める
累積した日々の集計で日々変動する値があり、前日と比較して上昇している期間を求めるSQLを考えてみます。
今まで私が触ったことがあるSQLを使ったデータベースはACCESSのみですが、SQLをもう少し勉強したいと思い、「達人に学ぶSQL徹底指南書 ミック著」をチャレンジしてみました。
ACCESSでは実行できないSQLがたくさんありましたが、ACCESSでできるようにするにはどうしたらいいのかと考えながら読みました。
この本の中のP207が今後参考になると思い書き留めました。
SQL文の作成
左が元となる日々のデータを集積した表です。
価格の変動が日々あり、上昇した日は2021-09-23、2021-09-15～2021-09-20以下略で右が目的とする表です。元の表これをテーブル名累積表とします。
SELECT
A.*
, (SELECT
B.価格
FROM
(SELECT
X.*
, (SELECT
COUNT(*)
FROM
累積表 AS Y
WHERE
Y.取引日 <= X.取引日
) AS ROW
FROM
累積表 AS X
) AS B
WHERE
B.ROW = A.ROW - 1
) AS 前日
FROM
(SELECT
X.*
, (SELECT
COUNT(*)
FROM
累積表 AS Y
WHERE
Y.取引日 <= X.取引日
) AS ROW
FROM 累積表 AS X
) AS A;
上記SQLを実行すると右のような表になります。
土日、休日は取引がないため取引日は連続してません。前営業日と比較するためには、連続したインデックスが必要なってきます。それをROWとしています。
これで比較することができます。
SELECT
AA.取引日
, AA.価格
, SWITCH(AA.価格 > AA.前日, 'UP'
, AA.価格 < AA.前日, 'DOWN'
, TRUE, 'STAY') AS DIFF
, AA.ROW
FROM
(SELECT
A.*
, (SELECT
B.価格
FROM
(SELECT
X.*
, (SELECT
COUNT(*)
FROM
累積表 AS Y
WHERE
Y.取引日 <= X.取引日
) AS ROW
FROM
累積表 AS X
) AS B
WHERE
B.ROW = A.ROW - 1
) AS 前日
FROM
(SELECT
X.*
, (SELECT
COUNT(*)
FROM
累積表 AS Y
WHERE
Y.取引日 <= X.取引日
) AS ROW
FROM
累積表 AS X
) AS A
) AS AA
WHERE
SWITCH(AA.価格 > AA.前日, 'UP'
, AA.価格 < AA.前日, 'DOWN'
, TRUE, 'STAY') = 'UP';
上のSQL文を実行すると右の表になります。下のSQL文の中の仮表を上のSQLに置き換えて実行させたところACCESSが落ちてしまいました。
仕方がないので右のテーブルを作りこれを仮表とし、下記のSQLを実行すると最終目的の表となります。
SELECT
MIN(CC.取引日) AS 期間１
, '～' AS ～
,MAX(CC.取引日) AS 期間２
FROM
(SELECT
AAA.取引日
, COUNT(BBB.ROW) - MIN(AAA.ROW) AS GAP
FROM
仮表 AS AAA
INNER JOIN
仮表 AS BBB ON BBB.ROW <= AAA.ROW
GROUP BY AAA.取引日
) AS CC
GROUP BY CC.GAP;
このSQLのしていることは、ROWの値と行数の差を求めそれをグループ化しています。
1-3，2-4，3-5、4-9、5-11、6-12、7-13、8-14、9-17を計算して-2、-2、-2、-5、-6、-6、-6、-6、-8
これをグループ化して取引日の最小値と最大値をとっています。