-
-
Save jflambert/e0126922e13b1a6b8d8fcd9fe8f50ee5 to your computer and use it in GitHub Desktop.
timescaledb code example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE mine_data | |
( | |
serial TEXT, | |
timestamp TIMESTAMPTZ, | |
values JSONB NOT NULL DEFAULT '{}', | |
PRIMARY KEY (serial, timestamp) | |
); | |
SELECT CREATE_HYPERTABLE('mine_data', 'timestamp'); | |
INSERT INTO mine_data (serial, timestamp, values) VALUES | |
('123', '2020-01-01', '{"a": 1, "b": 1, "c": 1}'), | |
('123', '2020-01-02', '{ "b": 2, "c": 2}'), | |
('123', '2020-01-03', '{"a": 3, "c": 3}'), | |
('123', '2020-01-04', '{"a": 4, "b": 4 }'), | |
('123', '2020-01-06', '{"a": 6, "b": 6, "c": 6}'); | |
CREATE FUNCTION mine_data_gapfill(serial TEXT, start_date TIMESTAMPTZ, end_date TIMESTAMPTZ, time_bucket INTERVAL = '1 DAY', locf_prev INTERVAL = '1 DAY') | |
RETURNS SETOF mine_data AS $$ | |
SELECT $1, ts, JSONB_OBJECT_AGG(key_name, gapfilled) | |
FROM ( | |
SELECT | |
serial, | |
TIME_BUCKET_GAPFILL(time_bucket, MT.timestamp) AS ts, | |
jsondata.key AS key_name, | |
LOCF(AVG((jsondata.value)::REAL)::REAL, treat_null_as_missing:=TRUE, prev:=( | |
SELECT (values->>jsondata.key)::REAL | |
FROM mine_data | |
WHERE values->>jsondata.key IS NOT NULL AND serial = $1 | |
AND timestamp < start_date AND timestamp >= start_date - locf_prev | |
ORDER BY timestamp DESC LIMIT 1 | |
)) AS gapfilled | |
FROM mine_data MT, JSONB_EACH(MT.values) AS jsondata | |
WHERE MT.serial = $1 AND MT.timestamp >= start_date AND MT.timestamp <= end_date | |
GROUP BY ts, jsondata.key, serial | |
ORDER BY ts ASC, jsondata.key ASC | |
) sourcedata | |
GROUP BY ts, serial | |
ORDER BY ts ASC; | |
$$ LANGUAGE SQL STABLE; | |
SELECT * FROM mine_data_gapfill('123', '2020-01-01', '2020-01-06', '1 DAY'); |
Author
jflambert
commented
Dec 2, 2022
•
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment