常见sql整理 常见hive sql整理

1.求两个时间间隔在一定范围内的所有数据

selec t UNIX_TIMESTAMP(endDateTime) - UNIX_TIMESTAMP(beginDateTime)) uid from tblName where UNIX_TIMESTAMP(endDateTime) - UNIX_TIMESTAMP(beginDateTime)) > 

2.求第二大的数 去重后的 来自leetcode

select MAX(Salary) as SecondHighestSalaryfrom Employeewhere Salary < (select MAX(Salary) from Employee);

3.求含有重复的字段 来自leetcode

select Email from Person group by Email having (count(Email) > 1);

其中having是专门针对有group进行分组的数据  where是针对单条数据的条件约束

4.提取出hive里面json字段的内容

比如如下字符串

201612131.2yohobuy_ios2977iPhone OS10.1.1iPhone99c26c6b666ae72ff7104617c4aefcb9cbd590a367A8AD05-8842-46AD-820C-E70EEE5431E267641317-3E62-4A09-8D96-6A673D743FF91242x220802:00:00:00:00:005.2.05.2.0.161125000142CNzh-Hans-CN113.373750588871623.20405762814926151759663d5b01216f28f296e5f6ff65899dfbe691481591306273YB_MAIN_EVENTzh-Hans-CN;q=12016-12-13 09:07:04analytics.yhurl.com112.96.164.64eventsYH_Mall_iPhone_C1/5.2.0 (iPhone; iOS 10.1.1; Scale/3.00){"C_ID":"1","F_ID":"9","F_INDEX":1,"F_NAME":"focus","F_URL":"https:\/\/feature.yoho.cn\/1210\/1210GIFTBOY\/index.html?title=超值礼包&share_id=1481&openby:yohobuy={\"action\":\"go.h5\",\"params\":{\"param\":{\"share_id\":\"1481\",\"title\":\"超值礼包\"},\"share\":\"\/operations\/api\/v5\/webshare\/getShare\",\"shareparam\":{\"share_id\":\"1481\"},\"title\":\"超值礼包\",\"url\":\"https:\/\/feature.yoho.cn\/1210\/1210GIFTBOY\/index.html\"}}","I_INDEX":5}6d5b01216f28f296e5f6ff65899dfbe694444011h96d147c5de2b00b7521ced7db451a3b620161213

提取出里面url对应的内容

select  get_json_object(get_json_object(split(get_json_object(param,'$.F_URL'),'yohobuy=')[1],'$.params'),'$.url')  from fact_mobile_click where concat(year,month,day)=20161213  and oper_id='YB_MAIN_EVENT' limit 1 ;

5.select province,city,
rank() over (order by people desc) rank,
dense_rank() over (order by people desc) dense_rank,
row_number() over(order by people desc) row_number
from pcp
group by province,city,people;

浙江    杭州    300    1    1    1
江苏    苏州    299    2    2    2
江苏    南京    270    3    3    3
江苏    某市    200    4    4    4
浙江    温州    200    4    4    5
浙江    宁波    150    6    5    6
江苏    某某市    100    7    6    7
浙江    嘉兴    100    7    6    8

6.

+----+------------------+| Id | Email            |+----+------------------+| 1  | [e-mail] || 2  | [e-mail]  || 3  | [e-mail] |

+----+------------------+| Id | Email            |+----+------------------+| 1  | [e-mail] || 2  | [e-mail]  |+----+------------------+

delete from Person where Id not in (select * from (select min(Id) from Person group by Email) t);