何連勝中（何連敗中）か判別するSQL

boxcodeadmin

2021年2月7日

ちょっと個人的に株価分析をしておりまして

何日連続で株価が上昇もしくは下降しているのか調べるSQLを作っていましたので、

その内容の一部をご紹介。

しかしながら、株価でもって何日連続して上昇下降しているのかを見るためには

前日から比べ上昇した場合は1、平行線の場合は0、下降した場合は-1のように

上昇下降が分かるような前処理をしなければなりません。

その前処理については長くなるため、改めて記事にしたく思いますので、

今回は株価データでなく、以下のようなスポーツ勝敗結果で持って、何連勝（何連敗）したかを判別するSQLをご参考いただければと思います。

データの説明

gamedate	winlose
2021/01/04	0
2021/01/05	-1
2021/01/06	1
2021/01/07	1
2021/01/08	1
2021/01/12	1
2021/01/13	0
・・・	・・・

上記はとあるチームの日毎の勝敗表になっています。

gamedateは試合が行われた日＝試合日です

winloseは勝敗を示し、1=勝利、0＝引き分け、-1=敗北を表します。

2021/01/06から2021/01/12の間、勝ちが続いており、ここに4連勝と返すSQLをイメージいただければと思います。

＜目標結果＞

gamedate	winlose	continuation
2021/01/04	0	1
2021/01/05	-1	1
2021/01/06	1	4
2021/01/07	1	4
2021/01/08	1	4
2021/01/12	1	4
2021/01/13	0	1
・・・	・・・

データの準備

データは以下のスクリプトを実行してください。

--テーブルの作成
create table winlose (
       gamedate date
      ,winlose numeric
)

--データセット
insert into winlose (gamedate, winlose)
values
('2021/01/04', 0),
('2021/01/05', -1),
('2021/01/06', 1),
('2021/01/07', 1),
('2021/01/08', 1),
('2021/01/12', 1),
('2021/01/13', 0),
('2021/01/14', 1),
('2021/01/15', 1),
('2021/01/18', -1),
('2021/01/19', 1),
('2021/01/20', 1),
('2021/01/21', -1),
('2021/01/22', -1),
('2021/01/25', 1),
('2021/01/26', -1),
('2021/01/27', 1),
('2021/01/28', -1),
('2021/01/29', 1)

勝利・引き分け・敗北が継続しているグループを作成する

ちょっと日本語わかりづらく、恐縮です。

継続している（連続している）勝利・引き分け・敗北をそれぞれグループ分けしていきます。

gamedate	winlose	group
2021/01/04	0	引き分け1グループ
2021/01/05	-1	敗北1グループ
2021/01/06	1	勝利1グループ
2021/01/07	1	勝利1グループ
2021/01/08	1	勝利1グループ
2021/01/12	1	勝利1グループ
2021/01/13	0	引き分け2グループ
・・・	・・・

2021/01/04に引き分けし、「引き分け1グループ」が作成されます。

続いて、残念ながら、2021/01/05に敗北しました。ここで「敗北1グループ」が出来上がります。

さらに、2021/01/06に勝利を収め、2021/01/12まで連勝が続きます。

ここは2021/01/06から2021/01/12までを「勝利1グループ」とします。

※後述しますが、実際には若い日付から順に1からカウントされていくわけではありません。

これを表すには以下のSQLで対応します。

select *
      ,row_number() over (order by gamedate) as dateindex
      ,row_number() over (partition by winlose order by gamedate) as eachindex 
      ,row_number() over (order by gamedate) - 
       row_number() over (partition by winlose order by gamedate) as groupid
  from winlose
 order by gamedate

以下のような結果を得られます。

それぞれを説明すると

row_number() over (order by gamedate) as dateindex　・・・A

は日付順に単純にシーケンス処理を施しております。

row_number() over (partition by winlose order by gamedate) as eachindex　・・・B

一方、上記はwinlose = 勝敗（引き分け含む）単位で日付順にシーケンス処理を行っています。

A – B をすることでwinlose毎に継続中か否かの判断（グループ分け）がなされるという流れです。

勝利・引き分け・敗北の各グループのレコード数をカウントする

次に勝利・引き分け・敗北のそれそれの各グループに属するレコード数をカウントします。

これによって、そのグループが何回継続で同じ状態なのかを判別することができます。

SQLは以下の通りです。

select groupid
      ,winlose
      ,sum(case when winlose = 1 then 1 else 0 end) as win
      ,sum(case when winlose = 0 then 1 else 0 end) as draw
      ,sum(case when winlose = -1 then 1 else 0 end) as lose
  from (select gamedate
              ,winlose
              ,row_number() over (order by gamedate) as dateindex
              ,row_number() over (partition by winlose order by gamedate) as eachindex 
              ,row_number() over (order by gamedate) - 
               row_number() over (partition by winlose order by gamedate) as groupid
          from winlose
         order by gamedate) a
 group by groupid
      ,winlose
 order by groupid

単純に勝利・引き分け・敗北それぞれのグループ単位にレコード数をカウントするために、

win=勝利・draw=引き分け・lose=敗北の列を作成し、対象の場合は1をセットし

グループ毎に1を積み上げていけば、そのグループの最終的な継続回数を返すことができます。

結果は以下の通りです。

勝敗表に勝利・引き分け・敗北の継続回数を結合する

最後にwinloseの勝敗表に勝利・引き分け・敗北の継続回数を返すようなSQLを作成します。

ここでは構成をシンプルにするためサブクエリではなく、with句を使いました。

with grouping as (select gamedate
                   ,winlose
                   ,row_number() over (order by gamedate) as dateindex
                   ,row_number() over (partition by winlose order by gamedate) as eachindex 
                   ,row_number() over (order by gamedate) - 
                    row_number() over (partition by winlose order by gamedate) as groupid
               from winlose
              order by gamedate),
 continuance as (select groupid
                       , winlose
                       ,sum(case when winlose = 1 then 1 else 0 end) as win
                       ,sum(case when winlose = 0 then 1 else 0 end) as draw
                       ,sum(case when winlose = -1 then 1 else 0 end) as lose
                   from grouping
                  group by groupid
                       ,winlose) 
 select wl.gamedate
       ,wl.winlose
       ,gp.groupid
       ,case when wl.winlose = 1 then ct.win
             when wl.winlose = 0 then ct.draw
             when wl.winlose = -1 then ct.lose end as continuation
   from winlose wl
   left join grouping gp
     on wl.gamedate = gp.gamedate
   left join continuance ct
     on gp.groupid = ct.groupid
    and gp.winlose = ct.winlose

特に説明は不要かと思います。

結果は以下の通りです。

さらに以下の文を追加すると、該当のレコードが何連勝（何連敗）中かを見ることができます。

23行目です。

with grouping as (select gamedate
                   ,winlose
                   ,row_number() over (order by gamedate) as dateindex
                   ,row_number() over (partition by winlose order by gamedate) as eachindex 
                   ,row_number() over (order by gamedate) - 
                    row_number() over (partition by winlose order by gamedate) as groupid
               from winlose
              order by gamedate),
 continuance as (select groupid
                       , winlose
                       ,sum(case when winlose = 1 then 1 else 0 end) as win
                       ,sum(case when winlose = 0 then 1 else 0 end) as draw
                       ,sum(case when winlose = -1 then 1 else 0 end) as lose
                   from grouping
                  group by groupid
                       ,winlose) 
 select wl.gamedate
       ,wl.winlose
       ,gp.groupid
       ,case when wl.winlose = 1 then ct.win
             when wl.winlose = 0 then ct.draw
             when wl.winlose = -1 then ct.lose end as continuation
       ,row_number() over (partition by cast(wl.winlose as varchar(2)) || cast(gp.groupid as varchar(4)) order by gp.eachindex) as consecutive
   from winlose wl
   left join grouping gp
     on wl.gamedate = gp.gamedate
   left join continuance ct
     on gp.groupid = ct.groupid
    and gp.winlose = ct.winlose

結果です。