Oracle兼容-语法-RATIO_TO_REPORT

1. 语法

  1. SET sql_mode = ORACLE;
  3. RATIO_TO_REPORT(expr)
  4.    OVER ([ query_partition_clause ])

2. 定义和用法

想要使用 RATIO_TO_REPORT 需要先切换到ORACLE模式下，RATIO_TO_REPORT 可用于数据占比分析，其分析公式为：结果 = 字段值/sum(字段)。

3. Oracle兼容说明

在GreatSQL和Oracle中的 RATIO_TO_REPORT 用法一致，具体实现功能如下：

  1. RATIO_TO_REPORT(expr) OVER (query_partition_clause) 对分组数据进行占比分析。

  2. RATIO_TO_REPORT(expr) OVER () 对所有数据进行占比分析。

  3. 在Oracle中不支持字符值使用 RATIO_TO_REPORT，但因GreatSQL中会自动隐式转换，支持这么用。

  4. 在GreatSQL中不支持ENUM、SET、JSON、GEOMETRY、时间等多个类型数据使用 RATIO_TO_REPORT。

  5. RATIO_TO_REPORT 分析的结果小数位数为该字段的小数位加上系统参数 @@div_precision_increment 的和。

  6. 字段值为NULL、''及相加和为0等几种情况下，RATIO_TO_REPORT 的结果为 NULL。

  7. 在GreatSQL中支持 BIT 类型字段用于 RATIO_TO_REPORT。

  8. 当于计算时数值过大产生溢出的情况，将按照GreatSQL原生的方式来处理。

4. 示例

创建测试表并填充数据：

  1. greatsql> CREATE TABLE `t1` (
  2.  sidea INT DEFAULT NULL,
  3.  sideb INT DEFAULT NULL,
  4.  sidec DECIMAL(50,10) GENERATED ALWAYS AS (SQRT(((`sidea` * `sidea`) + (`sideb` * `sideb`)))) VIRTUAL
  5. );
  7. greatsql> INSERT INTO t1(sidea,sideb) VALUES(1,2),(10,20),(null,null),(-1,20),(10,-100),(10,-123);
  8. greatsql> SELECT * FROM t1;
  9. +-------+-------+----------------+
  10. | sidea | sideb | sidec          |
  11. +-------+-------+----------------+
  12. |     1 |     2 |   2.2360679775 |
  13. |    10 |    20 |   22.360679775 |
  14. |  NULL |  NULL |           NULL |
  15. |    -1 |    20 |  20.0249843945 |
  16. |    10 |  -100 | 100.4987562112 |
  17. |    10 |  -123 |  123.405834546 |
  18. +-------+-------+----------------+
  19. 6 rows in set (0.00 sec)
    1. RATIO_TO_REPORT over (partition by) 对分组数据进行占比分析。
  1. greatsql> SET sql_mode = ORACLE;
  3. -- 查看 @@div_precision_increment 值
  4. greatsql> SELECT @@div_precision_increment;
  5. +---------------------------+
  6. | @@div_precision_increment |
  7. +---------------------------+
  8. |                         4 |
  9. +---------------------------+
  11. greatsql> SELECT sidec, sidea, RATIO_TO_REPORT(sidec) OVER (PARTITION BY sidea) c FROM t1;
  12. +----------------+-------+------------------+
  13. | sidec          | sidea | c                |
  14. +----------------+-------+------------------+
  15. |           NULL |  NULL |             NULL |
  16. |  20.0249843945 |    -1 |                1 |
  17. |   2.2360679775 |     1 |                1 |
  18. |   22.360679775 |    10 | 0.09079916029847 |
  19. | 100.4987562112 |    10 | 0.40809146979602 |
  20. |  123.405834546 |    10 | 0.50110936990551 |
  21. +----------------+-------+------------------+
  22. 6 rows in set (0.00 sec)
  24. greatsql> SELECT sideb, sidea, RATIO_TO_REPORT(sideb) OVER (PARTITION BY sidea) c FROM t1;
  25. +-------+-------+---------+
  26. | sideb | sidea | c       |
  27. +-------+-------+---------+
  28. |  NULL |  NULL |    NULL |
  29. |    20 |    -1 |       1 |
  30. |     2 |     1 |       1 |
  31. |    20 |    10 | -0.0985 |
  32. |  -100 |    10 |  0.4926 |
  33. |  -123 |    10 |  0.6059 |
  34. +-------+-------+---------+
  35. 6 rows in set (0.01 sec)
    1. RATIO_TO_REPORT OVER() 对所有数据进行占比分析。
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> SELECT sidec, sidea, RATIO_TO_REPORT(sidec) OVER () c FROM t1;
  4. +----------------+-------+------------------+
  5. | sidec          | sidea | c                |
  6. +----------------+-------+------------------+
  7. |   2.2360679775 |     1 | 0.00832718354505 |
  8. |   22.360679775 |    10 | 0.08327183545048 |
  9. |           NULL |  NULL |             NULL |
  10. |  20.0249843945 |    -1 | 0.07457363649836 |
  11. | 100.4987562112 |    10 | 0.37426035229721 |
  12. |  123.405834546 |    10 |  0.4595669922089 |
  13. +----------------+-------+------------------+
  14. 6 rows in set (0.01 sec)
    1. 字段相加的和为0
  1. -- 再新写入两条记录
  2. greatsql> INSERT INTO t1(sidea,sideb) VALUES (20,100),(20,-100);
  4. greatsql> SELECT * FROM t1;
  5. +-------+-------+----------------+
  6. | sidea | sideb | sidec          |
  7. +-------+-------+----------------+
  8. |     1 |     2 |   2.2360679775 |
  9. |    10 |    20 |   22.360679775 |
  10. |  NULL |  NULL |           NULL |
  11. |    -1 |    20 |  20.0249843945 |
  12. |    10 |  -100 | 100.4987562112 |
  13. |    10 |  -123 |  123.405834546 |
  14. |    20 |   100 | 101.9803902719 |
  15. |    20 |  -100 | 101.9803902719 |
  16. +-------+-------+----------------+
  17. 8 rows in set (0.00 sec)
  19. greatsql> SET sql_mode = ORACLE;
  21. greatsql> SELECT sideb, sidea, RATIO_TO_REPORT(sideb) OVER (PARTITION BY sidea) c FROM t1;
  22. +-------+-------+---------+
  23. | sideb | sidea | c       |
  24. +-------+-------+---------+
  25. |  NULL |  NULL |    NULL |
  26. |    20 |    -1 |       1 |
  27. |     2 |     1 |       1 |
  28. |    20 |    10 | -0.0985 |
  29. |  -100 |    10 |  0.4926 |
  30. |  -123 |    10 |  0.6059 |
  31. |   100 |    20 |    NULL |
  32. |  -100 |    20 |    NULL |
  33. +-------+-------+---------+
  34. 8 rows in set (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx