首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >跨行垂直排列元素的最小/最大值

问跨行垂直排列元素的最小/最大值
EN

Stack Overflow用户
提问于 2020-12-17 12:11:09
回答 1查看 345关注 0票数 2

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

代码语言:javascript
复制
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。

示例数据如下:

代码语言:javascript
复制
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个值元素:

代码语言:javascript
复制
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似乎不起作用:

代码语言:javascript
复制
=> 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个成员,但我想知道是否有更好的方法。

EN

回答 1

Stack Overflow用户

发布于 2020-12-17 12:27:22

step-by-step demo:db<>fiddle

代码语言:javascript
复制
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值重新聚合到数组中。序号可以用来保持原来的顺序。
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/65340511

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档