掌握数仓3个常用函数,提数时可能事倍功半,准时下班回家吃饭。
1、时间区段的提取:Extract
语法
extract(field FROM source)
其中field可以是day, hour, minute, month, quarter, second, week 和 year等
source可以是date、timestamp类型
使用
SELECT extract(year FROM '2021-07-29 21:30:09'); -- 结果为 2021
SELECT extract(quarter FROM '2021-07-29 21:30:09'); -- 结果为 3
SELECT extract(month FROM '2021-07-29 21:30:09'); -- 结果为 7
SELECT extract(week FROM '2021-07-29 21:30:09'); -- 结果为 30,一年中的第几周
SELECT extract(day FROM '2021-07-29 21:30:09'); -- 结果为 29
SELECT extract(hour FROM '2021-07-29 21:30:09'); -- 结果为 21
SELECT extract(minute FROM '2021-07-29 21:30:09'); -- 结果为 30
SELECT extract(second FROM '2021-07-29 21:30:09'); -- 结果为 9
2、周的提取 next_day
语法
-- 返回当前日期对应的下一个周几对应的日期
-- 2021-07-29是周四
SELECT next_day('2021-07-29','MO'); -- 下一个周一对应的日期:2021-08-02
SELECT next_day('2021-07-29','TU'); -- 下一个周二对应的日期:2021-08-03
SELECT next_day('2021-07-29','WE'); -- 下一个周三对应的日期:2021-08-04
SELECT next_day('2021-07-29','TH'); -- 下一个周四对应的日期:2021-08-05
SELECT next_day('2021-07-29','FR'); -- 下一个周五对应的日期:2021-07-30,即为本周五
SELECT next_day('2021-07-29','SA'); -- 下一个周六对应的日期:2021-07-31,即为本周六
SELECT next_day('2021-07-29','SU'); -- 下一个周日对应的日期:2021-08-01,即为本周日
-- 其中MO、TU为星期英文的字母开头
-- 星期一到星期日的英文
(Monday,Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday)
使用
如果我们想获取当前日期所在周的周一对应的日期,怎么做?其实不难,我们只需要先获取当前日期的下周一对应的日期,然后减去7天即可
SELECT date_add(next_day('2021-07-29','MO'),-7); --结果2021-07-26 星期一
同样,要是想获取当前日期所在周的周日对应的日期,只需要先获取当前日期的下周一对应的日期,然后减去1天,即可获得:
select date_add(next_day('2021-07-29','MO'),-1) --结果2021-08-01 星期日
3、 月的提取 last_day
语法
如何将月份从单一日期提取出来呢,LAST_DAY这个函数可以将每个月中的日期变成该月的最后一天(28号,29号,30号或31号),如下:
last_day(STRING date)
使用
SELECT last_day('2021-07-29'); -- 2021-07-31
除了上面的方式,也可以使用date_format函数,比如:
SELECT date_format('2021-07-29','yyyy-MM');
-- 2021-07
-- 返回加减月份之后对应的日期
select add_months('2021-07-29', -1)
--结果 2021-06-29
-- 返回当前日期的月初日期
select trunc("2021-07-29",'MM')
--结果 2021-07-01
使用add_months,减N个月的用法,可以刚好取到整数月的数据,但如果加上trunc()函数,则会从前N个月1号开始取值。
-- 选取2021-06-29到2021-07-29 这个区间的数据
BETWEEN add_months('2021-07-29', -1) AND '2021-07-29'
-- 选取2021-06-01到2021-07-29之间所有数据
BETWEEN select add_months(trunc("2021-07-29",'MM'),-1) AND '2021-07-29'
-END-
微信公众号:爱开发