常见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 SecondHighestSalary
from Employee
where 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字段的内容

比如如下字符串

20161213	1.2	yohobuy_ios	2977	iPhone OS	10.1.1	iPhone	99c26c6b666ae72ff7104617c4aefcb9cbd590a3	67A8AD05-8842-46AD-820C-E70EEE5431E2	67641317-3E62-4A09-8D96-6A673D743FF9	1242x2208	02:00:00:00:00:00	5.2.0	5.2.0.1611250001	4	2	CN	zh-Hans-CN	113.3737505888716	23.20405762814926	15175966	3	d5b01216f28f296e5f6ff65899dfbe69	1481591306273	YB_MAIN_EVENT	zh-Hans-CN;q=1	2016-12-13 09:07:04	analytics.yhurl.com	112.96.164.64	events	YH_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}	6	d5b01216f28f296e5f6ff65899dfbe69	44	4401	1	h96d147c5de2b00b7521ced7db451a3b6	2016	12	13

提取出里面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  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

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