CODEWITHSUNDEEP
arrayspostgresqltimescaledb
karihre
karihre

Reputation: 437

MIN/MAX of array elements vertically across rows

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.

Upvotes: 2

Views: 1152

Answers (1)

S-Man
S-Man

Reputation: 23726

step-by-step demo:db<>fiddle

SELECT 
    ARRAY_AGG(min ORDER BY position),
    ARRAY_AGG(max ORDER BY position)
FROM (
    SELECT
        position,
        MIN(element),
        MAX(element)
    FROM t,
        unnest(val) WITH ORDINALITY as s(element, position)
    GROUP BY position
) s
  1. Expand all array elements into one row each. Add the WITH ORDINALITY to store the position of each element in the array.
  2. Group by position and find the related MIN and MAX values per array position
  3. Reaggregate the MIN and MAX values into an array. The ordinality can be used to keep the original order.

Upvotes: 2

Related Questions