forked from llooker/demo_segment
-
Notifications
You must be signed in to change notification settings - Fork 1
/
_A_alias_mapping.view.lkml
145 lines (131 loc) · 3.87 KB
/
_A_alias_mapping.view.lkml
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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
view: page_aliases_mapping {
derived_table: {
sql: with
-- Establish all child-to-parent edges from tables (tracks, pages, aliases)
all_mappings as (
select anonymous_id
, user_id
, received_at as received_at
from zola_sales.tracks
union
select user_id
, null
, received_at
from zola_sales.tracks
union
select anonymous_id
, user_id
, received_at
from zola_sales.pages
union
select user_id
, null
, received_at
from zola_sales.pages
)
select
distinct anonymous_id as alias
, coalesce(first_value(user_id ignore nulls)
over(
partition by anonymous_id
order by received_at
rows between unbounded preceding and unbounded following),anonymous_id) as looker_visitor_id
from all_mappings
;;
}
# Anonymous ID
dimension: alias {
primary_key: yes
sql: ${TABLE}.alias ;;
}
# User ID
dimension: looker_visitor_id {
sql: ${TABLE}.looker_visitor_id ;;
}
measure: count {
type: count
}
measure: count_visitor {
type: count_distinct
sql: ${looker_visitor_id} ;;
}
}
### More Complex Aliasing Using Alias Table ###
# sql: |
# with
#
# -- Establish all child-to-parent edges from tables (tracks, pages, aliases)
# all_mappings as (
# select anonymous_id
# , user_id
# , received_at
# from hoodie.tracks
#
# union
#
# select user_id
# , null
# , received_at
# from hoodie.tracks
#
# union
#
# select previous_id
# , user_id
# , received_at
# from hoodie.aliases
#
# union
#
# select user_id
# , null
# , received_at
# from hoodie.aliases
#
# union
#
# select anonymous_id
# , user_id
# , received_at
# from hoodie.pages
#
# union
#
# select user_id
# , null
# , received_at
# from hoodie.pages
# ),
#
# -- Only keep the oldest non-null parent for each child
# realiases as (
# select distinct alias
# , first_value(next_alias ignore nulls) over(partition by alias order by realiased_at rows between unbounded preceding and unbounded following) as next_alias
# from all_mappings
# )
#
# -- Traverse the tree upwards and point every node at its root
# select distinct r0.alias
# , coalesce(r9.next_alias
# , r9.alias
# , r8.alias
# , r7.alias
# , r6.alias
# , r5.alias
# , r4.alias
# , r3.alias
# , r2.alias
# , r1.alias
# , r0.alias
# ) as looker_visitor_id
# from realiases r0
# left join realiases r1 on r0.next_alias = r1.alias
# left join realiases r2 on r1.next_alias = r2.alias
# left join realiases r3 on r2.next_alias = r3.alias
# left join realiases r4 on r3.next_alias = r4.alias
# left join realiases r5 on r4.next_alias = r5.alias
# left join realiases r6 on r5.next_alias = r6.alias
# left join realiases r7 on r6.next_alias = r7.alias
# left join realiases r8 on r7.next_alias = r8.alias
# left join realiases r9 on r8.next_alias = r9.alias
#