我正在构建一个包含数组中值的表的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,包含表中不同行的min/max值。类似于以下查询的内容,理想情况下只包含所有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 / min/max似乎不起作用:
=> 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结果的第一个条目具有所有分组行的第一个条目的最小值,第二个条目具有所有分组行的第二个条目的最小值,等等。我意识到我可以通过扩展上面的查询来详细说明value的所有127个成员,但我想知道是否有更好的方法。
发布于 2020-12-17 12:27:22
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
) sWITH ORDINALITY以存储数组中每个元素的位置。position分组,并在每个数组位置找到相关的MIN和MAX值MIN和MAX值重新聚合到数组中。序号可以用来保持原来的顺序。https://stackoverflow.com/questions/65340511
复制相似问题