一個操作，三種寫法：跨引擎查詢的心智模型

簡述

• 情境
  • 手上的專案同時用 ClickHouse、Spark、MongoDB 當資料來源。常常需要直接寫 raw SQL 快速撈到目標結果。
  • 但我對 raw SQL 不熟，多半丟給 AI 快速生一段。它能動，可是我每次都卡在同一個地方：它寫了 count(*)，我卻想不起來還有沒有 count()？哪個是 ClickHouse 才有的（它兩個都吃）？count(col) 到底算不算 NULL？想判斷某個值在不在 array 裡，ClickHouse 要 has()、Spark 是 array_contains()、Mongo 又變成 $in？

• 動機

  • 每次都靠 AI 生、卻記不牢。手上只有一堆散落的 syntax。沒有一套心智模型串起來。所以想乾脆從頭學一次，把知識重新整理過。
  • 順便釐清 Mongo query 怎麼下（有時也會用到）。
  • 順便釐清 Mongo Compass 左側欄位的精確術語。例如 collection 對應到關聯式裡的 table。

• 最終目標

  • 學會一套通用的方式，去 query ClickHouse 和 Spark 裡那些常見的資料型別：struct、array、byte 這類東西。
  • 先抓住一個 operation 的抽象目的。再對應到三個資料庫各自的寫法。例如「在字串裡找子字串」或「判斷某個元素在不在 array 裡」。

拒絕勝過默默給錯。 一個「看起來對、其實錯」的 query 比沒有還糟。它回傳一個數字，數字看起來正常。沒人發現它算錯了 row，直到它進了報表才爆。工程上有條原則我一直記著。會產生「看似合理卻錯誤」結果的路徑。寧可大聲失敗，也不要默默放行。一個你無法驗證的 AI query，正好踩在這條線上。

反直覺的轉折：用 operation 思考，不要背 syntax

後來好好靜下心來思考要怎麼要效率地學習。重點不在把三套 syntax 都背起來。而是換一個索引方式。從「syntax」改成「operation 的意圖」。你對資料做的事，來來去去就那十幾種：

• count 全部
• count 非 NULL
• 給 NULL 一個預設值
• 搜尋子字串
• 判斷 array 成員
• 對 array 做 map/filter
• 把多列聚成一個 array
• 依條件分支

每一個都是一個 intent。換到不同引擎只是拼法變了而已。

這趟學習旅程事先定義的 Roadmap/Milestone

1. 先定位三個 query engine：ClickHouse 和 Spark 都用 SQL。所以約 80% 可以互通。Mongo 因為是 nosql，所以他是自己一套獨立的 query 語句。
2. 對齊詞彙：table = collection、row = document、column = field。→ MongoDB 運算子與詞彙對照
3. 先懂型別：要玩 array/struct/map 的操作。得先搞清楚每個引擎裡的 array「到底是什麼」才有意義。ClickHouse 的 array 甚至是從 index 1 開始數的。→ ClickHouse 資料型別、Spark 資料型別
4. 一次學會可攜的核心：COUNT、CASE WHEN、GROUP BY/HAVING、LIKE/ILIKE、IN/BETWEEN/IS NULL，這就是那 80%。→ 可攜式 SQL 核心、count(*) vs count(欄位)、LIKE / ILIKE 子字串搜尋
5. 再學各引擎特有的寫法：ClickHouse 的 arrayMap/Filter/Exists/All、multiIf、has → ClickHouse 陣列函式。Spark 的 transform/filter/exists/forall、collect_list/set → Spark 陣列函式與 collect。
6. 用類比把同樣的 operation 搬到 Mongo：$regex ≈ LIKE、$in ≈ IN、$exists 判斷欄位存在。

先給結論～ 後來整理出的表格 operation × engine table

標記 † 的 Mongo 寫法超出原筆記範圍，取自標準 MongoDB（query 或 aggregation operator）。使用前請再確認。

操作 (intent)	Common SQL	ClickHouse	Spark SQL	MongoDB
計數所有列（含 NULL）	COUNT(*)	count(*) / count()	count(*)	countDocuments({}) †
計數非 NULL	COUNT(col)	count(col)	count(col)	{col:{$ne:null}} †
NULL 給預設值	。	ifNull(x,d)	NVL(x,d)	$ifNull †
子字串搜尋	LIKE / ILIKE	LIKE / ILIKE	LIKE / rlike	{f:{$regex,$options:"i"}}
array 成員判斷	。	has(arr,v)	array_contains(arr,v)	{f:{$in:[v]}}
任一元素符合	。	arrayExists(x->…,arr)	exists(arr,x->…)	$elemMatch †
全部元素符合	。	arrayAll(x->…,arr)	forall(arr,x->…)	。
對 array 逐一映射	。	arrayMap(x->…,arr)	transform(arr,x->…)	$map †
篩選 array	。	arrayFilter(x->…,arr)	filter(arr,x->…)	$filter †
多列聚成 array	。	groupArray() / groupUniqArray()	collect_list() / collect_set()	$push / $addToSet †
條件分支	CASE WHEN	if() / multiIf()	CASE WHEN ... THEN ... ELSE ...	$cond / $switch †

結尾

學到最後才發現，真正要記的東西其實沒幾樣。一份 operation 清單。加上一張把它翻成各引擎寫法的對照表。AI 可以幫我寫 SQL。但我得知道這段 SQL 到底在問什麼。會報錯的 query 不可怕。可怕的是那種跑得乾乾淨淨、卻答錯了問題的。

驗收一下自己 → 跨引擎查詢練習（附解答）。

---

參考來源

• ClickHouse SQL Reference：https://clickhouse.com/docs/en/sql-reference
• Spark SQL Built-in Functions：https://spark.apache.org/docs/latest/api/sql/
• MongoDB Query & Aggregation Operators：https://www.mongodb.com/docs/manual/reference/operator/