How to pivot subgroups?

Question

We have created a flat table for Clickhouse and are trying to get records from this table to create a Materialized view. The logic is if e_id is null the record is 'TypeB', if e_id is not null then record is 'TypeA'. Both TypeA and TypeB records will have the same p_id and s_id. We want to create one record per p_id+s_id combination.

The query given below works well with filter (p_id =1 and s_id = 1) but without filters - the exception is "DB::Exception: Scalar subquery returned more than one row" Is it possible to do this in ClickHouse? Would it be possible to create Materialized View with such a query?

select p_id,s_id,
       groupArray(e_id),
       groupArray(name),
       (select groupArray(name) 
           from flat_table
           where e_id is null and p_id =1 and s_id = 1
           group by p_id,s_id) as typeB
       from flat_table
       where e_id is not null and p_id =1 and s_id = 1
       group by p_id,s_id;
/*
This what the table looks like:
Flat_table
p_id  s_id  e_id  name
1     1      1     Jake
1     1      2     Bob
1     1      null  Barby
1     1      null  Ella

This is expected result:
p_id  s_id  e_id  typeA        typeB
1      1    [1,2] [Jake,Bob]   [Barby,Ella]
*/

vladimir vladimir · Accepted Answer · 2019-09-12T20:56:27

Let's try this query:

SELECT p_id, s_id, e_ids, typeA, typeB
FROM (
  SELECT
      p_id,
      s_id,
      groupArray((e_id, name)) eid_names,
      arrayMap(x -> x.1, arrayFilter(x -> not isNull(x.1), eid_names)) e_ids,
      arrayMap(x -> x.2, arrayFilter(x -> not isNull(x.1), eid_names)) typeA,
      arrayMap(x -> x.2, arrayFilter(x -> isNull(x.1), eid_names)) typeB
  FROM test.test_006
  GROUP BY p_id, s_id)
/* Result
┌─p_id─┬─s_id─┬─e_ids─┬─typeA────────────┬─typeB──────────────┐
│    2 │    2 │ [1,2] │ ['Jake2','Bob2'] │ ['Barby2','Ella2'] │
│    1 │    1 │ [1,2] │ ['Jake','Bob']   │ ['Barby','Ella']   │
└──────┴──────┴───────┴──────────────────┴────────────────────┘
*/

/* Data preparation queries */

CREATE TABLE test.test_006
(
    `p_id` Int32,
    `s_id` Int32,
    `e_id` Nullable(Int32),
    `name` String
)
ENGINE = Memory

INSERT INTO test.test_006
VALUES (1, 1, 1, 'Jake'), (1, 1, 2, 'Bob'), (1, 1, null, 'Barby'), (1, 1, null, 'Ella'),
  (2, 2, 1, 'Jake2'), (2, 2, 2, 'Bob2'), (2, 2, null, 'Barby2'), (2, 2, null, 'Ella2')

How to pivot subgroups?

1 Answers