本篇文章主要是对入职以来两个多月的hive SQL查询进行总结,希望在不断实习和做数据需求中积累更多的经验。加油!!!
有个注意事项要提前说明一下,Hive SQL和SQL还是有些区别的,所以有些函数没往上写,部分函数说明是借鉴别的博客和官网说明,不是抄袭贴。
昂,我来二更了,我们用的smart出数据报表,其中报表有两种出数方式,一种是lighting(支持大量数据的逻辑计算,需要我们在我们的库中先处理数据再呈现在smart中),一种是MySQL(支持小量数据,这种是先建表,数据从他们的sql库中处理数据,小量数据的处理也不容易崩)
一、实际数据中的各种时间类型
1.datetime——标准时间数据类型
● datetime类型中其实包含几种不同的时间类型,例如date,time等等。
● datetime类型的数据可以直接利用datediff()或者dateadd()函数进行时间上的加减
● DATETIME类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值,支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
2.string——字符串时间数据类型
有时一些时间数据会被保存成字符串格式,方便进行不同的数据转化
DATE只保存日期,不保存时分秒
3.timestamp——时间戳数据类型
TIMESTAMP列类型提供一种类型,你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列,只有第一个自动更新。
自动更新第一个TIMESTAMP列在下列任何条件下发生:
列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。
列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。(注意一个UPDATE设置一个列为它已经有的值,这将不引起TIMESTAMP列被更新,因为如果你设置一个列为它当前的值,MySQL为了效率而忽略更改。)
你明确地设定TIMESTAMP列为NULL.
除第一个以外的TIMESTAMP列也可以设置到当前的日期和时间,只要将列设为NULL,或NOW()。
自动更新TIMESTAMP时建表语句应为如下类似
CREATE TABLE `t_jindou_xx` (
`account` varchar(18) NOT NULL default '',
`money` decimal(10,2) NOT NULL default '0.00',
`update_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
TIMESTAMP类型保存的值不能比1970早或比2037晚,这意味着,一个日期例如'1968-01-01',当作为一个DATETIME或DATE值合法时,它不是一个正确TIMESTAMP值,并且 如果赋值给这样一个对象,它将被变换到0。
4.timestamp和datetime比较
datetime
1、允许为空值,可以自定义值,系统不会自动修改其值。
2、不可以设定默认值,所以在不允许为空值的情况下,必须手动指定datetime字段的值才可以成功插入数据。
3、虽然不可以设定默认值,但是可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。
结论:datetime类型适合用来记录数据的原始的创建时间,因为无论你怎么更改记录中其他字段的值,datetime字段的值都不会改变,除非你手动更改它。
timestamp
1、允许为空值,但是不可以自定义值,所以为空值时没有任何意义。
2、默认值为CURRENT_TIMESTAMP(),其实也就是当前的系统时间。
3、数据库会自动修改其值,所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值,你只需要在设计表的时候添加一个timestamp字段即可,插入后该字段的值会自动变为当前系统时间。
4、以后任何时间修改表中的记录时,对应记录的timestamp值会自动被更新为当前的系统时间。
结论:timestamp类型适合用来记录数据的最后修改时间,因为只要你更改了记录中其他字段的值,timestamp字段的值都会被自动更新。
二、各种时间类型的处理函数(部分为字符串处理函数)
目前实习遇到过很多的时间数据,在时间数据中有不同的类型,一般的时间数据最终都要输出成datetime类型的,方便进行各种时间(例如datediff())的处理
1.to_char(data,‘format’)
to_char(data,'format')
函数是将数据变成想要的数据格式
例如:service_time为datetime类型,2014-06-04 16:30:00为其中的一个数据
select to_char(service_time,'yyyy-mm-dd')//输出为2014-06-04
select to_char(service_time, '阿里金融yyyyddmm')//输出为阿里金融20140604
注意,此时的输出是字符串格式,要想用datediff(time1,time2,‘dd’)函数,其中的time1和time2必须是datetime类型
2.to_date(datetime, ‘format’)
用途: 将一个字符串按照format指定的格式转成日期值。
参数说明:
● datetime: string类型,要转换的字符串格式的日期值,若输入为bigint, double, datetime类型会隐式转换为string类型后参与运算,为其它类型抛异常,为空串时抛异常。
● format: string类型常量,日期格式。非常量或其他类型会引发异常。format参数中标识的间类型的元素(如年、月、日等),可选值见上表10-1,其他字符作为无用字符在parse时忽略。format参数至少包含’yyyy’,否则引发异常,如果format string中出现多余的格式串,则只取第一个格式串对应的日期数值,其余的会被视为分隔符。
如to_date('1234-2234 ', 'yyyy-yyyy ')会返回1234-01-01 00:00:00
返回值:
返回datetime类型。若任一输入为NULL,返回NULL值。
select to_date(time ,'yyyy-mm-dd')//其中time为字符串类型,最终返回的是时间类型
3.cast(expr as)
用途:
将表达式的结果转换成目标类型,如cast(‘1’ as BIGINT)将字符串’1’转为整数类型的1,如果转换不成功或不支持的类型转换会引发异常。
参数说明:
● cast(double as bigint),将double值trunc成bigint
● cast(string as bigint) 在将字符串转为BIGINT时,如果字符串中是以整型表达的数字,会直接转为BIGINT类型。如果字符串中是以浮点数或指数形式表达的数字,则会先转为DOUBLE类型,再转为BIGINT类型。
● cast(string as datetime) 或 cast(datetime as string)时,会采用默认的日期格式yyyy-mm-dd hh:mi:ss。
下面的bind_time的数据类型是timestamp
其中的一个数据为:2019-03-18 11:42:18.000
select cast(bind_time as datetime)//输出为2019-03-18 11:42:18
4.substr(string1, start_position[, length])
用途:
返回字符串string1从start_position开始长度为length的子串。
参数说明:
● string1: string类型,若输入为bigint, double, datetime类型会隐式转换为string后参与运算,其它类型报异常。
● start_position: bigint类型,当start_position为负数时表示开始位置是从字符串的结尾往前倒数,最后一个字符是-1。其它类型抛异常。
● length: bigint类型,大于0,其它类型或小于等于0抛异常。子串的长度。
返回值:
在string1中的子串,若任一输入为NULL,返回NULL.
备注:
当length被省略时,返回到string1结尾的子串。
示例:
● substr("abc", 2)
返回bc
● substr("abc",1,2)
返回b
这个函数在时间间隔计算中很好用,可以取到每个时间的月或年再加减’
select bind_time, SUBSTR(bind_time,7 ), SUBSTR(bind_time,1,7 )
//输出为:2019-03-18 11:42:18.000 3-18 11:42:18 2019-03
5.concat(string A, string B…)
参数说明:
● A,B等为string类型,若输入为bigint, double, datetime类型会隐式转换为string后参与运算,其它类型报异常。
返回值:
string
用途:
返回值是将参数中的所有字符串连接在一起的结果。
备注:
如果没有参数或者某个参数为NULL,结果均返回NULL
concat(), concat(null, 'a'), concat('a', null, 'b')返回值都是NULL。
select concat('a', 'null', 'b')//输出为:anullb
三、不同的取数方法
1.统计x天以上首次下单的用户数据
首先解释一下,x天以上首次下单的含义是在最近的x天内数据表中没有记录但在现在有记录的用户,不用考虑在x天以前是否有记录。
例如:想要取时间段为:7月1号到7月31号,之前30天未下单的首次下单的用户数,要弄清楚这个30天是静态的30 还是动态的30天。
静态的30天
:无论统计的是7月1号的数据还是7月31号的数据,标签都是7月1号以前30天未下单的用户
动态的30天
:7月1号统计的是前30天也就是6月1号到6月30号未下单的用户,7月2号统计的是6月2号到7月1号未下单的用户数据
静态30天的取数逻辑:
取用户的订单表和前三十天的订单数据进行 left join
,对前三十天数据为空的用户打上未下单标签,按照日期分组,
count( distinct case when 用户标签为未下单 then user_id else null end )
动态30天的取数逻辑:
取想要的时间段内的用户订单数据(包含time1),和用户订单的整体数据(包含time2)进行关联,关联时设置条件time1>time2,这样可以筛选出不在当前时间之后的用户订单信息,这时取的max(time2) as max_time
就是在我们统计的时间之前的最近下单时间,如果这个时间和time1的时间差大于30就认为是30天以上首次下单的用户,这时还可以顺便统计新用户的个数max_time is null
,以下是一个比较经典的例子:
select time1,
count(distinct case when tag_time=1 then user_id else null end ) 30天以上首次下单用户数,
count (distinct case when max_time is null then user_id else null end ) 新用户数
from(
SELECT user_id,time1,max_time,
case when DATEDIFF(to_date(time1 ,'yyyy-mm-dd') ,to_date(max_time ,'yyyy-mm-dd'),'dd')>30 then 1 else 0 end tag_time
from(
SELECT c1.user_id,time1,max(time2) max_time
FROM
(
select user_id,to_char(service_time,'yyyy-mm-dd') time1
from table1
where to_char(service_time,'yyyy-mm-dd') between '2021-07-01' and '2021-07-30'
)c1
left join
(
select user_id,to_char(service_time,'yyyy-mm-dd') time2
from table1
) c2 on c1.user_id= c2.user_id and time1>time2
group by c1.user_id,time1
)
)
group by time1
2.在取数结果中显示数据的类别(group by 中使用case when )
现实情境:订单等候时长(wait_time)分布(40以下,40-60,60以上的数量),wait_time is null 为0,(0,40]为1,(40,60]为2,>60为3,以下为部分结果:
城市 | 类别 | 时长个数 |
---|---|---|
北京 | 0 | 50 |
北京 | 1 | 100 |
北京 | 2 | 47 |
北京 | 3 | 68 |
select city_name
,case when wait_time is null then 0
when wait_time between 0 and 39 then 1
when wait_time between 40 and 59 then 2
when wait_time between 60 and 120 then 3
else 4 end as wait_time_lev
,count(a.order_id) as order_cnt
from table2 a
left join ( select order_id
from table2 ) b on a.order_id = b.order_id
where city_name ='北京'
group by city_name
,case when wait_time is null then 0
when wait_time between 0 and 39 then 1
when wait_time between 40 and 59 then 2
when wait_time between 60 and 120 then 3
else 4 end;
3.次日留存,三日留存,七日留存用户问题
这个问题是在实习中遇到的最多的问题,而且每次都要纠结好久,不知道怎么给用户打标签[裂开.jpg]
不管次日,3日还是7日,最终都是在看用户后续会不会下单。
解释一下留存的定义:
7日留存
指的是在7天内再次下单
3日留存
是3日内再次下单,次日就不解释了
取数逻辑:取两张订单表,一张是近期的用户下单情况,一张是x日留存的时间段数据,进行关联;取出两个表中的time1和time2(都是datetime类型的字段),利用datediff(time2,time1,'dd')<=x
来判断是否x日留存
下面是一个7日留存的具体的例子:
select m.service_date1
,count(distinct case when datediff(cast(service_date2 as datetime),cast(service_date1 as datetime),'dd')<=7 then m.user_id else null end) Y
from
(
SELECT service_date1,a.user_id
FROM
(
SELECT to_char(service_time,'yyyy-mm-dd') as service_date1 ,user_id
from table1
Where to_char(service_time,'yyyy-mm-dd') between '2021-07-05' and '2021-07-14'
group by to_char(service_time,'yyyy-mm-dd'),user_id
) a
group by service_date1,a.user_id
)m
left join
(
SELECT to_char(service_time,'yyyy-mm-dd') service_date2,user_id
from table1
Where to_char(service_time,'yyyy-mm-dd') between '2021-07-12' and '2021-07-14'
group by to_char(service_time,'yyyy-mm-dd'),user_id
) n
on m.user_id = n.user_id
group by m.service_date1;
4.月留存,月持续留存
其中的${time}
是我们在取数时可以手动输入的日期
max( s_time ) as last_s_time
max( case when substr(s_time,1,7) =
substr(dateadd( to_date('${time}','yyyy-mm-dd'),-1,'mm' ),1,7)
then s_time else null end ) as 上个月是否有记录
max( case when substr(s_time,1,7) <=
substr(dateadd( to_date('${time}','yyyy-mm-dd'),-2,'mm' ),1,7)
then s_time else null end ) as 上上月是否有记录
5.月峰值数据量(排名问题)
这种取数需求一般是取每一组排名前几的数据,涉及到的函数为
row_number() over(partition by 分组列 order by 排序列 desc)
在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行
例如,我们想看每个城市的历史峰值月及其峰值量
select *
from
(
select ROW_NUMBER()over(partition by 城市 order by order_cnt desc) rowId,*
from
(
SELECT a.city_name 城市,to_char(service_time,'yyyy-mm-dd') as 日期 ,
count(order_id) as 月单量
from table1 a
group by a.city_name,to_char(service_time,'yyyy-mm-dd')
)
) cnt_Records
where rowId=1;//想取前X个数只需要rowID<=X即可
最终输出大致如下:
rowid | 城市 | 日期 | 月单量 |
---|---|---|---|
1 | 北京 | 2014-01-03 | 200000 |
1 | 上海 | 2021-01-01 | 25000 |
1 | 杭州 | 2020-06-09 | 8000 |
1 | 南京 | 2014-03-01 | 5000 |
6.行转列问题
假设原表为:
日期 | 城市 | 订单类型 | 订单量 | 订单总金额 | 退单量 |
---|---|---|---|---|---|
2021-08-09 | 北京 | 服装类 | 30000 | 480000 | 2500 |
2021-08-09 | 北京 | 零食类 | 31000 | 500000 | 2300 |
2021-08-09 | 北京 | 家纺类 | 2500 | 9000 | 100 |
2021-08-09 | 上海 | 服装类 | 4000 | 60000 | 900 |
2021-08-09 | 上海 | 零食类 | 40000 | 900000 | 2900 |
2021-08-09 | 上海 | 家纺类 | 8000 | 10000 | 3000 |
2021-08-09 | 南京 | 零食类 | 23000 | 450000 | 2100 |
2021-08-09 | 南京 | 家纺类 | 3000 | 20000 | 300 |
将上表行转列,最终要达到的效果如下:
日期 | 城市 | 统计维度 | 服装类 | 零食类 | 家纺类 |
---|---|---|---|---|---|
2021-08-09 | 北京 | 订单量 | 30000 | 31000 | 2500 |
2021-08-09 | 北京 | 订单总金额 | 480000 | 500000 | 9000 |
2021-08-09 | 北京 | 退单量 | 2500 | 2300 | 100 |
2021-08-09 | 上海 | 订单量 | 4000 | 40000 | 8000 |
2021-08-09 | 上海 | 订单总金额 | 60000 | 900000 | 10000 |
2021-08-09 | 上海 | 退单量 | 900 | 2900 | 3000 |
2021-08-09 | 南京 | 订单量 | - | 23000 | 3000 |
2021-08-09 | 南京 | 订单总金额 | - | 450000 | 20000 |
2021-08-09 | 南京 | 退单量 | - | 2100 | 300 |
select '${d_date}' as stat_date,city_name, '订单量' as tag,
nvl(sum( case when a.user_tag='服装类' then cnt_d else null end ),0) 服装类,
sum( case when a.user_tag='零食类' then cnt_d else null end ) 零食类,
sum( case when a.user_tag='家纺类' then cnt_d else null end ) 家纺类
from table1
where dt= '${d_date}'
group by city_name
union ALL
select '${d_date}' as stat_date,city_name, '订单总金额' as tag,
nvl(sum( case when a.user_tag='服装类' then cnt_e else null end ),0) 服装类,
sum( case when a.user_tag='零食类' then cnt_e else null end ) 零食类,
sum( case when a.user_tag='家纺类' then cnt_e else null end ) 家纺类
from table1
where dt= '${d_date}'
group by city_name
union ALL
select '${d_date}' as stat_date,city_name, '退单量' as tag,
nvl(sum( case when a.user_tag='服装类' then cnt_t else null end ),0) 服装类,
sum( case when a.user_tag='零食类' then cnt_t else null end ) 零食类,
sum( case when a.user_tag='家纺类' then cnt_t else null end ) 家纺类
from table1
where dt= '${d_date}'
group by city_name