clickHouse之SQL语法之select（⼆）
limit n by
可含任意多个分组字段表达式列表，与limit⽆关系，limit n by columns可⽤在每个columns分组中求最⼤的n⾏数据
为每个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
having
过滤group by后是数据，类似where；where在聚合前执⾏，having聚合后执⾏，不存在聚合不能使⽤having
order by
如存在order by，则该⼦句中须存在⼀个表达式列表，列表中每个表达式都可分配desc或asc，默认asc
1、字符串在指定排序规则时，不区分⼤⼩写
2、排序中相同值的⾏以任意顺序输出；省略order by，结果的顺序也是不固定的
3、collate效率低于正常排序，少量使⽤
NaN和NULL：
使⽤nulls first，先输出null，然后nan，最后其他值
使⽤nulls last，先输出其他值，nan，null（默认）
SELECT * FROM t_null_nan ORDER BY y NULLS FIRST:
┌─x─┬────y─┐
│ 1 │ᴺᵁᴸᴸ│
│ 7 │ᴺᵁᴸᴸ│
│ 1 │  nan │
│ 6 │  nan │
│ 2 │    2 │
│ 2 │    2 │
│ 3 │    4 │
│ 5 │    6 │
│ 6 │    7 │
│ 8 │    9 │
4、浮点类型，nan总出现all后⾯
5、order by⼦句后limit较⼩数值，将使⽤较少内存，否则内存使⽤量将于需排序的数据成正⽐
分布式查询：省略了group by，在远程服务器上执⾏部分排序，最后再请求服务器上合并排序结果，排序数据量可以⼤于单台服务器内存
6、⽆⾜够内存，可使⽤外部排序（磁盘建临时⽂件，效率低）max_bytes_before_external_sort设置外部排序，=0禁⽤、启⽤该功能，要排序的数据量达到所指定的字节数时 当前排序结果被转存到临时⽂件中，全部数据读取完毕后，所有临时⽂件被合并成最后结果，临时⽂件在config⽂件配置的/var/lib/clickhouse/tmp/⽬录中，修改tmp_path调整
7、查询运⾏使⽤的内存要⾼于max_bytes_before_external_sort，为此这个配置须远⼩于max_memory_usage配置的值
select⼦句：
存在聚合函数：对聚合函数之前的表达式进⾏分析
聚合函数与聚合函数前的表达式都将在聚合期间完成计算
distinct⼦句
完全相同的⾏去重，在GROUP BY不包含聚合函数，并对全部SELECT部分都包含在GROUP BY中时的作⽤⼀样？？
不存在order by⼦句并存在limit时，查询将在同时满⾜distinct 与limit情况下⽴即发下SQL，停⽌查询
处理数据同时输出结果
select表达式中存在array类型的列时，不能使⽤distinct
distinct可与null⼀起⼯作，与null不同组合仅能出现⼀次
limit
limit m结果中选择前m⾏数据，limit n，m查结果中选择n-m⾏数据
未指定order by，结果可能任意的顺序，且不确定
union all
ELECT CounterID, 1 AS table, toInt64(count()) AS c
FROM test.hits
GROUP BY CounterID
UNION ALL
SELECT CounterID, 2 AS table, sum(Sign) AS c
FROM test.visits
GROUP BY CounterID
HAVING c > 0
1、仅⽀持union all，如需union distinct，可使⽤union all中包含select distinct的⼦查询⽅式
2、union all中查询可同时运⾏，他们的结果混合在⼀起
3、查询结果：列数量和类型须相同，列名可不同：最终的列名将从第⼀个查询获取，union会为查询间进⾏类型转换
4、union all查询的部分不能包含在括号内，order by与limit⼦句应该被应⽤在每个查询中，如需要做最终结果转换，可将union all作为⼦查询含在from⼦句中
into outfile⼦句
将查询结果重定向输出到指定⽂件中，与mysql不同，执⾏结果将在客户端建⽴，如⽂件存在则查询将失败，⼯作在命令⾏客户端与clickhouse-local中，默认输出格式是tabseparated
format
指定返回数据的格式，可使⽤它⽅便的转换或创建数据的转储
不存在format⼦句，使⽤默认格式：取决与DB配置及所使⽤的客户端
批量模式的http客户端和命令⾏，默认格式tabseparated；交互模式下命令⾏客户端，默认格式是prettycompact
使⽤命令⾏，数据以内部⾼效的格式在服务器与客户端间传递，客户端将单独解析format，助格式转换
in
对应in 、not in、global in global not in操作符被分别实现，
SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...
如左侧是单列且是⼀个索引，且右是⼀组常量时，系统使⽤索引来处理查询
不⽤在列表列太多值，数据集很⼤，将他们放⼊临时表，再使⽤⼦查询
SELECT
EventDate,sort命令排序
avg(UserID IN
(
SELECT UserID
FROM test.hits
WHERE EventDate = toDate('2014-03-17')
)) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC
null处理
in操作符总是假定null值的操作结果=0，null值不应该被包含在数据集中：彼此不对应、不能⽐较
┌─x─┬────y─┐│ 1 │ᴺᵁᴸᴸ│
│ 2 │    3 │
└───┴──────┘┌─x─┐│ 2 │└───┘
SELECT x FROM t_null WHERE y IN (NULL,3)不确定null是否包含在（null,3）返回0，结果集中排除该⾏
分布式⼦查询带有⼦查询的in
1、普通in，查询发送到远程服务器，且每个服务器中运⾏in或join⼦句的⼦查询
2、global in、global join时，先为global in、global join运⾏所有⼦查询，将结果收到临时表且将表发给每个服务器，使⽤临时表运⾏查
询
⾮分布式查询，使⽤普通的in、join
假设集中每个服务器都存在⼀个正常表local_table，与分布式表distributed_table
对distributed_table查询，查询被送到服务器且使⽤local_table运⾏查询
SELECT uniq(UserID) FROM distributed_table
发送如下查询到服务器
SELECT uniq(UserID) FROM local_table
并⾏执⾏，直到达到可组合数据的中间结果状态，然后中间结果将返回到服务器且在合并，最终将结果发送给客户端
2、SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34) SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
in⼦句中的数据集将被每台服务器独⽴收集，仅与每台服务器上的本地存储上的数据计算交集，未将数据分散到每台服务器上，结果不准确
改为：
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34) SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
⼦查询将在每台服务器上执⾏，因为⼦查询是分布式表，so每个服务器上的⼦查询将查询再次发送给所有的远程服务器
SELECT UserID FROM local_table WHERE CounterID = 34
台多，使⽤global
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE Counter 服务器上运⾏⼦查询：
SELECT UserID FROM distributed_table WHERE CounterID = 34
将结果放⼊内存中临时表中，然后将请求发送到每个服务器（临时表也随着⼀起发送）
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1
注意：
1、创建临时表，在⼦查询中使⽤distinct
2、临时表将发送到服务器，传输不考虑⽹络拓扑结构
3、向服务器发送数据，⽹络带宽限制不可配置，对⽹络负载造成压⼒
4、尝试将数据跨服务器分布，将不需要⽤global in
5、如经常使⽤global all，请规划你的clickhouse集位置，以便副本间不跨数据中⼼，且具有快速⽹络交换能⼒：查询可完全在⼀个数据中⼼完成
extreme values
将extremes=1，获得结果列最值（数字、⽇期类型进⾏计算，其他类型输出默认值）
额外计算的最值：两⾏数据仅在JSON*, TabSeparated*, and Pretty* 格式与其他⾏分开出输出，不⽀
持其他输出格式
json*格式，extreme值在单独的extremes中
在tabseparated*格式，在其他结果与totals后输出，且使⽤空⾏与其分隔
在pretty*中，与其他结果与totals后以单独表格输出
在计算extreme值同时使⽤了limit，extreme计算结果将含offset跳过的⾏
流式请求中，可能还含多余limit的少量⾏的值
注意：
group by与order by不⽀持使⽤列位置信息作为参数，group by 1,2 ==按照常量进⾏分组：all⾏聚合成⼀⾏可在查询的任何部分使⽤AS
任何部分添加*（不仅仅是表达式），分析查询时*被替换为all的列（不含materialized 与 alias列）
不推荐使⽤，如下可以使⽤：合理
创建表存储时、对于仅包含⼏个列的表、获取表中韩列信息（应⽤limit 1 更好的是desc table）
使⽤prewhere在少数⼏个列上做强过滤、⼦查询中（外部查询不需要的列被排除在⼦查询外）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。