mysql、hive、presto对时间维度字段按照天、周、月、季度、年等进行聚合操作

10,262

背景

最近在做一个BI产品,遇到了一个需求,就是用户在做数据分析时,希望可以根据数据的时间维度字段来进行天、周、月、季度、年等聚合操作。

简单的描述一下。假设我们有如下一批明细数据

下单时间下单金额下单用户
2020-09-10101
2020-09-12202
2020-10-10303
2020-11-10404

如果我们想根据下单时间来统计一下每周的下单金额汇总、每月的下单金额汇总或者是每个季度的下单金额汇总,那么我们该如何做呢?

解决办法:

我们可以将时间通过函数转换到对应的每周的第一天、每月的第一天、每个季度的第一天,然后再根据这个时间进行分组和统计,就可以实现上面的这个需求了。

所以最终的目的,是我们如何把时间维度字段转换对应的每周的第一天、每月的第一天、每个季度的第一天、每年的第一天。

因为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);