Hive SQL

HiveSQL

info	举例
id	1,2(唯一)
name	a，b(唯一)
sex	[male,famale]
age	[13,70]
city	beijing,shanghai
firstactivetime	2019-01-03 12:22:21
level	[1,10]
extra1	string:{“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphone”}
extra2	{“systemtype”:“ios”,“education”:“master”,“marriage_status”:“1”,“phonebrand”:“iphone”}

trade	举例
name	a,b
piece	购买数量
price	价格
pay	支付金额
goods	商品类型
time	1323334567 时间戳
dt	partition ’2019-09-09

refund	举例
name	a,b
piece	退款数量
money	退款金额
time	1323334567 时间戳
dt	partition ’2019-09-09

一、HiveSQL
1，group by 分类汇总 常用搭配聚合函数，不允许嵌套组合avg(count(*))
2，group by …… having ，having对分组对象进行筛选，仅返回符合条件的结果
–2019年8月，支付金额超过5万云的用户–

 select 
    	name,sum(pay) as total_pay
    from trade 
    where dt between ‘2019-08-01’ and ‘2019-04-30’
    group by name having sum(pay) > 50000

3,order by 进行排序 默认升序-asc，desc-降序，
4，写sql注意执行顺序
5，把时间戳转换为日期.日期转换为时间戳

--转换为日期
from_unixtime(bigint unixtime,'yyyy-MM-dd hh:mm:ss')

--转换为时间戳
unix_timestamp(string date)

6，时间间隔

datediff(string enddate,string startdate)
-- 日期增加函数
date_add(string startdate,int days)
--日期减少函数
date_sub(string startdate,int days)

7,条件函数

case when '条件' then '什么'
	 when '条件' then '什么'
	 when '条件' then '什么'
	 ……
else '什么' end

if('条件','真','假')

8,字符串函数

substr(string A,int start,int len)

9，复杂数据类型

--json格式的string
extra1(string)
{
  "systemtype":"ios","education":"master","marriage_status":"1","phonebrand":"iphone"}
获取：get_json_object(extra1,'$.education')

--map类型
extra2(map<string,string>）
{
  "systemtype":"ios","education":"master","marriage_status":"1","phonebrand":"iphone"}
获取：extra2['education']

--array类型
extra3(array(string))
获取：extra3[0]

--struct类型
extra4(struct<age:string,sex:string,addr:string>)
获取：extra4.addr    extra4.age

–练习–

激活天数距今超过300天的男女分布情况(使用info)
	
select sex,
 
	count(distinct id