会員ステータス管理のテーブルからそのステータスであった期間を集計したい

Question

###会員ステータス管理のテーブルからそのステータスであった期間を集計したい

会員ID,入退会区分,入退会日,コース というカラムで構成されたテーブルがあります。
入退会日はvarcharでyyyymmddの形で値が入っています。

会員ID	入退会区分	入退会日	コース
0001	1	20180101	おためしコース
0001	9	20180110
0001	2	20180115	本格コース
0001	9	20180120
0002	1	20180101	おためしコース
0002	9	20180110
0002	2	20180115	本格コース
0002	9	20180120

入退会区分のフラグは以下のとおりです。

• 0:コース変更
• 1:入会
• 2:再入会
• 3:継続
• 8:退会取消
• 9:退会

入会はどのユーザーでも1回しか存在しません。
その後退会して再入会したときには同じ会員IDで再入会できます。
1は最初の入会で一つだけ、
2再入会と9退会は複数回あれば対になります。
ただ、今回は再入会している人は人数が少ないため無視してもよいとしています。

yyyy年に入会した人が

• 今も継続している
• yyyy+1年で退会している人数
• yyyy+2年で退会している人数
• yyyy+3年で退会している人数

を出したいです。

アウトプットのイメージとしては縦軸に入会年、
横軸に退会年と会員IDで紐付いた別テーブルで持っている会員ランク（A~C）も絡めて出力したいです。

	2018A	2018B	2018C	2019A	2019B	2019C	2020A	2020B	2020C
2018	10	5	3	10	5	3	10	5	3
2019	-	-	-	8	5	8	5	8	5
2020	-	-	-	-	-	-	3	8	5

退会日-入会日が同じ年か1年後か2年後か…
入会日しか存在しないかを分けて出力する方法を模索したのですが
うまく出せる方法が導き出せませんでした。

ご教示いただけないでしょうか。

環境

macローカル　PostgreSQL 13.4(pgadmin4)

Comments

[takanaweb5]

同一日の同一会員IDのレコードが複数存在することは想定するのでしょうか？
一度「9:退会」して同じ日に「8:退会取消」するようなケースの想定が必要か？

ある会員が「9:退会」して「2:再入会」した場合は２名とカウントするのでしょうか？
それとも１名でカウントするのでしょうか？　１名でカウントするのであれば退会日は１番最初の退会日を計算に使用するのか、１番最後の退会日を使用するのかいずれでしょうか？

[shintaro1001]

ありがとうございます。
> 一度「9:退会」して同じ日に「8:退会取消」するようなケースの想定が必要か？
データ上は「8:退会取消」も存在するのですが、件数が少なく集計上あまり意味がないので、想定は不要です。
> ある会員が「9:退会」して「2:再入会」した場合は２名とカウントするのでしょうか？
会員IDの個数でカウントするので2名とカウントしてよいと思います。
（実際は1名の2回の在籍期間という認識です）

Answer 1

同一の会員IDにおいても
入会と退会がセットになった時点でカウントアップするものとします。

CREATE TABLE 会員マスタ
(
    会員ID     varchar(10)
  , 会員ランク varchar(5)
);

CREATE TABLE ステータス管理
(
    会員ID     varchar(10)
  , 入退会区分 varchar(3)
  , 入退会日   char(8)
);

INSERT INTO 会員マスタ
    ( 会員ID, 会員ランク )
VALUES
    ( '0001', 'A' )
  , ( '0002', 'B' )
  , ( '0003', 'C' )
  , ( '0004', 'A' )
  , ( '0005', 'B' )
  , ( '0006', 'C' )
  , ( '0007', 'A' )
  , ( '0008', 'B' )
;

INSERT INTO ステータス管理
    ( 会員ID, 入退会区分, 入退会日 )
VALUES
    ( '0001', '1', '20180101' )
  , ( '0001', '9', '20180110' )
  , ( '0001', '2', '20180115' )
  , ( '0001', '9', '20180120' )
  , ( '0002', '1', '20190101' )
  , ( '0002', '9', '20190110' )
  , ( '0002', '2', '20190115' )
  , ( '0002', '9', '20200120' )
  , ( '0003', '1', '20180115' )
  , ( '0003', '9', '20201231' )
  , ( '0004', '1', '20180101' )
  , ( '0004', '9', '20210331' )
  , ( '0005', '1', '20180120' )
  , ( '0006', '1', '20190331' )
  , ( '0007', '1', '20200115' )
  , ( '0007', '9', '20200115' )
  , ( '0007', '2', '20200115' )
  , ( '0008', '1', '20201201' )
;

WITH step1 ( 会員ID, 区分, 入退会日, 枝番 ) AS 
(
    SELECT 会員ID
         , ( 入退会区分::int + 3 ) / 2 - 1
         , 入退会日::date
         , sum( case when 入退会区分 in ( '1', '2' ) then 1 else 0 end )
               over( partition by 会員ID 
                         order by 入退会日, ( 入退会区分::int + 1 ) % 2, 入退会区分::int
               )
     FROM ステータス管理
    WHERE 入退会区分 IN ( '1', '2', '9' )
      /* 集計対象を2018年以降に入会した会員に限定しています。
         不要なら以下の条件は削除して下さい */
      AND 入退会日::date >= date_trunc( 'year', now() - interval '3 year' )
),
    step2 ( 会員ID, 会員ランク, 枝番, 入会年, 在籍年数 ) AS
(
    SELECT x.会員ID
         , x.会員ランク
         , y.枝番
         , date_part( 'year', max( y.入退会日 ) filter( where 区分 = 1 ) )
         , extract( year from 
               age( max( y.入退会日 ) filter( where 区分 > 1 ) -- 退会
                  , max( y.入退会日 ) filter( where 区分 = 1 ) -- 入会
               )
           )
     FROM 会員マスタ x
     JOIN step1      y
    USING ( 会員ID )
    GROUP BY x.会員ID
           , x.会員ランク
           , y.枝番
)

SELECT 入会年
     , sum(1) filter( where 在籍年数 is null ) 継続
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 and 会員ランク = 'A' ) A_2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 and 会員ランク = 'B' ) B_2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 and 会員ランク = 'C' ) C_2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 and 会員ランク = 'A' ) A_2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 and 会員ランク = 'B' ) B_2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 and 会員ランク = 'C' ) C_2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 and 会員ランク = 'A' ) A_2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 and 会員ランク = 'B' ) B_2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 and 会員ランク = 'C' ) C_2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 and 会員ランク = 'A' ) A_2021
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 and 会員ランク = 'B' ) B_2021
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 and 会員ランク = 'C' ) C_2021
 FROM step2
WHERE 入会年 Is Not Null
GROUP BY 入会年
ORDER BY 入会年
;

入会年　継続　A_2018　B_2019　A_2020　B_2020　C_2020　A_2021
--------------------------------------------------------------
 2018　　1　　　2　　　　　　　　　　　　　　　　1　　　　1　　
--------------------------------------------------------------
 2019　　1　　　　　　　1　　　　　　　　1　　　　　　　　　　
--------------------------------------------------------------
 2020　　2　　　　　　　　　　　 1　　　　　　　　　　　　　　　
--------------------------------------------------------------

　
追記：
コメント欄で返信したインラインビューを用いた一筆書きのSQLです

SELECT 入会年
     , sum(1) filter( where 在籍年数 is null ) 継続
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 and 会員ランク = 'A' ) A_2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 and 会員ランク = 'B' ) B_2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 and 会員ランク = 'C' ) C_2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 and 会員ランク = 'A' ) A_2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 and 会員ランク = 'B' ) B_2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 and 会員ランク = 'C' ) C_2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 and 会員ランク = 'A' ) A_2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 and 会員ランク = 'B' ) B_2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 and 会員ランク = 'C' ) C_2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 and 会員ランク = 'A' ) A_2021
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 and 会員ランク = 'B' ) B_2021
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 and 会員ランク = 'C' ) C_2021
FROM
(
    SELECT x.会員ID
         , x.会員ランク
         , y.入退会区分
         , date_part( 'year', y.入退会日::date ) 入会年
         , extract( year from
               age( lead( y.入退会日, 1 ) 
                        over( partition by y.会員ID 
                                  order by y.入退会日
                                         , ( y.入退会区分::int + 1 ) % 2
                                         , y.入退会区分::int
                        )::date
                  , y.入退会日::date
               )
           ) 在籍年数
     FROM 会員マスタ     x
     JOIN ステータス管理 y
    USING ( 会員ID )
    WHERE y.入退会区分 IN ( '1', '2', '9' )
      AND y.入退会日::date >= date_trunc( 'year', now() - interval '3 year' )
) q
WHERE 入退会区分 IN ( '1', '2' )
GROUP BY 入会年
ORDER BY 入会年
;

　
2021/10/31 コメント欄にいただいたご要望を追記：

「ステータス管理」で完結する形で、入会年と退会年のクロス集計をしたい

SELECT 入会年
     , sum(1) filter( where 在籍年数 is null ) 継続
     , sum(1) filter( where 入会年 + 在籍年数 = 2018 ) _2018
     , sum(1) filter( where 入会年 + 在籍年数 = 2019 ) _2019
     , sum(1) filter( where 入会年 + 在籍年数 = 2020 ) _2020
     , sum(1) filter( where 入会年 + 在籍年数 = 2021 ) _2021
FROM
(
    SELECT 会員ID
         , 入退会区分
         , date_part( 'year', 入退会日::date ) 入会年
         , extract( year from
               age( lead( 入退会日, 1 ) 
                        over( partition by 会員ID 
                                  order by 入退会日
                                         , ( 入退会区分::int + 1 ) % 2
                                         , 入退会区分::int
                        )::date
                  , 入退会日::date
               )
           ) 在籍年数
     FROM ステータス管理
    WHERE 入退会区分 IN ( '1', '2', '9' )
      AND 入退会日::date >= date_trunc( 'year', now() - interval '3 year' )
) q
WHERE 入退会区分 IN ( '1', '2' )
GROUP BY 入会年
ORDER BY 入会年
;

Comments

[shintaro1001]

mayu-様
いつもありがとうございます。
実際のテーブル名、カラム名に置き換えて実行したのですが、下記のエラーが発生してしまいました。
---------------------------
ERROR: operator does not exist: character varying + integer
LINE 4: , ( 入退会区分 + 3 ) / 2 - 1
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
---------------------------
お伝えしそびれていましたが、入退会区分のデータ型はvarcharですので、それによって発生したエラーでしょうか。

型を変換して実行してみようと思いますが、そもそもSQLの意図を捉えられていない箇所がありますのでご教示いただければと思います。。

, ( 入退会区分 + 3 ) / 2 - 1
と
, sum( case when 入退会区分 in ( 1, 2 ) then 1 else 0 end )
over( partition by 会員ID
order by 入退会日, ( 入退会区分 + 1 ) % 2, 入退会区分
は何を意図したものでしょうか。

よろしくお願いします。

[mayu-]

【 1 】
> 入退会区分のデータ型はvarcharですので、それによって発生したエラーでしょうか。

はい。
文字列型の「 数字 」に算術演算子は使えません。
数値型にキャストしてから演算を実施する必要がありますね。

回答のSQL文を修正しておきました。

【 2 】
> ( 入退会区分 + 3 ) / 2 - 1

1( 入会 )と 2( 再入会 ) をともに 1( 入会 ) として扱うための演算です。

(2 + 3) / 2 = 2
(1 + 3) / 2 = 2
となり、
2 から 1 を引くと 1 になります。

9( 退会 )は、演算の結果が 5 になりますので
3種類の値( 1, 2, 9 )を、2種類の値( 1 と 5 )へ再分類することが目的です。

ただ、元の値( 1, 2, 9 )のままでも入会と退会は判別できますから
わかりにくいようでしたら、ご自身が理解しやすい記述へ変更することをお薦めします。

【 3 】
> , sum( case when 入退会区分 in ( 1, 2 ) then 1 else 0 end )
> over( partition by 会員ID
> order by 入退会日, ( 入退会区分 + 1 ) % 2, 入退会区分

分析関数の sum は、直近の入会・退会を1組のペアとして扱い、
会員ID毎にグループ連番を振るために使用しています。
また、over句での order by は
ステータス管理テーブルの データ例：0007 のように
試用期限から本番への切替えが同日というデータの存在を懸念した処置になります。
( 翌日の切替えとなる仕様がベストですし、そうなっているとは思いますが念のため )

同日で複数の区分が混在した場合、入退会日 だけをソートキーにすると
入退会区分 がどのような並び順になるかは保証されません。
入退会区分 をソートキーに加えても

20200115　　1
20200115　　2
20200115　　9

という並びにはなるのですけど、入会と退会を繰り返した場合は

20200115　　1
20200115　　9
20200115　　2

の並び順になるよう、手を加える必要があります。
したがって、ソートキーをもう一つ増やし

20200115　　0　　1
20200115　　0　　9
20200115　　1　　2

という３つのキーでソートしています。
同日の入退会が２回以上ループして発生することは無いでしょうし
あるとしたら、別の手段を講じることになるでしょう。

[shintaro1001]

出力することができました！ありがとうございました！
> したがって、ソートキーをもう一つ増やし
>
> 20200115　　0　　1
> 20200115　　0　　9
> 20200115　　1　　2
かなりレアですが、上記のようなことまで考慮していただいて感謝します。
できてから聞くのも変ですが、SQLの処理についてももう少し教えていただきたいのですが、
step1,step2を通して出てくる「枝番」はどこで定義して、どういう役割を果たしているのでしょうか？
目的は果たすことができたのですが、後学のために教えていただけますと幸いです。

[mayu-]

> step1,step2を通して出てくる「枝番」はどこで定義して、
> どういう役割を果たしているのでしょうか？

データをどのように変形させているか順番に説明しますね。

【 1 】
ステータス管理テーブルの生データ

( 会員ID, 入退会区分, 入退会日 )
----------------------------------
0001　　1　　20180101
0001　　9　　20180110
0001　　2　　20180115
0001　　9　　20180120

【 2 】
sum関数でグループ連番(枝番)を付与します
( step1 )

( 会員ID, 入退会区分, 入退会日, 枝番 )
----------------------------------
0001　　1　　20180101　　1
0001　　9　　20180110　　1
0001　　2　　20180115　　2
0001　　9　　20180120　　2

【 3 】
会員ID + 枝番 でグループ化し、入会日と退会日を同じ行に並べます
( step2 )

( 会員ID, 会員ランク, 枝番, 入会日, 退会日, 入会年, 在籍年数 )
------------------------------------------------------
0001　　A　　1　　20180101　　20180110　　2018　　0
0001　　A　　2　　20180115　　20180120　　2018　　0

こんな感じでしょうか。

なお、今回の回答では　 step1　→　step2　→　集計
とデータ加工の段階ごとにクエリを分けて記述したのですけど
階層構造になっている SQL の読み書きに抵抗が無いようでしたら
分析関数の lead を使って一筆書きで記述することも可能です。

ついでなので、そのSQL文も回答に追記しておきますね。

[shintaro1001]

時間が空いてしまいましたが、改めてありがとうございました。
> アウトプットのイメージとしては縦軸に入会年、
> 横軸に退会年と会員IDで紐付いた別テーブルで持っている会員ランク（A~C）も絡めて出力したいです。
時間が空いたにも関わらず恐縮ですが、上記の別テーブルと連携する形ではなく、「ステータス管理」で完結する形で、入会年と退会年のクロス集計をしたい（会員ランクは無視）のですが、方法がわかりません。
最初に教えていただいたSQLの結果から「A_2018」と「B_2018」と「C_2018」を合算すれば2018年分を出すことはできますが、検算を兼ねて「ステータス管理」で完結する形で出力する方法を教えていただけませんでしょうか。
よろしくお願いいたします。

[mayu-]

回答に追記しておきました。

[shintaro1001]

ありがとうございました！確認できました。