在 ERP、BI 等系統中，數據透視分析（Pivot Analysis）是非常常見的需求：用戶希望按任意維度（如門店、時間、商品分類等）進行分組統計，同時選擇不同的指標（如 GMV、訂單數、客單價等）進行聚合計算。

這種需求如果直接寫固定 SQL 會非常死板，而通過 FreeMarker 模板 動態構造 SQL，我們可以在保證安全的前提下，讓維度、指標、過濾條件、排序、分頁等全部可配置化，從而實現靈活的數據分析能力。

---

1. 需求建模

一個典型的數據透視分析需要定義三部分信息：

1. 維度（dimensions）
   按哪些字段分組，如 shop_id、sale_date 等。

2. 指標（measures）
   對哪些字段做聚合計算，如 SUM(amount)、COUNT(order_id)。
   例如你提供的結構：

   "measures": [{ "func": "sum",   "field": "amount",   "alias": "gmv" },{ "func": "count", "field": "order_id", "alias": "orders" }
   ]
   

3. 過濾條件（filters）
   如時間區間、門店、商品分類等限制。

---

2. 安全策略

動態 SQL 最大的風險是 SQL 注入，所以必須做到：

• 列名白名單：
  允許的字段映射表，如：

  Map<String, String> columnMap = Map.of("amount",   "t.amount","order_id", "t.order_id","shop_id",  "t.shop_id"
  );
  

• 聚合函數白名單：

  Set<String> aggs = Set.of("SUM", "COUNT", "AVG", "MIN", "MAX");
  

• 值參數綁定：
  所有值通過命名參數 :param 傳入，不直接拼到 SQL 字符串中。

---

3. FreeMarker SQL 模板

下面是一個支持維度、指標、過濾、排序、分頁的 FreeMarker 模板 pivot.ftl：

SELECT<#-- 維度 --><#if dimensions?has_content><#list dimensions as d>${d.sql} AS ${d.alias}<#if d_has_next>,</#if></#list><#if measures?has_content>,</#if></#if><#-- 指標 --><#list measures as m>${m.sql} AS ${m.alias}<#if m_has_next>,</#if></#list>FROM ${table} t<#if whereClauses?has_content>
WHERE<#list whereClauses as w>(${w})<#if w_has_next> AND </#if></#list>
</#if><#if dimensions?has_content>
GROUP BY<#list dimensions as d>${d.sql}<#if d_has_next>,</#if></#list>
</#if><#if orderBy?has_content>
ORDER BY<#list orderBy as o>${o.sql} ${o.dir}<#if o_has_next>,</#if></#list>
</#if><#if limit??>
LIMIT :p_limit<#if offset??>OFFSET :p_offset</#if>
</#if>

---

4. Java 構造 SQL

在 Java 端，根據前端傳入的 {func, field, alias} 格式，映射成模板可用的 SQL 片段。

List<Map<String, Object>> measureList = measures.stream().map(m -> {String agg = m.getFunc().toUpperCase();if (!aggs.contains(agg)) {throw new IllegalArgumentException("非法聚合函數: " + agg);}String col = Optional.ofNullable(columnMap.get(m.getField())).orElseThrow(() -> new IllegalArgumentException("非法列: " + m.getField()));String sqlExpr = agg + "(" + col + ")";return Map.of("name", m.getField(),"alias", m.getAlias(),"sql", sqlExpr);
}).toList();

這樣，如果用戶傳入：

"measures": [{ "func": "sum",   "field": "amount",   "alias": "gmv" },{ "func": "count", "field": "order_id", "alias": "orders" }
]

模板渲染后就會生成：

SELECTSUM(t.amount) AS gmv,COUNT(t.order_id) AS orders
FROM order_table t

---

5. 加入維度

如果加上維度：

"dimensions": [{ "field": "shop_id", "alias": "shop" }
]

生成的 SQL 會是：

SELECTt.shop_id AS shop,SUM(t.amount) AS gmv,COUNT(t.order_id) AS orders
FROM order_table t
GROUP BY t.shop_id

---

6. 好處

• 靈活性高：維度、指標、過濾條件全可配置。

• 安全：列、函數、表等全部走白名單；值參數綁定，防止注入。

• 可擴展：可以輕松加入條件聚合實現行轉列（透視列）。

• 通用性：同一套模板可支持不同業務場景（銷售、庫存、財務等）。

---

7. 總結

通過 FreeMarker 模板 + 白名單映射 + 參數綁定，我們可以優雅地實現一個安全、靈活的數據透視分析引擎。

核心要點：

1. 模板只負責結構，不直接拼用戶輸入。

2. Java 端負責校驗、映射、生成 SQL 片段。

3. 所有值用參數綁定，杜絕 SQL 注入。

這種方式不僅可以滿足 ERP 復雜報表的需求，也能作為通用 BI 引擎的核心實現方案。