数仓面试|四个在工作后才知道的SQL密技

SQL是大数据从业者的必备技能，大部分的大数据技术框架也都提供了SQL的解决方案。可以说SQL是一种经久不衰、历久弥新的编程语言。尤其是在数仓领域，使用SQL更是家常便饭。本文会分享四个在面试和工作中常用的几个使用技巧，具体包括：

• 日期与期间的使用
• 临时表与Common Table Expression (WITH)
• Aggregation 与CASE WHEN的结合使用
• Window Function的其他用途

数仓？不就是写写SQL吗… [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kMDnXByk-1596778620120)(数仓面试-四个在工作后才知道的SQL密技/0.jpg)]

第一：日期与期间的使用

日期与时间段的筛选在工作中是经常被用到的，因为在拉取报表、仪表板和各种分析时，周、月、季度、年度的表现往往是分析需要考量的重点。

时间区段的提取：Extract

• 语法

-- field可以是day、hour、minute, month, quarter等等
-- source可以是date、timestamp类型
extract(field FROM source)

• 使用

SELECT extract(year FROM '2020-08-05 09:30:08');   -- 结果为 2020
SELECT extract(quarter FROM '2020-08-05 09:30:08');   -- 结果为 3
SELECT extract(month FROM '2020-08-05 09:30:08');   -- 结果为 8
SELECT extract(week FROM '2020-08-05 09:30:08');   -- 结果为 31,一年中的第几周
SELECT extract(day FROM '2020-08-05 09:30:08');  -- 结果为 5
SELECT extract(hour FROM '2020-08-05 09:30:08');   -- 结果为 9
SELECT extract(minute FROM '2020-08-05 09:30:08');   -- 结果为 30
SELECT extract(second FROM '2020-08-05 09:30:08');   -- 结果为 8

上面可供提取的字段，不同的数据库存在些许的差异。以Hive为例，支持day, dayofweek, hour, minute, month, quarter, second, week 和 year。其中周、月、年使用最为广泛，因为无论是公司内部产品，还是商用的产品所提供的数据后台统计，周报和月报(比如近7天、近30天)最注重表现的周期。

注意：

impala支持：YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH

Hive支持：day, dayofweek, hour, minute, month, quarter, second, week 和 year

Hive是从Hive2.2.0版本开始引入该函数

周的提取

• 语法

在按照周的区间进行统计时，需要识别出周一的日期与周日的日期，这个时候经常会用到下面的函数：

next_day(STRING start_date, STRING day_of_week)
-- 返回当前日期对应的下一个周几对应的日期
-- 2020-08-05为周三
SELECT next_day('2020-08-05','MO') -- 下一个周一对应的日期：2020-08-10
SELECT next_day('2020-08-05','TU') -- 下一个周二对应的日期：2020-08-11
SELECT next_day('2020-08-05','WE') -- 下一个周三对应的日期：2020-08-12
SELECT next_day('2020-08-05','TH') -- 下一个周四对应的日期：2020-08-06，即为本周四
SELECT next_day('2020-08-05','FR') -- 下一个周五对应的日期：2020-08-07，即为本周五
SELECT next_day('2020-08-05','SA') -- 下一个周六对应的日期：2020-08-08，即为本周六
SELECT next_day('2020-08-05','SU') -- 下一个周日对应的日期：2020-08-09，即为本周日
-- 星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

• 使用

那么该如何获取当前日期所在周的周一对应的日期呢？只需要先获取当前日期的下周一对应的日期，然后减去7天，即可获得：

SELECT date_add(next_day('2020-08-05','MO'),-7);

同理，获取当前日期所在周的周日对应的日期，只需要先获取当前日期的下周一对应的日期，然后减去1天，即可获得：

select date_add(next_day('2020-08-05',