按日、周、月、季、年等对时间维度字段进行 mysql、hive、presto 聚合操作。
背景
最近在做一个BI产品,遇到了一个需求,就是用户在做数据分析时,希望可以根据数据的时间维度字段来进行天、周、月、季度、年等聚合操作。
简单的描述一下。假设我们有如下一批明细数据
下单时间 | 下单金额 | 下单用户 |
---|---|---|
2020-09-10 | 10 | 1 |
2020-09-12 | 20 | 2 |
2020-10-10 | 30 | 3 |
2020-11-10 | 40 | 4 |
如果我们想根据下单时间来统计一下每周的下单金额汇总、每月的下单金额汇总或者是每个季度的下单金额汇总,那么我们该如何做呢?
解决办法:
我们可以将时间通过函数转换到对应的每周的第一天、每月的第一天、每个季度的第一天,然后再根据这个时间进行分组和统计,就可以实现上面的这个需求了。
所以最终的目的,是我们如何把时间维度字段转换对应的每周的第一天、每月的第一天、每个季度的第一天、每年的第一天。
因为BI工具需要支持对多个数据源进行分析,所以下面给出了常用的几个数据源的操作方法。
话不多说,开干!
Mysql
天
//输出2020-09-10
SELECT DATE('2020-09-10 18:16:17');
//输出2020-09-10
SELECT DATE('2020-09-10');
周
//输出 2020-09-21 9.24是周四 对应的周一是9.21
SELECT DATE_SUB('2020-09-24',INTERVAL WEEKDAY('2020-09-24') day);
月
//输出 2020-09-21
select DATE_SUB('2020-09-24', INTERVAL DAYOFMONTH('2020-09-24') - 1 DAY);
季度
//输出2020-07-01
SELECT (MAKEDATE(YEAR('2020-09-24'), 1) + INTERVAL QUARTER('2020-09-24') QUARTER - INTERVAL 1 Quarter);
年
//输出2020-01-01
SELECT MAKEDATE(YEAR('2020-09-24'),1);
Hive
天
//输出 2020-09-24
select TO_DATE('2020-09-24 11:21:23');
//输出 2020-09-24
select TO_DATE('2020-09-24');
周
//输出 2020-09-21
select date_sub(next_day('2020-09-24','MO'),7);
月
//输出 2020-09-01
select trunc('2020-09-24', 'MM');
季度
//输出 2020-07-01
select to_date(concat(year('2020-09-24'),'-',lpad(ceil(month('2020-09-24')/3) * 3 -2,2,0),'-01'));
年
//输出 2020-01-01
select trunc('2020-09-24', 'YY');
Presto
presto在这几个sql引擎中是最麻烦的,因为presto对数据类型要求比较严格,并且不能够对数据进行隐式的类型转换。所以假设你的表中存储的时间字段不是用时间类型存储的话,那么使用presto的时间函数将会很恶心,下面举例说明。
现有一张presto表如下:
假如我们想统计create_time列的对应的时间在本周的哪一天 可以使用day_of_month
这个函数。
操作如下:
由上图我们可以清晰的得知,presto的时间函数只接受 入参为时间格式的字段,那么我们hive中有很多表中的日期都是按照string类型来存储。这样的话,该如何解决呢?
其实不管是2020-09-24
还是2020-09-24 11:00:00
这种类型的字符串,我们只需要截取前10位字符串,然后将这个字符串转为日期,那么解决问题。
解决办法如下:
select day_of_month(date(substr(create_time,1,10))) from student2;
下面具体讲述各个时间维度聚合的操作
天
如果时间字段是字符串类型:
//将字符串的时间字段 转换为时间类型
select date_format(date(substr('2020-09-10 12:00:00',1,10)),'%Y-%m-%d');
如果时间字段是datetime类型:
//无需格式转换 只需要根据自己需求日期格式化一下即可
select date_format(create_time,'%Y-%m-%d')
周
如果时间字段是字符串类型:
//输出2020-09-21
select date_add('day', -(day_of_week(date(substr('2020-09-24',1,10)))-1),date(substr('2020-09-24',1,10)))
如果时间字段是datetime类型:
select date_add('day', -(day_of_week('create_time')-1),%s)
月
如果时间字段是字符串类型:
//输出2020-09-01
select date_add('day', -(day_of_month(date(substr('2020-09-24',1,10)))-1),date(substr('2020-09-24',1,10)))
如果时间字段是datetime类型:
select date_add('day', -(day_of_month('create_time')-1),%s);
上一篇: Bug攻略