本文系統解析 SQLMesh 的四個核心內置宏，涵蓋行列轉換的
@PIVOT、精準去重的@DEDUPLICATE、靈活生成日期范圍的@DATE_SPINE，以及動態表路徑解析的@RESOLVE_TEMPLATE。通過真實案例演示參數配置與 SQL 渲染邏輯，并對比宏調用與傳統 SQL 的差異，助您高效構建標準化數據處理流程。
SQLMesh 提供了一系列強大的內置宏，用于簡化 SQL 查詢和數據處理任務。本文將深入介紹四個常用宏：
@PIVOT：將長格式數據轉換為寬格式（行列轉換）。@DEDUPLICATE：基于分區列和排序條件去重。@DATE_SPINE：生成日期范圍表，便于時間序列分析。@RESOLVE_TEMPLATE：動態解析表路徑，適用于云存儲場景。
通過實際示例，我們將展示這些宏如何提升 SQL 查詢的靈活性和效率。
在這里插入圖片描述
1. @PIVOT：行列轉換利器
功能概述
@PIVOT 用于將長格式數據（單列存儲多個值）轉換為寬格式（每行對應多個列）。常用于統計分析，如訂單狀態分布、用戶行為分類等。
參數說明
| 參數 | 說明 | 默認值 |
|---|---|---|
column | 要透視的列 | 必填 |
values | 用于透視的值列表 | 必填 |
alias | 是否為結果列添加別名 | true |
agg | 聚合函數（如 SUM, COUNT） | SUM |
cmp | 比較運算符（=, >, <） | = |
prefix / suffix | 別名前綴/后綴 | 無 |
then_value / else_value | 條件成立/不成立時的值 | 1 / 0 |
quote | 是否對別名加引號 | true |
distinct | 聚合時是否去重 | false |
示例
假設有一個 rides 表，記錄騎行訂單狀態（cancelled, completed），我們希望統計每日各狀態的訂單數：
SQLMesh 宏寫法
SELECTdate_day,@PIVOT(status, ['cancelled', 'completed'])
FROM rides
GROUP BY 1
渲染后的 SQL
SELECTdate_day,SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS "'cancelled'",SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS "'completed'"
FROM rides
GROUP BY 1
進階用法
如果只想統計 completed 狀態的訂單數，并計算取消率：
SELECTdate_day,@PIVOT(status, ['completed'], agg := 'COUNT') AS completed_orders,@PIVOT(status, ['cancelled'], agg := 'COUNT') AS cancelled_orders,@PIVOT(status, ['cancelled'], agg := 'COUNT') * 1.0 / NULLIF(@PIVOT(status, ['completed'], agg := 'COUNT'), 0) AS cancellation_rate
FROM rides
GROUP BY 1
2. @DEDUPLICATE：高效去重
功能概述
@DEDUPLICATE 使用窗口函數 ROW_NUMBER() 對數據進行去重，適用于需要保留每組最新/最舊記錄的場景。
參數說明
| 參數 | 說明 | 示例 |
|---|---|---|
relation | 表名或 CTE | my_table |
partition_by | 分區列 | [id, event_date] |
order_by | 排序條件 | ['event_date DESC', 'status ASC'] |
示例
假設 my_table 存儲用戶事件日志，我們希望保留每個用戶每天的最新記錄：
SQLMesh 宏寫法
WITH raw_data AS (@DEDUPLICATE(my_table, [id, CAST(event_date AS DATE)], ['event_date DESC', 'status ASC'])
)
SELECT * FROM raw_data
渲染后的 SQL
WITH "raw_data" AS (SELECT *FROM "my_table" AS "my_table"QUALIFY ROW_NUMBER() OVER (PARTITION BY "id", CAST("event_date" AS DATE) ORDER BY "event_date" DESC, "status" ASC) = 1
)
SELECT * FROM "raw_data" AS "raw_data"
3. @DATE_SPINE：生成日期范圍表
功能概述
@DATE_SPINE 用于生成連續的日期序列，常用于時間序列分析、數據補全等場景。
參數說明
| 參數 | 說明 | 示例 |
|---|---|---|
datepart | 時間粒度（day, week, month） | 'day' |
start_date | 起始日期 | '2024-01-01' |
end_date | 結束日期 | '2024-01-16' |
示例
生成 2024 年 1 月 1 日至 1 月 16 日的日期表：
SQLMesh 宏寫法
WITH discount_promotion_dates AS (@DATE_SPINE('day', '2024-01-01', '2024-01-16')
)
SELECT * FROM discount_promotion_dates
渲染后的 SQL（DuckDB）
WITH "discount_promotion_dates" AS (SELECT "_exploded"."date_day" AS "date_day"FROM UNNEST(CAST(GENERATE_SERIES(CAST('2024-01-01' AS DATE), CAST('2024-01-16' AS DATE), INTERVAL '1' DAY) AS DATE[])) AS "_exploded"("date_day")
)
SELECT "discount_promotion_dates"."date_day" AS "date_day"
FROM "discount_promotion_dates" AS "discount_promotion_dates"
4. @RESOLVE_TEMPLATE：動態表路徑解析
功能概述
@RESOLVE_TEMPLATE 用于動態生成表路徑，適用于云存儲（如 S3）或引擎元數據管理。
參數說明
| 參數 | 說明 | 示例 |
|---|---|---|
template | 字符串模板 | 's3://bucket/@{catalog_name}/@{schema_name}/@{table_name}' |
mode | 返回類型（literal 或 table） | literal |
示例
在模型定義中動態指定存儲路徑：
SQLMesh 宏寫法
MODEL (name datalake.landing.customers,physical_properties (location = @resolve_template('s3://warehouse-data/@{catalog_name}/prod/@{schema_name}/@{table_name}'))
)
渲染后的 SQL
-- 生成的表路徑示例：
-- s3://warehouse-data/datalake/prod/sqlmesh__landing/landing__customers__2517971505
總結
SQLMesh 的內置宏極大提升了 SQL 查詢的靈活性和可維護性：
@PIVOT簡化行列轉換，適用于統計分析。@DEDUPLICATE高效去重，優化數據清洗流程。@DATE_SPINE自動生成日期序列，簡化時間序列分析。@RESOLVE_TEMPLATE動態解析表路徑，適配云存儲場景。
掌握這些宏的使用方法，可以顯著提升 SQL 開發效率，減少重復代碼。建議在實際項目中結合業務需求靈活運用！ 🚀
)