forked from timescale/timescaledb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
merge.sql
76 lines (65 loc) · 2.54 KB
/
merge.sql
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
-- This file and its contents are licensed under the Apache License 2.0.
-- Please see the included NOTICE for copyright information and
-- LICENSE-APACHE for a copy of the license.
-- Create conditions table with location and temperature
DROP TABLE IF EXISTS conditions;
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location SMALLINT NOT NULL,
temperature DOUBLE PRECISION NULL
);
SELECT create_hypertable(
'conditions',
'time',
chunk_time_interval => INTERVAL '5 seconds');
INSERT INTO conditions
SELECT time, location, 14 as temperature
FROM generate_series(
'2021-01-01 00:00:00',
'2021-01-01 00:00:09',
INTERVAL '5 seconds'
) as time,
generate_series(1,4) as location;
-- Create conditions_updated table with location and temperature
DROP TABLE IF EXISTS conditions_updated;
CREATE TABLE conditions_updated (
time TIMESTAMPTZ NOT NULL,
location SMALLINT NOT NULL,
temperature DOUBLE PRECISION NULL
);
SELECT create_hypertable(
'conditions_updated',
'time',
chunk_time_interval => INTERVAL '5 seconds');
-- Generate data that overlaps with conditions table
INSERT INTO conditions_updated
SELECT time, location, 80 as temperature
FROM generate_series(
'2021-01-01 00:00:05',
'2021-01-01 00:00:14',
INTERVAL '5 seconds'
) as time,
generate_series(1,4) as location;
-- Print table/rows/num of chunks
select * from conditions order by time, location asc;
select * from conditions_updated order by time, location asc;
select hypertable_name, count(*) as num_of_chunks from timescaledb_information.chunks group by hypertable_name;
-- Print expected values in the conditions table once conditions_updated is merged into it
-- If a key exists in both tables, we take average of the temperature measured
-- average logic here is a mess but it works
SELECT COALESCE(c.time, cu.time) as time,
COALESCE(c.location, cu.location) as location,
(COALESCE(c.temperature, cu.temperature) + COALESCE(cu.temperature, c.temperature))/2 as temperature
FROM conditions AS c FULL JOIN conditions_updated AS cu
ON c.time = cu.time AND c.location = cu.location;
-- Merge conditions_updated into conditions
\set ON_ERROR_STOP 0
MERGE INTO conditions c
USING conditions_updated cu
ON c.time = cu.time AND c.location = cu.location
WHEN MATCHED THEN
UPDATE SET temperature = (c.temperature + cu.temperature)/2
WHEN NOT MATCHED THEN
INSERT (time, location, temperature) VALUES (cu.time, cu.location, cu.temperature);
SELECT * FROM conditions ORDER BY time, location ASC;
\set ON_ERROR_STOP 1