首页 > 专利学习

五ClickHouse查询语法

五ClickHouse查询语法5.1 with

ClickHouse⽀持CTE（Common Table Expression，公共表表达式），以增强查询语句的表达

SELECT pow(2, 2)

┌─pow(2, 2)─┐

│ 4 │

└───────────┘

SELECT pow(pow(2, 2), 2)

┌─pow(pow(2, 2), 2)─┐

│ 16 │

└───────────────────┘

在改⽤CTE的形式后，可以极⼤地提⾼语句的可读性和可维护性，\

with pow(2,2) as a select pow(a,3) ;

1) 定义变量

WITH

1 AS start,

10 AS end

SELECT

id + start,

FROM tb_mysql

┌─plus(id, start)─┬─id─┬─name─┬─age─┐

│ 2 │ 1 │ zss │ 23 │

│ 3 │ 2 │ lss │ 33 │

│ 4 │ 3 │ ww │ 44 │

│ 2 │ 1 │ zss │ 23 │

│ 3 │ 2 │ lss │ 33 │

│ 2 │ 1 │ zss │ 23 │

│ 3 │ 2 │ lss │ 33 │

└─────────────────┴────┴──────┴─────┘

2) 调⽤函数

SELECT *

FROM tb_partition

┌─id─┬─name─┬────────────birthday─┐

│ 1 │ xl │ 2021-05-20 10:50:46 │

│ 2 │ xy │ 2021-05-20 11:17:47 │

└────┴──────┴─────────────────────┘

┌─id─┬─name─┬────────────birthday─┐

│ 3 │ xf │ 2021-05-19 11:11:12 │

└────┴──────┴───────────---------─┘

WITH toDate(birthday) AS bday

SELECT

id,

name,

bday

FROM tb_partition

┌─id─┬─name─┬───────bday─┐

│ 1 │ xl │ 2021-05-20 │

│ 2 │ xy │ 2021-05-20 │

└────┴──────┴────────────┘

┌─id─┬─name─┬───────bday─┐

│ 3 │ xf │ 2021-05-19 │

└────┴──────┴────────────┘

3) ⼦查询

可以定义⼦查询，但是⼀定还要注意的是,⼦查询只能返回⼀⾏结果，否则会跑出异常

WITH

(

SELECT *

FROM tb_partition

WHERE id = 1

) AS sub

SELECT

sub

FROM tb_partition

┌─id─┬─name─┬────────────birthday─┬─sub────────────────────────────┐

│ 1 │ xl │ 2021-05-20 10:50:46 │ (1,'xl','2021-05-20 10:50:46') │

│ 2 │ xy │ 2021-05-20 11:17:47 │ (1,'xl','2021-05-20 10:50:46') │

└────┴──────┴─────────────────────┴────────────────────────────────┘

┌─id─┬─name─┬────────────birthday─┬─sub────────────────────────────┐

│ 3 │ xf │ 2021-05-19 11:11:12 │ (1,'xl','2021-05-20 10:50:46') │

5.2 from

SQL是⼀种⾯向集合的编程语⾔ ,from决定了程序从那⾥读取数据

1. 表中查询数据

2. ⼦查询中查询数据

3. 表函数中查询数据 select * from numbers(3) ;

表函数

构建表的函数 , 使⽤场景如下:

SELECT查询的[FROM)⼦句。

1 file

file(path, format, structure)

path — The relative path to the file from user_files_path. Path to file support following globs in readonly mode: *, ?, {abc,def} and {N..M} where N, M — numbers, `'abc', 'def' — strings. format — The format of the file.

structure — Structure of the table. Format 'column1_name column1_type, column2_name column2_type, ...'.　

数据⽂件必须在指定的⽬录下 /var/lib/clickhouse/user_files

SELECT *

FROM file('demo.csv', 'CSV', 'id Int8,name String , age UInt8')

-- ⽂件夹下任意的⽂件

SELECT *

FROM file('*', 'CSV', 'id Int8,name String , age UInt8')　

2 numbers

SELECT *

FROM numbers(10) ;

SELECT *

FROM numbers(2, 10) ;

SELECT *

FROM numbers(10) limit 3 ;

SELECT toDate('2020-01-01') + number AS d

FROM numbers(365)

3 mysql

CH可以直接从mysql服务中查询数据

mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

SELECT *

FROM mysql('linux01:3306', 'db_doit_ch', 'emp', 'root', 'root')

4 hdfs

SELECT *FROM hdfs('hdfs://hdfs1:9000/test', 'TSV', 'column1 UInt32, column2 UInt32, column3 UIn

t32')LIMIT 2

SELECT *

FROM hdfs('hdfs://linux01:8020/demo.csv', 'CSV', 'id Int8 ,name String , age Int8')

5.3 array join

ARRAY JOIN⼦句允许在数据表的内部，与数组或嵌套类型的字段进⾏JOIN操作，从⽽将⼀⾏数组展开为多⾏。类似于hive中的explode炸裂函数的功能! CREATE TABLE test_arrayjoin

(

`vs` Array(Int8)

)

ENGINE = Memory ;

insert into test_arrayjoin values('xw',[1,2,3]),('xl',[4,5]),('xk',[1]);

-- 将数组中的数据展开

SELECT

FROM test_arrayjoin

ARRAY JOIN vs AS s

┌─name─┬─vs──────┬─s─┐

│ xw │ [1,2,3] │ 1 │

│ xw │ [1,2,3] │ 2 │

│ xw │ [1,2,3] │ 3 │

│ xl │ [4,5] │ 4 │

│ xl │ [4,5] │ 5 │

│ xk │ [1] │ 1 │

└──────┴─────────┴───┘

-- arrayMap ⾼阶函数，对数组中的每个元素进⾏操作

SELECT

arrayMap(x->x*2 , vs) vs2智能娃娃机

FROM test_arrayjoin ;

SELECT

arrayMap(x -> (x * 2), vs) AS vs2

FROM test_arrayjoin

┌─name─┬─vs──────┬─vs2─────┐

│ xw │ [1,2,3] │ [2,4,6] │

│ xl │ [4,5] │ [8,10] │

│ xk │ [1] │ [2] │

└──────┴─────────┴─────────┘

SELECT

arrayMap(x -> (x * 2), vs) AS vs2 ,

vv1 ,

vv2

FROM test_arrayjoin

array join

vs as vv1 ,

vs2 as vv2 ;

┌─name─┬─vs──────┬─vs2─────┬─vv1─┬─vv2─┐

│ xw │ [1,2,3] │ [2,4,6] │ 1 │ 2 │

│ xw │ [1,2,3] │ [2,4,6] │ 2 │ 4 │

│ xw │ [1,2,3] │ [2,4,6] │ 3 │ 6 │

│ xl │ [4,5] │ [8,10] │ 4 │ 8 │

│ xl │ [4,5] │ [8,10] │ 5 │ 10 │

│ xk │ [1] │ [2] │ 1 │ 2 │

└──────┴─────────┴─────────┴─────┴─────┘

select

id ,

h ,

from

tb_array_join

array join

hobby as h ,

arrayEnumerate(hobby) as xx ;

┌─id─┬─h─────┬─xx─┐

│ 1 │ eat │ 1 │

│ 1 │ drink │ 2 │

生态养猪场

│ 1 │ sleep │ 3 │

│ 2 │ study │ 1 │

│ 2 │ sport │ 2 │

│ 2 │ read │ 3 │

└────┴───────┴────┘

┌─id─┬─h─────┬─xx─┐

│ 3 │ eat │ 1 │

│ 3 │ drink │ 2 │

案例

a,2017-02-05,200

a,2017-02-06,300

a,2017-02-07,200

a,2017-02-08,400

a,2017-02-08,300

a,2017-02-10,600

b,2017-02-05,200

b,2017-02-06,300

b,2017-02-08,200

b,2017-02-09,400

b,2017-02-10,600

c,2017-01-31,200

c,2017-02-01,300

c,2017-02-02,200

c,2017-02-03,400

c,2017-02-10,600

a,2017-03-01,200

a,2017-03-02,300

a,2017-03-03,200

a,2017-03-04,400

a,2017-03-05,600

drop table if exists tb_shop ;

自行葫芦CREATE TABLE tb_shop

(

`name` String,

`cdate` Date,

`cost` Float64

)engine=ReplacingMergeTree(cdate)

order by (name,cdate) ;

-- 导⼊数据

clickhouse-client -q 'insert into doit23.tb_shop format CSV' < ; ┌─name─┬──────cdate─┬─cost─┐

│ a │ 2017-02-05 │ 200 │

│ a │ 2017-02-06 │ 300 │

│ a │ 2017-02-07 │ 200 │

│ a │ 2017-02-08 │ 400 │

│ a │ 2017-02-10 │ 600 │

│ a │ 2017-03-01 │ 200 │

│ a │ 2017-03-04 │ 400 │

│ a │ 2017-03-05 │ 888 │

│ b │ 2017-02-05 │ 200 │

│ b │ 2017-02-06 │ 300 │

│ b │ 2017-02-08 │ 200 │

│ b │ 2017-02-09 │ 400 │

│ b │ 2017-02-10 │ 600 │

│ c │ 2017-01-31 │ 200 │

│ c │ 2017-02-01 │ 300 │

│ c │ 2017-02-02 │ 200 │

│ c │ 2017-02-03 │ 400 │

│ c │ 2017-02-10 │ 600 │

└──────┴────────────┴──────┘

select

name ,

groupArray(cdate) arr ,

arrayEnumerate(arr) as indexs

from

tb_shop

group by name;

┌─name─┬─arr─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─indexs─────────────────┐

│ b │ ['2017-02-05','2017-02-06','2017-02-08','2017-02-09','2017-02-10'] │ [1,2,3,4,5] │

│ c │ ['2017-01-31','2017-02-01','2017-02-02','2017-02-03','2017-02-10'] │ [1,2,3,4,5] │

│ a │ ['2017-02-05','2017-02-06','2017-02-07','2017-02-08','2017-02-10','2017-03-01','2017-03-02','2017-03-03','2017-03-04','2017-03-05'] │ [1,2,3,4,5,6,7,8,9,10] │

└──────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────┘select

name ,

dt - num

from

(select

name ,

groupArray(cdate) arr ,

arrayEnumerate(arr) as indexs

from

tb_shop

group by name

)

array join

arr as dt ,

indexs as num ;

┌─name─┬─minus(dt, num)─┐

│ b │ 2017-02-04 │

│ b │ 2017-02-05 │

│ c │ 2017-01-30 │

│ c │ 2017-02-05 │

│ a │ 2017-02-04 │

│ a │ 2017-02-05 │

│ a │ 2017-02-23 │

└──────┴────────────────┘

select

name ,

diff ,

count(1) cnt

from

(select

name ,

(dt - num) as diff

from

(select

name ,

groupArray(cdate) arr ,

arrayEnumerate(arr) as indexs

from

tb_shop

group by name

)

大米添加剂array join

arr as dt ,

indexs as num

)

group by name , diff;

┌─name─┬───────diff─┬─count(1)─┐

│ b │ 2017-02-04 │ 2 │

│ a │ 2017-02-23 │ 5 │

│ c │ 2017-01-30 │ 4 │

│ c │ 2017-02-05 │ 1 │

│ a │ 2017-02-04 │ 4 │

│ b │ 2017-02-05 │ 3 │

│ a │ 2017-02-05 │ 1 │

└──────┴────────────┴──────────┘

select

name ,

diff ,

count(1) cnt

from

(select

name ,

(dt - num) as diff

from

(select

name ,

groupArray(cdate) arr ,

arrayEnumerate(arr) as indexs

from

tb_shop

group by name

)

indexs as num

)

group by name , diff

order by cnt desc

limit 1 by name ;

睡眠袜┌─name─┬───────diff─┬─cnt─┐

│ a │ 2017-02-23 │ 5 │

│ c │ 2017-01-30 │ 4 │

│ b │ 2017-02-05 │ 3 │

└──────┴────────────┴─────┘

5.4 关联查询

所有标准⽀持类型:

INNER JOIN, only matching rows are returned.

LEFT OUTER JOIN, non-matching rows from left table are returned in addition to matching rows.

RIGHT OUTER JOIN, non-matching rows from right table are returned in addition to matching rows.

FULL OUTER JOIN, non-matching rows from both tables are returned in addition to matching rows.

CROSS JOIN, produces cartesian product of whole tables, “join keys” are not specified.

JOIN⼦句可以对左右两张表的数据进⾏连接，这是最常⽤的查询⼦句之⼀。它的语法包含连接精度和连接类型两部分。

连接精度

连接精度决定了JOIN查询在连接数据时所使⽤的策略，⽬前⽀持ALL、ANY和ASOF三种类型。如果

不主动声明，则默认是ALL。可以通过join_default_strictness配置参数修改默认的连接精度类型。

对数据是否连接匹配的判断是通过JOIN KEY进⾏的，⽬前只⽀持等式（EQUAL JOIN）。交叉连接（CROSS JOIN）不需要使⽤JOIN KEY，因为它会产⽣笛卡⼉积。

-- 准备数据

drop table if exists yg ;

create table yg(

id Int8 ,

name String ,

age UInt8 ,

bid Int8

)engine=Log ;

压电陶瓷超声换能器insert into yg values(1,'AA',23,1) ,

(2,'BB',24,2) ,

(3,'VV',27,1) ,

(4,'CC',13,3) ,

(5,'KK',53,3) ,

(6,'MM',33,3) ;

drop table if exists bm ;

create table bm(

bid Int8 ,

name String

)engine=Log ;

insert into bm values(1,'x'),(2,'Y'),(3,'Z');

drop table if exists gz ;

drop table gz ;

create table gz(

id Int8 ,

jb Int64 ,

jj Int64

)engine=Log ;

insert into gz values (1,1000,2000),(1,1000,2000),(2,2000,1233),(3,2000,3000),(4,4000,1000),(5,5000,2000);

1)all

如果左表内的⼀⾏数据，在右表中有多⾏数据与之连接匹配，则返回右表中全部连接的数据。⽽判断连接匹配的依据是左表与右表内的数据，基于连接键（JOIN KEY）的取值完全相等（equal），等同

于 left.key=right.key。

SELECT *

FROM yg AS inser

ALL INNER JOIN gz ON yg.id = gz.id ;

SELECT *

FROM yg AS inser

ALL JOIN gz ON yg.id = gz.id ;

SELECT *

FROM yg AS inser

JOIN gz ON yg.id = gz.id ;

┌─id─┬─name─┬─age─┬─bid─┬─gz.id─┬───jb─┬───jj─┐

│ 1 │ AA │ 23 │ 1 │ 1 │ 1000 │ 2000 │

│ 2 │ BB │ 24 │ 2 │ 2 │ 2000 │ 1233 │

│ 3 │ VV │ 27 │ 1 │ 3 │ 2000 │ 3000 │

│ 4 │ CC │ 13 │ 3 │ 4 │ 4000 │ 1000 │

本文发布于:2024-09-22 05:27:54，感谢您对本站的认可！

本文链接：https://www.17tex.com/tex/1/167913.html

上一篇：转：查表法转换YV12到RGB24的例子（附代码）

下一篇：计算水力学代码

标签：连接数据查询精度类型

留言与评论（共有 0 条评论）