clickhouse嵌套Map：用total指定

mu0hgdu0 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(2)|浏览(800)

带嵌套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 .
我需要在同一个select中的总响应中获得特定的代码响应计数：

-- where idx1 = 1
/*
┌─code─┬─count─┬─totalCnt─┐
│  200 │     5 │       70 │
│  204 │     5 │       70 │
│  404 │    25 │       70 │
│  502 │    35 │       70 │
└──────┴───────┴──────────┘

* /

clickhouse select Nested

来源：https://stackoverflow.com/questions/60001261/clickhouse-nested-map-get-specified-with-total

2条答案

按热度按时间

dxpyg8gm1#

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 │
└──────┴──────┴─────┴───────┘

赞(0）回复(0）举报 2021-07-15

wnrlj8wa2#

这种方式并不完美，因为 ARRAY JOIN （grafana临时过滤器不适用于此类查询）。

SELECT idx1, code, val, total
FROM (

    SELECT
        idx1,
        sumMap(codeMap.key, codeMap.value) as arr,
        sum(totalCnt) as total
    FROM Test.NestedTest2
    WHERE idx1 = 1
    GROUP BY idx1

)
ARRAY JOIN arr.1 as code, arr.2 as val
ORDER BY idx1, code
/*
┌─idx1─┬─code─┬─val─┬─total─┐
│    1 │  200 │   5 │    70 │
│    1 │  204 │   5 │    70 │
│    1 │  404 │  25 │    70 │
│    1 │  502 │  35 │    70 │
└──────┴──────┴─────┴───────┘

* /

赞(0）回复(0）举报 2021-07-15

我来回答

clickhouse嵌套Map：用total指定

2条答案

相关问题

热门标签

最新问答