回答編集履歴
3
体裁
answer
CHANGED
|
@@ -39,7 +39,7 @@
|
|
|
39
39
|
select
|
|
40
40
|
user_id
|
|
41
41
|
, date
|
|
42
|
-
, case when first_value(id) over(partition by user_id order by date
|
|
42
|
+
, case when first_value(id) over(partition by user_id order by date) = id
|
|
43
43
|
then 1 else 0 end as 初登場
|
|
44
44
|
from tx
|
|
45
45
|
), sub2 as
|
2
スキーマ定義を他の人の回答にあわせる
answer
CHANGED
|
@@ -6,16 +6,16 @@
|
|
|
6
6
|
select
|
|
7
7
|
id
|
|
8
8
|
, user_id
|
|
9
|
-
,
|
|
9
|
+
, date
|
|
10
|
-
, case when first_value(id) over(partition by user_id order by
|
|
10
|
+
, case when first_value(id) over(partition by user_id order by date, id) = id
|
|
11
11
|
then 1 else 0 end as 初登場
|
|
12
|
-
from
|
|
12
|
+
from tx
|
|
13
13
|
order by id
|
|
14
14
|
```
|
|
15
15
|
|
|
16
16
|
結果
|
|
17
17
|
|
|
18
|
-
| id | user_id |
|
|
18
|
+
| id | user_id | date | 初登場 |
|
|
19
19
|
| :--- | :------ | :--------- | :----- |
|
|
20
20
|
| 1 | 1 | 1970-01-01 | 1 |
|
|
21
21
|
| 2 | 1 | 1970-01-01 | 0 |
|
|
@@ -34,28 +34,28 @@
|
|
|
34
34
|
上記の手法を使って、やりたいことを実現するSQLは以下となります。
|
|
35
35
|
|
|
36
36
|
```sql
|
|
37
|
-
with sub1
|
|
37
|
+
with sub1 as
|
|
38
38
|
(
|
|
39
39
|
select
|
|
40
40
|
user_id
|
|
41
|
-
,
|
|
41
|
+
, date
|
|
42
|
-
, case when first_value(id) over(partition by user_id order by
|
|
42
|
+
, case when first_value(id) over(partition by user_id order by date, id) = id
|
|
43
|
-
then 1 else 0 end
|
|
43
|
+
then 1 else 0 end as 初登場
|
|
44
|
-
from
|
|
44
|
+
from tx
|
|
45
|
-
), sub2
|
|
45
|
+
), sub2 as
|
|
46
46
|
(
|
|
47
47
|
select
|
|
48
|
-
|
|
48
|
+
date
|
|
49
49
|
, count(distinct user_id) as 日別UU
|
|
50
50
|
, sum(初登場) as 通算UU
|
|
51
51
|
from sub1
|
|
52
|
-
group by
|
|
52
|
+
group by date
|
|
53
53
|
)
|
|
54
54
|
|
|
55
55
|
select
|
|
56
|
-
|
|
56
|
+
date
|
|
57
57
|
, 日別UU
|
|
58
|
-
, sum(通算UU) over(order by
|
|
58
|
+
, sum(通算UU) over(order by date) as 通算UU
|
|
59
59
|
from sub2
|
|
60
|
-
order by
|
|
60
|
+
order by date
|
|
61
61
|
```
|
1
要約変更
answer
CHANGED
|
@@ -1,4 +1,4 @@
|
|
|
1
|
-
|
|
1
|
+
結合や相関サブクエリを使わずにwindow関数のみで実現する方法を紹介します。
|
|
2
2
|
|
|
3
3
|
通算UU でuser_idの重複を除いてカウントするために、user_idが初めて登場した行に初登場のフラグを立てます
|
|
4
4
|
|