1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
-- add tag column
ALTER TABLE umami.website_event ADD COLUMN "distinct_id" String AFTER "tag";
ALTER TABLE umami.website_event_stats_hourly ADD COLUMN "distinct_id" String AFTER "tag";
ALTER TABLE umami.session_data ADD COLUMN "distinct_id" String AFTER "data_type";
-- update materialized view
DROP TABLE umami.website_event_stats_hourly_mv;
CREATE MATERIALIZED VIEW umami.website_event_stats_hourly_mv
TO umami.website_event_stats_hourly
AS
SELECT
website_id,
session_id,
visit_id,
hostname,
browser,
os,
device,
screen,
language,
country,
region,
city,
entry_url,
exit_url,
url_paths as url_path,
url_query,
utm_source,
utm_medium,
utm_campaign,
utm_content,
utm_term,
referrer_domain,
page_title,
gclid,
fbclid,
msclkid,
ttclid,
li_fat_id,
twclid,
event_type,
event_name,
views,
min_time,
max_time,
tag,
distinct_id,
timestamp as created_at
FROM (SELECT
website_id,
session_id,
visit_id,
hostname,
browser,
os,
device,
screen,
language,
country,
region,
city,
argMinState(url_path, created_at) entry_url,
argMaxState(url_path, created_at) exit_url,
arrayFilter(x -> x != '', groupArray(url_path)) as url_paths,
arrayFilter(x -> x != '', groupArray(url_query)) url_query,
arrayFilter(x -> x != '', groupArray(utm_source)) utm_source,
arrayFilter(x -> x != '', groupArray(utm_medium)) utm_medium,
arrayFilter(x -> x != '', groupArray(utm_campaign)) utm_campaign,
arrayFilter(x -> x != '', groupArray(utm_content)) utm_content,
arrayFilter(x -> x != '', groupArray(utm_term)) utm_term,
arrayFilter(x -> x != '', groupArray(referrer_domain)) referrer_domain,
arrayFilter(x -> x != '', groupArray(page_title)) page_title,
arrayFilter(x -> x != '', groupArray(gclid)) gclid,
arrayFilter(x -> x != '', groupArray(fbclid)) fbclid,
arrayFilter(x -> x != '', groupArray(msclkid)) msclkid,
arrayFilter(x -> x != '', groupArray(ttclid)) ttclid,
arrayFilter(x -> x != '', groupArray(li_fat_id)) li_fat_id,
arrayFilter(x -> x != '', groupArray(twclid)) twclid,
event_type,
if(event_type = 2, groupArray(event_name), []) event_name,
sumIf(1, event_type = 1) views,
min(created_at) min_time,
max(created_at) max_time,
arrayFilter(x -> x != '', groupArray(tag)) tag,
distinct_id,
toStartOfHour(created_at) timestamp
FROM umami.website_event
GROUP BY website_id,
session_id,
visit_id,
hostname,
browser,
os,
device,
screen,
language,
country,
region,
city,
event_type,
distinct_id,
timestamp);
|