回答編集履歴

SQLの記述を追記

2021/10/31 08:52

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -329,3 +329,75 @@
 ;
 ```
+**2021/10/31 コメント欄にいただいたご要望を追記：**
+> 「ステータス管理」で完結する形で、入会年と退会年のクロス集計をしたい
+```SQL
+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 入会年
+;
+```

SQLの記述を追記

2021/10/31 08:52

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -235,3 +235,97 @@
 --------------------------------------------------------------
 ```
+**追記：**
+コメント欄で返信したインラインビューを用いた一筆書きのSQLです
+```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 入会年
+;
+```

型の変更に伴うSQL文修正及びコメントの追加

2021/10/22 17:44

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -24,7 +24,7 @@
     会員ID     varchar(10)
-  , 入退会区分 int
+  , 入退会区分 varchar(3)
   , 入退会日   char(8)
@@ -64,41 +64,41 @@
 VALUES
-    ( '0001', 1, '20180101' )
+    ( '0001', '1', '20180101' )
-  , ( '0001', 9, '20180110' )
+  , ( '0001', '9', '20180110' )
-  , ( '0001', 2, '20180115' )
+  , ( '0001', '2', '20180115' )
-  , ( '0001', 9, '20180120' )
+  , ( '0001', '9', '20180120' )
-  , ( '0002', 1, '20190101' )
+  , ( '0002', '1', '20190101' )
-  , ( '0002', 9, '20190110' )
+  , ( '0002', '9', '20190110' )
-  , ( '0002', 2, '20190115' )
+  , ( '0002', '2', '20190115' )
-  , ( '0002', 9, '20200120' )
+  , ( '0002', '9', '20200120' )
-  , ( '0003', 1, '20180115' )
+  , ( '0003', '1', '20180115' )
-  , ( '0003', 9, '20201231' )
+  , ( '0003', '9', '20201231' )
-  , ( '0004', 1, '20180101' )
+  , ( '0004', '1', '20180101' )
-  , ( '0004', 9, '20210331' )
+  , ( '0004', '9', '20210331' )
-  , ( '0005', 1, '20180120' )
+  , ( '0005', '1', '20180120' )
-  , ( '0006', 1, '20190331' )
+  , ( '0006', '1', '20190331' )
-  , ( '0007', 1, '20200115' )
+  , ( '0007', '1', '20200115' )
-  , ( '0007', 9, '20200115' )
+  , ( '0007', '9', '20200115' )
-  , ( '0007', 2, '20200115' )
+  , ( '0007', '2', '20200115' )
-  , ( '0008', 1, '20201201' )
+  , ( '0008', '1', '20201201' )
 ;
@@ -112,21 +112,21 @@
     SELECT 会員ID
-         , ( 入退会区分 + 3 ) / 2 - 1
+         , ( 入退会区分::int + 3 ) / 2 - 1
          , 入退会日::date
-         , sum( case when 入退会区分 in ( 1, 2 ) then 1 else 0 end )
+         , sum( case when 入退会区分 in ( '1', '2' ) then 1 else 0 end )
                over( partition by 会員ID
-                         order by 入退会日, ( 入退会区分 + 1 ) % 2, 入退会区分
+                         order by 入退会日, ( 入退会区分::int + 1 ) % 2, 入退会区分::int
                )
      FROM ステータス管理
-    WHERE 入退会区分 IN ( 1, 2, 9 )
+    WHERE 入退会区分 IN ( '1', '2', '9' )
       /* 集計対象を2018年以降に入会した会員に限定しています。
@@ -150,9 +150,9 @@
          , extract( year from
-               age( max( y.入退会日 ) filter( where 区分 != 1 )
+               age( max( y.入退会日 ) filter( where 区分 > 1 ) -- 退会
-                  , max( y.入退会日 ) filter( where 区分  = 1 )
+                  , max( y.入退会日 ) filter( where 区分 = 1 ) -- 入会
                )

コメント追加

2021/10/21 21:07

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -128,6 +128,10 @@
     WHERE 入退会区分 IN ( 1, 2, 9 )
+      /* 集計対象を2018年以降に入会した会員に限定しています。
+         不要なら以下の条件は削除して下さい */
       AND 入退会日::date >= date_trunc( 'year', now() - interval '3 year' )
 ),

SQL文修正

2021/10/21 14:28

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -200,6 +200,8 @@
  FROM step2
+WHERE 入会年 Is Not Null
 GROUP BY 入会年
 ORDER BY 入会年