@[toc]
前言
使用的集群环境为：
hive 3.1.2spark 3.0.2dayofweek 函数官方说明
dayofweek(date) - Returns the day of the week for date/timestamp (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
根据所给日期，返回该日期对应的周几数字，如：
周日 —— 1
周一 —— 2
周二 —— 3
......
周六 —— 7
BUG 重现
当前我有一张订单详情信息测试表，如下所示：
需求：获取某一个区间内周一至周日每天各个商品的售卖量，Hive SQL 代码如下：
select
sku_id,
sum(if(`dayofweek`(create_date)=2,sku_num,0)) Monday,
sum(if(`dayofweek`(create_date)=3,sku_num,0)) Tuesday,
sum(if(`dayofweek`(create_date)=4,sku_num,0)) Wednesday,
sum(if(`dayofweek`(create_date)=5,sku_num,0)) Thursday,
sum(if(`dayofweek`(create_date)=6,sku_num,0)) Friday,
sum(if(`dayofweek`(create_date)=7,sku_num,0)) Saturday,
sum(if(`dayofweek`(create_date)=1,sku_num,0)) Sunday
from
order_detail
where
create_date >= "2021-09-27"
and
create_date <= "2021-10-03"
group by
sku_id;
该代码运行后，部分结果如下所示：
在我验证结果的时候，第一行就出现了错误，查看订单详情信息测试表发现，sku_id 为 1 的商品在 2021-09-27（周一）这天售出了 2 件商品；
而在我们上面的代码运行结果中，发现 monday 周一统计的出售商品数量为 0，但是我们的 SQL 代码逻辑并没有出现问题啊。
反而像是该函数执行的结果，将整体向后平移了一个维度，saturday 的值是 sunday 的值，sunday 的值是 monday 的值......
那么为什么会出现这个问题呢？我就想到了可能是数据类型的问题，上面表结构中提到了，create_date 这个字段的类型是 string，而 dayofweek() 函数的参数是一个 date 类型的数据。
于是，我对数据类型进行了转换，然后便有了下面的结果：
select
sku_id,
sum(if(`dayofweek`(cast(create_date as date))=2,sku_num,0)) Monday,
sum(if(`dayofweek`(cast(create_date as date))=3,sku_num,0)) Tuesday,
sum(if(`dayofweek`(cast(create_date as date))=4,sku_num,0)) Wednesday,
sum(if(`dayofweek`(cast(create_date as date))=5,sku_num,0)) Thursday,
sum(if(`dayofweek`(cast(create_date as date))=6,sku_num,0)) Friday,
sum(if(`dayofweek`(cast(create_date as date))=7,sku_num,0)) Saturday,
sum(if(`dayofweek`(cast(create_date as date))=1,sku_num,0)) Sunday
from
order_detail
where
create_date >= "2021-09-27"
and
create_date <= "2021-10-03"
group by
sku_id;
这次得到的结果就是正确的了。
但是令我不解的又来了，在我的印象中 dayofweek() 函数是可以直接传递一个 string 类型的数据啊。
如下所示：
2021-09-27 是周一，所以返回的结果是 2，显然这是正确的，那么为什么同样是 string 类型，这里是正常的，上面的代码中发生了异常。
然后我就想到，会不是是因为多个函数嵌套引发的 BUG，因为我们在最初的 SQL 中是这样写的：
sum(if(`dayofweek`(create_date)=2,sku_num,0)) Monday,
sum(if(`dayofweek`(create_date)=3,sku_num,0)) Tuesday,
sum(if(`dayofweek`(create_date)=4,sku_num,0)) Wednesday,
sum(if(`dayofweek`(create_date)=5,sku_num,0)) Thursday,
sum(if(`dayofweek`(create_date)=6,sku_num,0)) Friday,
sum(if(`dayofweek`(create_date)=7,sku_num,0)) Saturday,
sum(if(`dayofweek`(create_date)=1,sku_num,0)) Sunday
嵌套了 sum、if 以及 dayofweek 函数，于是，我不执行 sum 操作，并且不进行数据类型转换，看看周几判断结果是否正确，如下所示：
select
sku_id,
if(`dayofweek`(create_date)=2,sku_num,0) Monday,
if(`dayofweek`(create_date)=3,sku_num,0) Tuesday,
if(`dayofweek`(create_date)=4,sku_num,0) Wednesday,
if(`dayofweek`(create_date)=5,sku_num,0) Thursday,
if(`dayofweek`(create_date)=6,sku_num,0) Friday,
if(`dayofweek`(create_date)=7,sku_num,0) Saturday,
if(`dayofweek`(create_date)=1,sku_num,0) Sunday
from
order_detail
where
create_date >= "2021-09-27"
and
create_date <= "2021-10-03"
可以发现周几判断结果是对的。
但只要我加上 sum 聚合操作之后，就会发生异常，这种错误呢又可以通过转换数据类型进行规避，属于是闭环了家人们。
Spark SQL 中的使用
当我使用 Spark SQL 完成上面【BUG重现】中的需求时，发现【BUG重现】中最开始的 SQL 代码在这里是正常运行的，即使没有进行数据类型转换、而且使用了聚合函数的嵌套。
spark.sql(
"""
|select
| sku_id,
| sum(if(`dayofweek`(create_date)=2,sku_num,0)) Monday,
| sum(if(`dayofweek`(create_date)=3,sku_num,0)) Tuesday,
| sum(if(`dayofweek`(create_date)=4,sku_num,0)) Wednesday,
| sum(if(`dayofweek`(create_date)=5,sku_num,0)) Thursday,
| sum(if(`dayofweek`(create_date)=6,sku_num,0)) Friday,
| sum(if(`dayofweek`(create_date)=7,sku_num,0)) Saturday,
| sum(if(`dayofweek`(create_date)=1,sku_num,0)) Sunday
|from
| order_detail
|where
| create_date >= "2021-09-27"
| and
| create_date <= "2021-10-03"
|group by
| sku_id
|order by
| sku_id;
|""".stripMargin).show()
最终的结果是正确的。
可以看出 Spark SQL 对其进行了优化，正经人平常谁用【Hive Cli / HiveServer2】啊！一天天整这破事。
我不是正经人......
总结
当我们的使用场景为【Hive Cli / HiveServer2】时：
在嵌套使用 dayofweek 函数的时候，将传递的参数转换为 date 类型，不要用 string 类型，从而避免引发该 BUG。
当我们的使用场景为【Spark SQL】时：
并不会出现该BUG。
