Oracle SQL Model 子句详解

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

1. 概述

MODEL 子句支持电子表格式计算[1]：

2. 基本

2.1 结构

SELECT ...
FROM table
MODEL
  [PARTITION BY (cols)]
  DIMENSION BY (cols)
  MEASURES (cols)
  RULES (rules);

2.2 示例

SELECT year, region, sales
FROM sales_history
MODEL
  DIMENSION BY (year, region)
  MEASURES (sales)
  RULES (
    sales[2026, 'East'] = sales[2025, 'East'] * 1.1,
    sales[2026, 'West'] = sales[2025, 'West'] * 1.15
  )
ORDER BY year, region;

3. PARTITION BY

SELECT year, region, product, sales
FROM sales
MODEL
  PARTITION BY (region)
  DIMENSION BY (year, product)
  MEASURES (sales)
  RULES (
    sales[2026, 'A'] = sales[2025, 'A'] * 1.1
  );

4. DIMENSION BY

4.1 多维

DIMENSION BY (year, month, region)

4.2 索引

DIMENSION BY (year, product)
MEASURES (sales)

RULES (
  sales[2025, 'A'] = 100,
  sales[2025, 'B'] = 200
);

5. MEASURES

MEASURES (sales, cost, profit)
RULES (
  profit[2025, 'A'] = sales[2025, 'A'] - cost[2025, 'A']
);

6. RULES

6.1 UPSERT

RULES UPSERT (
  sales[2026, 'East'] = sales[2025, 'East'] * 1.1
)
-- 不存在则插入

6.2 UPDATE

RULES UPDATE (
  sales[2025, 'East'] = sales[2025, 'East'] * 1.1
)
-- 仅更新存在

6.3 AUTOMATIC ORDER

RULES AUTOMATIC ORDER (
  sales[2026, 'East'] = sales[2025, 'East'] * 1.1,
  sales[2027, 'East'] = sales[2026, 'East'] * 1.1
)
-- 自动顺序

6.4 SEQUENTIAL ORDER

RULES SEQUENTIAL ORDER (...)
-- 顺序执行

7. 单元引用

7.1 绝对

sales[2025, 'A']

7.2 相对

sales[CV() - 1, 'A']  -- 上一年
sales[CV(year) - 1, CV()]

7.3 任意

sales[ANY, 'A']  -- 所有年
sales[FOR year IN (2023, 2024, 2025), 'A']

8. 函数

8.1 聚合

RULES (
  sales[2025, 'Total'] = SUM(sales)[2025, ANY]
);

8.2 分析

RULES (
  sales[2025, 'A'] = AVG(sales)[2023:2024, 'A']
);

9. FOR 循环

RULES (
  sales[FOR year IN (2026, 2027, 2028), 'A'] = 
    sales[CV() - 1, 'A'] * 1.1
);

-- 范围
RULES (
  sales[FOR year FROM 2026 TO 2030 INCREMENT 1, 'A'] =
    sales[CV() - 1, 'A'] * 1.1
);

10. 应用场景

10.1 预测

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

10.2 移动平均

RULES (
  sales[2025, 'MA'] = AVG(sales)[2020:2024, ANY]
);

10.3 增长率

RULES (
  growth[2025, FOR region IN (SELECT DISTINCT region FROM sales)] =
    (sales[2025, CV()] - sales[2024, CV()]) / sales[2024, CV()]
);

10.4 行间计算

RULES (
  diff[2025, 'A'] = sales[2025, 'A'] - sales[2024, 'A'],
  pct[2025, 'A'] = diff[2025, 'A'] / sales[2024, 'A'] * 100
);

10.5 总计

RULES (
  sales[2025, 'ALL'] = SUM(sales)[2025, ANY]
);

11. 复杂示例

11.1 预算分配

SELECT year, dept, expense
FROM budget
MODEL
  PARTITION BY (year)
  DIMENSION BY (dept)
  MEASURES (expense)
  RULES (
    expense['Total'] = SUM(expense)[ANY],
    expense['IT'] = expense['Total'] * 0.3,
    expense['Sales'] = expense['Total'] * 0.5,
    expense['Admin'] = expense['Total'] * 0.2
  );

11.2 递归

SELECT year, sales
FROM sales_history
MODEL
  DIMENSION BY (year)
  MEASURES (sales)
  RULES AUTOMATIC ORDER (
    sales[2025] = 1000,
    sales[FOR year FROM 2026 TO 2030 INCREMENT 1] = sales[CV() - 1] * 1.1
  );

12. 性能

12.1 索引

- 维度索引
- 性能

12.2 内存

- 数据量
- 排序
- ORDER

12.3 优化

- AUTOMATIC ORDER
- 避免 ALL
- 范围限制

13. 常见坑与排错

13.1 循环引用

- AUTOMATIC ORDER
- 顺序

13.2 不存在单元

- UPSERT
- PRESENTV / PRESENTNNV / ABSENT

13.3 数据类型

- 一致
- 转换

14. 辅助函数

14.1 PRESENTV

PRESENTV(cell, expr1, expr2)
-- cell 存在返回 expr1，否则 expr2

14.2 PRESENTNNV

PRESENTNNV(cell, expr1, expr2)
-- cell 存在且非 NULL

14.3 ITERATION_NUMBER

RULES ITERATE (10) (
  sales[2025 + ITERATION_NUMBER] = sales[2024 + ITERATION_NUMBER] * 1.1
)

14.4 PREVIOUS

RULES ITERATE (10) (
  sales[2025] = PREVIOUS(sales[2025]) * 1.1
)

15. 最佳实践

DIMENSION BY：维度
MEASURES：度量
UPSERT：插入
AUTOMATIC ORDER：依赖
FOR 循环：批量
CV()：相对
聚合：计算
预测：场景
测试：验证
性能：限制

16. 参考资料

[1] Oracle Database Data Warehousing Guide 19c, “SQL for Aggregation” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwh/