clickhouse sql（一）

clickhouse sql 语法

array join

类似laterl view 写法
left array join

--建表
CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;
--插入数据
INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4]), ('Goodbye', []);
select * from arrays_test;
┌─s───────┬─arr───┐
│ Hello   │ [1,2] │
│ World   │ [3,4] │
│ Goodbye │ []    │
└─────────┴───────┘
--left array join 
SELECT s, arr FROM arrays_test LEFT ARRAY JOIN arr;
─s───────┬─arr─┐
│ Hello   │   1 │
│ Hello   │   2 │
│ World   │   3 │
│ World   │   4 │
│ Goodbye │   0 │
└─────────┴─────┘
没有值数组，会置为0/null/空字符串
# 笛卡尔积关联，array join 和 left array join 值一样
SELECT s,arr, arr_external FROM arrays_test ARRAY JOIN ['p0','p1'] AS arr_external;
┌─s───────┬─arr───┬─arr_external─┐
│ Hello   │ [1,2] │ p0           │
│ Hello   │ [1,2] │ p1           │
│ World   │ [3,4] │ p0           │
│ World   │ [3,4] │ p1           │
│ Goodbye │ []    │ p0           │
│ Goodbye │ []    │ p1           │
└─────────┴───────┴──────────────┘
SELECT s,arr, arr_external FROM arrays_test left ARRAY JOIN ['p0','p1'] AS arr_external;
┌─s───────┬─arr───┬─arr_external─┐
│ Hello   │ [1,2] │ p0           │
│ Hello   │ [1,2] │ p1           │
│ World   │ [3,4] │ p0           │
│ World   │ [3,4] │ p1           │
│ Goodbye │ []    │ p0           │
│ Goodbye │ []    │ p1           │
└─────────┴───────┴──────────────┘

distinct

INSERT INTO arrays_test
VALUES ('', [1,2]), (null, [3,4]), ('Goodbye', []);
INSERT INTO arrays_test
VALUES ('', [0]), (null, [0]), ('', []);

数据如下：
┌─s───────┬─arr───┐
│ Hello   │ [1,2] │
│ World   │ [3,4] │
│ Goodbye │ []    │
└─────────┴───────┘
┌─s─┬─arr─┐
│   │ [0] │
│   │ [0] │
│   │ []  │
└───┴─────┘
┌─s───────┬─arr───┐
│         │ [1,2] │
│         │ [3,4] │
│ Goodbye │ []    │
└─────────┴───────┘

SELECT DISTINCT s  FROM arrays_test;
''和null 都去重，只有一行了
┌─s───────┐
│ Hello   │
│ World   │
│ Goodbye │
└─────────┘
┌─s─┐
│   │
└───┘
SELECT DISTINCT ON (s)*  FROM arrays_test;
┌─s───────┬─arr───┐
│         │ [1,2] │
│ Goodbye │ []    │
└─────────┴───────┘
┌─s─────┬─arr───┐
│ Hello │ [1,2] │
│ World │ [3,4] │
空的值，随便保留一行数据

count

–对null 和 ‘’不过滤

select count(s) from arrays_test;
┌─count(s)─┐
│        9 │
└──────────┘
select count(1) from arrays_test;
┌─count()─┐
│       9 │
└─────────┘

大于、小于等比较运算

clickhouse 将空字符串和null，看成特殊的值‘’

select * from arrays_test  where s=' '; 
─s─┬─arr───┐
│   │ [1,2] │
│   │ [3,4] │
└───┴───────┘
┌─s─┬─arr─┐
│   │ [0] │
│   │ [0] │
│   │ []  │
└───┴─────┘

intersect和except

intersect distinct 取交集，并去重
intersect 直接取交集
except 取差值

group by

’ '和null同意成空，clickhouse 并将空看成特殊的值

select s,count(1) from arrays_test group by s; 

┌─s───────┬─count()─┐
│         │       5 │
│ World   │       1 │
│ Goodbye │       2 │
│ Hello   │       1 │

rollup 上卷

group by all

cube 数据立方体

total

alter table

注意事项：分布式引擎不支持 alter drop partiion;

#分布式删除分区一
ALTER TABLE wnos.dm_wirelesskpi_stat_d on CLUSTER cluster_2shard_2replicas DROP PARTITION (2,'2023-03-06');
报错：Code: 48. DB::Exception: Table engine Distributed doesn't support partitioning. (NOT_IMPLEMENTED) (version 22.9.3.18 (official build))

#本地表删除分区二 
ALTER TABLE wnos.dm_wirelesskpi_stat_d  DROP PARTITION (2,'2023-03-06');