-
Notifications
You must be signed in to change notification settings - Fork 42
/
Copy path_A_alias_mapping.view.lkml
148 lines (134 loc) · 3.98 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
146
147
148
view: page_aliases_mapping {
derived_table: {
sql_trigger_value: select current_date ;;
sortkeys: ["looker_visitor_id", "alias"]
distribution: "alias"
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 segment.tracks
union
select user_id
, null
, received_at
from segment.tracks
union
select anonymous_id
, user_id
, received_at
from segment.pages
union
select user_id
, null
, received_at
from segment.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
#