时间戳

首页 > 解决方案 > 垂直跨行的数组元素的 MIN/MAX

arrays - 垂直跨行的数组元素的 MIN/MAX

问题描述

我正在 Timescaledb 中构建一个包含数组中值的表的连续聚合。

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()

该列value的固定基数为 127。

示例数据如下所示：

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)

我希望我的聚合视图具有列min和max长度为 127 的数组类型，其中包含表不同行的最小值/最大值。类似于以下查询的内容，仅理想情况下包含所有 127 个值元素：

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)

一个简单的最小值/最大值value似乎没有奏效：

=> 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)

所以我想知道是否有办法在表条目之间垂直执行 MIN/MAX（或其他聚合函数） ，所以 MIN 结果的第一个条目具有所有分组行的第一个条目的最小值，第二个条目具有所有分组行的第二个条目的最小值，依此类推。我意识到我可以通过扩展上面的查询来拼出 的所有 127 个成员来实现这一点value，但我想知道是否有更好的方法。

标签： arrayspostgresqltimescaledb

解决方案

分步演示：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. 将所有数组元素分别展开为一行。添加WITH ORDINALITY以存储数组中每个元素的位置。
  2. 分组position并查找每个数组位置的相关值MIN和值MAX
  3. 将MIN和MAX值重新聚合到一个数组中。序数可以用来保持原来的顺序。

推荐阅读