Clickhouse select查询(含with、sample、array join、prewhere、having、limit by、union all、执行计划等)

最新推荐文章于 2024-05-16 03:18:15 发布
最新推荐文章于 2024-05-16 03:18:15 发布
阅读量6.3k 收藏 15
点赞数 2
分类专栏： # Clickhouse 文章标签： clickhouse sample array join prewhere limit by
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yy8623977/article/details/119516455
版权

目录

1. select完整语法

[WITH expr |(subquery)]
SELECT [DISTINCT] expr
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE expr]
[[LEFT] ARRAY JOIN]
[GLOBAL] [ALL|ANY|ASOF] [INNER | CROSS | [LEFT|RIGHT|FULL [OUTER]] ] JOIN
(subquery)|table ON|USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr] [WITH ROLLUP|CUBE|TOTALS]
[HAVING expr]
[ORDER BY expr]
[LIMIT [n[,m]]
[UNION ALL]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT [offset] n BY columns]

语法的解析大致是按此顺序进行的

1.1 准备数据

clickhouse1 :) 
clickhouse1 :) create table select_table_test(
:-] id UInt32,
:-] name String,
:-] age UInt8,
:-] city String,
:-] score Float64
:-] ) engine = MergeTree()
:-] order by (id, intHash32(id))
:-] sample by intHash32(id);
clickhouse1 :) 
clickhouse1 :) insert into select_table_test(id, name, age, city, score) values(1, 'Zhangsan', 30, 'Beijing', 70);
clickhouse1 :) insert into select_table_test(id, name, age, city, score) values(2, 'Lisi', 40, 'Shanghai', 80);
clickhouse1 :) insert into select_table_test(id, name, age, city, score) values(3, 'Wangwu', 50, 'Guangzhou', 90);
clickhouse1 :)
clickhouse1 :) select * from select_table_test;
┌─id─┬─name─────┬─age─┬─city────┬─score─┐
│  1 │ Zhangsan │  30 │ Beijing │    70 │
└────┴──────────┴─────┴─────────┴───────┘
┌─id─┬─name─┬─age─┬─city─────┬─score─┐
│  2 │ Lisi │  40 │ Shanghai │    80 │
└────┴──────┴─────┴──────────┴───────┘
┌─id─┬─name───┬─age─┬─city──────┬─score─┐
│  3 │ Wangwu │  50 │ Guangzhou │    90 │
└────┴────────┴─────┴───────────┴───────┘

2. with

clickhouse1 :) 
clickhouse1 :) with 40 as fit_age
:-] select * from select_table_test where age = fit_age;
┌─id─┬─name─┬─age─┬─city─────┬─score─┐
│  2 │ Lisi │  40 │ Shanghai │    80 │
└────┴──────┴─────┴──────────┴───────┘
clickhouse1 :) 
clickhouse1 :) with sum(score) as sum_score
:-] select city, toString(sum_score), toUInt16(sum_score) from select_table_test group by city;
┌─city──────┬─toString(sum_score)─┬─toUInt16(sum_score)─┐
│ Shanghai  │ 80                  │                  80 │
│ Beijing   │ 70                  │                  70 │
│ Guangzhou │ 90                  │                  90 │
└───────────┴─────────────────────┴─────────────────────┘
clickhouse1 :) 
clickhouse1 :) with (
:-] select age from select_table_test where city = 'Beijing'
:-] ) as fit_age 
:-] select * from select_table_test where age = fit_age;
┌─id─┬─name─────┬─age─┬─city────┬─score─┐
│  1 │ Zhangsan │  30 │ Beijing │    70 │
└────┴──────────┴─────┴─────────┴───────┘
clickhouse1 :)
  • 也可在from子句中的select查询使用with

3. from

clickhouse1 :)
clickhouse1 :) select number from numbers(3);
┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘
clickhouse1 :) 
clickhouse1 :) select 10 from system.one;
┌─10─┐
│ 10 │
└────┘
clickhouse1 :) 
clickhouse1 :) select 10;
┌─10─┐
│ 10 │
└────┘
clickhouse1 :)
  • select 10会从虚拟表system.one查询
  • from后的final关键字，表示先强制partition合并，再得到结果

4. sample

  • 在数据相同，采样规则相同，采样结果是一样的
  • create table的sample by int_type必须整数类型
  1. sample 0 ~ 1
clickhouse1 :)
clickhouse1 :) select name, _sample_factor from select_table_test sample 0.4;
┌─name─────┬─_sample_factor─┐
│ Zhangsan │            2.5 │
└──────────┴────────────────┘
┌─name───┬─_sample_factor─┐
│ Wangwu │            2.5 │
└────────┴────────────────┘
clickhouse1 :) 
clickhouse1 :) select count(*) * any(_sample_factor) from select_table_test sample 4 / 10;
┌─multiply(count(), any(_sample_factor))─┐
│                                      5 │
└────────────────────────────────────────┘
clickhouse1 :)
  • 采样因子等于0时，表示不进行采用，返回所有数据
  1. sample 0 ~ 1 offset 0 ~ 1
clickhouse1 :)
clickhouse1 :) select * from select_table_test sample 0.3 offset 0.1;
┌─id─┬─name─────┬─age─┬─city────┬─score─┐
│  1 │ Zhangsan │  30 │ Beijing │    70 │
└────┴──────────┴─────┴─────────┴───────┘
┌─id─┬─name───┬─age─┬─city──────┬─score─┐
│  3 │ Wangwu │  50 │ Guangzhou │    90 │
└────┴────────┴─────┴───────────┴───────┘
clickhouse1 :)
  • 假如共有10条数据，offset为0.8，sample为0.3；则会从数据的8/10(第9条)开始进行采样，采走3(10 * 0.3)条数据，但实际剩余只有2条数据，最终采样结果为2条数据
  1. sample rows
clickhouse1 :)
clickhouse1 :) select * from select_table_test sample 2;
┌─id─┬─name─────┬─age─┬─city────┬─score─┐
│  1 │ Zhangsan │  30 │ Beijing │    70 │
└────┴──────────┴─────┴─────────┴───────┘
┌─id─┬─name─┬─age─┬─city─────┬─score─┐
│  2 │ Lisi │  40 │ Shanghai │    80 │
└────┴──────┴─────┴──────────┴───────┘
┌─id─┬─name───┬─age─┬─city──────┬─score─┐
│  3 │ Wangwu │  50 │ Guangzhou │    90 │
└────┴────────┴─────┴───────────┴───────┘
clickhouse1 :)
  • 表示采样后有rows行数据
  • 采样的rows参数必须大于1，当rows参数设置的比index_granularity索引粒度值小的多时，也会返回近似index_granularity条数据

5. array join

  • 表A array join 表A的数组Array或嵌套类型Nested字段，将一行数据打平至多行
  • 在一条select语句中，只能由一个array join(from子句除外)
  • 可以直接array join nested_parent_column as n
  1. 准备数据
clickhouse1 :)
clickhouse1 :) create table array_join_table(
:-] fruit String,
:-] values Array(UInt8),
:-] city String,
:-] values2 Array(UInt8)
:-] ) engine = TinyLog;
clickhouse1 :) 
clickhouse1 :) insert into array_join_table(fruit, values, city, values2) values('banana', [1,2,3], 'Hainan', [18,19]);
clickhouse1 :) insert into array_join_table(fruit, values, city, values2) values('orange', [66,88], 'Hunan', [6,7,8,9]);
clickhouse1 :) insert into array_join_table(fruit, values, city, values2) values('apple',[], 'Shangxi', [11,12,13]);
clickhouse1 :) 
clickhouse1 :) select * from array_join_table;
┌─fruit──┬─values──┬─city────┬─values2────┐
│ banana │ [1,2,3] │ Hainan  │ [18,19]    │
│ orange │ [66,88] │ Hunan   │ [6,7,8,9]  │
│ apple  │ []      │ Shangxi │ [11,12,13] │
└────────┴─────────┴─────────┴────────────┘
clickhouse1 :)
  1. inner array join(默认)
clickhouse1 :) 
clickhouse1 :) select fruit, values from array_join_table array join values;
┌─fruit──┬─values─┐
│ banana │      1 │
│ banana │      2 │
│ banana │      3 │
│ orange │     66 │
│ orange │     88 │
└────────┴────────┘
clickhouse1 :) 
clickhouse1 :) select fruit, values, v from array_join_table array join values as v;
┌─fruit──┬─values──┬──v─┐
│ banana │ [1,2,3] │  1 │
│ banana │ [1,2,3] │  2 │
│ banana │ [1,2,3] │  3 │
│ orange │ [66,88] │ 66 │
│ orange │ [66,88] │ 88 │
└────────┴─────────┴────┘
clickhouse1 :)
  • 排除掉了空数组
  1. left array join
clickhouse1 :) 
clickhouse1 :) select fruit, values, v from array_join_table left array join values as v;
┌─fruit──┬─values──┬──v─┐
│ banana │ [1,2,3] │  1 │
│ banana │ [1,2,3] │  2 │
│ banana │ [1,2,3] │  3 │
│ orange │ [66,88] │ 66 │
│ orange │ [66,88] │ 88 │
│ apple  │ []      │  0 │
└────────┴─────────┴────┘
clickhouse1 :) 
clickhouse1 :) select fruit, values, v, arrayMap(x -> x * 2, values) as map_values, m_v from array_join_table left array join values as v, map_values as m_v;
┌─fruit──┬─values──┬──v─┬─map_values─┬─m_v─┐
│ banana │ [1,2,3] │  1 │ [2,4,6]    │   2 │
│ banana │ [1,2,3] │  2 │ [2,4,6]    │   4 │
│ banana │ [1,2,3] │  3 │ [2,4,6]    │   6 │
│ orange │ [66,88] │ 66 │ [132,176]  │ 132 │
│ orange │ [66,88] │ 88 │ [132,176]  │ 176 │
│ apple  │ []      │  0 │ []         │   0 │
└────────┴─────────┴────┴────────────┴─────┘
clickhouse1 :)
  • 数组为空的出现在结果中
  • 与多个数组字段进行array join时，在一行数据中多个数组的元素个数要相同
    ，也是将一行数据打平至多行

6. join

6.1 数据准备

join_table_test1

clickhouse1 :) 
clickhouse1 :) create table join_table_test1(
:-] id UInt32,
:-] name String,
:-] datetime Datetime
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into join_table_test1(id, name, datetime) values(1, 'clickhouse', '2021-08-10 11:00:00');
clickhouse1 :) insert into join_table_test1(id, name, datetime) values(2, 'spark', '2021-08-10 12:01:00');
clickhouse1 :) insert into join_table_test1(id, name, datetime) values(2, 'flink', '2021-08-10 12:02:00');
clickhouse1 :) 
clickhouse1 :) select * from join_table_test1;
┌─id─┬─name───────┬────────────datetime─┐
│  1 │ clickhouse │ 2021-08-10 11:00:00 │
└────┴────────────┴─────────────────────┘
┌─id─┬─name──┬────────────datetime─┐
│  2 │ spark │ 2021-08-10 12:01:00 │
└────┴───────┴─────────────────────┘
┌─id─┬─name──┬────────────datetime─┐
│  2 │ flink │ 2021-08-10 12:02:00 │
└────┴───────┴─────────────────────┘
clickhouse1 :)

join_table_test2

clickhouse1 :) 
clickhouse1 :) create table join_table_test2(
:-] id UInt32,
:-] rate UInt8,
:-] datetime Datetime
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into join_table_test2(id, rate, datetime) values(2, 100, '2021-08-10 11:58:00');
clickhouse1 :) insert into join_table_test2(id, rate, datetime) values(2, 90, '2021-08-10 11:59:00');
clickhouse1 :) insert into join_table_test2(id, rate, datetime) values(3, 80, '2021-08-10 13:00:00');
clickhouse1 :) 
clickhouse1 :) select * from join_table_test2;
┌─id─┬─rate─┬────────────datetime─┐
│  2 │  100 │ 2021-08-10 11:58:00 │
└────┴──────┴─────────────────────┘
┌─id─┬─rate─┬────────────datetime─┐
│  2 │   90 │ 2021-08-10 11:59:00 │
└────┴──────┴─────────────────────┘
┌─id─┬─rate─┬────────────datetime─┐
│  3 │   80 │ 2021-08-10 13:00:00 │
└────┴──────┴─────────────────────┘
clickhouse1 :)

join_table_test3

clickhouse1 :) 
clickhouse1 :) create table join_table_test3(
:-] id UInt32,
:-] star UInt32
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into join_table_test3(id, star) values(1, 1000);
clickhouse1 :) insert into join_table_test3(id, star) values(2, 900);
clickhouse1 :) 
clickhouse1 :) select * from join_table_test3;
┌─id─┬─star─┐
│  1 │ 1000 │
└────┴──────┘
┌─id─┬─star─┐
│  2 │  900 │
└────┴──────┘
clickhouse1 :)

6.2 连接精度all配合连接类型(inner、left、right、full)

  • 默认连接精度，可通过join_default_strictness配置参数修改
  • 基准表一行数据与非基准表多行数据匹配，返回非基准表多行数据
clickhouse1 :) 
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a all inner join join_table_test2 b on a.id = b.id;
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
│  2 │ spark │   90 │
└────┴───────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ flink │  100 │
│  2 │ flink │   90 │
└────┴───────┴──────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a all left join join_table_test2 b on a.id = b.id;
┌─id─┬─name───────┬─rate─┐
│  1 │ clickhouse │    0 │
└────┴────────────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
│  2 │ spark │   90 │
└────┴───────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ flink │  100 │
│  2 │ flink │   90 │
└────┴───────┴──────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a all right join join_table_test2 b on a.id = b.id;
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
│  2 │ spark │   90 │
└────┴───────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ flink │  100 │
│  2 │ flink │   90 │
└────┴───────┴──────┘
┌─id─┬─name─┬─rate─┐
│  0 │      │   80 │
└────┴──────┴──────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a all full join join_table_test2 b on a.id = b.id;
┌─id─┬─name───────┬─rate─┐
│  1 │ clickhouse │    0 │
└────┴────────────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
│  2 │ spark │   90 │
└────┴───────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ flink │  100 │
│  2 │ flink │   90 │
└────┴───────┴──────┘
┌─id─┬─name─┬─rate─┐
│  0 │      │   80 │
└────┴──────┴──────┘
clickhouse1 :)

6.3 连接精度any配合连接类型(inner、left、right)

  • 基准表一行数据与非基准表多行数据匹配，返回非基准表第一行数据
  • 注意inner join时，只返回了一行数据
clickhouse1 :) 
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a any inner join join_table_test2 b on a.id = b.id;
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
└────┴───────┴──────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a any left join join_table_test2 b on a.id = b.id;
┌─id─┬─name───────┬─rate─┐
│  1 │ clickhouse │    0 │
└────┴────────────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
└────┴───────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ flink │  100 │
└────┴───────┴──────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a any right join join_table_test2 b on a.id = b.id;
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
│  2 │ spark │   90 │
└────┴───────┴──────┘
┌─id─┬─name─┬─rate─┐
│  0 │      │   80 │
└────┴──────┴──────┘
clickhouse1 :)

6.4 连接精度asof配合连接类型(inner、left)

  • join key一般都是等于(join key1 = join key2)匹配，asof允许定义一个或多个等于(join key1 = join key2)匹配 + 一个不等于(join key1 >、>=、<、<= join key2)匹配
  • join的步骤
    1. join key等于匹配按all的连接精度关联后
    2. 再用join key不等于匹配取最近的一条；使用using时，最后一个字段为asof column，表示匹配右表asof column与左表asof column最近的值
  • asof column必须是整形、浮点型、日期型这种有序数据类型
clickhouse1 :) 
clickhouse1 :) select a.id, a.name, b.rate, a.datetime, b.datetime from join_table_test1 a asof inner join join_table_test2 b on a.id = b.id and a.datetime > b.datetime;
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ spark │   90 │ 2021-08-10 12:01:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ flink │   90 │ 2021-08-10 12:02:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate, a.datetime, b.datetime from join_table_test1 a asof left join join_table_test2 b on a.id = b.id and a.datetime > b.datetime;
┌─id─┬─name───────┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  1 │ clickhouse │    0 │ 2021-08-10 11:00:00 │ 1970-01-01 08:00:00 │
└────┴────────────┴──────┴─────────────────────┴─────────────────────┘
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ spark │   90 │ 2021-08-10 12:01:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ flink │   90 │ 2021-08-10 12:02:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate, a.datetime, b.datetime from join_table_test1 a asof inner join join_table_test2 b using(id, datetime);
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ spark │   90 │ 2021-08-10 12:01:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ flink │   90 │ 2021-08-10 12:02:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
clickhouse1 :) 
clickhouse1 :) select a.id, a.name, b.rate, a.datetime, b.datetime from join_table_test1 a asof left join join_table_test2 b using(id, datetime);
┌─id─┬─name───────┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  1 │ clickhouse │    0 │ 2021-08-10 11:00:00 │ 1970-01-01 08:00:00 │
└────┴────────────┴──────┴─────────────────────┴─────────────────────┘
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ spark │   90 │ 2021-08-10 12:01:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
┌─id─┬─name──┬─rate─┬────────────datetime─┬──────────b.datetime─┐
│  2 │ flink │   90 │ 2021-08-10 12:02:00 │ 2021-08-10 11:59:00 │
└────┴───────┴──────┴─────────────────────┴─────────────────────┘
clickhouse1 :)

6.5 连接类型cross join

clickhouse1 :)
clickhouse1 :) select a.id, a.name, b.rate from join_table_test1 a cross join join_table_test2 b;
┌─id─┬─name───────┬─rate─┐
│  1 │ clickhouse │  100 │
│  1 │ clickhouse │   90 │
│  1 │ clickhouse │   80 │
└────┴────────────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ spark │  100 │
│  2 │ spark │   90 │
│  2 │ spark │   80 │
└────┴───────┴──────┘
┌─id─┬─name──┬─rate─┐
│  2 │ flink │  100 │
│  2 │ flink │   90 │
│  2 │ flink │   80 │
└────┴───────┴──────┘
clickhouse1 :)

6.6 多表join、join的注意事项

  1. 多表join

对于3个表，先将前2个表进行join，再将它们的结果集与第3个表进行join; 其它情形以此类推

  1. 性能问题

    1. join时请遵循左大右小，因为会将右表加载至内存，与左表关联
    2. 可以考虑使用join表引擎或字典表提示join性能

  2. 空值策略

    • 默认是使用数据类型的默认值填充，可以将join_use_nulls参数设置为1，用Null值填充

7. where和prewhere

  • 会根据where条件字段，判定select是否启用了索引
  • prewhere只能用于mergeTree系列，先对条件字段进行过滤，再对符合的行，进行select其它字段
  • 当使用where的时候，clickhouse会根据where的条件字段，判断是否自动启用prewhere；但下列几种不会自动启用prewhere
    • 常量表达式where 1 = 1
    • where a = 3, a字段为primary key或使用了alias默认值
    • select a ...... where a = 3, select的字段与where字段相同
    • ...... from tb array join nest_field where nest_field.col1 = 10, select查询包含了array join、global in、global not in、 indexHint(函数)

8. group by

  1. 准备数据
clickhouse1 :) create table group_table_test(
:-] province String,
:-] city String,
:-] season String,
:-] sales Float64
:-] ) engine = Memory();
clickhouse1 :) 
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '长沙', '春', 1100);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '长沙', '夏', 1200);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '长沙', '秋', 1300);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '长沙', '冬', 1400);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '株洲', '春', 2100);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '株洲', '夏', 2200);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '株洲', '秋', 2300);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '株洲', '冬', 2400);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '湘潭', '春', 3100);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '湘潭', '夏', 3200);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '湘潭', '秋', 3300);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('湖南', '湘潭', '冬', 3400);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '广州', '春', 4100);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '广州', '夏', 4200);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '广州', '秋', 4300);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '广州', '冬', 4400);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '东莞', '春', 5100);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '东莞', '夏', 5200);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '东莞', '秋', 5300);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '东莞', '冬', 5400);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '深圳', '春', 6100);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '深圳', '夏', 6200);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '深圳', '秋', 6300);
clickhouse1 :) insert into group_table_test(province, city, season, sales) values('广东', '深圳', '冬', 6400);
clickhouse1 :)
  1. with rollup
  • group by province, city with rollup: 从右向左进行上钻，形成组合[province, city]、[province]、[ ]，再对sum(sales)进行计算
clickhouse1 :) 
clickhouse1 :) select province, city, sum(sales) from group_table_test group by province, city with rollup order by province;
┌─province─┬─city─┬─sum(sales)─┐
│          │      │      90000 │
│ 广东     │      │      63000 │
│ 广东     │ 深圳 │      25000 │
│ 广东     │ 广州 │      17000 │
│ 广东     │ 东莞 │      21000 │
│ 湖南     │      │      27000 │
│ 湖南     │ 株洲 │       9000 │
│ 湖南     │ 湘潭 │      13000 │
│ 湖南     │ 长沙 │       5000 │
└──────────┴──────┴────────────┘
clickhouse1 :)
  1. with cube
  • group by province, city with cube: 对所有维度进行组合，形成组合[province, city]、[province]、[city]、[ ]，再对sum(sales)进行计算
clickhouse1 :)
clickhouse1 :) select province, city, sum(sales) from group_table_test group by province, city with cube order by province, city;
┌─province─┬─city─┬─sum(sales)─┐
│          │      │      90000 │
│          │ 东莞 │      21000 │
│          │ 广州 │      17000 │
│          │ 株洲 │       9000 │
│          │ 深圳 │      25000 │
│          │ 湘潭 │      13000 │
│          │ 长沙 │       5000 │
│ 广东     │      │      63000 │
│ 广东     │ 东莞 │      21000 │
│ 广东     │ 广州 │      17000 │
│ 广东     │ 深圳 │      25000 │
│ 湖南     │      │      27000 │
│ 湖南     │ 株洲 │       9000 │
│ 湖南     │ 湘潭 │      13000 │
│ 湖南     │ 长沙 │       5000 │
└──────────┴──────┴────────────┘
clickhouse1 :)
  1. with totals
  • 对所有的数据进行sum(sales)计算
clickhouse1 :)
clickhouse1 :) select province, city, sum(sales) from group_table_test group by province, city with totals order by province, city;
┌─province─┬─city─┬─sum(sales)─┐
│ 广东     │ 东莞 │      21000 │
│ 广东     │ 广州 │      17000 │
│ 广东     │ 深圳 │      25000 │
│ 湖南     │ 株洲 │       9000 │
│ 湖南     │ 湘潭 │      13000 │
│ 湖南     │ 长沙 │       5000 │
└──────────┴──────┴────────────┘

Totals:
┌─province─┬─city─┬─sum(sales)─┐
│          │      │      90000 │
└──────────┴──────┴────────────┘
clickhouse1 :)

9. having

对group by聚合之后的结果，再次进行过滤

clickhouse1 :) 
clickhouse1 :) select province, city, sum(sales) total_sales from group_table_test group by province, city having total_sales > 10000 order by province, city;
┌─province─┬─city─┬─total_sales─┐
│ 广东     │ 东莞 │       21000 │
│ 广东     │ 广州 │       17000 │
│ 广东     │ 深圳 │       25000 │
│ 湖南     │ 湘潭 │       13000 │
└──────────┴──────┴─────────────┘
clickhouse1 :)

10. order by

  • 不同partition的数据不能进行排序
  1. nulls first
clickhouse1 :)
clickhouse1 :) with arrayJoin([30, null, 0/0, 50]) as v
:-] select v order by v nulls first;
┌────v─┐
│ ᴺᵁᴸᴸ │
│  nan │
│   30 │
│   50 │
└──────┘
clickhouse1 :)
  1. nulls last
  • 默认操作
clickhouse1 :) 
clickhouse1 :) with arrayJoin([30, null, 0/0, 50]) as v
:-] select v order by v nulls last;
┌────v─┐
│   30 │
│   50 │
│  nan │
│ ᴺᵁᴸᴸ │
└──────┘
clickhouse1 :)

11. limit by

clickhouse1 :)
clickhouse1 :) select province, city, season, sales from group_table_test limit 1 offset 1 by province, city;
┌─province─┬─city─┬─season─┬─sales─┐
│ 湖南     │ 长沙 │ 夏     │  1200 │
└──────────┴──────┴────────┴───────┘
┌─province─┬─city─┬─season─┬─sales─┐
│ 湖南     │ 株洲 │ 夏     │  2200 │
└──────────┴──────┴────────┴───────┘
┌─province─┬─city─┬─season─┬─sales─┐
│ 湖南     │ 湘潭 │ 夏     │  3200 │
└──────────┴──────┴────────┴───────┘
┌─province─┬─city─┬─season─┬─sales─┐
│ 广东     │ 广州 │ 夏     │  4200 │
└──────────┴──────┴────────┴───────┘
┌─province─┬─city─┬─season─┬─sales─┐
│ 广东     │ 东莞 │ 夏     │  5200 │
└──────────┴──────┴────────┴───────┘
┌─province─┬─city─┬─season─┬─sales─┐
│ 广东     │ 深圳 │ 夏     │  6200 │
└──────────┴──────┴────────┴───────┘
clickhouse1 :)
  • 执行于order by之后和limit之前
  • 按province、city进行分组，在每组中，跳过1条数据，取1条数据
  • 简写形式：limit 1, 1 by province, city

12. limit

clickhouse1 :)
clickhouse1 :) select * from select_table_test limit 1 offset 1;
┌─id─┬─name─┬─age─┬─city─────┬─score─┐
│  2 │ Lisi │  40 │ Shanghai │    80 │
└────┴──────┴─────┴──────────┴───────┘
clickhouse1 :)
clickhouse1 :) select * from select_table_test limit 1, 1;
┌─id─┬─name─┬─age─┬─city─────┬─score─┐
│  2 │ Lisi │  40 │ Shanghai │    80 │
└────┴──────┴─────┴──────────┴───────┘
clickhouse1 :)
  • 如结果数据位于多个partition, 且未使用order by, 每次limit返回的数据可能不同

13. select

正则匹配查询字段

clickhouse1 :)
clickhouse1 :) select columns('^i'), columns('a') from select_table_test;
┌─id─┬─name─────┬─age─┐
│  1 │ Zhangsan │  30 │
│  2 │ Lisi     │  40 │
│  3 │ Wangwu   │  50 │
└────┴──────────┴─────┘
clickhouse1 :)
  • 返回以i开头的字段，和包含a的字段

14. union all

clickhouse1 :) 
clickhouse1 :) select * from select_table_test where id = 1
:-] union all
:-] select * from select_table_test where id = 2;
┌─id─┬─name─────┬─age─┬─city────┬─score─┐
│  1 │ Zhangsan │  30 │ Beijing │    70 │
└────┴──────────┴─────┴─────────┴───────┘
┌─id─┬─name─┬─age─┬─city─────┬─score─┐
│  2 │ Lisi │  40 │ Shanghai │    80 │
└────┴──────┴─────┴──────────┴───────┘
clickhouse1 :)
  • union all后的字段名称以左表的为准

15. 执行计划

clickhouse1 :) 
clickhouse1 :) explain
:-] with 30 as fit_age
:-] select age, count() num from select_table_test
:-] where age = fit_age
:-] group by age 
:-] having num = 1 
:-] order by num 
:-] limit 1;
┌─explain─────────────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                                     │
│   Limit (preliminary LIMIT)                                                                 │
│     MergingSorted (Merge sorted streams for ORDER BY)                                       │
│       MergeSorting (Merge sorted blocks for ORDER BY)                                       │
│         PartialSorting (Sort each block for ORDER BY)                                       │
│           Expression (Before ORDER BY)                                                      │
│             Filter (HAVING)                                                                 │
│               Aggregating                                                                   │
│                 Expression (Before GROUP BY)                                                │
│                   Filter (WHERE)                                                            │
│                     SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│                       ReadFromMergeTree                                                     │
└─────────────────────────────────────────────────────────────────────────────────────────────┘
clickhouse1 :)

也可以通过查看执行日志来查看具体的信息：

[root@clickhouse1 ~]# 
[root@clickhouse1 ~]# clickhouse-client -u default --password default123 --send_logs_level=trace <<< 'select * from select_table_test' > /dev/null
[clickhouse1] 2021.08.09 18:20:59.417657 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Debug> executeQuery: (from [::1]:39664, using production parser) select * from select_table_test 
[clickhouse1] 2021.08.09 18:20:59.418669 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Trace> ContextAccess (default): Access granted: SELECT(id, name, age, city, score) ON default.select_table_test
[clickhouse1] 2021.08.09 18:20:59.418786 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Debug> default.select_table_test (0d5feec0-6dbe-4e23-8d5f-eec06dbe4e23) (SelectExecutor): Key condition: unknown
[clickhouse1] 2021.08.09 18:20:59.418808 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Debug> default.select_table_test (0d5feec0-6dbe-4e23-8d5f-eec06dbe4e23) (SelectExecutor): Selected 1/1 parts by partition key, 1 parts by primary key, 1/1 marks by primary key, 1 marks to read from 1 ranges
[clickhouse1] 2021.08.09 18:20:59.418867 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[clickhouse1] 2021.08.09 18:20:59.418954 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Debug> MergeTreeSelectProcessor: Reading 1 ranges from part all_1_3_1, approx. 3 rows starting from 0
[clickhouse1] 2021.08.09 18:20:59.420643 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Information> executeQuery: Read 3 rows, 135.00 B in 0.0028947 sec., 1036 rows/sec., 45.54 KiB/sec.
[clickhouse1] 2021.08.09 18:20:59.420674 [ 1608 ] {9f16732f-dd99-454d-8878-f6be5bba8345} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
[root@clickhouse1 ~]#
Bulut0907
关注
  • 2
    点赞
  • 踩
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
clickhouse union all之后数据量不一致
QYmufeng的博客
09-15 847
问题：clickhouse使用union all查询结果与每一段sql查询结果只和不一致。原因：因为clickhouse版本问题，官方给出不同的解释。解决方案：将union all的每一段sql用括号括起来。clickhouse版本：22.8.16.32。
ClickHouse查询语句详解
fwdwqdwq的博客
09-11 4512
深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
select into 和 insert into select 两种表复制语句
09-11
select into 和 insert into select 两种表复制语句，需要的朋友可以参考下。
ClickHouse教程 — 第三章 ClickHouse的SQL语句_clickhouse正则匹配查询
最新发布
2401_84900243的博客
05-16 541
默认会记录执行的查询语句到日志表里，随着查询的增多，日志表里的数据也会逐渐累积，导致占用过多空间。表，这两个表不清除的话，时间久了，会挺大的，可以通过设置。
ClickHouse大数据分析技术与实战
11-10
ClickHouse是一个用于OLAP的数据分析引擎，由俄罗斯搜索巨头Yandex公司开源。1、可支持PB级超大容量的数据库管理系统。2、基于SQL语句， 使用成本低。 3、超亿级数据量分析的秒级响应，计算性能横向扩展。4、海量数据即查即用。5、提供数据的预聚合能力，进一步提升数据查询的效率。6、列式存储， 数据压缩，降低磁盘IO和网络IO，提升计算性能，节约70%物理存储。7、支持副本， 实现跨机房的数据容灾。课程基于ClickHouse的**的20.x版本，从理论到实践，由浅入深详解ClickHouse的原理和使用。1. 本课程从零开始，有Linux操作系统和SQL使用经验即可。2. 课程以案例驱动，包含大量的实践案例。3. 将ClickHouse与MySQL、Kafka、HDFS等第三方系统集成，增强ClickHouse的功能。4. 课程覆盖面广，既有基础功能的详细讲解， 也包含了集群部署、查询优化、运维管理等高级内容。
ClickHouse利用WITH语句实现的参数式SQL
蓬莱人形
05-06 5357
ClickHouse的WITH语句功能比较强大，with一般有四种用法 WITH的使用方法 1.使用常量作为"变量" eg: WITH '2019-08-01 15:23:00' as ts_upper_bound SELECT * FROM hits WHERE EventDate = toDate(ts_upper_bound) AND EventTime <= ts_upper_bound; 2.封装表达式 eg: WITH sum(bytes) as exp_sum,form
ClickHouse入门篇-with语法
S1124654的博客
03-02 1056
ClickHouse入门篇-with语法
MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍
09-10
在MySQL查询优化中，连接查询（join）与排序（order by）和限制返回结果的数量（limit）是常见的操作，但当它们结合在一起时，可能会导致性能下降。这个问题在标题和描述中已经阐述得很清楚，主要涉及到如何高效地...
MySQL中使用or、in与union all在查询命令下的效率对比
12-15
在MySQL中，OR、IN和UNION ALL是三种常见的SQL查询...在不确定的情况下，可以使用`EXPLAIN`来分析查询计划，了解MySQL如何执行查询，并据此优化查询语句。同时，定期评估和调整数据库索引策略，以确保最佳的查询性能。
mysql执行计划id为空（UNION关键字）详解
01-19
本文主要给大家介绍了关于mysql执行计划id为空（UNION关键字）的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的 介绍吧 UNION 操作符用于合并两个或多个 SELECT 语句的结果集。例如，我有两个...
Mysql联合查询UNION和UNION ALL的使用介绍
01-19
一、UNION和UNION ALL的作用和语法 UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中...
CC00045.clickhouse——|Hadoop&OLAP_ClickHouse.V17|——|ClickHouse.v17|ClickHouse语法|UnionAll|
yanqi_vip
04-14 816
一、UNION ALL子句 ### --- 你可以使用 UNION ALL 结合任意数量的 SELECT 来扩展其结果。 示例: ~~~ 结果列通过它们的索引进行匹配(在内部的顺序 SELECT). 如果列名称不匹配， ~~~ 则从第一个查询中获取最终结果的名称。 ~~~ 对联合执行类型转换。 ~~~ 例如，如果合并的两个查询具有相同的字段与非-Nu...
Clickhouse 查询语句之 WITH语句 FROM语句
vkingnew 的技术博客
07-02 1万+
WITH语句已经成为SQL语句必不可少的部分，Clickhouse目前的版本对CTE(Common Table Expression 公共表达式)的支持比较有限，相关的限制如下： 1.Recursive queries are not supported. 2.When subquery is used inside WITH section, it’s result should be scalar with exactly one row. 3.Expression’s results are no
【解决】ClickHouse union all 错误386 There is no supertype for types UInt64(类型A), Int64(类型B)
少年单排，记录点滴
05-14 1万+
适用范围： There is no supertype for types A, B 问题复现： 在用union all，将两个相同字段名称的查询结果相连时报错。 SQL语句： select count(distinct user) as "value", name from table1 union all select sum(userTotal) as "value" , name from table2 报错日志： SQL 错误 [386]: ClickHouse excep..
clickhouse 子查询_Clickhouse 查询语句之 WITH语句 FROM语句
weixin_39736547的博客
12-19 3582
WITH语句已经成为SQL语句必不可少的部分，Clickhouse目前的版本对CTE(Common Table Expression 公共表达式)的支持比较有限，相关的限制如下：1.Recursive queries are not supported.2.When subquery is used inside WITH section, it’s result should be scalar...
clickhouse - select语句/select子句/inot outfile/with/prewhere/sample
开码河粉
02-05 1239
dd
clickHouse之SQL语法之select（—）
热门推荐
高精尖发展
01-22 2万+
select SELECT [DISTINCT] expr_list [FROM [db.]table | (subquery) | table_function] [FINAL] [SAMPLE sample_coeff] [ARRAY JOIN ...] [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table US...
2022-08-04 clickhouse的select子句
技术之路
08-04 422
SELECT子句决定了一次查询语句最终返回哪些列字段或表达式。与直观的感受不同，虽然SELECT位于SQL语句的起始位置，但它却是在上述一众子句之后执行的。在其他子句执行之后，SELECT会将选取的字段或表达式作用于每行数据之上。............
sql select 多表关联 group by having order by limit举例
04-24
SQL中的SELECT语句可以用于从一个或多个表中检索数据，并且可以使用多表关联、GROUP BY、HAVING、ORDER BY和LIMIT等子句来进一步筛选和排序数据。下面是一个示例： 假设我们有两个表：学生表（students）和成绩表（scores）。学生表包含学生的信息，包括学生ID（student_id）、姓名（name）和年龄（age）。成绩表包含学生的成绩信息，包括学生ID（student_id）和成绩（score）。 我们想要查询每个学生的平均成绩，并按照平均成绩降序排列，只显示平均成绩大于80的学生信息。可以使用以下SQL语句实现： ``` SELECT students.student_id, students.name, AVG(scores.score) AS average_score FROM students JOIN scores ON students.student_id = scores.student_id GROUP BY students.student_id, students.name HAVING average_score > 80 ORDER BY average_score DESC LIMIT 10; ``` 上述SQL语句的含义如下： - 使用JOIN子句将学生表和成绩表进行关联，通过学生ID进行匹配。 - 使用GROUP BY子句按照学生ID和姓名进行分组。 - 使用AVG函数计算每个学生的平均成绩，并将其命名为average_score。 - 使用HAVING子句筛选出平均成绩大于80的学生。 - 使用ORDER BY子句按照平均成绩降序排列。 - 使用LIMIT子句限制结果集的数量为10条记录。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值