升级“脚本小子”

脚本小子

脚本小子并不自己写代码，而是四处 copy 代码。还有的情况是 copy 自己写的代码。

如何做的更好写

再写 sql 的时候，注意总结解决一类问题的通用方法。单纯 copy 代码，不如封装函数。copy 相似逻辑的代码，不如重写逻辑。

使用 git log --since='2019-12-25' -p file 对提交的 git 日志进行分析。找出做了哪些修改。我在写 sql 的时候，每修改一次基脚本，
就 add commit 一次，然后到服务器上测试代码。这样做的好处是，能够记录下来每次做了哪些修改，也能发现 sql 常犯的错误。

hive CET 语法的 bug

cet 语法增强 DML 语句的可读性。但是也会有一些问题。

• 子查询中的字段类型的时候，on 上的条件失效，即使 cast 类型转换后也会保存。
• 子查询 join 后，grouping set 语法做计算后，有些 group__id 下的数值统计错误
• 使用子查询做 union all 操作后，会出现 hive 系统的中间表不存在的错误。

这些错误解决起来也简单，一方面可以向 hive 社区提交 bug ，但是哪里的反应失效可能会比较差。另一方面，我们可以绕过这个坑，将自查的内容些到中间表里面，然后再进行关联后，将结果保存到目标表中。例如

with sub_query1 as (
  select ... from ... where ...
),sub_query2 as (
  select ... from ... where ...
), ...
,sub_queryN as (
  select ... from ... where ...
)
insert overwrite table target_table
select ...
  from sub_query join
  where ...

如果遇到上述问题，可以如下改造。

create table sub_query1 
as
  select ... from ... where ...
;
create table sub_query2
as 
  select ... from ... where ...
;
 ...
create table sub_queryN 
as
select ... from ... where ...
;
insert overwrite table target_table 
select ...
  from sub_query join
  where ...

开窗函数陷阱

开窗函数在做子集计算聚合计算的时候是非常好用的，但是会有一个问题，请看下面的例子：

门店	交易日期	外卖份数
A	2020年2月3日	50
A	2020年2月10日	50
A	2020年2月11日	50
A	2020年2月27日	50

这里有个需求是，要统计出月至今的每天的外卖份数。

select trade_date
      ,shop_name 
      ,sum(sale_copy) over(partition by trade_date , shop_name) as sale_copy
 from fct_sale

得到的结果是这样的：

|trade_date|shop_name|sale_copy |
| A| 2020年2月3日| 50 |
| A| 2020年2月10日| 100 |
| A| 2020年2月11日| 150 |
| A| 2020年2月27日| 200 |

这里有个问题是在 2020年2月4日 ~ 2020年2月4日这些天内，A 门店没有销售记录，那就不会有累加的销售量。有一个解决办法，我们需要一个维表 trede_date_shop。

日期	门店
2020年2月1日	A
2020年2月2日	A
2020年2月3日	A
2020年2月4日	A
2020年2月5日	A
2020年2月6日	A
2020年2月7日	A
2020年2月8日	A
2020年2月9日	A

有了这个维表，query 写成下面的样子了：

     select a.trade_date
           ,a.shop_name 
           ,sum(coalesce(sale_copy,0)) over(partition by a.trade_date , a.shop_name ) as sale_copy
       from trede_date_shop as a 
  left join fct_sale as b
         on a.trade_date = b.trade_date
        and a.shop_name = b.shop_name