Hive SQL 小练习

set hive.execution.engine=tez;
set hive.cli.print.current.db=true;
set hive.exec.dynamic.partition.mode=nonstrict;

1.求购买前后用户都做了哪些事情？

select
*
from
(
select
id,
action one,
lead(action,1,'null') over(partition by id order by ts) two,
lead(action,2,'null') over(partition by id order by ts) three
from action 
)t1 where two='buy'

2.求各路公交的上行首站，上行末站，下行首站，下行末站
哪位大佬有简单的方法给我说一下？？？？？？？？？

3.一个表两个字段，url和ts。ts是时间戳。
求对于每一类url，在哪一秒点击量最大，最大是多少？
求点击量最大的五个url?
求每一类url的占比？

第一种解法

select
*
from
(
  select
first_value(url) over(partition by url order by ct desc) url,
first_value(seconds) over(partition by url order by ct desc) seconds,
first_value(ct) over(partition by url order by ct desc) ct
from 
(
select
url,
from_unixtime(cast(ts as bigint)) seconds,
count(*) ct
from clicks group by from_unixtime(cast(ts as bigint)),url
)t1
)t2 group by url,seconds,ct

第二种解法

select
*
from
(
select
url,
seconds,
ct,
row_number() over(partition by url order by ct desc) rn
from
(
select
url,
from_unixtime(cast(ts as bigint)) seconds,
count(*) ct
from clicks group by from_unixtime(cast(ts as bigint)),url
)t1
)t2 where rn =1

第二问

select
url,
count(*) ct
from clicks group by url order by ct desc limit 5

第三问

select
url,
ct,
ct/count
from
(
select
url,
count(*) ct
from clicks group by url
)t1 join 
(
select
count(*) count
from clicks
)t2

select
*
from 
(
select
url,
count(*) over(partition by url) ct,
(count(*) over(partition by url))/count(*) over() zhanbi
from clicks
)t1 group by url,ct,zhanbi

4.列转行 要求格式为：id,语文成绩，数学成绩，英语成绩

第一种解法：

select
id,
sum(yuwen) yuwen,
sum(shuxue) shuxue,
sum(yingyu) yingyu
from
(
select
id,
score yuwen,
0 shuxue,
0 yingyu
from stu_score where subject='语文'
union all
select
id,
0,
score,
0 yingyu
from stu_score where subject='数学'
union all
select
id,
0,
0,
score
from stu_score where subject='英语'
)t1 group by id

第二种解法：

select
id,
sum(yuwen) yuwen,
sum(shuxue) shuxue,
sum(yingyu) yingyu
from
(
select
id,
if(subject='语文',score,0) yuwen,
if(subject='数学',score,0) shuxue,
if(subject='英语',score,0) yingyu
from stu_score
)t1 group by id