在clickhouse中聚合时如何计数元素？

Question

我有这样的数据

customer_id - product_id

1- 10

1-11

1- 12

1-11

2- 15

2- 20

在聚合之后，我想得到：

customer_id - product_id

{10:1，11:2，12:1}

2- {15:1，20:1}

Answer 1

请尝试以下查询：

SELECT
    customer_id,
    arrayMap((product_id, count) -> (product_id, count),
        untuple(sumMap([product_id], [1]))) AS result
FROM 
(
    /* Emulate the test dataset. */
    SELECT
        data.1 AS customer_id,
        data.2 AS product_id
    FROM 
    (
        SELECT arrayJoin([(1, 10), (1, 11), (1, 12), (1, 11), 
                          (2, 15), (2, 20)]) AS data
    )
)
GROUP BY customer_id

/*
┌─customer_id─┬─result─────────────────┐
│           1 │ [(10,1),(11,2),(12,1)] │
│           2 │ [(15,1),(20,1)]        │
└─────────────┴────────────────────────┘
*/

Answer 2

WITH dataset AS
    (
        SELECT
            data.1 AS customer_id,
            data.2 AS product_id
        FROM
        (
            SELECT arrayJoin([
              (1, 10), (1, 11), (1, 12), (1, 11), (2, 15), (2, 20)
            ]) AS data
        )
    )



SELECT
    customer_id,
    arrayMap(
      x -> (x, arrayCount(y -> (y = x), groupArray(product_id) AS product_ids)),
      arrayDistinct(product_ids)
    ) AS result
FROM dataset
GROUP BY customer_id

┌─customer_id─┬─result─────────────────┐
│           1 │ [(10,1),(11,2),(12,1)] │
│           2 │ [(15,1),(20,1)]        │
└─────────────┴────────────────────────┘