Oracle MODEL 子句（电子表格式查询）

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

MODEL 子句 实现电子表格式查询[1]，支持行间计算和引用：

特点：

• 类似 Excel 公式
• 行间引用
• 跨行计算
• 适合复杂报表

---

2. 基本语法

SELECT ...
FROM table
MODEL
  [PARTITION BY (cols)]
  DIMENSION BY (cols)
  MEASURES (cols)
  RULES (
    -- 规则定义
  );

---

3. 基本示例

3.1 销售数据

-- 原始数据
SELECT * FROM sales;
-- year | region | amount
-- 2025 | East   | 100
-- 2025 | West   | 150
-- 2026 | East   | 120
-- 2026 | West   | 180

3.2 MODEL 查询

SELECT year, region, amount
FROM sales
MODEL
  DIMENSION BY (year, region)
  MEASURES (amount)
  RULES (
    amount[2027, 'East'] = amount[2026, 'East'] * 1.1,
    amount[2027, 'West'] = amount[2026, 'West'] * 1.1
  );

3.3 输出

year | region | amount
2025 | East   | 100
2025 | West   | 150
2026 | East   | 120
2026 | West   | 180
2027 | East   | 132  -- 新行
2027 | West   | 198  -- 新行

---

4. 引用方式

4.1 位置引用

RULES (
  amount[2027, 'East'] = amount[2026, 'East'] * 1.1
)

4.2 符号引用

RULES (
  amount[year=2027, region='East'] = amount[year=2026, region='East'] * 1.1
)

4.3 ANY 引用

RULES (
  amount[ANY, 'East'] = amount[CV(), 'East'] * 1.1
)
-- CV() = 当前值

---

5. 跨行计算

5.1 同比增长

SELECT year, region, amount, growth
FROM sales
MODEL
  DIMENSION BY (year, region)
  MEASURES (amount, 0 AS growth)
  RULES (
    growth[ANY, ANY] = 
      (amount[CV(), CV()] - amount[CV()-1, CV()]) / 
      amount[CV()-1, CV()]
  );

5.2 累计求和

SELECT year, region, amount, running_total
FROM sales
MODEL
  PARTITION BY (region)
  DIMENSION BY (year)
  MEASURES (amount, 0 AS running_total)
  RULES (
    running_total[ANY] = SUM(amount)[year <= CV()]
  );

---

6. 迭代

6.1 ITERATE

MODEL
  DIMENSION BY (year)
  MEASURES (amount)
  RULES ITERATE (10) (
    amount[2027 + ITERATION_NUMBER] = amount[2026 + ITERATION_NUMBER] * 1.1
  );

6.2 UNTIL

RULES ITERATE (100) UNTIL (amount[2027 + ITERATION_NUMBER] > 1000) (
  amount[2027 + ITERATION_NUMBER] = amount[2026 + ITERATION_NUMBER] * 1.1
);

---

7. UPSERT vs UPDATE

7.1 UPSERT（默认）

-- 不存在则插入
RULES (
  amount[2027, 'East'] = ...
)

7.2 UPDATE

-- 仅更新已存在
RULES UPDATE (
  amount[2027, 'East'] = ...
)

---

8. 应用场景

8.1 预测

-- 未来 5 年预测
SELECT year, amount
FROM sales
WHERE region = 'East'
MODEL
  DIMENSION BY (year)
  MEASURES (amount)
  RULES (
    amount[2027] = amount[2026] * 1.1,
    amount[2028] = amount[2027] * 1.1,
    amount[2029] = amount[2028] * 1.1,
    amount[2030] = amount[2029] * 1.1
  );

8.2 行转列

SELECT year, east, west
FROM sales
MODEL
  RETURN UPDATED ROWS
  DIMENSION BY (region, year)
  MEASURES (amount)
  RULES (
    east[NULL, ANY] = amount['East', CV()],
    west[NULL, ANY] = amount['West', CV()]
  );

8.3 财务报表

-- 资产负债表
SELECT item, year, value
FROM financials
MODEL
  DIMENSION BY (item, year)
  MEASURES (value)
  RULES (
    value['Total Assets', ANY] = value['Cash', CV()] + value['Inventory', CV()],
    value['Total Liabilities', ANY] = value['Debt', CV()] + value['AP', CV()],
    value['Equity', ANY] = value['Total Assets', CV()] - value['Total Liabilities', CV()]
  );

---

9. 常见坑与排错

9.1 ORA-32638: 非单元矩阵

-- 维度列有多个值
-- 1. 加 PARTITION BY
-- 2. 聚合

9.2 性能差

-- 1. 限制数据量
-- 2. 加索引
-- 3. 简化规则

9.3 规则循环

-- 避免循环引用
-- 使用 ORDER 指定顺序
RULES ORDER (
  ...
)

---

10. 最佳实践

1. 复杂报表用 MODEL：灵活
2. 分区减少数据：PARTITION BY
3. 迭代谨慎：避免无限
4. UPSERT vs UPDATE：明确意图
5. RETURN UPDATED ROWS：仅新行
6. 测试验证：复杂逻辑
7. 性能监控：大数据量

---

11. 参考资料

[1] Oracle Database Data Warehousing Guide 19c, “MODEL Clause” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/model-clause.html