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
|
-----------------------------------------------------
-- PostgreSQL
-----------------------------------------------------
INSERT INTO "revenue"
SELECT gen_random_uuid() revenue_id,
ed.website_id,
we.session_id,
we.event_id,
we.event_name,
currency.string_value currency,
coalesce(ed.number_value, cast(ed.string_value as numeric(19,4))) revenue,
ed.created_at
FROM event_data ed
JOIN website_event we
ON we.event_id = ed.website_event_id
JOIN (SELECT website_event_id, string_value
FROM event_data
WHERE data_key ilike '%currency%') currency
ON currency.website_event_id = ed.website_event_id
WHERE ed.data_key ilike '%revenue%';
-----------------------------------------------------
-- MySQL
-----------------------------------------------------
INSERT INTO `revenue`
SELECT UUID() revenue_id,
ed.website_id,
we.session_id,
we.event_id,
we.event_name,
currency.string_value currency,
coalesce(ed.number_value, cast(ed.string_value as decimal(19,4))) revenue,
ed.created_at
FROM event_data ed
JOIN website_event we
ON we.event_id = ed.website_event_id
JOIN (SELECT website_event_id, string_value
FROM event_data
WHERE data_key like '%currency%') currency
ON currency.website_event_id = ed.website_event_id
WHERE ed.data_key like '%revenue%';
|