トップ PostgreSQLに関する質問 postgresqlで日毎に通算のユニークユーザー数を算出したい

編集履歴

回答編集履歴

SQL文の修正

2021/09/18 13:37

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -40,7 +40,7 @@
 ##更に追記:
-シンプルにできたかも。`id`も使わない。
+シンプルにできたかも。※ver`9.4`以降
 ```SQL
@@ -48,7 +48,7 @@
      , count( distinct user_id ) 日別UU
-     , sum( count( distinct case when ymd = f then user_id end ) )
+     , sum( count( distinct user_id ) filter( where ymd = f ) )
            over( order by ymd ) 通算UU
@@ -60,7 +60,7 @@
          , user_id
-         , min( tx."date" ) over ( partition by user_id  ) f
+         , min( tx."date" ) over( partition by user_id ) f
     FROM tx
@@ -128,7 +128,7 @@
   , ( 7, '1970-01-06' )
-  ;
+;

SQLの条件表記を略すことにした

2021/09/18 13:37

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -48,7 +48,7 @@
      , count( distinct user_id ) 日別UU
-     , sum( count( distinct case when ymd = f then user_id else null end ) )
+     , sum( count( distinct case when ymd = f then user_id end ) )
            over( order by ymd ) 通算UU

SQL文中のミスを修正

2021/09/18 06:38

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -46,7 +46,7 @@
 SELECT ymd 年月日
-     , count( distinct ymd ) 日別UU
+     , count( distinct user_id ) 日別UU
      , sum( count( distinct case when ymd = f then user_id else null end ) )

SQLのロジックを追記

2021/09/18 06:09

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -38,6 +38,40 @@
+##更に追記:
+シンプルにできたかも。`id`も使わない。
+```SQL
+SELECT ymd 年月日
+     , count( distinct ymd ) 日別UU
+     , sum( count( distinct case when ymd = f then user_id else null end ) )
+           over( order by ymd ) 通算UU
+FROM
+(
+    SELECT tx."date" ymd
+         , user_id
+         , min( tx."date" ) over ( partition by user_id  ) f
+    FROM tx
+) q
+GROUP BY ymd ;
+```
 ##追記：
 新規登録の`user_id`が存在しない日でも

SQLを極力シンプルな記述に変更

2021/09/18 05:02

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -24,6 +24,8 @@
 過去日に登場した`user_id`は当日に再度現れてもカウントしない。
+＃ 実績のある`user_id`の個体数を算出したいのではないか、と推測
 また、初回登録された日に`user_id`がユニークな値にならないのは
@@ -38,11 +40,9 @@
 ##追記：
-あまり良い記述とは思えませんけど
 新規登録の`user_id`が存在しない日でも
-日別UU と 通算UU が両方とも表示されるようにはしておきます。
+日別UU と 通算UU が両方とも表示されるよう外部結合を適用。
@@ -98,136 +98,104 @@
+SELECT x.ymd 年月日
+     , x.duu 日別UU
-WITH m1 ( ymd1, ymd2, 日別UU ) AS
+     , max( y.tuu ) over( order by x.ymd ) 通算UU
+FROM
-(
+(
+    SELECT tx."date" ymd
+         , count( distinct user_id ) duu
+    FROM tx
+    GROUP BY tx."date"
+) x
+LEFT JOIN
+(
-    SELECT x.ymd
+    SELECT DISTINCT
+           ymd
-         , max( y.ymd ) over ( order by x.ymd )
+         , count(1) over( order by ymd ) tuu
-         , x.uu
     FROM
     (
-        SELECT tx."date" ymd
+        SELECT min( tx."date" ) ymd
-             , count( distinct user_id ) uu
         FROM tx
-        GROUP BY tx."date"
+        GROUP BY user_id
-    ) x
+    ) q
+) y
+USING ( ymd )
+;
+```
+推理が当たっていた場合に限りますけど
+~~`通算UU`をわかりやすく計算するのなら~~　←そんなことは無かった
+以下のような`３段のクエリ`になりそうです。
+```SQL
-    LEFT JOIN
+SELECT 年月
+     , sum( 新規uid数 )
+           over( order by 年月
+                 rows between unbounded preceding and current row
+       ) 通算UU
+FROM
+(
+    SELECT 年月
+         , count(1) 新規uid数
+    FROM
     (
-        SELECT DISTINCT min( tx."date" ) ymd
-        FROM tx
-        GROUP BY user_id
+        SELECT user_id
-    ) y
-    ON x.ymd = y.ymd
-),
-    m2 ( ymd2, 通算UU ) AS
-(
-    SELECT DISTINCT
-           ymd
-         , count(1) over( order by ymd )
-    FROM
-    (
-        SELECT min( tx."date" ) ymd
+             , min( t."date" ) 年月
-        FROM tx
+        FROM テーブル名 t
         GROUP BY user_id
-    ) q
+    ) q2
-)
-SELECT m1.ymd1 年月日
-     , m1.日別UU
-     , m2.通算UU
-FROM m1
-JOIN m2
-USING ( ymd2 )
-ORDER BY 1
+    GROUP BY 年月
+) q1
 ;
 ```
-推理が当たっていた場合に限りますけど
-~~`通算UU`をわかりやすく計算するのなら~~　←そんなことは無かった
-以下のような`３段のクエリ`になりそうです。
-```SQL
-SELECT 年月
-     , sum( 新規uid数 )
-           over( order by 年月
-                 rows between unbounded preceding and current row
-       ) 通算UU
-FROM
-(
-    SELECT 年月
-         , count(1) 新規uid数
-    FROM
-    (
-        SELECT user_id
-             , min( t."date" ) 年月
-        FROM テーブル名 t
-        GROUP BY user_id
-    ) q2
-    GROUP BY 年月
-) q1
-;
-```

SQL文中のゴミを削除

2021/09/18 02:41

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -104,7 +104,7 @@
     SELECT x.ymd
-         , coalesce( y.ymd, max( y.ymd ) over ( order by x.ymd ) )
+         , max( y.ymd ) over ( order by x.ymd )
          , x.uu

2021/09/18 02:18

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -152,84 +152,82 @@
     (
+        SELECT min( tx."date" ) ymd
+        FROM tx
+        GROUP BY user_id
+    ) q
+)
+SELECT m1.ymd1 年月日
+     , m1.日別UU
+     , m2.通算UU
+FROM m1
+JOIN m2
+USING ( ymd2 )
+ORDER BY 1
+;
+```
+推理が当たっていた場合に限りますけど
+~~`通算UU`をわかりやすく計算するのなら~~　←そんなことは無かった
+以下のような`３段のクエリ`になりそうです。
+```SQL
+SELECT 年月
+     , sum( 新規uid数 )
+           over( order by 年月
+                 rows between unbounded preceding and current row
+       ) 通算UU
+FROM
+(
+    SELECT 年月
+         , count(1) 新規uid数
+    FROM
+    (
         SELECT user_id
-             , min( tx."date" ) ymd
+             , min( t."date" ) 年月
-        FROM tx
+        FROM テーブル名 t
         GROUP BY user_id
-    ) q
+    ) q2
-)
-SELECT m1.ymd1 年月日
-     , m1.日別UU
-     , m2.通算UU
-FROM m1
-JOIN m2
-USING ( ymd2 )
-ORDER BY 1
+    GROUP BY 年月
+) q1
 ;
 ```
-推理が当たっていた場合に限りますけど
-~~`通算UU`をわかりやすく計算するのなら~~　←そんなことは無かった
-以下のような`３段のクエリ`になりそうです。
-```SQL
-SELECT 年月
-     , sum( 新規uid数 )
-           over( order by 年月
-                 rows between unbounded preceding and current row
-       ) 通算UU
-FROM
-(
-    SELECT 年月
-         , count(1) 新規uid数
-    FROM
-    (
-        SELECT user_id
-             , min( t."date" ) 年月
-        FROM テーブル名 t
-        GROUP BY user_id
-    ) q2
-    GROUP BY 年月
-) q1
-;
-```

2021/09/17 18:36

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -186,7 +186,7 @@
 推理が当たっていた場合に限りますけど
-`通算UU`をわかりやすく計算するのなら
+~~`通算UU`をわかりやすく計算するのなら~~　←そんなことは無かった
 以下のような`３段のクエリ`になりそうです。

SQL文の修正

2021/09/17 18:12

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -98,7 +98,7 @@
-WITH m0 ( ymd1, ymd2 ) AS
+WITH m1 ( ymd1, ymd2, 日別UU ) AS
 (
@@ -106,14 +106,20 @@
          , coalesce( y.ymd, max( y.ymd ) over ( order by x.ymd ) )
+         , x.uu
     FROM
     (
-        SELECT DISTINCT tx."date" ymd
+        SELECT tx."date" ymd
+             , count( distinct user_id ) uu
         FROM tx
+        GROUP BY tx."date"
     ) x
     LEFT JOIN
@@ -132,20 +138,6 @@
 ),
-    m1 ( ymd1, 日別UU ) AS
-(
-    SELECT tx."date"
-         , count( distinct user_id )
-    FROM tx
-    GROUP BY tx."date"
-),
     m2 ( ymd2, 通算UU ) AS
 (
@@ -172,21 +164,17 @@
 )
-SELECT m0.ymd1 年月日
+SELECT m1.ymd1 年月日
      , m1.日別UU
      , m2.通算UU
-FROM m0
+FROM m1
-JOIN m1
-  ON m0.ymd1 = m1.ymd1
-JOIN m2
+JOIN m2
-  ON m0.ymd2 = m2.ymd2
+USING ( ymd2 )
 ORDER BY 1

SQL文の修正

2021/09/17 17:57

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -104,7 +104,7 @@
     SELECT x.ymd
-         , max( y.ymd )
+         , coalesce( y.ymd, max( y.ymd ) over ( order by x.ymd ) )
     FROM
@@ -128,9 +128,7 @@
     ) y
-    ON x.ymd >= y.ymd
+    ON x.ymd = y.ymd
-    GROUP BY x.ymd
 ),

結合の欠陥を補正(SQL)

2021/09/17 17:25

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -12,11 +12,11 @@
 マスタではなく、トランザクションテーブルにおいて
-固有の`user_id`が`初めて登場した年月`( サンプルデータでは`日`ですが )を
+固有の`user_id`が初めて登場した年月( サンプルデータでは`日`ですが )を
 `user_id`の「`登録日`」と見なして
-( 過去月を含む )当日までの`新規user_id`の総数( 累計 )を計算したい。
+( 過去月を含む )当日までの新規`user_id`の総数( 累計 )を計算したい。
@@ -36,11 +36,19 @@
+##追記：
+あまり良い記述とは思えませんけど
+新規登録の`user_id`が存在しない日でも
+日別UU と 通算UU が両方とも表示されるようにはしておきます。
 ```SQL
-CREATE TABLE テーブル名
+CREATE TABLE tx
 (
@@ -54,7 +62,7 @@
-INSERT INTO テーブル名
+INSERT INTO tx
     ( user_id, "date" )
@@ -78,33 +86,145 @@
   , ( 6, '1970-01-03' )
+  , ( 2, '1970-01-04' )
+  , ( 2, '1970-01-04' )
+  , ( 3, '1970-01-05' )
+  , ( 7, '1970-01-06' )
+  ;
+WITH m0 ( ymd1, ymd2 ) AS
+(
+    SELECT x.ymd
+         , max( y.ymd )
+    FROM
+    (
+        SELECT DISTINCT tx."date" ymd
+        FROM tx
+    ) x
+    LEFT JOIN
+    (
+        SELECT DISTINCT min( tx."date" ) ymd
+        FROM tx
+        GROUP BY user_id
+    ) y
+    ON x.ymd >= y.ymd
+    GROUP BY x.ymd
+),
+    m1 ( ymd1, 日別UU ) AS
+(
+    SELECT tx."date"
+         , count( distinct user_id )
+    FROM tx
+    GROUP BY tx."date"
+),
+    m2 ( ymd2, 通算UU ) AS
+(
+    SELECT DISTINCT
+           ymd
+         , count(1) over( order by ymd )
+    FROM
+    (
+        SELECT user_id
+             , min( tx."date" ) ymd
+        FROM tx
+        GROUP BY user_id
+    ) q
+)
+SELECT m0.ymd1 年月日
+     , m1.日別UU
+     , m2.通算UU
+FROM m0
+JOIN m1
+  ON m0.ymd1 = m1.ymd1
+JOIN m2
+  ON m0.ymd2 = m2.ymd2
+ORDER BY 1
 ;
+```
+推理が当たっていた場合に限りますけど
+`通算UU`をわかりやすく計算するのなら
+以下のような`３段のクエリ`になりそうです。
+```SQL
+SELECT 年月
-WITH x ( 年月日, 日別UU ) AS
+     , sum( 新規uid数 )
+           over( order by 年月
+                 rows between unbounded preceding and current row
+       ) 通算UU
+FROM
-(
+(
-    SELECT t."date"
-         , count( distinct user_id )
-    FROM テーブル名 t
-    GROUP BY t."date"
-),
-    y ( 年月日, 通算UU ) AS
-(
-    SELECT DISTINCT
+    SELECT 年月
-           ymd
-         , count(1) over( order by ymd )
+         , count(1) 新規uid数
     FROM
@@ -112,84 +232,18 @@
         SELECT user_id
-             , min( t."date" ) ymd
+             , min( t."date" ) 年月
         FROM テーブル名 t
         GROUP BY user_id
-    ) q
+    ) q2
-)
-SELECT x.年月日
-     , x.日別UU
-     , y.通算UU
-FROM x
-JOIN y
-USING ( 年月日 )
-ORDER BY 1
+    GROUP BY 年月
+) q1
 ;
 ```
-推理が当たっていた場合に限りますけど
-`通算UU`をわかりやすく計算するのなら
-以下のような`３段のクエリ`になりそうです。
-```SQL
-SELECT 年月
-     , sum( 新規uid数 )
-           over( order by 年月
-                 rows between unbounded preceding and current row
-       ) 通算UU
-FROM
-(
-    SELECT 年月
-         , count(1) 新規uid数
-    FROM
-    (
-        SELECT user_id
-             , min( t."date" ) 年月
-        FROM テーブル名 t
-        GROUP BY user_id
-    ) q2
-    GROUP BY 年月
-) q1
-;
-```

文章中の紛らわしい表現を修正

2021/09/17 15:09

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -16,17 +16,17 @@
 `user_id`の「`登録日`」と見なして
-( 過去月を含む )当月(日)までの`新規user_id`の総数( 累計 )を計算したい。
+( 過去月を含む )当日までの`新規user_id`の総数( 累計 )を計算したい。
 カウントしたいのはあくまで`新規`なので
-過去月(日)に登場した`user_id`は当月に再度現れてもカウントしない。
+過去日に登場した`user_id`は当日に再度現れてもカウントしない。
-また、初回登録された月(日)に`user_id`がユニークな値にならないのは
+また、初回登録された日に`user_id`がユニークな値にならないのは
 実際は、同日に複数のアクション( 商品購入など )が発生していたりするのでしょう。

文章におかしな箇所がいくつかあったので修正

2021/09/17 09:20

投稿

mayu-

スコア335

test CHANGED Viewed

@@ -12,25 +12,25 @@
 マスタではなく、トランザクションテーブルにおいて
-固有の`user_id`が`初めて登場した年月`を
+固有の`user_id`が`初めて登場した年月`( サンプルデータでは`日`ですが )を
 `user_id`の「`登録日`」と見なして
-( 過去月を含む )当月までの`新規user_id`の総数( 累計 )を計算したい。
+( 過去月を含む )当月(日)までの`新規user_id`の総数( 累計 )を計算したい。
 カウントしたいのはあくまで`新規`なので
-過去月に登場した`user_id`は当月に再度現れてもカウントしない。
+過去月(日)に登場した`user_id`は当月に再度現れてもカウントしない。
-また、初回登録された月に`user_id`がユニークな値にならないのは
+また、初回登録された月(日)に`user_id`がユニークな値にならないのは
 実際は、同日に複数のアクション( 商品購入など )が発生していたりするのでしょう。
-ひょっとすると`date`も「`年月日`」の「`日`」の部分が切り捨てられていて
+ひょっとすると`date`も「`時刻`」が切り捨てられていて
 元の`date`は別の値なのかもしれませんね。
@@ -99,8 +99,6 @@
     y ( 年月日, 通算UU ) AS
 (
     SELECT DISTINCT