CODEWITHSUNDEEP
sqlclickhouse
lino
lino

Reputation: 207

Transpose rows to columns in clickhouse

I have this table

SQL for create table

CREATE TABLE myTable
(
    `fecha` Date,
    `type` String,
    `Field1` String,
    `Field2` UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY fecha
SETTINGS index_granularity = 8192

insert into myTable VALUES 
('2021-10-10','A','aa',10),
('2021-10-10','A','aa',10),
('2021-10-10','B','bb',11),
('2021-10-10','B','aa',1),
('2021-10-10','C','c',33),
('2021-10-11','A','aa',100),
('2021-10-11','A','zzz',100),
('2021-10-11','B','bb',111),
('2021-10-11','C','cc',122)
date type Field1 Field2
2021-10-10 A aa 10
2021-10-10 A aa 10
2021-10-10 B bb 11
2021-10-10 B aa 1
2021-10-10 C c 33
2021-10-11 A aa 100
2021-10-11 A zzz 100
2021-10-11 B bb 111
2021-10-11 C cc 122

I don't know column names nor number of columns and result desired is

date Type_A_uniq_values_Field1 Type_A_sum_Field2 Type_B_uniq_values_Field1 Type_B_sum_Field2 Type_C_uniq_values_Field1 Type_C_sum_Field2
2021-10-10 1 20 2 12 1 33
2021-10-11 2 200 1 111 1 122

thanks!!

Upvotes: 2

Views: 7224

Answers (1)

Denny Crane
Denny Crane

Reputation: 13310

SELECT
    fecha,
    length(groupUniqArrayIf(Field1, type = 'A')) AS Type_A_1,
    sumIf( Field2, type = 'A') AS Type_A_2,
    length(groupUniqArrayIf(Field1, type = 'B')) AS Type_B_1,
    sumIf( Field2, type = 'B') AS Type_B_2,
    length(groupUniqArrayIf(Field1, type = 'C')) AS Type_C_1,
    sumIf( Field2, type = 'C') AS Type_C_2
FROM myTable
GROUP BY fecha
┌──────fecha─┬─Type_A_1─┬─Type_A_2─┬─Type_B_1─┬─Type_B_2─┬─Type_C_1─┬─Type_C_2─┐
│ 2021-10-10 │        1 │       30 │        2 │       12 │        1 │       33 │
│ 2021-10-11 │        2 │      200 │        1 │      111 │        1 │      122 │
└────────────┴──────────┴──────────┴──────────┴──────────┴──────────┴──────────┘


SELECT
    fecha,
    length(groupUniqArrayIf(Field1, type = 'A')) AS Type_A_1,
    arraySum(groupArrayIf( Field2, type = 'A')) AS Type_A_2,
    length(groupUniqArrayIf(Field1, type = 'B')) AS Type_B_1,
    arraySum(groupArrayIf( Field2, type = 'B')) AS Type_B_2,
    length(groupUniqArrayIf(Field1, type = 'C')) AS Type_C_1,
    arraySum(groupArrayIf( Field2, type = 'C')) AS Type_C_2
FROM myTable
GROUP BY fecha
┌──────fecha─┬─Type_A_1─┬─Type_A_2─┬─Type_B_1─┬─Type_B_2─┬─Type_C_1─┬─Type_C_2─┐
│ 2021-10-10 │        1 │       30 │        2 │       12 │        1 │       33 │
│ 2021-10-11 │        2 │      200 │        1 │      111 │        1 │      122 │
└────────────┴──────────┴──────────┴──────────┴──────────┴──────────┴──────────┘


SELECT
    fecha,
    length(Type_A.1) AS Type_A_1,
    arraySum(Type_A.2) AS Type_A_2,
    length(Type_B.1) AS Type_B_1,
    arraySum(Type_B.2) AS Type_B_2,
    length(Type_C.1) AS Type_C_1,
    arraySum(Type_C.2) AS Type_C_2
FROM
(
    SELECT
        fecha,
        sumMapIf([Field1], [Field2], type = 'A') AS Type_A,
        sumMapIf([Field1], [Field2], type = 'B') AS Type_B,
        sumMapIf([Field1], [Field2], type = 'C') AS Type_C
    FROM myTable
    GROUP BY fecha
)
┌──────fecha─┬─Type_A_1─┬─Type_A_2─┬─Type_B_1─┬─Type_B_2─┬─Type_C_1─┬─Type_C_2─┐
│ 2021-10-10 │        1 │       30 │        2 │       12 │        1 │       33 │
│ 2021-10-11 │        2 │      200 │        1 │      111 │        1 │      122 │
└────────────┴──────────┴──────────┴──────────┴──────────┴──────────┴──────────┘



SELECT
    fecha,
    untuple(anyIf(f, type = 'A')),
    untuple(anyIf(f, type = 'B')),
    untuple(anyIf(f, type = 'C'))
FROM
(
    SELECT
        fecha,
        type,
        (length((sumMap([Field1], [Field2]) AS x).1), arraySum(x.2)) AS f
    FROM myTable
    GROUP BY
        fecha,
        type
)
GROUP BY fecha;
┌──────fecha─┬─tupleElement(anyIf(f, equals(type, 'A')), 1)─┬─tupleElement(anyIf(f, equals(type, 'A')), 2)─┬─tupleElement(anyIf(f, equals(type, 'B')), 1)─┬─tupleElement(anyIf(f, equals(type, 'B')), 2)─┬─tupleElement(anyIf(f, equals(type, 'C')), 1)─┬─tupleElement(anyIf(f, equals(type, 'C')), 2)─┐
│ 2021-10-10 │                                            1 │                                           30 │                                            2 │                                           12 │                                            1 │                                           33 │
│ 2021-10-11 │                                            2 │                                          200 │                                            1 │                                          111 │                                            1 │                                          122 │
└────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┘


SELECT
    fecha,
    groupArrayIf((Field1, Field2), type = 'A') AS Type_A,
    groupArrayIf((Field1, Field2), type = 'B') AS Type_B,
    groupArrayIf((Field1, Field2), type = 'C') AS Type_C
FROM myTable
GROUP BY fecha
┌──────fecha─┬─Type_A──────────────────────────┬─Type_B───────────────┬─Type_C───────┐
│ 2021-10-10 │ [('aa',10),('aa',10),('aa',10)] │ [('bb',11),('aa',1)] │ [('c',33)]   │
│ 2021-10-11 │ [('aa',100),('zzz',100)]        │ [('bb',111)]         │ [('cc',122)] │
└────────────┴─────────────────────────────────┴──────────────────────┴──────────────┘


SELECT
    fecha,
    type,
    sumMap([Field1], [Field2])
FROM myTable
GROUP BY
    fecha,
    type
┌──────fecha─┬─type─┬─sumMap(array(Field1), array(Field2))─┐
│ 2021-10-10 │ B    │ (['aa','bb'],[1,11])                 │
│ 2021-10-10 │ C    │ (['c'],[33])                         │
│ 2021-10-11 │ A    │ (['aa','zzz'],[100,100])             │
│ 2021-10-10 │ A    │ (['aa'],[30])                        │
│ 2021-10-11 │ C    │ (['cc'],[122])                       │
│ 2021-10-11 │ B    │ (['bb'],[111])                       │
└────────────┴──────┴──────────────────────────────────────┘


SELECT
    fecha,
    sumMapIf([Field1], [Field2], type = 'A') AS Type_A,
    sumMapIf([Field1], [Field2], type = 'B') AS Type_B,
    sumMapIf([Field1], [Field2], type = 'C') AS Type_C
FROM myTable
GROUP BY fecha
┌──────fecha─┬─Type_A───────────────────┬─Type_B───────────────┬─Type_C─────────┐
│ 2021-10-10 │ (['aa'],[30])            │ (['aa','bb'],[1,11]) │ (['c'],[33])   │
│ 2021-10-11 │ (['aa','zzz'],[100,100]) │ (['bb'],[111])       │ (['cc'],[122]) │
└────────────┴──────────────────────────┴──────────────────────┴────────────────┘


SELECT
    fecha,
    arrayStringConcat(Type_A.1, '/') AS Type_A_1,
    arraySum(Type_A.2) AS Type_A_2,
    arrayStringConcat(Type_B.1, '/') AS Type_B_1,
    arraySum(Type_B.2) AS Type_B_2,
    arrayStringConcat(Type_C.1, '/') AS Type_C_1,
    arraySum(Type_C.2) AS Type_C_2
FROM
(
    SELECT
        fecha,
        sumMapIf([Field1], [Field2], type = 'A') AS Type_A,
        sumMapIf([Field1], [Field2], type = 'B') AS Type_B,
        sumMapIf([Field1], [Field2], type = 'C') AS Type_C
    FROM myTable
    GROUP BY fecha
)
┌──────fecha─┬─Type_A_1─┬─Type_A_2─┬─Type_B_1─┬─Type_B_2─┬─Type_C_1─┬─Type_C_2─┐
│ 2021-10-10 │ aa       │       30 │ aa/bb    │       12 │ c        │       33 │
│ 2021-10-11 │ aa/zzz   │      200 │ bb       │      111 │ cc       │      122 │
└────────────┴──────────┴──────────┴──────────┴──────────┴──────────┴──────────┘

Upvotes: 4

Related Questions