今回は以前記事にした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 日経225',27258),
('2021-01-05','1001 日経225',27159),
('2021-01-06','1001 日経225',27056),
('2021-01-07','1001 日経225',27490),
('2021-01-08','1001 日経225',28139),
('2021-01-12','1001 日経225',28164),
('2021-01-13','1001 日経225',28457),
('2021-01-14','1001 日経225',28698),
('2021-01-15','1001 日経225',28519),
('2021-01-18','1001 日経225',28242),
('2021-01-19','1001 日経225',28633),
('2021-01-20','1001 日経225',28523),
('2021-01-21','1001 日経225',28757),
('2021-01-22','1001 日経225',28631),
('2021-01-25','1001 日経225',28822),
('2021-01-26','1001 日経225',28546),
('2021-01-27','1001 日経225',28635),
('2021-01-28','1001 日経225',28197),
('2021-01-29','1001 日経225',27663),
('2021-02-01','1001 日経225',28091),
('2021-02-02','1001 日経225',28362),
('2021-02-03','1001 日経225',28647),
('2021-02-04','1001 日経225',28342),
('2021-02-05','1001 日経225',28779),
('2021-02-08','1001 日経225',29389),
('2021-02-09','1001 日経225',29506),
('2021-02-10','1001 日経225',29563),
('2021-02-12','1001 日経225',29520),
('2021-02-15','1001 日経225',30084),
('2021-02-16','1001 日経225',30468),
('2021-02-17','1001 日経225',30292),
('2021-02-18','1001 日経225',30236),
('2021-02-19','1001 日経225',30018),
('2021-02-22','1001 日経225',30156),
('2021-02-24','1001 日経225',29671),
('2021-02-25','1001 日経225',30168),
('2021-02-26','1001 日経225',28966),
('2021-03-01','1001 日経225',29664),
('2021-03-02','1001 日経225',29408),
('2021-03-03','1001 日経225',29559),
('2021-03-04','1001 日経225',28930),
('2021-03-05','1001 日経225',28864),
('2021-03-08','1001 日経225',28743),
('2021-03-09','1001 日経225',29028),
('2021-03-10','1001 日経225',29037),
('2021-03-11','1001 日経225',29212),
('2021-03-12','1001 日経225',29717),
('2021-01-04','1002 TOPIX',1794),
('2021-01-05','1002 TOPIX',1791),
('2021-01-06','1002 TOPIX',1796),
('2021-01-07','1002 TOPIX',1826),
('2021-01-08','1002 TOPIX',1854),
('2021-01-12','1002 TOPIX',1857),
('2021-01-13','1002 TOPIX',1864),
('2021-01-14','1002 TOPIX',1873),
('2021-01-15','1002 TOPIX',1856),
('2021-01-18','1002 TOPIX',1845),
('2021-01-19','1002 TOPIX',1855),
('2021-01-20','1002 TOPIX',1849),
('2021-01-21','1002 TOPIX',1860),
('2021-01-22','1002 TOPIX',1856),
('2021-01-25','1002 TOPIX',1862),
('2021-01-26','1002 TOPIX',1848),
('2021-01-27','1002 TOPIX',1860),
('2021-01-28','1002 TOPIX',1838),
('2021-01-29','1002 TOPIX',1808),
('2021-02-01','1002 TOPIX',1829),
('2021-02-02','1002 TOPIX',1847),
('2021-02-03','1002 TOPIX',1871),
('2021-02-04','1002 TOPIX',1865),
('2021-02-05','1002 TOPIX',1890),
('2021-02-08','1002 TOPIX',1923),
('2021-02-09','1002 TOPIX',1925),
('2021-02-10','1002 TOPIX',1930),
('2021-02-12','1002 TOPIX',1933),
('2021-02-15','1002 TOPIX',1953),
('2021-02-16','1002 TOPIX',1965),
('2021-02-17','1002 TOPIX',1961),
('2021-02-18','1002 TOPIX',1941),
('2021-02-19','1002 TOPIX',1928),
('2021-02-22','1002 TOPIX',1938),
('2021-02-24','1002 TOPIX',1903),
('2021-02-25','1002 TOPIX',1926),
('2021-02-26','1002 TOPIX',1864),
('2021-03-01','1002 TOPIX',1902),
('2021-03-02','1002 TOPIX',1894),
('2021-03-03','1002 TOPIX',1904),
('2021-03-04','1002 TOPIX',1884),
('2021-03-05','1002 TOPIX',1896),
('2021-03-08','1002 TOPIX',1893),
('2021-03-09','1002 TOPIX',1917),
('2021-03-10','1002 TOPIX',1919),
('2021-03-11','1002 TOPIX',1924),
('2021-03-12','1002 TOPIX',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でした。
そのほか、身近なところで
こういうデータを抽出したい?
こういう分析をしたい場合、どういうデータを準備したら良い?
などあれば、別途お問い合わせください。
わかる範囲でお答えします。
では。