如何在clickhouse中实现“pivot”，就像在dolphindb中一样

我想做一些 pivot 一些数据。就像跟在后面一样。

>>> df = pd.DataFrame({'foo': ['one', 'one', 'one', 'two', 'two',
...                            'two'],
...                    'bar': ['A', 'B', 'C', 'A', 'B', 'C'],
...                    'baz': [1, 2, 3, 4, 5, 6],
...                    'zoo': ['x', 'y', 'z', 'q', 'w', 't']})
>>> df
    foo   bar  baz  zoo
0   one   A    1    x
1   one   B    2    y
2   one   C    3    z
3   two   A    4    q
4   two   B    5    w
5   two   C    6    t
>>> df.pivot(index='foo', columns='bar', values='baz')
bar  A   B   C
foo
one  1   2   3
two  4   5   6

我知道海豚能做什么 pivot 在sql中。

dateValue=2007.08.01
    num=500
    syms = (exec count(*) from taq 
    where 
        date = dateValue, 
        time between 09:30:00 : 15:59:59, 
        0<bid, bid<ofr, ofr<bid*1.2
    group by symbol order by count desc).symbol[0:num]

    priceMatrix = exec avg(bid + ofr)/2.0 as price from taq 
    where 
        date = dateValue, Symbol in syms, 
        0<bid, bid<ofr, ofr<bid*1.2, 
        time between 09:30:00 : 15:59:59 
    pivot by time.minute() as minute, Symbol

但怎么办呢 pivot 在clickhouse？我应该使用客户端api来获取数据吗？但是有太多的行，处理太多的行太困难了。如果我不能使用 pandas ，如何实施 pivot 操作容易吗？

这是初步的实施，可以帮助您开始。
评论：
不支持行中的“孔”（每列应包含值）
所有列的类型转换为公共类型（字符串）
引入了ordernum字段。它是结果中源列的顺序号（例如，“bar”-第二列）
结果表示为一行一列的数组类型。数组项的顺序由ordernum定义。
准备测试数据：

CREATE TABLE test.pivot_test
(    
    orderNum Int,
    s String,
    values Array(String)
) ENGINE = Memory;

INSERT INTO test.pivot_test
VALUES 
  (1, 'foo', ['one', 'one', 'one', 'two', 'two', 'two']),
  (3, 'baz', ['1', '2', '3', '4', '5', '6']),
  (4, 'zoo', ['x', 'y', 'z', 'q', 'w', 't']),
  (2, 'bar', ['A', 'B', 'C', 'A', 'B', 'C']);

/* 
The content of table test.pivot_test:

┌─orderNum─┬─s───┬─values────────────────────────────────┐
│        1 │ foo │ ['one','one','one','two','two','two'] │
│        3 │ baz │ ['1','2','3','4','5','6']             │
│        4 │ zoo │ ['x','y','z','q','w','t']             │
│        2 │ bar │ ['A','B','C','A','B','C']             │
└──────────┴─────┴───────────────────────────────────────┘

* /

数据透视模拟：

SELECT arrayMap(x -> x.1, arraySort(x -> x.2, groupArray(value_ordernum))) as row
FROM
(
    SELECT
        (value, orderNum) AS value_ordernum,
        value_index
    FROM test.pivot_test
    ARRAY JOIN
        values AS value,
        arrayEnumerate(values) AS value_index
  /*
    The result of execution the nested query:

    ┌─value_ordernum─┬─value_index─┐
    │ ('one',1)      │           1 │
    │ ('one',1)      │           2 │
    │ ('one',1)      │           3 │
    │ ('two',1)      │           4 │
    │ ('two',1)      │           5 │
    │ ('two',1)      │           6 │
    │ ('1',3)        │           1 │
    │ ('2',3)        │           2 │
    │ ('3',3)        │           3 │
    │ ('4',3)        │           4 │
    │ ('5',3)        │           5 │
    │ ('6',3)        │           6 │
    │ ('x',4)        │           1 │
    │ ('y',4)        │           2 │
    │ ('z',4)        │           3 │
    │ ('q',4)        │           4 │
    │ ('w',4)        │           5 │
    │ ('t',4)        │           6 │
    │ ('A',2)        │           1 │
    │ ('B',2)        │           2 │
    │ ('C',2)        │           3 │
    │ ('A',2)        │           4 │
    │ ('B',2)        │           5 │
    │ ('C',2)        │           6 │
    └────────────────┴─────────────┘  
  */
)
GROUP BY value_index;

/*
The final result:

┌─row─────────────────┐
│ ['two','A','4','q'] │
│ ['one','C','3','z'] │
│ ['one','B','2','y'] │
│ ['two','B','5','w'] │
│ ['one','A','1','x'] │
│ ['two','C','6','t'] │
└─────────────────────┘

* /

如何在clickhouse中实现“pivot”，就像在dolphindb中一样

1条答案

相关问题

热门标签

最新问答