clickHouse之SQL语法之select（—）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 USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list]
select distinct from
[LIMIT [n, ]m]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT n BY columns]
from⼦句
1、从哪个表、⼦查询（构建在外部查询的内部，外部没有⽤到的列将被忽略）、表函数取数据
2、查询中未列出如何的列select count() from test，将额外从表中取些列以计算⾏数
3、final使查询结果在查询过程中被聚合，仅能⽤在SELECT from CollapsingMergeTree中
在单个流中读取所有相关的主键列，合并需要的数据（查询变慢，避免使⽤）
sample⼦句
1、近似查询，近⼯作在MergeTree*类型的表中，且建表时指定采样表达式
2、sample⼦句可⽤sample k来表⽰，k可以是0-1的⼩数值或⼀个⾜够⼤的正整数
k为⼩数时，查询将使⽤k做百分⽐选取数据，sample 0.1只检索数据总量的10%
k⾜够⼤的正整数，k为最⼤样本数，sample 10000000检索最多10000000⾏数据
取数据总量的0.1 （10%）的数据，查不会⾃动校正聚合函数最终结果，为更精确结果，count()*10
SELECT
Title,
count() * 10 AS PageViews
FROM hits_distributed
SAMPLE 0.1
WHERE
CounterID = 34
AND toDate(EventDate) >= toDate('2013-01-29')
AND toDate(EventDate) <= toDate('2013-02-04')
AND NOT DontCountHits
AND NOT Refresh
AND Title != ''
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000
3、相同采样率（建表使⽤相同采⽤表达式）得到结果⼀致
array join⼦句
1、等同于innert join；使查询与数组和nested类型连接，类似array join函数
2、⼀个查询只能出现⼀个array join，where/prewhere使⽤array join⼦句，其将优先于where/prewhere⼦句执⾏，否则将在where/prewhere⼦句之后执⾏
SELECT * FROM arrays_test
┌─s───────┬─arr─────┐
│ Hello  │ [1,2]  │
│ World  │ [3,4,5] │
│ Goodbye │ []      │
SELECT s, arr FROM arrays_test ARRAY JOIN arr
┌─s─────┬─arr─┐
│ Hello │  1 │
│ Hello │  2 │
│ World │  3 │
│ World │  4 │
│ World │  5 │
SELECT s, arr, a FROM arrays_test ARRAY JOIN arr AS a
┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2]  │ 1 │
│ Hello │ [1,2]  │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
SELECT s, arr, a, num, mapped FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(lambda(tuple(x), plus(x, 1)), arr) AS mapped
┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2]  │ 1 │  1 │      2 │
│ Hello │ [1,2]  │ 2 │  2 │      3 │
│ World │ [3,4,5] │ 3 │  1 │      4 │
│ World │ [3,4,5] │ 4 │  2 │      5 │
│ World │ [3,4,5] │ 5 │  3 │      6 │
SELECT s, arr, a, num, arrayEnumerate(arr) FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num
┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2]  │ 1 │  1 │ [1,2]              │
│ Hello │ [1,2]  │ 2 │  2 │ [1,2]              │
│ World │ [3,4,5] │ 3 │  1 │ [1,2,3]            │
│ World │ [3,4,5] │ 4 │  2 │ [1,2,3]            │
│ World │ [3,4,5] │ 5 │  3 │ [1,2,3]            │
join⼦句
SELECT <expr_list>
FROM <left_subquery>
[GLOBAL] [ANY|ALL] INNER|LEFT|RIGHT|FULL|CROSS [OUTER] JOIN <right_subquery>
(ON <expr_list>)|(USING <column_list>) ...
any与all
all：同SQL的JOIN，右表存在多个与左表关联的数据
any：右表存在多个与左关联的数据，仅返回第⼀各与左表匹配的数据
global distribution
普通join，查询发送给远程服务器，并在其上⽣成右表与之关联（右表来⾃服务器）
使⽤global join，在请求服务器上计算右表并⼀临时表的⽅式将其发送到所有服务器，每台服务器使⽤它计算
从⼦查询中删除所有join不需要的列
执⾏时⽆进⾏执⾏顺序的优化：join先于where与聚合执⾏，为显⽰指定执⾏顺序，使⽤⼦查询⽅式执⾏join
⼦查询
SELECT
CounterID,
hits,
visits
FROM
(
SELECT
CounterID,
count() AS hits
FROM test.hits
GROUP BY CounterID
) ANY LEFT JOIN
(
SELECT
CounterID,
sum(Sign) AS visits
FROM test.visits
GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10
┌─CounterID─┬───hits─┬─visits─┐
│  1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│  2237260 │ 196036 │  9522 │
│  23057320 │ 147211 │  7689 │
│    722818 │  90109 │  17847 │
│    48221 │  85379 │  4652 │
│  19762435 │  77807 │  7026 │
│    722884 │  77492 │  11056 │
⼦查询不允许设置别名或在其他地⽅引⽤， USING中指定的列必须在两个⼦查询中具有相同的名称。可通过使⽤别名来更改⼦查询的列名（⽰例中就分别使⽤了'hits'与'visits'别名）
USING⼦句指定要进⾏链接的⼀或多个列，系统将列在两表中相等的值连接起来。如果列是⼀个列表，不需要使⽤括号包裹。同时JOIN 不⽀持其他更复杂的Join⽅式，只能在查询中指定⼀个JOIN；若运⾏多个JOIN，你可将它们放⼊⼦查询中。
右表（⼦查询的结果）将会保存在内存中。如果没有⾜够的内存，则⽆法运⾏JOIN；每次运⾏相同的
JOIN查询，会再次计算， 为避免这情况，可⽤‘Join’引擎，⼀个预处理的Join数据结构，总是保存在内存中。更多信息，参见“Join引擎”部分。
在各种类型的JOIN中，最⾼效的是ANY LEFT JOIN，然后是ANY INNER JOIN，效率最差的是ALL LEFT JOIN以及ALL INNER JOIN。
如果你需要使⽤JOIN来关联⼀些纬度表（包含纬度属性的⼀些相对⽐较⼩的表，例如⼴告活动的名称），那么JOIN可能不是好的选择，因为语法负责，并且每次查询都将重新访问这些表。对于这种情况，您应该使⽤“外部字典”的功能来替换JOIN。更多信息，参见  部分。
null的处理
1、JOIN的⾏为受  的影响。当join_use_nulls=1时，JOIN的⼯作与SQL标准相同。
2、如果JOIN的key是  类型的字段，则⾄少⼀个存在  值的key不会被关联。
where⼦句
该⼦句中须包含⼀个UInt8类型的表达式：通常是⼀个带有⽐较和逻辑的表达式：在所有数据转换前⽤来过滤数据；如果在⽀持索引的数据库表引擎中，这个表达式将被评估是否使⽤索引。
PREWHERE ⼦句
与WHERE⼦句的意思相同。主要不同在于表数据的读取；PREWHERE 仅⽀持*MergeTree系列引擎
使⽤PREWHERE，只读取PREWHERE表达式中需要的列，根据PREWHERE执⾏的结果读取其他需要的列。
如在过滤条件中有少量不适合索引过滤的列，但它们⼜可提供很强的过滤能⼒，使⽤PREWHERE很有意义，帮助减少数据的读取。        例如，在⼀个需要提取⼤量列的查询中为少部分列编写PREWHERE是很有作⽤的。
1、在⼀个查询中可以同时指定PREWHERE和WHERE，在这种情况下，PREWHERE优先于WHERE执⾏。
2、PREWHERE不适合⽤于已经存在于索引中的列：列已经存在于索引中，只有满⾜索引的数据块才会被读取。
3、如将'optimize_move_to_prewhere'设置为1，并且在查询中不包含PREWHERE，则系统将⾃动的把适合PREWHERE表达式的部分从WHERE中抽离到PREWHERE中。
group by
必含⼀个表达式列表，每个表达式将被称之为“key”。 SELECT，HAVING，ORDER BY⼦句中的表达式列表必须来⾃于这
些“key”或聚合函数。被选择的列中不能包含⾮聚合函数或key之外的其他列。
如查询表达式中仅含聚合函数，则可省略GROUP BY，这时会假定将所有数据聚合成⼀组空“key”。
SELECT
count(),
median(FetchTiming > 60 ? 60 : FetchTiming),
count() - sum(Refresh)
FROM hits
1、与SQL标准不同，如表中不存在任何数据（表不存在任何数据，或被WHERE过滤掉了），将返回⼀个空结果，⽽不是⼀个包含聚合函数初始值的结果。
2、与MySQL不同（实际上这是符合SQL标准的），不能获得⼀个不在key中的⾮聚合函数列（除了常
量表达式），但可使⽤‘any’（返回遇到的第⼀个值）、max、min等聚合函数使它⼯作
ELECT
domainWithoutWWW(URL) AS domain,
count(),
any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain
GROUP BY⼦句会为遇到的每⼀个不同的key计算⼀组聚合函数的值
在GROUP BY⼦句中不使⽤Array类型的列
常量不能作为聚合函数的参数传⼊聚合函数中，如sum(1)，这种情况下你可以省略常量：count()
┌─x─┬────y─┐
│ 1 │    2 │
│ 2 │ᴺᵁᴸᴸ│
│ 3 │    2 │
│ 3 │    3 │
│ 3 │ᴺᵁᴸᴸ│
└───┴──────┘┌─sum(x)─┬────y─┐│      4 │    2 │
│      3 │    3 │
│      5 │ᴺᵁᴸᴸ│
└────────┴──────┘
再向GROUP BY中放⼊⼏个key，结果将列出所有的组合可能SELECT sum(x), y FROM t_null_big GROUP BY y
null处理
对于GROUP BY⼦句，ClickHouse将  解释为⼀个值，并且⽀持NULL=NULL
WITH TOTALS 修饰符
将会在结果中得到⼀个被额外计算出的⾏，这⼀⾏中将包含所有key的默认值（零或者空值），以及所有聚合函数对所有被选择数据⾏的聚合结果；该⾏仅在JSON*, TabSeparated*, Pretty*输出格式中与其他⾏分开输出。
在JSON*输出格式中，这⾏将出现在Json的‘totals’字段中。在TabSeparated*输出格式中，这⾏将位于其他结果之后，同时与其他结果使⽤空⽩⾏分隔。在Pretty*输出格式中，这⾏将作为单独的表在所有结果之后输出。
当WITH TOTALS与HAVING⼦句同时存在，它的⾏为受‘totals_mode’配置的影响。 默认情况下，totals_mode =
'before_having'，这时WITH TOTALS将会在HAVING前计算最多不超过max_rows_to_group_by⾏的数据。
在group_by_overflow_mode = 'any'并指定了max_rows_to_group_by的情况下，WITH TOTALS的⾏为受totals_mode的影响。
after_having_exclusive - 在HAVING后进⾏计算，计算不超过max_rows_to_group_by⾏的数据。
after_having_inclusive - 在HAVING后进⾏计算，计算不少于max_rows_to_group_by⾏的数据。
after_having_auto - 在HAVING后进⾏计算，采⽤统计通过HAVING的⾏数，在超过不超过‘max_rows_to_group_by’指定值（默认为50%）的情况下，包含所有⾏的结果。否则排除这些结果。
totals_auto_threshold - 默认 0.5，是after_having_auto的参数。
如果group_by_overflow_mode != 'any'并没有指定max_rows_to_group_by情况下，所有的模式都与after_having相同。
你可以在⼦查询，包含⼦查询的JOIN⼦句中使⽤WITH TOTALS（在这种情况下，它们各⾃的总值会被组合在⼀起）
GROUP BY 使⽤外部存储设备
你可以在GROUP BY中允许将临时数据转存到磁盘上，以限制对内存的使⽤。 max_bytes_before_external_group_by这个配置确定了

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