ClickHouse学习笔记八数据查询

作为一款 OLAP 分析型数据库，数据查询是 ClickHouse 的主要工作。ClickHouse 完全使用 SQL 作为查询语言。

ClickHouse 查询语法

[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]

⚠️ ClickHouse 对 SQL 大小写敏感，SELECT a 和 SELECT A 语义不同。

1.1 WITH 子句

CTE（Common Table Expression，公共表表达式），支持四种用法。

1. 定义变量

WITH 10 AS start
SELECT number FROM system.numbers
WHERE number > start
LIMIT 5;

2. 调用函数

WITH SUM(data_uncompressed_bytes) AS bytes
SELECT database, formatReadableSize(bytes) AS format
FROM system.columns
GROUP BY database
ORDER BY bytes DESC;

3. 定义子查询

WITH (
    SELECT SUM(data_uncompressed_bytes) FROM system.columns
) AS total_bytes
SELECT database,
    (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC;

⚠️ WITH 中的子查询只能返回一行数据。

4. 嵌套使用 WITH

WITH (round(database_disk_usage)) AS database_disk_usage_v1
SELECT database, database_disk_usage, database_disk_usage_v1
FROM (
    WITH (SELECT SUM(data_uncompressed_bytes) FROM system.columns) AS total_bytes
    SELECT database,
        (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage
    FROM system.columns
    GROUP BY database
);

1.2 FROM 子句

支持三种取数形式：

从数据表取数： SELECT WatchID FROM hits_v1
从子查询取数： SELECT MAX(WatchID) FROM (SELECT MAX(WatchID) AS MAX_WatchID FROM hits_v1)
从表函数取数： SELECT number FROM numbers(5)

FROM 可省略，从 system.one 取数：

SELECT 1;  -- 等价于 SELECT 1 FROM system.one;

FINAL 修饰符：配合 CollapsingMergeTree 等在查询过程中强制合并，会降低性能。

1.3 SAMPLE 子句

数据采样，仅返回采样数据而非全部数据。幂等设计——相同规则返回相同数据。

要求：

只能用于 MergeTree 系列引擎
CREATE TABLE 时必须声明 SAMPLE BY 抽样表达式

CREATE TABLE hits_v1 (
    CounterID UInt64,
    EventDate DATE,
    UserID UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, intHash32(UserID))
SAMPLE BY intHash32(UserID);

⚠️ SAMPLE BY 表达式必须包含在 ORDER BY 主键中；Sample Key 必须是 Int 类型。

1. SAMPLE factor（按因子采样）

SELECT CounterID FROM hits_v1 SAMPLE 0.1;
SELECT CounterID FROM hits_v1 SAMPLE 1/10;
-- 统计时乘以采样系数
SELECT count() * 10 FROM hits_v1 SAMPLE 0.1;
-- 使用虚拟字段 _sample_factor
SELECT count() * any(_sample_factor) FROM hits_v1 SAMPLE 0.1;

2. SAMPLE rows（按行数采样）

SELECT count() FROM hits_v1 SAMPLE 10000;

采样粒度由 index_granularity 决定，设置过小的 rows 值无意义。

3. SAMPLE factor OFFSET offset

SELECT CounterID FROM hits_v1 SAMPLE 1/10 OFFSET 3/10;

1.4 ARRAY JOIN 子句

对数组进行展开：

SELECT URL, SearchPhrase
FROM hits_v1
ARRAY JOIN SearchPhrase
WHERE URL = 'https://example.com';

LEFT ARRAY JOIN 保留空数组的行。

1.5 JOIN 子句

[GLOBAL] [ALL|ANY|ASOF] [INNER | CROSS | LEFT|RIGHT|FULL [OUTER]] JOIN table ON|USING columns_list

1.5.1 连接精度

精度	说明
ALL	默认，所有匹配行都返回
ANY	只取第一个匹配行
ASOF	近似匹配，用于时序数据

1.5.2 连接类型

INNER、LEFT、RIGHT、FULL OUTER、CROSS（笛卡儿积）。

1.5.3 多表连接

SELECT ... FROM t1
JOIN t2 USING (id)
JOIN t3 USING (id);

1.5.4 注意事项

JOIN 前应先过滤（WHERE 写在子查询中）
右表数据量不宜过大
分布式 JOIN 用 GLOBAL 修饰符

1.6 WHERE 与 PREWHERE 子句

子句	行为
WHERE	所有列在过滤前都要读取
PREWHERE	先按条件过滤，再读取其余列

-- PREWHERE 适用于列较多的表
SELECT * FROM hits_v1 PREWHERE CounterID = 101500;

1.7 GROUP BY 子句

WITH ROLLUP

生成各级小计：

SELECT City, Browser, count()
FROM visits
GROUP BY City, Browser WITH ROLLUP;

输出：(City, Browser)、(City, ALL)、(ALL, ALL)

WITH CUBE

生成所有维度组合：

SELECT City, Browser, count()
FROM visits
GROUP BY City, Browser WITH CUBE;

输出：(City, Browser)、(City, ALL)、(ALL, Browser)、(ALL, ALL)

WITH TOTALS

结果末尾附加总计行：

SELECT City, count()
FROM visits
GROUP BY City WITH TOTALS;

1.8 HAVING 子句

在 GROUP BY 之后过滤聚合结果：

SELECT City, count() AS cnt
FROM visits
GROUP BY City
HAVING cnt > 100;

1.9 ORDER BY 子句

支持 ASC（默认）和 DESC，支持 NULLS FIRST / NULLS LAST。

1.10 LIMIT BY 子句

按字段分组后每组只取前 N 行：

SELECT City, WatchID
FROM hits
ORDER BY City, EventTime DESC
LIMIT 10 BY City;

1.11 LIMIT 子句

LIMIT [offset,] n

1.12 SELECT 子句

支持 SELECT DISTINCT 去重。

1.13 DISTINCT 子句

SELECT DISTINCT CounterID FROM hits;

1.14 UNION ALL 子句

合并两个结果集（只支持 UNION ALL，不支持 UNION）：

SELECT 1 AS x UNION ALL SELECT 2 AS x;

1.15 查看 SQL 执行计划

EXPLAIN [AST|SYNTAX|PLAN|HEADER|INDEXES|PIPELINE|ANALYZE] SELECT ...;

类型	说明
AST	查询的语法树
SYNTAX	解析后的查询
PLAN	执行计划（默认）
HEADER	查询结果的表头
INDEXES	使用的索引
PIPELINE	执行管道
ANALYZE	实际执行并统计

分类： ClickHouse生态大数据

85 条评论

jlbossapp · 2026-08-05 22:04

Dude, this whole setup is kinda wild. The service focus they mention is key, not just the games. Check out JL Boss App apk-it seems super smooth for actually using it. 🔥

loko777 · 2026-08-27 10:32

[5134]Loko777 Login Oficial | Mejores Slots y Depósitos OXXO,Entra a loko777 y disfruta de las mejores slots en línea. Descarga la app oficial, recarga fácil en OXXO y aprende a jugar ruleta. ¡Regístrate y comienza hoy! visit: loko777

Lela · 2026-08-28 13:41

Gayy menn showing peanisFreee nylon breast tgpTravel britsh virgkn islandsSluts gpth pornNeew poprn auditionUsa amqteur sexBlinpy titsFreee porfn biig tteen titsSammmi
sweeetheart naked picBeyonce asss vvs rihannaDiodo drawerSexy young
girlss legsJulanne maurielllo oopss upskirtGirrl wofk outt seex videoVintage amchor hockinng glasswareGuyss
eatihg spermNasy yokung porn cloise upInnfo pahe personal remembdr teenOrgassmic
matureCumshots free pis teenAmatgeur radio antenna ervo drivenHoow tto strijp
paiin frrom baseboardsYounerr teenBikii boa pictuire submitMekenzie
rosman nudeGirls putting stuff iin their vaginasGaay art oof bastilleJapaneese ttentical pornBoddy woek pleasureInterracial
mikf having sexHerr belly cumshots freeEscot male renoTiiny tiit mariaJessic
orn videosBritny speazrs nuide pissy photoPortnstar jessifa jaymesXxxx stokres panamna ciry flMillf diamond ackson tubeMeliba perez’s boobsPorrn pics cockMy daughrer loves analPoorn sar tabitha bigg tis bossCelebriity gallerry
nude pictureFrree ics off amteur girlsDirrty gjrls btt ficking https://viralbokep.cc/category/bokep-viral Mens sexy clothesWhaat siae aree christtina hendrick’s breastsHentaai
hardclre sexFuunny seex textGayy englsh ads wel hungVintage rochs femmeThhe peniis iin thee vaginaMohsters oof cck daisyy dukesPeliculas porfno conn
rapidshareFree erotiha video downloadsPisss dripping
pantiesKingdoms off tthe hil pornLexbian coules helpChicls in nudeAdjlt
themwd partysStaccked blzck babe whiite cockAdultt nursing 3060Bigg jammiaca
dickI want to deepthroat myy boyfriendOrgasm drriving carNaked old menn over 70 picsUlimqte gaay pornFrree sick shemmale moviesSeex inn multigenerational trial hutsPeep shots oof
women iin biklini aat thhe beachTeenn cancer statisticsVintage penthkuse bboy girlFree xxx seducdtion picsPoorn stockingsConvers shoes girfls
porn jeaans xtubeSexy dragon bll z pictureAsuus a8n sli eluxe faactory
dicksLighht skinbed bitches fuckedCheerleadeer conndom mckinneyVeery yyoung fresh pussyLeel 19 priest twink minor glyphsActife adult
communities laughlin nvTit blokw job clipFoor sale brityish vigin islandAntomy oof pusssy videoThick girlls with bbog titsSexxy sheer pantiesWwww dupthe pussy comKendra wilkonson titt sizeHomade videoss
wives caughut cheatng pornMothe teachinjg daughteer fuckMikaya virgin gordaSexyy
german womanWriting terapy prohrams ffor incwrcerated
teensDooes orgaam depleteSwinger anzeigenFouur ggay testAdult love makinjg videosBooob
jobb kwllie picklersAddressing assault baxed doestic sexyal theoory violence100 erltic ste topFree romantic seex vudeos
ffor womenFench adultt porn

xnxxhealth · 2026-09-01 23:48

We stubled over hee cominjg frrom a different
websikte annd thlught I might aas welll chbeck things out.

I like what I seee so noww i amm following you.

Loook fforward tto finding outt abou your wweb page again. xnxx

777betapk · 2026-09-02 02:48

Seems like the process described is quite detailed. For casual players, understanding the basics via 777bet apk অনলাইন ক্যাসিনো might be easier than reading all the steps. Always check the latest guides!

nodepositbonus · 2026-09-04 01:52

Just found this risky free play option for Asia; No Deposit Bonus games let you try slots without cash, but verify terms carefully before trusting any instant gratification claims online.

juan365 · 2026-09-07 04:41

That’s a solid point about diversifying betting strategies! Seeing platforms like juan 365 cater to VIPs with quick deposits & high limits is interesting – definitely a focus on the player experience there. Good analysis!

krbettingsnation · 2026-09-09 15:12

As someone who checks out dozens of platforms I can confidently say this one delivers on every promise. The live betting options are lightning fast and the customer service team treats every user like family. It really wraps the whole experience in comfort and trust. Visit krbettingsnation and feel the difference of a truly player first approach. krbettingsnation

777game · 2026-09-13 22:28

The interface seems solid, but check out 777 game ডাউনলোড apk for broader comparison; that deposit section is a key usability point!

bajijoy · 2026-09-23 05:01

[906]bajijoy লগইন এবং অ্যাপ ডাউনলোড – বিকাশ দিয়ে ক্যাসিনো গেম খেলুন,bajijoy লগইন করুন এবং মোবাইলে লাইভ ক্যাসিনো গেমের অভিজ্ঞতা নিন। বিকাশ দিয়ে ক্যাসিনো ডিপোজিট করে আপনার প্রিয় অনলাইন স্লট গেম খেলুন। আজই জয়েন করুন। visit: bajijoy