SQL是大数据从业者的必备技能,大部分的大数据技术框架也都提供了SQL的解决方案。可以说SQL是一种经久不衰、历久弥新的编程语言。尤其是在数仓领域,使用SQL更是家常便饭。本文会分享四个在面试和工作中常用的几个使用技巧,具体包括:
- 日期与期间的使用
- 临时表与Common Table Expression (WITH)
- Aggregation 与CASE WHEN的结合使用
- Window Function的其他用途
数仓?不就是写写SQL吗…
第一:日期与期间的使用
日期与时间段的筛选在工作中是经常被用到的,因为在拉取报表、仪表板和各种分析时,周、月、季度、年度的表现往往是分析需要考量的重点。
时间区段的提取:Extract
- 语法
-- field可以是day、hour、minute, month, quarter等等-- source可以是date、timestamp类型extract(field FROM source)
- 使用
SELECT extract(year FROM '2020-08-05 09:30:08'); -- 结果为 2020SELECT extract(quarter FROM '2020-08-05 09:30:08'); -- 结果为 3SELECT extract(month FROM '2020-08-05 09:30:08'); -- 结果为 8SELECT extract(week FROM '2020-08-05 09:30:08'); -- 结果为 31,一年中的第几周SELECT extract(day FROM '2020-08-05 09:30:08'); -- 结果为 5SELECT extract(hour FROM '2020-08-05 09:30:08'); -- 结果为 9SELECT extract(minute FROM '2020-08-05 09:30:08'); -- 结果为 30SELECT 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-10SELECT next_day('2020-08-05','TU') -- 下一个周二对应的日期:2020-08-11SELECT next_day('2020-08-05','WE') -- 下一个周三对应的日期:2020-08-12SELECT 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、