是否有任何函数(将元组更改为数组)或(按键求和数组)?

wqlqzqxt  于 2021-07-15  发布在  ClickHouse
关注(0)|答案(1)|浏览(333)

q1和q2在不同的方面是同一个问题。如果数据存储为元组(键、值),任何sql都能得到相同的结果?

(1,3)(2,5)(4,7)
(1,3)(2,5)(3,4)
(2,3)(7,5)(10,4)

问题1:summap可以将数组更改为元组,但是如何将元组更改为数组?

select sumMap(a, b) from (
select array(1,2,4) as a, array(3,5,7) as b
union all
select array(1,2,3) as a, array(3,5,4) as b
union all
select array(2,7,10) as a, array(3,5,4) as b);

│ ([1,2,3,4,7,10],[6,13,4,7,5,4]) │

错误sql:

select sumMap(a, b).[0], sumMap(a, b).[1] from tbl

[1,2,3,4,7,10]   [6,13,4,7,5,4]

问题2:如何按键对数组求和,比如summap?

select array(1,2,4) as a, array(3,5,7) as b
union all
select array(1,2,3) as a, array(3,5,4) as b
union all
select array(2,7,10) as a, array(3,5,4) as b

│ [1,2,4] │ [3,5,7] │
│ [2,7,10]│ [3,5,4] │
│ [1,2,3] │ [3,5,4] │

错误sql:

select sumBykey(a, a), sumBykey(b, a).key2 from tbl

[1,2,3,4,7,10]   [6,13,4,7,5,4]
u91tlkcl

u91tlkcl1#

它需要使用元组访问操作符。

SELECT
    sumMap(a, b) AS summap,
    summap.1 AS a1,
    summap.2 AS a2
FROM
(
    SELECT [1, 2, 4] AS a, [3, 5, 7] AS b
    UNION ALL
    SELECT [1, 2, 3] AS a, [3, 5, 4] AS b
    UNION ALL
    SELECT [2, 7, 10] AS a, [3, 5, 4] AS b
)

/* Result:
    ┌─summap──────────────────────────┬─a1─────────────┬─a2─────────────┐
    │ ([1,2,3,4,7,10],[6,13,4,7,5,4]) │ [1,2,3,4,7,10] │ [6,13,4,7,5,4] │
    └─────────────────────────────────┴────────────────┴────────────────┘

* /

此时summap只支持数字键值。对其他类型的密钥使用哈希:

SELECT
    sumMap(arrayMap(x -> xxHash32(x), a), b) AS summap,
    summap.1 AS a1,
    summap.2 AS a2
FROM
(
    SELECT ['1', '2', '4'] AS a, [3, 5, 7] AS b
    UNION ALL
    SELECT ['1', '2', '3'] AS a, [3, 5, 4] AS b
    UNION ALL
    SELECT ['2', '7', '10'] AS a, [3, 5, 4] AS b
)

/* Result:
┌─summap─────────────────────────────────────────────────────────────────────────────┬─a1────────────────────────────────────────────────────────────────┬─a2─────────────┐
│ ([205742900,548432130,1150380693,1842982710,2632741828,3068971186],[13,5,4,7,4,6]) │ [205742900,548432130,1150380693,1842982710,2632741828,3068971186] │ [13,5,4,7,4,6] │
└────────────────────────────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────────────┴────────────────┘

* /

相关问题