6、编写sql语句实现每班前三名，分数一样不并列，同时求出前三名按名次排序的一次的分差：
数据： stu表
Stu_no class score 1 1901 90 2 1901 90 3 1901 83 4 1901 60 5 1902 66 6 1902 23 7 1902 99 8 1902 67 9 1902 87
drop table stu; create table stu( Stu_no int, class string, score int ) row format delimited fields terminated by 't' ; load data local inpath './hivedata/stu.txt' into table stu;
编写sql实现，结果如下：
+--------+---------+--------+-----+----------+--+ | class | stu_no | score | rn | rn_diff | +--------+---------+--------+-----+----------+--+ | 1901 | 2 | 90 | 1 | 90 | | 1901 | 1 | 90 | 2 | 0 | | 1901 | 3 | 83 | 3 | -7 | | 1902 | 7 | 99 | 1 | 99 | | 1902 | 9 | 87 | 2 | -12 | | 1902 | 8 | 67 | 3 | -20 | +--------+---------+--------+-----+----------+--+
问什么？ 编写sql语句实现每班前三名，分数一样不并列，同时求出前三名按名次排序的一次的分差 分析： 分数一样不并列：row_number() 前三名：分组<3 按名次排序的一次的分差 有bug？？？ select class,stu_no,score,rn,rn_diff from( select class,stu_no,score. row_number() over(partition by class order by score desc) rn, score-nvl(lag(score,1) over(partition by class order by score desc,0) rn_diff from stu ）t where t.rn<4;
7、对于行列互换，你有哪些解决方式，详细说明每一种方式？ 使用语言描述即可
collect_set,collect_list,explode......
8、编写sql实现行列互换。数据如下：
id sid subject int 1,001,语文,90 2,001,数学,92 3,001,英语,80 4,002,语文,88 5,002,数学,90 6,002,英语,75.5 7,003,语文,70 8,003,数学,85 9,003,英语,90 10,003,政治,82
编写sql实现，得到结果如下：
+---------+--------+--------+--------+--------+-----------+--+ | sid | 语文 | u2.数学 | u2.英语 | u2.政治 | u2.total | +---------+--------+--------+--------+--------+-----------+--+ | 001 | 90.0 | 92.0 | 80.0 | 0.0 | 262.0 | | 002 | 88.0 | 90.0 | 75.5 | 0.0 | 253.5 | | 003 | 70.0 | 85.0 | 90.0 | 82.0 | 327.0 | | total | 248.0 | 267.0 | 245.5 | 82.0 | 842.5 | +---------+--------+--------+--------+--------+-----------+--+
drop table score; create table score( id int, sid string, subject string, score double ) row format delimited fields terminated by ',' ; load data local inpath './hivedata/score.txt' into table score;
实现行和列的互换： select sid, sum(if(subject="语文",score,0)) as `语文`, sum(if(subject="数学",score,0)) as `数学`, sum(case when subject="英语" then score else 0 end) as `英语`, sum(case when subject="政治" then score else 0 end) as `政治`, sum(score) total from score group by sid union select "total",sum(`语文`),sum(`数学`),sum(`英语`),sum(`政治`),sum(total) from ( select sid, sum(if(subject="语文",score,0)) as `语文`, sum(if(subject="数学",score,0)) as `数学`, sum(case when subject="英语" then score else 0 end) as `英语`, sum(case when subject="政治" then score else 0 end) as `政治`, sum(score) total from score group by sid )t; result: 001 90.0 92.0 80.0 0.0 262.0 002 88.0 90.0 75.5 0.0 253.5 003 70.0 85.0 90.0 82.0 327.0 total 248.0 267.0 245.5 82.0 842.5
9、编写sql实现如下：
数据： t1表
uid tags 1 1,2,3 2 2,3 3 1,2
编写sql实现如下结果：
uid tag 1 1 1 2 1 3 2 2 2 3 3 1 3 2
drop table t1; create table t1( uid int, tags string ) row format delimited fields terminated by 't' ; load data local inpath './hivedata/t1.txt' into table t1;
就是行转列： 利用一张虚拟表，进行切割，按逗号进行切割 select uid,tag from t1 lateral view explode(split(tags,",")) t as tag; result: 1 1 1 2 1 3 2 2 2 3 3 1 3 2
10、行转列
数据： T2表:
Tags 1,2,3 1,2 2,3
T3表:
id lab 1 A 2 B 3 C
根据T2和T3表的数据，编写sql实现如下结果：
+--------+--------+--+ | tags | labs | +--------+--------+--+ | 1,2 | A,B | | 1,2,3 | A,B,C | | 2,3 | B,C | +--------+--------+--+ 分析： 数字按照升序排序，字母按照字典排序
create table t2( tags string ); load data local inpath './hivedata/t2.txt' overwrite into table t2; create table t3( id int, lab string ) row format delimited fields terminated by ' ' ; load data local inpath './hivedata/t3.txt' overwrite into table t3;
select tags,
concat_ws(",",collect_set(lab)) labs
from
(select tags,lab
from
(select tags,tag
from t2 lateral view explode(split(tags,","))A as tag ) B
join t3 on B.tag = t3.id) C
group by tags;
result:
1,2 A,B
1,2,3 A,B,C
2,3 B,C
