SUM(A)+SUM(B)和SUM(A+B)不相等问题踩坑

有数的编程笔记

已于 2024-04-07 11:20:28 修改

阅读量2.4k

点赞数 1

分类专栏： Spark/Hive 文章标签： hive 数据仓库

于 2021-12-14 13:32:42 首次发布

本文链接：https://blog.csdn.net/qq_33446500/article/details/121925110

版权

Spark/Hive 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

SUN(A)+SUM(B)!=SUM(A+B)的情况

在hive、presto、spark中，当字段A或字段B列中存在null值时，SUN(A)+SUM(B)!=SUM(A+B)

1.null + 数值 = null

   > select null + 0;
   +-------+
   |  _c0  |
   +-------+
   | NULL  |
   +-------+

2.在对null进行判断时，null无法和任何值（包括其本身）通过= != < > <= >=进行比较，值均为null。

   select null == 1;
   select null != 1;
   select null == null;
   select null >= 1;
   -- 结果都为null
   +-------+
   |  _c0  |
   +-------+
   | NULL  |
   +-------+

使用聚合函数遇到null值，结果分别如下：

聚合函数	col列值都为null	col列值存在不为null
sum(col)	null	非null值的和
avg(col)	null	非null值的平均值
count(col)	0	非null值的个数
max(col)	null	非null值中最大值
min(col)	null	非null值中最小值

1. 验证

创建测试表，并插入测试数据（default库）

CREATE TABLE table1(customer_no STRING, stock_fee double, advertisement_fee double);

INSERT INTO table1 VALUES 
("C0001", NULL, 1130.52)
,("C0001", 0.1, 1084.72)
,("C0001", NULL, 1738.32)
,("C0001", NULL, 1817.67)
,("C0001", NULL, 3271.45)
,("C0002", NULL, 2587.76)
,("C0002", NULL, 1397.60)
,("C0002", NULL, 2871.75)
,("C0002", NULL, 812.08)
,("C0002", NULL, 1643.39)
,("C0003", 2635.23, 2587.76)
,("C0003", 1253.69, 1397.60)
,("C0003", 253.69, 2871.75)
,("C0003", 1528.12, 812.08)
,("C0003", 412.53, 1643.39);

select customer_no,sum(stock_fee) as sum,avg(stock_fee) as avg,count(stock_fee) as count,MAX(stock_fee) as max,MIN(stock_fee) as min from table1 group by customer_no;
+--------------+--------------------+---------------------+--------+----------+---------+
| customer_no  |        sum         |         avg         | count  |   max    |   min   |
+--------------+--------------------+---------------------+--------+----------+---------+
| C0001        | 0.2                | 0.1                 | 2      | 0.1      | 0.1     |
| C0002        | NULL               | NULL                | 0      | NULL     | NULL    |
| C0003        | 6083.259999999999  | 1216.6519999999998  | 5      | 2635.23  | 253.69  |
+--------------+--------------------+---------------------+--------+----------+---------+

1.1. 对单独的一列进行SUM

SELECT  customer_no
       ,SUM(stock_fee)
FROM table1
GROUP BY  customer_no;

+--------------+--------------------+
| customer_no  |        _c1         |
+--------------+--------------------+
| C0001        | 0.1                |
| C0002        | NULL               |
| C0003        | 6083.259999999999  |
+--------------+--------------------+

当列值中存在NULL时(C0001)，sum结果不会报错，且计算结果正确
当列值中全部为NULL时(C0002)，sum结果为null
当列值中不存在NULL值时(C0003)，对数值进行sum，无异议

1.2. SUM(A)+SUM(B)

SELECT  customer_no
       ,SUM(stock_fee)+SUM(advertisement_fee)
FROM table1
GROUP BY  customer_no;

+--------------+-----------+
| customer_no  |    _c1    |
+--------------+-----------+
| C0001        | 9042.78   |
| C0002        | NULL      |
| C0003        | 15395.84  |
+--------------+-----------+

对于c0001，SUM(stock_fee)=0.1，SUM(advertisement_fee)=9042.68，因此SUM(stock_fee)+SUM(advertisement_fee)=9042.78
对于c0002，SUM(stock_fee)=null，SUM(advertisement_fee)=9312.58，因此SUM(stock_fee)+SUM(advertisement_fee)=null
对于c0003，SUM(stock_fee)=6083.259999999999，SUM(advertisement_fee)=9312.58，因此SUM(stock_fee)+SUM(advertisement_fee)=15395.84

1.3. SUM(A+B)

SELECT  customer_no
       ,SUM(stock_fee + advertisement_fee)
FROM table1
GROUP BY  customer_no;

+--------------+---------------------+
| customer_no  |         _c1         |
+--------------+---------------------+
| C0001        | 1084.82             |
| C0002        | NULL                |
| C0003        | 15395.839999999998  |
+--------------+---------------------+

对于c0001，只有0.1 + 1084.72 不为null，因此SUM(stock_fee + advertisement_fee)结果为1084.82
对于c0002，只有stock_fee + advertisement_fee都为null，因此SUM(stock_fee + advertisement_fee)结果为null
对于c0003，只有stock_fee + advertisement_fee都不为为null，因此SUM(stock_fee + advertisement_fee)结果为15395.839999999998

2. 解决

想要sum时得到正确结果，在sum时对每个字段值都判断是否为null，当为null时置为0

以下二者结果相同

SELECT  customer_no
       ,SUM(if(stock_fee is null,0,stock_fee) + if(advertisement_fee is null,0,advertisement_fee))
FROM table1
GROUP BY  customer_no;

SELECT  customer_no
       ,SUM(if(stock_fee is null,0,stock_fee)) + SUM(if(advertisement_fee is null,0,advertisement_fee))
FROM table1
GROUP BY  customer_no;

+--------------+-----------+
| customer_no  |    _c1    |
+--------------+-----------+
| C0001        | 9042.78   |
| C0002        | 9312.58   |
| C0003        | 15395.84  |
+--------------+-----------+