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