I am building a continuous aggregate in Timescaledb of a table containing values in an array.
db=> \d voltage_harmonics
Table "public.voltage_harmonics"
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
time | timestamp without time zone | | not null |
meter_id | integer | | not null |
phase | character varying(2) | | not null |
value | double precision[] | | not null |
Indexes:
"voltage_harmonics_time_idx" btree ("time" DESC)
Foreign-key constraints:
"voltage_harmonics_meter_id_fkey" FOREIGN KEY (meter_id) REFERENCES meters(id)
Triggers:
ts_insert_blocker BEFORE INSERT ON voltage_harmonics FOR EACH ROW EXECUTE FUNCTION timescaledb_internal.insert_blocker()
The column value is of fixed cardinality of 127.
Example data looks as follows:
db=> SELECT time, meter_id, phase, value[1], value[2], value[3] FROM voltage_harmonics WHERE meter_id=1 AND phase='TN' ORDER BY time LIMIT 4;
time | meter_id | phase | value | value | value
----------------------------+----------+-------+--------------------+---------------------+--------------------
2020-12-17 10:21:26.673998 | 1 | TN | 233.50682067871094 | 0.16370028257369995 | 1.062761664390564
2020-12-17 10:21:27.693663 | 1 | TN | 233.524169921875 | 0.17024844884872437 | 1.0571166276931763
2020-12-17 10:21:28.691197 | 1 | TN | 233.50201416015625 | 0.1778242588043213 | 1.066114902496338
2020-12-17 10:21:29.690272 | 1 | TN | 233.4673309326172 | 0.2070794403553009 | 1.0743005275726318
(4 rows)
What I would like is for my aggregate view to have columns min and max of type array of length 127 containing the min/max values across the different rows of the table. Something similar to the following query, only ideally containing all 127 elements of value:
db=> SELECT time_bucket('1 hour', time) AS hour, meter_id, phase, ARRAY[MIN(value[1]), MIN(value[2]), MIN(value[3]), MIN(value[4])] AS min, ARRAY[MAX(value[1]), MAX(value[2]), MAX(value[3])] AS max FROM voltage_harmonics WHERE meter_id=1 AND phase='RN' GROUP BY hour, meter_id, phase ORDER BY hour ASC limit 2;
hour | meter_id | phase | min | max
---------------------+----------+-------+----------------------------------------------------------------------------------+-------------------------------------------------------------
2020-12-17 10:00:00 | 1 | RN | {232.52615356445312,0.011430807411670685,0.367933064699173,0.367933064699173} | {234.22560119628906,0.47944650053977966,0.7849964499473572}
2020-12-17 11:00:00 | 1 | RN | {233.45909118652344,0.00765242101624608,0.43139347434043884,0.43139347434043884} | {234.91810607910156,0.5121793150901794,0.8960586190223694}
(2 rows)
A plain min/max of value does not seem to do the trick:
=> SELECT time_bucket('1 hour', time) AS hour, meter_id, phase, MIN(value[1:4]), MAX(value[1:4]) FROM voltage_harmonics WHERE meter_id=1 AND phase='RN' GROUP BY hour, meter_id, phase ORDER BY hour ASC limit 2;
hour | meter_id | phase | min | max
---------------------+----------+-------+------------------------------------------------------------------------------------+---------------------------------------------------------------------------------
2020-12-17 10:00:00 | 1 | RN | {232.52615356445312,0.049829818308353424,0.41124674677848816,0.056136056780815125} | {234.22560119628906,0.09132575988769531,0.6057599186897278,0.05052584037184715}
2020-12-17 11:00:00 | 1 | RN | {233.45909118652344,0.23476898670196533,0.6725903749465942,0.12308577448129654} | {234.91810607910156,0.20045030117034912,0.57932049036026,0.05590963736176491}
(2 rows)
So I'm wondering if there is a way to do MIN/MAX (or other aggregate functions) vertically across table entries, so the first entry of the MIN result has the minimum value of the first entries of all grouped rows, the second entry has the minimum value of the second entry of all grouped rows and so on. I realize I can achieve this by extending my query above to spell out all the 127 members of value, but I wonder if there is a better way.