clickhouse - select语句/select子句/inot outfile/with/prewhere/sample

 

1.SELECT语句语法

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename]
[FORMAT format]

2.with子句

示例1：使用常量表达式作为“变量”

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：从SELECT子句列列表中逐出一个sum（bytes）表达式结果

WITH sum(bytes) as s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

示例3：使用标量子查询的结果

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

示例4：在子查询中重用表达式

WITH test1 AS (SELECT i + 1, j + 1 FROM test1) 
SELECT * FROM test1;

3.sample子句

(1) sample k 
k表示因子系数，采样因子，取值范围[0,1]，若在0--1之间的小数则表示采样，若为0或者1则等同于不采样。
select CounterID from clicks sample 0.1
等同于：
select CounterID from clicks sample 1/10

(2)sample n
n表示采样的样本数量。n表示至少采样多少行数据。n=1表示不使用采样，n的范围从2到表的总行数。
select count() from clicks sample 10000;

(3)sample k offset n
表示按照因子系数和偏移量采样。
select CounterID,_sample_factor from clicks sample 0.4 offset 0.5 limit 1; -- 读取后半部分数据,从中抽样40%,结果只显示出1条
offset 0.5表示查询从数据序号偏移一半开始
sample 0.4表示采样40%的数据 
limit 1 结果只显示一条

 

4.join子句

所有标准 SQL JOIN 支持类型:

• INNER JOIN，只返回匹配的行。
• LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
• RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
• FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
• CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。

ClickHouse中提供的其他联接类型:

• LEFT SEMI JOIN 和 RIGHT SEMI JOIN,白名单 “join keys”，而不产生笛卡尔积。
• LEFT ANTI JOIN 和 RIGHT ANTI JOIN，黑名单 “join keys”，而不产生笛卡尔积。
• LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN, partially (for opposite side of LEFT and RIGHT) or completely (for INNER and FULL) disables the cartesian product for standard JOIN types.
• ASOF JOIN and LEFT ASOF JOIN, joining sequences with a non-exact match. 
   

使用SEMI LEFT JOIN时，使用右表中存在的key去过滤左表中的key，如果左表存在与右表相同的key，则输出。
使用SEMI RIGHT JOIN时，使用左表中存在的key去过滤右表中的key，如果右表中存在与左表相同的key，则输出。
换句话说，SEMI JOIN返回key在另外一个表中存在的记录行。
ANTI JOIN和SEMI JOIN相反，他返回的是key在另外一个表中不存在的记录行。
SEMI JOIN和ANTI JOIN都允许从两个表中获取数据。对于被过滤的表，返回的是与key相同的记录行。对于ANTI JOIN，另外一个表返回的是默认值，比如空值或0。

 

5.into outfile子句

实现导出数据至本地文件

样例：SELECT * FROM test.table_name INTO OUTFILE '/data/t_city.csv' FORMAT CSVWithNames;

• 只能在  command-line client and clickhouse-local 情景下使用，默认输出格式为TabSeparated
• 通出 format 命令可指定输出格式

 

6.PREWHERE子句

prewhere 目前只适用于*MergeTree系列的表引擎，可以看做是对where的一种优化，和where语句的作用相同，用来过滤数据。
不同之处在于prewhere首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select 声明的列字段来补全其余属性。
在某些场合下，prewhere语句比where语句处理的数据量更少性能更高。

样例:  select * from test.table_name prewhere col1='us';

不能自动优化情景：

• 使用常量表达式
• 使用默认值为alias类型的字段
• 包含了arrayJOIN，globalIn，globalNotIn或者indexHint的查询
• select查询的列字段和where的谓词相同
• 使用了主键字段

 

7.union子句

包括union all / union distinct / union 3命令格式，union all对结果不去重，union distinct对结果去重; 

当只用 union 命令时，到底实现union all 或是 union distinct，由union_default_mode 决定，SET union_default_mode = 'DISTINCT' 模式

 

8.limit子句 

limit  m   ： 显示前m条数据

limit n , m : 相当  LIMIT m OFFSET n , 即从n+1条数据开始，共返回m条

 

9.limit by子句

两种格式：

• limit m by expressions 
• limit n,m by expressions

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;  -- 每个id只显示前两条数据

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id; --每个id跳过第1条数据，显示第2,3条数据

以下查询返回每domain, device_type对的前5个引荐来源网址，总共最多包含100行（LIMIT n BY + LIMIT）。

SELECT
    domainWithoutWWW(URL) AS domain,
    domainWithoutWWW(REFERRER_URL) AS referrer,
    device_type,
    count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100

 

10.order by 

NaN和NULL排序顺序：

• 默认情况下或使用NULLS LAST修饰符：首先是值，然后是NaN，然后是NULL。

• 使用NULLS FIRST修饰符：首先NULL，然后是NaN，然后是其他值。

对于按字符串值排序，可以指定排序规则（比较）。示例：ORDER BY SearchPhrase COLLATE 'tr'-假设字符串是UTF-8编码的，则使用土耳其字母按升序按关键字排序，不区分大小写。

COLLATE可以为ORDER BY中的每个表达式分别指定或不指定。如果指定ASC或DESC，COLLATE则在其后指定。使用时COLLATE，排序始终不区分大小写

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';