回答編集履歴
13
SQL文の修正
answer
CHANGED
@@ -19,17 +19,17 @@
|
|
19
19
|
元の`date`は別の値なのかもしれませんね。
|
20
20
|
|
21
21
|
##更に追記:
|
22
|
-
シンプルにできたかも。`
|
22
|
+
シンプルにできたかも。※ver`9.4`以降
|
23
23
|
```SQL
|
24
24
|
SELECT ymd 年月日
|
25
25
|
, count( distinct user_id ) 日別UU
|
26
|
-
, sum( count( distinct
|
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
|
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の条件表記を略すことにした
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
|
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文中のミスを修正
answer
CHANGED
@@ -22,7 +22,7 @@
|
|
22
22
|
シンプルにできたかも。`id`も使わない。
|
23
23
|
```SQL
|
24
24
|
SELECT ymd 年月日
|
25
|
-
, count( distinct
|
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のロジックを追記
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を極力シンプルな記述に変更
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
|
-
|
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
|
-
|
56
|
+
SELECT tx."date" ymd
|
59
|
-
|
57
|
+
, count( distinct user_id ) duu
|
60
|
-
|
58
|
+
FROM tx
|
61
|
-
|
59
|
+
GROUP BY tx."date"
|
62
|
-
|
60
|
+
) x
|
63
|
-
|
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 (
|
73
|
+
USING ( ymd )
|
89
|
-
ORDER BY 1
|
90
74
|
;
|
91
75
|
```
|
92
76
|
|
8
SQL文中のゴミを削除
answer
CHANGED
@@ -51,7 +51,7 @@
|
|
51
51
|
WITH m1 ( ymd1, ymd2, 日別UU ) AS
|
52
52
|
(
|
53
53
|
SELECT x.ymd
|
54
|
-
,
|
54
|
+
, max( y.ymd ) over ( order by x.ymd )
|
55
55
|
, x.uu
|
56
56
|
FROM
|
57
57
|
(
|
7
answer
CHANGED
@@ -75,8 +75,7 @@
|
|
75
75
|
, count(1) over( order by ymd )
|
76
76
|
FROM
|
77
77
|
(
|
78
|
-
SELECT user_id
|
79
|
-
|
78
|
+
SELECT min( tx."date" ) ymd
|
80
79
|
FROM tx
|
81
80
|
GROUP BY user_id
|
82
81
|
) q
|
6
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文の修正
answer
CHANGED
@@ -48,14 +48,17 @@
|
|
48
48
|
, ( 7, '1970-01-06' )
|
49
49
|
;
|
50
50
|
|
51
|
-
WITH
|
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
|
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
|
84
|
+
SELECT m1.ymd1 年月日
|
89
85
|
, m1.日別UU
|
90
86
|
, m2.通算UU
|
91
|
-
FROM
|
87
|
+
FROM m1
|
92
|
-
JOIN m1
|
93
|
-
ON m0.ymd1 = m1.ymd1
|
94
|
-
JOIN m2
|
88
|
+
JOIN m2
|
95
|
-
|
89
|
+
USING ( ymd2 )
|
96
90
|
ORDER BY 1
|
97
91
|
;
|
98
92
|
```
|
4
SQL文の修正
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
|
66
|
+
ON x.ymd = y.ymd
|
67
|
-
GROUP BY x.ymd
|
68
67
|
),
|
69
68
|
m1 ( ymd1, 日別UU ) AS
|
70
69
|
(
|
3
結合の欠陥を補正(SQL)
answer
CHANGED
@@ -5,9 +5,9 @@
|
|
5
5
|
推理すると以下のようなことでしょうか。
|
6
6
|
|
7
7
|
マスタではなく、トランザクションテーブルにおいて
|
8
|
-
固有の`user_id`が
|
8
|
+
固有の`user_id`が初めて登場した年月( サンプルデータでは`日`ですが )を
|
9
9
|
`user_id`の「`登録日`」と見なして
|
10
|
-
( 過去月を含む )当日までの
|
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
|
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
|
71
|
+
SELECT tx."date"
|
46
72
|
, count( distinct user_id )
|
47
|
-
FROM
|
73
|
+
FROM tx
|
48
|
-
GROUP BY
|
74
|
+
GROUP BY tx."date"
|
49
75
|
),
|
50
|
-
|
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(
|
84
|
+
, min( tx."date" ) ymd
|
59
|
-
FROM
|
85
|
+
FROM tx
|
60
86
|
GROUP BY user_id
|
61
87
|
) q
|
62
88
|
)
|
63
|
-
|
64
|
-
SELECT
|
89
|
+
SELECT m0.ymd1 年月日
|
65
|
-
,
|
90
|
+
, m1.日別UU
|
66
|
-
,
|
91
|
+
, m2.通算UU
|
67
|
-
FROM
|
92
|
+
FROM m0
|
68
|
-
JOIN
|
93
|
+
JOIN m1
|
94
|
+
ON m0.ymd1 = m1.ymd1
|
69
|
-
|
95
|
+
JOIN m2
|
96
|
+
ON m0.ymd2 = m2.ymd2
|
70
97
|
ORDER BY 1
|
71
98
|
;
|
72
99
|
```
|
2
文章中の紛らわしい表現を修正
answer
CHANGED
@@ -7,12 +7,12 @@
|
|
7
7
|
マスタではなく、トランザクションテーブルにおいて
|
8
8
|
固有の`user_id`が`初めて登場した年月`( サンプルデータでは`日`ですが )を
|
9
9
|
`user_id`の「`登録日`」と見なして
|
10
|
-
( 過去月を含む )当
|
10
|
+
( 過去月を含む )当日までの`新規user_id`の総数( 累計 )を計算したい。
|
11
11
|
|
12
12
|
カウントしたいのはあくまで`新規`なので
|
13
|
-
過去
|
13
|
+
過去日に登場した`user_id`は当日に再度現れてもカウントしない。
|
14
14
|
|
15
|
-
また、初回登録された
|
15
|
+
また、初回登録された日に`user_id`がユニークな値にならないのは
|
16
16
|
実際は、同日に複数のアクション( 商品購入など )が発生していたりするのでしょう。
|
17
17
|
ひょっとすると`date`も「`時刻`」が切り捨てられていて
|
18
18
|
元の`date`は別の値なのかもしれませんね。
|
1
文章におかしな箇所がいくつかあったので修正
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 )
|