今回は以前記事にしたlagを使って前のレコードの値から上昇したのか、下降したのか判定するSQLを紹介します。
今回はsignを使います。
sign(dp or numeric)
--戻り値型＝（入力型と同一）
--引数の符号（-1、0、+1）
--例 sign(-8.4) → -1を返す
--0より大きい → 1を返す
--0より小さい → -1を返す
--0 → 0を返すPostgreSQL 12.4文書
第9章 関数と演算子
9.3. 算術関数と演算子
https://www.postgresql.jp/document/12/html/functions-math.html
データの準備
今回は以下のような株価の終値を使って、前日から上昇しているのか、下降しているのか判定するような想定でいます。
--テーブル作成
create table market(
取引日 DATE
,銘柄 varchar(30)
,終値 numeric)
--データセット
insert into market
values
('2021-01-04','1001 日経２２５',27258),
('2021-01-05','1001 日経２２５',27159),
('2021-01-06','1001 日経２２５',27056),
('2021-01-07','1001 日経２２５',27490),
('2021-01-08','1001 日経２２５',28139),
('2021-01-12','1001 日経２２５',28164),
('2021-01-13','1001 日経２２５',28457),
('2021-01-14','1001 日経２２５',28698),
('2021-01-15','1001 日経２２５',28519),
('2021-01-18','1001 日経２２５',28242),
('2021-01-19','1001 日経２２５',28633),
('2021-01-20','1001 日経２２５',28523),
('2021-01-21','1001 日経２２５',28757),
('2021-01-22','1001 日経２２５',28631),
('2021-01-25','1001 日経２２５',28822),
('2021-01-26','1001 日経２２５',28546),
('2021-01-27','1001 日経２２５',28635),
('2021-01-28','1001 日経２２５',28197),
('2021-01-29','1001 日経２２５',27663),
('2021-02-01','1001 日経２２５',28091),
('2021-02-02','1001 日経２２５',28362),
('2021-02-03','1001 日経２２５',28647),
('2021-02-04','1001 日経２２５',28342),
('2021-02-05','1001 日経２２５',28779),
('2021-02-08','1001 日経２２５',29389),
('2021-02-09','1001 日経２２５',29506),
('2021-02-10','1001 日経２２５',29563),
('2021-02-12','1001 日経２２５',29520),
('2021-02-15','1001 日経２２５',30084),
('2021-02-16','1001 日経２２５',30468),
('2021-02-17','1001 日経２２５',30292),
('2021-02-18','1001 日経２２５',30236),
('2021-02-19','1001 日経２２５',30018),
('2021-02-22','1001 日経２２５',30156),
('2021-02-24','1001 日経２２５',29671),
('2021-02-25','1001 日経２２５',30168),
('2021-02-26','1001 日経２２５',28966),
('2021-03-01','1001 日経２２５',29664),
('2021-03-02','1001 日経２２５',29408),
('2021-03-03','1001 日経２２５',29559),
('2021-03-04','1001 日経２２５',28930),
('2021-03-05','1001 日経２２５',28864),
('2021-03-08','1001 日経２２５',28743),
('2021-03-09','1001 日経２２５',29028),
('2021-03-10','1001 日経２２５',29037),
('2021-03-11','1001 日経２２５',29212),
('2021-03-12','1001 日経２２５',29717),
('2021-01-04','1002 ＴＯＰＩＸ',1794),
('2021-01-05','1002 ＴＯＰＩＸ',1791),
('2021-01-06','1002 ＴＯＰＩＸ',1796),
('2021-01-07','1002 ＴＯＰＩＸ',1826),
('2021-01-08','1002 ＴＯＰＩＸ',1854),
('2021-01-12','1002 ＴＯＰＩＸ',1857),
('2021-01-13','1002 ＴＯＰＩＸ',1864),
('2021-01-14','1002 ＴＯＰＩＸ',1873),
('2021-01-15','1002 ＴＯＰＩＸ',1856),
('2021-01-18','1002 ＴＯＰＩＸ',1845),
('2021-01-19','1002 ＴＯＰＩＸ',1855),
('2021-01-20','1002 ＴＯＰＩＸ',1849),
('2021-01-21','1002 ＴＯＰＩＸ',1860),
('2021-01-22','1002 ＴＯＰＩＸ',1856),
('2021-01-25','1002 ＴＯＰＩＸ',1862),
('2021-01-26','1002 ＴＯＰＩＸ',1848),
('2021-01-27','1002 ＴＯＰＩＸ',1860),
('2021-01-28','1002 ＴＯＰＩＸ',1838),
('2021-01-29','1002 ＴＯＰＩＸ',1808),
('2021-02-01','1002 ＴＯＰＩＸ',1829),
('2021-02-02','1002 ＴＯＰＩＸ',1847),
('2021-02-03','1002 ＴＯＰＩＸ',1871),
('2021-02-04','1002 ＴＯＰＩＸ',1865),
('2021-02-05','1002 ＴＯＰＩＸ',1890),
('2021-02-08','1002 ＴＯＰＩＸ',1923),
('2021-02-09','1002 ＴＯＰＩＸ',1925),
('2021-02-10','1002 ＴＯＰＩＸ',1930),
('2021-02-12','1002 ＴＯＰＩＸ',1933),
('2021-02-15','1002 ＴＯＰＩＸ',1953),
('2021-02-16','1002 ＴＯＰＩＸ',1965),
('2021-02-17','1002 ＴＯＰＩＸ',1961),
('2021-02-18','1002 ＴＯＰＩＸ',1941),
('2021-02-19','1002 ＴＯＰＩＸ',1928),
('2021-02-22','1002 ＴＯＰＩＸ',1938),
('2021-02-24','1002 ＴＯＰＩＸ',1903),
('2021-02-25','1002 ＴＯＰＩＸ',1926),
('2021-02-26','1002 ＴＯＰＩＸ',1864),
('2021-03-01','1002 ＴＯＰＩＸ',1902),
('2021-03-02','1002 ＴＯＰＩＸ',1894),
('2021-03-03','1002 ＴＯＰＩＸ',1904),
('2021-03-04','1002 ＴＯＰＩＸ',1884),
('2021-03-05','1002 ＴＯＰＩＸ',1896),
('2021-03-08','1002 ＴＯＰＩＸ',1893),
('2021-03-09','1002 ＴＯＰＩＸ',1917),
('2021-03-10','1002 ＴＯＰＩＸ',1919),
('2021-03-11','1002 ＴＯＰＩＸ',1924),
('2021-03-12','1002 ＴＯＰＩＸ',1951)lagとsignを使って上昇下降を判断する
早速、lagとsignを使って上昇下降を判断するSQLを書いてみます。
select 取引日
,銘柄
,終値
,lag(終値, 1) over (partition by 銘柄 order by 取引日) as 前日終値
,終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日) as 前日終値差
,round((1 - lag(終値, 1) over (partition by 銘柄 order by 取引日) / 終値) * 100 ,2) as 前日終値比率
,sign(終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日)) as 上昇下降符号
,case sign(終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日))
when 0 then '横ばい'
when 1 then '上昇'
when -1 then '下降'
else null end as 上昇下降
from market見ていただきたいのは7行目ですね。
sign(終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日))現在の終値から前日の終値を引いた値をsign関数で符号（1か0か-1）として返すようになっています。
結果は以下の通りです。
折角ですので、これまで紹介した以下のSQLを使って、株価分析に使えそう（実際に使えるかは不明）のSQLを紹介します。
データは同じものを使います。
with base as (select 取引日
,銘柄
,終値
,sign(終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日)) as 上昇下降符号
,case sign(終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日))
when 0 then '横ばい'
when 1 then '上昇'
when -1 then '下降'
else null end as 上昇下降
,lag(終値, 1) over (partition by 銘柄 order by 取引日) as 前日終値
,終値 - lag(終値, 1) over (partition by 銘柄 order by 取引日) as 前日終値差
,round((1 - lag(終値, 1) over (partition by 銘柄 order by 取引日) / 終値) * 100 ,2) as 前日終値比率
from market
),
grouping as (select 取引日
,銘柄
,上昇下降符号
,row_number() over (partition by 銘柄 order by 取引日) as 採番値
,row_number() over (partition by 銘柄,上昇下降符号 order by 取引日) as 上昇下降採番値
,row_number() over (partition by 銘柄 order by 取引日) -
row_number() over (partition by 銘柄,上昇下降符号 order by 取引日) as 連続上昇下降グループ
from base
),
continuance as (select 銘柄
,上昇下降符号
,連続上昇下降グループ
,sum(case when 上昇下降符号 = 0 then 1 else 0 end) as 連続横ばい
,sum(case when 上昇下降符号 = 1 then 1 else 0 end) as 連続上昇
,sum(case when 上昇下降符号 = -1 then 1 else 0 end) as 連続下降
from grouping
group by 銘柄
,上昇下降符号
,連続上昇下降グループ
)
select bs.取引日
,bs.銘柄
,bs.終値
,bs.上昇下降符号
,bs.上昇下降
,bs.前日終値
,bs.前日終値差
,bs.前日終値比率
,gp.採番値
,gp.上昇下降採番値
,gp.連続上昇下降グループ
,case bs.上昇下降符号 when 0 then ct.連続横ばい
when 1 then ct.連続上昇
when -1 then ct.連続下降
else null end as 継続
,row_number() over (partition by bs.銘柄,cast(bs.上昇下降符号 as varchar(2)) || cast(gp.連続上昇下降グループ as varchar(4))
order by gp.上昇下降採番値 ) 継続内訳
from base bs
left join grouping gp
on bs.取引日 = gp.取引日
and bs.銘柄 = gp.銘柄
left join continuance ct
on gp.銘柄 = ct.銘柄
and gp.上昇下降符号 = ct.上昇下降符号
and gp.連続上昇下降グループ = ct.連続上昇下降グループ
order by bs.銘柄
,bs.取引日結果は以下の通りです。
それぞれを説明しますと
★上昇下降符号・上昇下降 ＝ 前日から終値が上昇・下降・横ばいかどうか
★前日終値差・前日終値比率 ＝ 前日終値との差額、比率
★採番値 ＝ 各銘柄を取引日順にシーケンスした結果
★上昇下降採番値 ＝ 各銘柄について上昇・下降・横ばいそれぞれを取引日の順にシーケンス
★連続上昇下降グループ ＝ 連続して上昇・下降・横ばいが発生している場合のグループID ＝ 採番値から上昇下降採番値を引いた値
★継続 ＝ 連続中の上昇・下降・横ばいの最終値
★継続内訳 ＝ 該当取引日の上昇・下降・横ばいが何連続中かの値（多分あっていると思う？？）
以上。
株価分析で使えそう（使えるとは言っていない）なSQLでした。
そのほか、身近なところで
こういうデータを抽出したい？
こういう分析をしたい場合、どういうデータを準備したら良い？
などあれば、別途お問い合わせください。
わかる範囲でお答えします。
では。