hive函数与调优

函数
last_day()
是每个月月末最后一天；
例如：
SELECT
day, – 时间
DATE_ADD(day, 1 - DAYOFWEEK(day)) AS week_first_day, – 本周第一天_周日
DATE_ADD(day, 7 - DAYOFWEEK(day)) AS week_last_day, – 本周最后一天_周六
DATE_ADD(day, 1 - CASE WHEN DAYOFWEEK(day) = 1 THEN 7 ELSE DAYOFWEEK(day) - 1 END) AS week_first_day, – 本周第一天_周一
DATE_ADD(day, 7 - CASE WHEN DAYOFWEEK(day) = 1 THEN 7 ELSE DAYOFWEEK(day) - 1 END) AS week_last_day, – 本周最后一天_周日
NEXT_DAY(day,‘TU’) AS next_tuesday, – 当前日期的下个周二
TRUNC(day, ‘MM’) AS month_first_day, – 当月第一天
LAST_DAY(day) AS month_lASt_day, – 当月最后一天
TO_DATE(CONCAT(YEAR(day), ‘-’, LPAD(CEIL(month(day) / 3) * 3 - 2, 2, 0), ‘-01’)) AS season_first_day, – 当季第一天
LAST_DAY(TO_DATE(CONCAT(YEAR(day),’-’,lpad(ceil(month(day)/3) * 3,2,0),’-01’))) AS season_lASt_day, – 当季最后一天
TRUNC(day, ‘YY’) AS year_first_day, – 当年第一天
LAST_DAY(ADD_MONTHS(TRUNC(day, ‘YY’), 11)) AS year_last_day, – 当年最后一天
WEEKOFYEAR(day) AS weekofyear, – 当年第几周
SECOND(day) AS second, – 秒钟
MINUTE(day) AS minute, – 分钟
HOUR(day) AS hour, – 小时
DAY(day) AS day, – 日期
MONTH(day) AS month, – 月份
LPAD(CEIL(MONTH(day)/3),2,0) AS season, – 季度
YEAR(day) AS year – 年份
FROM (
SELECT ‘2019-01-02 01:01:01’ AS day UNION ALL
SELECT ‘2019-02-02 02:03:04’ AS day UNION ALL
SELECT ‘2019-03-02 03:05:07’ AS day UNION ALL
SELECT ‘2019-04-02 04:07:10’ AS day UNION ALL
SELECT ‘2019-05-02 05:09:13’ AS day UNION ALL
SELECT ‘2019-06-02 06:11:16’ AS day UNION ALL
SELECT ‘2019-07-02 07:13:19’ AS day UNION ALL
SELECT ‘2019-08-02 08:15:22’ AS day UNION ALL
SELECT ‘2019-09-02 09:17:25’ AS day UNION ALL
SELECT ‘2019-10-02 10:19:28’ AS day UNION ALL
SELECT ‘2019-11-02 11:21:31’ AS day UNION ALL
SELECT ‘2019-12-02 12:23:34’ AS day
) detail；

看了一下，似乎数据倾斜现在碰到的问题不是很严重，适用的方案：
数据倾斜的处理方法
join
处理null值

在关联前将两表的关联字段的Null值干掉。

如果业务需要保留，则单独拿出来计算，或将其替换为一个随机值（保证和新表对应不上）。

如果不需要，则直接删除null值