迁移 Oracle 到 PostgreSQL: OLAP 分析函数

七月 19, 2023

Oracle OLAP 函数通过提供基于一组行计算聚合值的功能来扩展标准 SQL 分析函数的功能。可以将 OLAP 函数应用于单个查询表达式范围内的逻辑分区结果集。OLAP 函数通常与商业智能报告和分析结合使用。它们可以帮助提高查询性能，作为使用更复杂的非 OLAP SQL 代码实现相同结果的替代方法。

Oracle 用法

常见的 Oracle OLAP 函数

函数类型 相关函数
聚合 average_rank, avg, count, dense_rank, max, min, rank, sum
分析 average_rank, avg, count, dense_rank, lag, lag_variance, lead_variance_percent, max, min, rank, row_number, sum, percent_rank, cume_dist, ntile, first_value, last_value
层次 hier_ancestor, hier_child_count, hier_depth, hier_level, hier_order, hier_parent, hier_top
滞后 lag, lag_variance, lag_variance_percent, lead, lead_variance, lead_variance_percent
OLAP DML olap_dml_expression
排序 average_rank, dense_rank, rank, row_number

有关详细信息，请参阅 Oracle 文档中的 OLAP 函数和函数。

PostgreSQL 用法

PostgreSQL 将 ANSI SQL 分析函数称为“窗口函数”。它们提供与 SQL 分析函数 和 Oracle 扩展 OLAP 函数相同的核心功能。PostgreSQL 中的窗口函数对结果集的逻辑“分区”或“窗口”进行操作，并为该“窗口”中的行返回值。

从数据库迁移的角度来看，您应该按类型检查 PostgreSQL 窗口函数，并将它们与等效的 Oracle OLAP 函数进行比较，以验证语法和输出的兼容性。

注意

即使 PostgreSQL 窗口函数提供与特定 Oracle OLAP 函数相同的功能，返回的数据类型也可能不同，并且需要更改应用程序。

PostgreSQL 支持两种主要类型的窗口函数：

  • 聚合函数。
  • 排序函数。

按类型划分的 PostgreSQL 窗口函数

函数类型 相关函数
聚合 avg, count, max, min, sum, string_agg
排序 row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value

例子

Oracle rank()函数和 PostgreSQL rank()函数提供相同的结果。

Oracle：

SELECT department_id, last_name, salary, commission_pct,
    RANK() OVER (PARTITION BY department_id
    ORDER BY salary DESC, commission_pct) "Rank"
    FROM employees WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY COMMISSION_PCT Rank
80            Russell   14000  .4             1
80            Partners  13500  .3             2
80            Errazuriz 12000  .3             3

PostgreSQL：

hr=# SELECT department_id, last_name, salary, commission_pct,
    RANK() OVER (PARTITION BY department_id
    ORDER BY salary DESC, commission_pct) "Rank"
    FROM employees WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY    COMMISSION_PCT Rank
80            Russell   14000.00  0.40           1
80            Partners  13500.00  0.30           2
80            Errazuriz 12000.00  0.30           3

注意

某些数值数据类型的返回格式是不同的。

Oracle CONNECT BY 在 PostgreSQL 中的替代

PostgreSQL提供了两种解决方法作为Oracle分层语句（如CONNECT BY语法）的替代方案：

  • 使用 PostgreSQL generate_series函数。
  • 使用 PostgreSQL 递归视图。

例

PostgreSQL generate_series函数。

SELECT "DATE"
  FROM generate_series(timestamp '2010-01-01',
                       timestamp '2017-01-01',
                       interval '1 day') s("DATE");

DATE
---------------------
2010-01-01 00:00:00
2010-01-02 00:00:00
2010-01-03 00:00:00
2010-01-04 00:00:00
2010-01-05 00:00:00
…

有关详细信息，请参阅 PostgreSQL 文档中的窗口函数和聚合函数。

总结

Oracle OLAP 函数 返回的数据类型 PostgreSQL 窗口函数 返回的数据类型 兼容的语法
Count Number Count bigint 是的
Max Number Max numeric、string、date/time、network或enum类型 是的
Min Number Min numeric、string、date/time、network或enum类型 是的
Avg Number Avg numeric、double，或者与参数相同的数据类型 是的
Sum Number Sum bigint，或者与参数相同的数据类型 是的
rank() Number rank() bigint 是的
row_number() Number row_number() bigint 是的
dense_rank() Number dense_rank() bigint 是的
percent_rank() Number percent_rank() double 是的
cume_dist() Number cume_dist() double 是的
ntile() Number ntile() integer 是的
lag() 与值相同的类型 lag() 与值相同的类型 是的
lead() 与值相同的类型 lead() 与值相同的类型 是的
first_value() 与值相同的类型 first_value() 与值相同的类型 是的
last_value() 与值相同的类型 last_value() 与值相同的类型 是的

搜索

分类

标签