How can I group by distinct value into a (value,count) array in clickhouse?

Question

for example, I have a table A

create table A (
    id Int64,
    discrete1 String
    discrete2 String
) engine=Log;

and some data

insert into A values
(1,'A','a')
(1,'B','b')
(1,'A','c')
(2,'C','a')
(1,'A','a');

How can I select this result,the tuple is (value,count)

1,[(A,3),(B,1)],[(a,2),(b,1),(c,1)]
2,[(C,1)],[(a,1)]

My table may have many discrete value columns, is there any way to do that in one select without group by discrete value columns one by one.

Answer 1

SELECT
    id,
    sumMap(arr, arrayResize(CAST([], 'Array(UInt64)'), length(arr), toUInt64(1))) AS s
FROM
(
    SELECT
        id,
        groupArrayArray([discrete1, discrete2]) AS arr
    FROM A
    GROUP BY id
)
GROUP BY id

┌─id─┬─s───────────────────────────────────┐
│  2 │ (['C','a'],[1,1])                   │
│  1 │ (['A','B','a','b','c'],[3,1,2,1,1]) │
└────┴─────────────────────────────────────┘


SELECT
    id,
    arrayMap((x, y) -> (x, y), (sumMap(arr, arrayResize(CAST([], 'Array(UInt64)'), length(arr), toUInt64(1))) AS s).1, s.2) AS r
FROM
(
    SELECT
        id,
        groupArrayArray([discrete1, discrete2]) AS arr
    FROM A
    GROUP BY id
)
GROUP BY id

┌─id─┬─r─────────────────────────────────────────┐
│  2 │ [('C',1),('a',1)]                         │
│  1 │ [('A',3),('B',1),('a',2),('b',1),('c',1)] │
└────┴───────────────────────────────────────────┘


SELECT
    id,
    arrayMap((x, y) -> (x, y),
      (arrayReduce('sumMap', [(groupArrayArray([discrete1, discrete2]) as arr)], 
              [arrayResize(CAST([], 'Array(UInt64)'), length(arr), toUInt64(1))]) as s).1,
      s.2) r
FROM A
GROUP BY id
┌─id─┬─r─────────────────────────────────────────┐
│  2 │ [('C',1),('a',1)]                         │
│  1 │ [('A',3),('B',1),('a',2),('b',1),('c',1)] │
└────┴───────────────────────────────────────────┘