Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE metrics(
- machine_id int NOT NULL,
- ts timestamptz NOT NULL,
- "V1_2" int NOT NULL,
- "V2_3" int NOT NULL,
- "V3_1" int NOT NULL
- );
- INSERT INTO metrics(machine_id, ts, "V1_2", "V2_3", "V3_1")
- SELECT n % 2 + 1, '20180711'::timestamptz + (n * interval '5 second'), n % 123, n % 438, n % 841
- FROM generate_series(1, 100000) AS g(n);
- CREATE INDEX ON metrics(machine_id, (date_trunc('day', ts AT TIME ZONE 'CEST')), ("V1_2" + "V2_3" + "V3_1"));
- -- This could be used in LATERAL join to generated dates (generate_series):
- SELECT MAX ("V1_2" + "V2_3" + "V3_1") AS vmax,
- MIN ("V1_2" + "V2_3" + "V3_1") AS vmin
- FROM metrics AS m
- WHERE m.machine_id = 1
- AND date_trunc('day', ts AT TIME ZONE 'CEST') = '20180713';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement