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

簡述

• 情境

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

  • 同一個 operation：判斷「array 是否含某值」
      │
      ├─ ClickHouse  →  has(arr, v)
      ├─ Spark       →  array_contains(arr, v)
      └─ MongoDB     →  { f: {$in: [v]} }

• 動機

  • 每次都靠 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/