带有嵌套 map的Clickhouse表:
CREATE TABLE IF NOT EXISTS Test.NestedTest2
(
-- slices
idx1 UInt8, -- just some index 1
idx2 UInt8, -- just some index 2
-- metrics
totalCnt UInt64, -- total http response count
codeMap Nested(
key UInt16, -- http response code
value UInt64 -- http response count
)
) Engine MergeTree()
PARTITION BY (idx1)
ORDER BY (idx1, idx2)
INSERT INTO Test.NestedTest2 (idx1, idx2, totalCnt, codeMap.key, codeMap.value)
VALUES
(1, 1, 5, array(200), array(5))
(1, 1, 5, array(204), array(5))
(1, 1, 15, array(404, 502), array(5, 10))
(1, 2, 45, array(404, 502), array(20, 25))
(2, 1, 20, array(404, 502, 200), array(5, 5, 10))
此处totalCnt
仅作为示例。它可能与总价值相关,例如memoryUsage
。
我需要在同一选择的总响应中获取特定的代码响应计数:
-- where idx1 = 1
/*
┌─code─┬─count─┬─totalCnt─┐
│ 200 │ 5 │ 70 │
│ 204 │ 5 │ 70 │
│ 404 │ 25 │ 70 │
│ 502 │ 35 │ 70 │
└──────┴───────┴──────────┘
*/
SELECT
idx1,
(arrayJoin(arrayMap((x, y) -> (x, y), (sumMap(codeMap.key, codeMap.value) AS a).1, a.2)) AS arr).1 AS code,
arr.2 AS cnt,
sum(totalCnt) AS total
FROM Test.NestedTest2
WHERE idx1 = 1
GROUP BY idx1
┌─idx1─┬─code─┬─cnt─┬─total─┐
│ 1 │ 200 │ 5 │ 70 │
│ 1 │ 204 │ 5 │ 70 │
│ 1 │ 404 │ 25 │ 70 │
│ 1 │ 502 │ 35 │ 70 │
└──────┴──────┴─────┴───────┘
顺便说一句,你可以计算出total
从codeMap.value
-arrayReduce('sum', groupArrayArray
SELECT
idx1,
(arrayJoin(arrayMap((x, y) -> (x, y), (sumMap(codeMap.key, codeMap.value) AS a).1, a.2)) AS arr).1 AS code,
arr.2 AS cnt,
arrayReduce('sum', groupArrayArray(codeMap.value)) AS total
FROM Test.NestedTest2
WHERE idx1 = 1
GROUP BY idx1
┌─idx1─┬─code─┬─cnt─┬─total─┐
│ 1 │ 200 │ 5 │ 70 │
│ 1 │ 204 │ 5 │ 70 │
│ 1 │ 404 │ 25 │ 70 │
│ 1 │ 502 │ 35 │ 70 │
└──────┴──────┴─────┴───────┘