Warm tip: This article is reproduced from stackoverflow.com, please click
select nested clickhouse

Clickhouse nested map: get specified with total

发布于 2020-03-28 23:14:14

Clickhouse table with nested map:

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))

Where totalCnt only as example. Same way it may by related to total value, such as memoryUsage.

I need to get specific code response count within the total responses in the same select:

-- where idx1 = 1
/*
┌─code─┬─count─┬─totalCnt─┐
│  200 │     5 │       70 │
│  204 │     5 │       70 │
│  404 │    25 │       70 │
│  502 │    35 │       70 │
└──────┴───────┴──────────┘
*/
Questioner
pulter-dev-2
Viewed
78
分享
Denis Zhuravlev 2020-02-01 04:08 
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 │
└──────┴──────┴─────┴───────┘

BTW, you can calculate total from 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 │
└──────┴──────┴─────┴───────┘

Related issues

1
How to group by month and year?
2
Calculate difference of multiple highest and lowest column values
3
Joining multiple child tables to one parent table WITHOUT getting all possible combinations
4
placeholder for select in vuejs 2.0.0
5
Select all columns except one in MySQL?
6
<input type="text" assign id value to php string
7
how to set value to <select> tag [NOTE that the value may not always constant]
8
SQL query in python
9
PostgreSQL return result of funtion in loop
10
Postgres: Distinct but only for one column