回答編集履歴

13

SQL文の修正

2021/09/18 13:37

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -19,17 +19,17 @@
19
19
  元の`date`は別の値なのかもしれませんね。
20
20
 
21
21
  ##更に追記:
22
- シンプルにできたかも。`id`も使わない。
22
+ シンプルにできたかも。※ver`9.4`以降
23
23
  ```SQL
24
24
  SELECT ymd 年月日
25
25
  , count( distinct user_id ) 日別UU
26
- , sum( count( distinct case when ymd = f then user_id end ) )
26
+ , sum( count( distinct user_id ) filter( where ymd = f ) )
27
27
  over( order by ymd ) 通算UU
28
28
  FROM
29
29
  (
30
30
  SELECT tx."date" ymd
31
31
  , user_id
32
- , min( tx."date" ) over ( partition by user_id ) f
32
+ , min( tx."date" ) over( partition by user_id ) f
33
33
  FROM tx
34
34
  ) q
35
35
  GROUP BY ymd ;
@@ -63,7 +63,7 @@
63
63
  , ( 2, '1970-01-04' )
64
64
  , ( 3, '1970-01-05' )
65
65
  , ( 7, '1970-01-06' )
66
- ;
66
+ ;
67
67
 
68
68
  SELECT x.ymd 年月日
69
69
  , x.duu 日別UU

12

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

2021/09/18 13:37

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -23,7 +23,7 @@
23
23
  ```SQL
24
24
  SELECT ymd 年月日
25
25
  , count( distinct user_id ) 日別UU
26
- , sum( count( distinct case when ymd = f then user_id else null end ) )
26
+ , sum( count( distinct case when ymd = f then user_id end ) )
27
27
  over( order by ymd ) 通算UU
28
28
  FROM
29
29
  (

11

SQL文中のミスを修正

2021/09/18 06:38

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -22,7 +22,7 @@
22
22
  シンプルにできたかも。`id`も使わない。
23
23
  ```SQL
24
24
  SELECT ymd 年月日
25
- , count( distinct ymd ) 日別UU
25
+ , count( distinct user_id ) 日別UU
26
26
  , sum( count( distinct case when ymd = f then user_id else null end ) )
27
27
  over( order by ymd ) 通算UU
28
28
  FROM

10

SQLのロジックを追記

2021/09/18 06:09

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -18,6 +18,23 @@
18
18
  ひょっとすると`date`も「`時刻`」が切り捨てられていて
19
19
  元の`date`は別の値なのかもしれませんね。
20
20
 
21
+ ##更に追記:
22
+ シンプルにできたかも。`id`も使わない。
23
+ ```SQL
24
+ SELECT ymd 年月日
25
+ , count( distinct ymd ) 日別UU
26
+ , sum( count( distinct case when ymd = f then user_id else null end ) )
27
+ over( order by ymd ) 通算UU
28
+ FROM
29
+ (
30
+ SELECT tx."date" ymd
31
+ , user_id
32
+ , min( tx."date" ) over ( partition by user_id ) f
33
+ FROM tx
34
+ ) q
35
+ GROUP BY ymd ;
36
+ ```
37
+
21
38
  ##追記:
22
39
  新規登録の`user_id`が存在しない日でも
23
40
  日別UU と 通算UU が両方とも表示されるよう外部結合を適用。

9

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

2021/09/18 05:02

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -11,6 +11,7 @@
11
11
 
12
12
  カウントしたいのはあくまで`新規`なので
13
13
  過去日に登場した`user_id`は当日に再度現れてもカウントしない。
14
+ # 実績のある`user_id`の個体数を算出したいのではないか、と推測
14
15
 
15
16
  また、初回登録された日に`user_id`がユニークな値にならないのは
16
17
  実際は、同日に複数のアクション( 商品購入など )が発生していたりするのでしょう。
@@ -18,9 +19,8 @@
18
19
  元の`date`は別の値なのかもしれませんね。
19
20
 
20
21
  ##追記:
21
- あまり良い記述とは思えませんけど
22
22
  新規登録の`user_id`が存在しない日でも
23
- 日別UU と 通算UU が両方とも表示されるようにはしておきます
23
+ 日別UU と 通算UU が両方とも表示されるよう外部結合を適用
24
24
 
25
25
  ```SQL
26
26
  CREATE TABLE tx
@@ -48,45 +48,29 @@
48
48
  , ( 7, '1970-01-06' )
49
49
  ;
50
50
 
51
+ SELECT x.ymd 年月日
52
+ , x.duu 日別UU
51
- WITH m1 ( ymd1, ymd2, 日別UU ) AS
53
+ , max( y.tuu ) over( order by x.ymd ) 通算UU
54
+ FROM
52
55
  (
53
- SELECT x.ymd
54
- , max( y.ymd ) over ( order by x.ymd )
55
- , x.uu
56
- FROM
57
- (
58
- SELECT tx."date" ymd
56
+ SELECT tx."date" ymd
59
- , count( distinct user_id ) uu
57
+ , count( distinct user_id ) duu
60
- FROM tx
58
+ FROM tx
61
- GROUP BY tx."date"
59
+ GROUP BY tx."date"
62
- ) x
60
+ ) x
63
- LEFT JOIN
61
+ LEFT JOIN
64
- (
65
- SELECT DISTINCT min( tx."date" ) ymd
66
- FROM tx
67
- GROUP BY user_id
68
- ) y
69
- ON x.ymd = y.ymd
70
- ),
71
- m2 ( ymd2, 通算UU ) AS
72
62
  (
73
63
  SELECT DISTINCT
74
64
  ymd
75
- , count(1) over( order by ymd )
65
+ , count(1) over( order by ymd ) tuu
76
66
  FROM
77
67
  (
78
68
  SELECT min( tx."date" ) ymd
79
69
  FROM tx
80
70
  GROUP BY user_id
81
71
  ) q
82
- )
72
+ ) y
83
- SELECT m1.ymd1 年月日
84
- , m1.日別UU
85
- , m2.通算UU
86
- FROM m1
87
- JOIN m2
88
- USING ( ymd2 )
73
+ USING ( ymd )
89
- ORDER BY 1
90
74
  ;
91
75
  ```
92
76
 

8

SQL文中のゴミを削除

2021/09/18 02:41

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -51,7 +51,7 @@
51
51
  WITH m1 ( ymd1, ymd2, 日別UU ) AS
52
52
  (
53
53
  SELECT x.ymd
54
- , coalesce( y.ymd, max( y.ymd ) over ( order by x.ymd ) )
54
+ , max( y.ymd ) over ( order by x.ymd )
55
55
  , x.uu
56
56
  FROM
57
57
  (

7

2021/09/18 02:18

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -75,8 +75,7 @@
75
75
  , count(1) over( order by ymd )
76
76
  FROM
77
77
  (
78
- SELECT user_id
79
- , min( tx."date" ) ymd
78
+ SELECT min( tx."date" ) ymd
80
79
  FROM tx
81
80
  GROUP BY user_id
82
81
  ) q

6

2021/09/17 18:36

投稿

mayu-
mayu-

スコア335

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

5

SQL文の修正

2021/09/17 18:12

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -48,14 +48,17 @@
48
48
  , ( 7, '1970-01-06' )
49
49
  ;
50
50
 
51
- WITH m0 ( ymd1, ymd2 ) AS
51
+ WITH m1 ( ymd1, ymd2, 日別UU ) AS
52
52
  (
53
53
  SELECT x.ymd
54
54
  , coalesce( y.ymd, max( y.ymd ) over ( order by x.ymd ) )
55
+ , x.uu
55
56
  FROM
56
57
  (
57
- SELECT DISTINCT tx."date" ymd
58
+ SELECT tx."date" ymd
59
+ , count( distinct user_id ) uu
58
60
  FROM tx
61
+ GROUP BY tx."date"
59
62
  ) x
60
63
  LEFT JOIN
61
64
  (
@@ -65,13 +68,6 @@
65
68
  ) y
66
69
  ON x.ymd = y.ymd
67
70
  ),
68
- m1 ( ymd1, 日別UU ) AS
69
- (
70
- SELECT tx."date"
71
- , count( distinct user_id )
72
- FROM tx
73
- GROUP BY tx."date"
74
- ),
75
71
  m2 ( ymd2, 通算UU ) AS
76
72
  (
77
73
  SELECT DISTINCT
@@ -85,14 +81,12 @@
85
81
  GROUP BY user_id
86
82
  ) q
87
83
  )
88
- SELECT m0.ymd1 年月日
84
+ SELECT m1.ymd1 年月日
89
85
  , m1.日別UU
90
86
  , m2.通算UU
91
- FROM m0
87
+ FROM m1
92
- JOIN m1
93
- ON m0.ymd1 = m1.ymd1
94
- JOIN m2
88
+ JOIN m2
95
- ON m0.ymd2 = m2.ymd2
89
+ USING ( ymd2 )
96
90
  ORDER BY 1
97
91
  ;
98
92
  ```

4

SQL文の修正

2021/09/17 17:57

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -51,7 +51,7 @@
51
51
  WITH m0 ( ymd1, ymd2 ) AS
52
52
  (
53
53
  SELECT x.ymd
54
- , max( y.ymd )
54
+ , coalesce( y.ymd, max( y.ymd ) over ( order by x.ymd ) )
55
55
  FROM
56
56
  (
57
57
  SELECT DISTINCT tx."date" ymd
@@ -63,8 +63,7 @@
63
63
  FROM tx
64
64
  GROUP BY user_id
65
65
  ) y
66
- ON x.ymd >= y.ymd
66
+ ON x.ymd = y.ymd
67
- GROUP BY x.ymd
68
67
  ),
69
68
  m1 ( ymd1, 日別UU ) AS
70
69
  (

3

結合の欠陥を補正(SQL)

2021/09/17 17:25

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -5,9 +5,9 @@
5
5
  推理すると以下のようなことでしょうか。
6
6
 
7
7
  マスタではなく、トランザクションテーブルにおいて
8
- 固有の`user_id`が`初めて登場した年月`( サンプルデータでは`日`ですが )を
8
+ 固有の`user_id`が初めて登場した年月( サンプルデータでは`日`ですが )を
9
9
  `user_id`の「`登録日`」と見なして
10
- ( 過去月を含む )当日までの`新規user_id`の総数( 累計 )を計算したい。
10
+ ( 過去月を含む )当日までの新規`user_id`の総数( 累計 )を計算したい。
11
11
 
12
12
  カウントしたいのはあくまで`新規`なので
13
13
  過去日に登場した`user_id`は当日に再度現れてもカウントしない。
@@ -17,16 +17,20 @@
17
17
  ひょっとすると`date`も「`時刻`」が切り捨てられていて
18
18
  元の`date`は別の値なのかもしれませんね。
19
19
 
20
+ ##追記:
21
+ あまり良い記述とは思えませんけど
22
+ 新規登録の`user_id`が存在しない日でも
23
+ 日別UU と 通算UU が両方とも表示されるようにはしておきます。
20
24
 
21
25
  ```SQL
22
- CREATE TABLE テーブル名
26
+ CREATE TABLE tx
23
27
  (
24
28
  id serial primary key
25
29
  , user_id int not null
26
30
  , "date" date not null
27
31
  );
28
32
 
29
- INSERT INTO テーブル名
33
+ INSERT INTO tx
30
34
  ( user_id, "date" )
31
35
  VALUES
32
36
  ( 1, '1970-01-01' )
@@ -38,16 +42,38 @@
38
42
  , ( 1, '1970-01-02' )
39
43
  , ( 2, '1970-01-03' )
40
44
  , ( 6, '1970-01-03' )
45
+ , ( 2, '1970-01-04' )
46
+ , ( 2, '1970-01-04' )
47
+ , ( 3, '1970-01-05' )
48
+ , ( 7, '1970-01-06' )
41
- ;
49
+ ;
42
50
 
43
- WITH x ( 年月日, 日別UU ) AS
51
+ WITH m0 ( ymd1, ymd2 ) AS
44
52
  (
53
+ SELECT x.ymd
54
+ , max( y.ymd )
55
+ FROM
56
+ (
57
+ SELECT DISTINCT tx."date" ymd
58
+ FROM tx
59
+ ) x
60
+ LEFT JOIN
61
+ (
62
+ SELECT DISTINCT min( tx."date" ) ymd
63
+ FROM tx
64
+ GROUP BY user_id
65
+ ) y
66
+ ON x.ymd >= y.ymd
67
+ GROUP BY x.ymd
68
+ ),
69
+ m1 ( ymd1, 日別UU ) AS
70
+ (
45
- SELECT t."date"
71
+ SELECT tx."date"
46
72
  , count( distinct user_id )
47
- FROM テーブル名 t
73
+ FROM tx
48
- GROUP BY t."date"
74
+ GROUP BY tx."date"
49
75
  ),
50
- y ( 年月日, 通算UU ) AS
76
+ m2 ( ymd2, 通算UU ) AS
51
77
  (
52
78
  SELECT DISTINCT
53
79
  ymd
@@ -55,18 +81,19 @@
55
81
  FROM
56
82
  (
57
83
  SELECT user_id
58
- , min( t."date" ) ymd
84
+ , min( tx."date" ) ymd
59
- FROM テーブル名 t
85
+ FROM tx
60
86
  GROUP BY user_id
61
87
  ) q
62
88
  )
63
-
64
- SELECT x.年月日
89
+ SELECT m0.ymd1 年月日
65
- , x.日別UU
90
+ , m1.日別UU
66
- , y.通算UU
91
+ , m2.通算UU
67
- FROM x
92
+ FROM m0
68
- JOIN y
93
+ JOIN m1
94
+ ON m0.ymd1 = m1.ymd1
69
- USING ( 年月日 )
95
+ JOIN m2
96
+ ON m0.ymd2 = m2.ymd2
70
97
  ORDER BY 1
71
98
  ;
72
99
  ```

2

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

2021/09/17 15:09

投稿

mayu-
mayu-

スコア335

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

1

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

2021/09/17 09:20

投稿

mayu-
mayu-

スコア335

answer CHANGED
@@ -5,16 +5,16 @@
5
5
  推理すると以下のようなことでしょうか。
6
6
 
7
7
  マスタではなく、トランザクションテーブルにおいて
8
- 固有の`user_id`が`初めて登場した年月`を
8
+ 固有の`user_id`が`初めて登場した年月`( サンプルデータでは`日`ですが )
9
9
  `user_id`の「`登録日`」と見なして
10
- ( 過去月を含む )当月までの`新規user_id`の総数( 累計 )を計算したい。
10
+ ( 過去月を含む )当月(日)までの`新規user_id`の総数( 累計 )を計算したい。
11
11
 
12
12
  カウントしたいのはあくまで`新規`なので
13
- 過去月に登場した`user_id`は当月に再度現れてもカウントしない。
13
+ 過去月(日)に登場した`user_id`は当月に再度現れてもカウントしない。
14
14
 
15
- また、初回登録された月に`user_id`がユニークな値にならないのは
15
+ また、初回登録された月(日)に`user_id`がユニークな値にならないのは
16
16
  実際は、同日に複数のアクション( 商品購入など )が発生していたりするのでしょう。
17
- ひょっとすると`date`も「`年月日`」の「`日`」の部分が切り捨てられていて
17
+ ひょっとすると`date`も「`時刻`」が切り捨てられていて
18
18
  元の`date`は別の値なのかもしれませんね。
19
19
 
20
20
 
@@ -49,7 +49,6 @@
49
49
  ),
50
50
  y ( 年月日, 通算UU ) AS
51
51
  (
52
-
53
52
  SELECT DISTINCT
54
53
  ymd
55
54
  , count(1) over( order by ymd )