hive學(xué)習(xí)之經(jīng)典sql 50題 hive版

建表:

create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';

create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';

create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';

生成數(shù)據(jù)

vi /export/data/hivedatas/student.csv

01 趙雷 1990-01-01 男
02 錢(qián)電 1990-12-21 男
03 孫風(fēng) 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吳蘭 1992-03-01 女
07 鄭竹 1989-07-01 女
08 王菊 1990-01-20 女

vi /export/data/hivedatas/course.csv

01  語(yǔ)文  02
02  數(shù)學(xué)  01
03  英語(yǔ)  03

vi /export/data/hivedatas/teacher.csv

01  張三
02  李四
03  王五

vi /export/data/hivedatas/score.csv

01  01  80
01  02  90
01  03  99
02  01  70
02  02  60
02  03  80
03  01  80
03  02  80
03  03  80
04  01  50
04  02  30
04  03  20
05  01  76
05  02  87
06  01  31
06  03  34
07  02  89
07  03  98

導(dǎo)數(shù)據(jù)到hive

load data local inpath '/export/data/hivedatas/student.csv' into table student;

load data local inpath '/export/data/hivedatas/course.csv' into table course;

load data local inpath '/export/data/hivedatas/teacher.csv' into table teacher;

load data local inpath '/export/data/hivedatas/score.csv' into table score;

–注:–hive查詢(xún)語(yǔ)法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
    FROM table_reference
    [WHERE where_condition]
    [GROUP BY col_list [HAVING condition]]
    [CLUSTER BY col_list
      | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
    ]
    [LIMIT number]

– 1、查詢(xún)"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù):

select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
  join score a on student.s_id=a.s_id and a.c_id='01'
  left join score b on student.s_id=b.s_id and b.c_id='02'
where  a.s_score>b.s_score;

–答案2

select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on  a.c_id='01'
join score b on  b.c_id='02'
where  a.s_id=student.s_id and b.s_id=student.s_id and a.s_score>b.s_score;

– 2髓需、查詢(xún)"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù):

select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on student.s_id=a.s_id and a.c_id='01'
left join score b on student.s_id=b.s_id and b.c_id='02'
where a.s_score<b.s_score;

–答案2

select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on  a.c_id='01'
join score b on  b.c_id='02'
where  a.s_id=student.s_id and b.s_id=student.s_id and a.s_score<b.s_score;

– 3校仑、查詢(xún)平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī):

select  student.s_id,student.s_name,tmp.平均成績(jī) from student
  join (
    select score.s_id,round(avg(score.s_score),1)as 平均成績(jī)
        from score group by s_id)as tmp
  on tmp.平均成績(jī)>=60
where student.s_id = tmp.s_id

–答案2

select  student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成績(jī) from student
join score on student.s_id = score.s_id
group by student.s_id,student.s_name
having avg (score.s_score) >= 60;

– 4冒滩、查詢(xún)平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī):
– (包括有成績(jī)的和無(wú)成績(jī)的)

select  student.s_id,student.s_name,tmp.avgScore from student
join (
select score.s_id,round(avg(score.s_score),1)as avgScore from score group by s_id)as tmp
on tmp.avgScore < 60
where student.s_id=tmp.s_id
union all
select  s2.s_id,s2.s_name,0 as avgScore from student s2
where s2.s_id not in
    (select distinct sc2.s_id from score sc2);

–答案2

select  score.s_id,student.s_name,round(avg (score.s_score),1) as avgScore from student
inner join score on student.s_id=score.s_id
group by score.s_id,student.s_name
having avg (score.s_score) < 60
union all
select  s2.s_id,s2.s_name,0 as avgScore from student s2
where s2.s_id not in
    (select distinct sc2.s_id from score sc2);

– 5畦娄、查詢(xún)所有同學(xué)的學(xué)生編號(hào)俩垃、學(xué)生姓名铸豁、選課總數(shù)蜈垮、所有課程的總成績(jī):

select student.s_id,student.s_name,(count(score.c_id) )as total_count,sum(score.s_score)as total_score
from student
left join score on student.s_id=score.s_id
group by student.s_id,student.s_name ;

– 6耗跛、查詢(xún)"李"姓老師的數(shù)量:

select t_name,count(1) from teacher  where t_name like '李%' group by t_name;

– 7、查詢(xún)學(xué)過(guò)"張三"老師授課的同學(xué)的信息:

select student.* from student
join score on student.s_id =score.s_id
join  course on course.c_id=score.c_id
join  teacher on course.t_id=teacher.t_id and t_name='張三';

– 8攒发、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師授課的同學(xué)的信息:

select student.* from student
left join (select s_id from score
      join  course on course.c_id=score.c_id
      join  teacher on course.t_id=teacher.t_id and t_name='張三')tmp
on  student.s_id =tmp.s_id
where tmp.s_id is null;

– 9调塌、查詢(xún)學(xué)過(guò)編號(hào)為"01"并且也學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息:

select * from student
join (select s_id from score where c_id =1 )tmp1
    on student.s_id=tmp1.s_id
join (select s_id from score where c_id =2 )tmp2
    on student.s_id=tmp2.s_id;

– 10、查詢(xún)學(xué)過(guò)編號(hào)為"01"但是沒(méi)有學(xué)過(guò)編號(hào)為"02"的課程的同學(xué)的信息:

select student.* from student
join (select s_id from score where c_id =1 )tmp1
    on student.s_id=tmp1.s_id
left join (select s_id from score where c_id =2 )tmp2
    on student.s_id =tmp2.s_id
where tmp2.s_id is null;

– 11惠猿、查詢(xún)沒(méi)有學(xué)全所有課程的同學(xué)的信息:
–先查詢(xún)出課程的總數(shù)量

   select count(1) from course;

–再查詢(xún)所需結(jié)果

select student.* from student
left join(
      select s_id
        from score
          group by s_id
            having count(c_id)=3)tmp
on student.s_id=tmp.s_id
where tmp.s_id is null;

–方法二(一步到位):

select student.* from student
join (select count(c_id)num1 from course)tmp1
left join(
      select s_id,count(c_id)num2
        from score group by s_id)tmp2
on student.s_id=tmp2.s_id and tmp1.num1=tmp2.num2
where tmp2.s_id is null;

– 12羔砾、查詢(xún)至少有一門(mén)課與學(xué)號(hào)為"01"的同學(xué)所學(xué)相同的同學(xué)的信息:

select student.* from student
join (select c_id from score where score.s_id=01)tmp1
join (select s_id,c_id from score)tmp2
    on tmp1.c_id =tmp2.c_id and student.s_id =tmp2.s_id
where student.s_id  not in('01')
group by student.s_id,s_name,s_birth,s_sex;

– 13、查詢(xún)和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息:
–備注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 實(shí)現(xiàn)

select student.*,tmp1.course_id from student
join (select s_id ,concat_ws('|', collect_set(c_id)) course_id from score
      group by s_id having s_id not in (1))tmp1
  on student.s_id = tmp1.s_id
join (select concat_ws('|', collect_set(c_id)) course_id2
            from score  where s_id=1)tmp2
      on tmp1.course_id = tmp2.course_id2;

– 14紊扬、查詢(xún)沒(méi)學(xué)過(guò)"張三"老師講授的任一門(mén)課程的學(xué)生姓名:

select student.* from student
  left join (select s_id from score
          join (select c_id from course join  teacher on course.t_id=teacher.t_id and t_name='張三')tmp2
          on score.c_id=tmp2.c_id )tmp
  on student.s_id = tmp.s_id
  where tmp.s_id is null;

– 15蜒茄、查詢(xún)兩門(mén)及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī):

select student.s_id,student.s_name,tmp.avg_score from student
inner join (select s_id from score
      where s_score<60
        group by score.s_id having count(s_id)>1)tmp2
on student.s_id = tmp2.s_id
left join (
    select s_id,round(AVG (score.s_score)) avg_score
      from score group by s_id)tmp
      on tmp.s_id=student.s_id;

– 16餐屎、檢索"01"課程分?jǐn)?shù)小于60檀葛，按分?jǐn)?shù)降序排列的學(xué)生信息:

select student.*,s_score from student,score
where student.s_id=score.s_id and s_score<60 and c_id='01'
order by s_score desc;

– 17、按平均成績(jī)從高到低顯示所有學(xué)生的所有課程的成績(jī)以及平均成績(jī):

select a.s_id,tmp1.s_score as chinese,tmp2.s_score as math,tmp3.s_score as english,
    round(avg (a.s_score),2) as avgScore
from score a
left join (select s_id,s_score  from score s1 where  c_id='01')tmp1 on  tmp1.s_id=a.s_id
left join (select s_id,s_score  from score s2 where  c_id='02')tmp2 on  tmp2.s_id=a.s_id
left join (select s_id,s_score  from score s3 where  c_id='03')tmp3 on  tmp3.s_id=a.s_id
group by a.s_id,tmp1.s_score,tmp2.s_score,tmp3.s_score order by avgScore desc;

– 18.查詢(xún)各科成績(jī)最高分腹缩、最低分和平均分：以如下形式顯示：課程ID屿聋，課程name，最高分藏鹊，最低分润讥，平均分，及格率盘寡，中等率楚殿，優(yōu)良率，優(yōu)秀率:
–及格為>=60竿痰，中等為：70-80脆粥，優(yōu)良為：80-90，優(yōu)秀為：>=90

select course.c_id,course.c_name,tmp.maxScore,tmp.minScore,tmp.avgScore,tmp.passRate,tmp.moderate,tmp.goodRate,tmp.excellentRates from course
join(select c_id,max(s_score) as maxScore,min(s_score)as minScore,
    round(avg(s_score),2) avgScore,
    round(sum(case when s_score>=60 then 1 else 0 end)/count(c_id),2)passRate,
    round(sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(c_id),2) moderate,
    round(sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(c_id),2) goodRate,
    round(sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(c_id),2) excellentRates
from score group by c_id)tmp on tmp.c_id=course.c_id;

– 19影涉、按各科成績(jī)進(jìn)行排序变隔，并顯示排名:
– row_number() over()分組排序功能(mysql沒(méi)有該方法)

select s1.*,row_number()over(order by s1.s_score desc) Ranking
    from score s1 where s1.c_id='01'order by noRanking asc
union all select s2.*,row_number()over(order by s2.s_score desc) Ranking
    from score s2 where s2.c_id='02'order by noRanking asc
union all select s3.*,row_number()over(order by s3.s_score desc) Ranking
    from score s3 where s3.c_id='03'order by noRanking asc;

– 20、查詢(xún)學(xué)生的總成績(jī)并進(jìn)行排名:

select score.s_id,s_name,sum(s_score) sumscore,row_number()over(order by sum(s_score) desc) Ranking
  from score ,student
    where score.s_id=student.s_id
    group by score.s_id,s_name order by sumscore desc;

后續(xù)部分參見(jiàn):
https://blog.csdn.net/Thomson617/article/details/83280617
Hive下的SQL語(yǔ)法總結(jié)：

(1).Hive不支持join的非等值連接,不支持or
分別舉例如下及實(shí)現(xiàn)解決辦法。
  不支持不等值連接
       錯(cuò)誤:select * from a inner join b on a.id<>b.id
       替代方法:select * from a inner join b on a.id=b.id and a.id is null;
 不支持or
       錯(cuò)誤:select * from a inner join b on a.id=b.id or a.name=b.name
       替代方法:select * from a inner join b on a.id=b.id
                union all
                select * from a inner join b on a.name=b.name
  兩個(gè)sql union all的字段名必須一樣或者列別名要一樣。
        
(2).分號(hào)字符:不能智能識(shí)別concat(‘;’,key)篷帅，只會(huì)將‘桥帆；’當(dāng)做SQL結(jié)束符號(hào)。
    ?分號(hào)是SQL語(yǔ)句結(jié)束標(biāo)記脸哀，在HiveQL中也是图筹，但是在HiveQL中篇裁，對(duì)分號(hào)的識(shí)別沒(méi)有那么智慧柑爸，例如：
        ?select concat(key,concat(';',key)) from dual;
    ?但HiveQL在解析語(yǔ)句時(shí)提示：
        FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification
    ?解決的辦法是检柬，使用分號(hào)的八進(jìn)制的ASCII碼進(jìn)行轉(zhuǎn)義，那么上述語(yǔ)句應(yīng)寫(xiě)成：
        ?select concat(key,concat('\073',key)) from dual;

(3).不支持INSERT INTO 表 Values（）, UPDATE, DELETE等操作.這樣的話竖配，就不要很復(fù)雜的鎖機(jī)制來(lái)讀寫(xiě)數(shù)據(jù)。
    INSERT INTO syntax is only available starting in version 0.8里逆。INSERT INTO就是在表或分區(qū)中追加數(shù)據(jù)进胯。

(4).HiveQL中String類(lèi)型的字段若是空(empty)字符串, 即長(zhǎng)度為0, 那么對(duì)它進(jìn)行IS NULL的判斷結(jié)果是False，使用left join可以進(jìn)行篩選行原押。

(5).不支持 ‘< dt <’這種格式的范圍查找胁镐，可以用dt in(”,”)或者between替代。

(6).Hive不支持將數(shù)據(jù)插入現(xiàn)有的表或分區(qū)中诸衔，僅支持覆蓋重寫(xiě)整個(gè)表盯漂，示例如下：
    INSERT OVERWRITE TABLE t1 SELECT * FROM t2;
    
(7).group by的字段,必須是select后面的字段，select后面的字段不能比group by的字段多.
    如果select后面有聚合函數(shù),則該select語(yǔ)句中必須有g(shù)roup by語(yǔ)句
    而且group by后面不能使用別名
    
(8).hive的0.13版之前select , where 及 having 之后不能跟子查詢(xún)語(yǔ)句(一般使用left join笨农、right join 或者inner join替代)

(9).先join(及inner join) 然后left join或right join

(10).hive不支持group_concat方法,可用 concat_ws('|', collect_set(str)) 實(shí)現(xiàn)

(11).not in 和 <> 不起作用,可用left join tmp on tableName.id = tmp.id where tmp.id is null 替代實(shí)現(xiàn)

– 21就缆、查詢(xún)不同老師所教不同課程平均分從高到低顯示:
– 方法1

select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore from course
    join teacher on teacher.t_id=course.t_id
    join score on course.c_id=score.c_id
    group by course.c_id,course.t_id,t_name order by avgscore desc;

– 方法2

select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore from course,teacher,score
   where teacher.t_id=course.t_id and course.c_id=score.c_id
    group by course.c_id,course.t_id,t_name order by avgscore desc;

– 22、查詢(xún)所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī):

select tmp1.* from
    (select * from score where c_id='01' order by s_score desc limit 3)tmp1
    order by s_score asc limit 2
union all select tmp2.* from
    (select * from score where c_id='02' order by s_score desc limit 3)tmp2
    order by s_score asc limit 2
union all select tmp3.* from
    (select * from score where c_id='03' order by s_score desc limit 3)tmp3
    order by s_score asc limit 2;

– 23谒亦、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱(chēng),[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id,c.c_name,tmp1.s0_60, tmp1.percentum,tmp2.s60_70, tmp2.percentum,tmp3.s70_85, tmp3.percentum,tmp4.s85_100, tmp4.percentum
from course c
join(select c_id,sum(case when s_score<60 then 1 else 0 end )as s0_60,
               round(100*sum(case when s_score<60 then 1 else 0 end )/count(c_id),2)as percentum
     from score group by c_id)tmp1 on tmp1.c_id =c.c_id
left join(select c_id,sum(case when s_score<70 and s_score>=60 then 1 else 0 end )as s60_70,
               round(100*sum(case when s_score<70 and s_score>=60 then 1 else 0 end )/count(c_id),2)as percentum
     from score group by c_id)tmp2 on tmp2.c_id =c.c_id
left join(select c_id,sum(case when s_score<85 and s_score>=70 then 1 else 0 end )as s70_85,
               round(100*sum(case when s_score<85 and s_score>=70 then 1 else 0 end )/count(c_id),2)as percentum
     from score group by c_id)tmp3 on tmp3.c_id =c.c_id
left join(select c_id,sum(case when s_score>=85 then 1 else 0 end )as s85_100,
               round(100*sum(case when s_score>=85 then 1 else 0 end )/count(c_id),2)as percentum
     from score group by c_id)tmp4 on tmp4.c_id =c.c_id;

– 24竭宰、查詢(xún)學(xué)生平均成績(jī)及其名次:

select tmp.*,row_number()over(order by tmp.avgScore desc) Ranking from
  (select student.s_id,
          student.s_name,
          round(avg(score.s_score),2) as avgScore
  from student join score
  on student.s_id=score.s_id
  group by student.s_id,student.s_name)tmp
order by avgScore desc;

– 25、查詢(xún)各科成績(jī)前三名的記錄

–課程id為01的前三名

select score.c_id,course.c_name,student.s_name,s_score from score
join student on student.s_id=score.s_id
join course on  score.c_id='01' and course.c_id=score.c_id
order by s_score desc limit 3;  

–課程id為02的前三名

select score.c_id,course.c_name,student.s_name,s_score 
from score
join student on student.s_id=score.s_id
join course on  score.c_id='02' and course.c_id=score.c_id
order by s_score desc limit 3; 

–課程id為03的前三名

select score.c_id,course.c_name,student.s_name,s_score 
from score
join student on student.s_id=score.s_id
join course on  score.c_id='03' and course.c_id=score.c_id  
order by s_score desc limit 3;

– 26份招、查詢(xún)每門(mén)課程被選修的學(xué)生數(shù):

select c.c_id,c.c_name,tmp.number from course c
    join (select c_id,count(1) as number from score
        where score.s_score<60 group by score.c_id)tmp
    on tmp.c_id=c.c_id;

– 27切揭、查詢(xún)出只有兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名:

select st.s_id,st.s_name from student st
  join (select s_id from score group by s_id having count(c_id) =2)tmp
    on st.s_id=tmp.s_id;

– 28、查詢(xún)男生锁摔、女生人數(shù):

select tmp1.man,tmp2.women from
    (select count(1) as man from student where s_sex='男')tmp1,
    (select count(1) as women from student where s_sex='女')tmp2;

– 29廓旬、查詢(xún)名字中含有"風(fēng)"字的學(xué)生信息:

select * from student where s_name like '%風(fēng)%';

– 30、查詢(xún)同名同性學(xué)生名單谐腰，并統(tǒng)計(jì)同名人數(shù):

select s1.s_id,s1.s_name,s1.s_sex,count(*) as sameName
from student s1,student s2
where s1.s_name=s2.s_name and s1.s_id<>s2.s_id and s1.s_sex=s2.s_sex
group by s1.s_id,s1.s_name,s1.s_sex;

– 31孕豹、查詢(xún)1990年出生的學(xué)生名單:

select * from student where s_birth like '1990%';

– 32、查詢(xún)每門(mén)課程的平均成績(jī)怔蚌，結(jié)果按平均成績(jī)降序排列巩步，平均成績(jī)相同時(shí)，按課程編號(hào)升序排列:

select score.c_id,c_name,round(avg(s_score),2) as avgScore from score
  join course on score.c_id=course.c_id
    group by score.c_id,c_name order by avgScore desc,score.c_id asc;

– 33桦踊、查詢(xún)平均成績(jī)大于等于85的所有學(xué)生的學(xué)號(hào)椅野、姓名和平均成績(jī):

select score.s_id,s_name,round(avg(s_score),2)as avgScore from score
    join student on student.s_id=score.s_id
    group by score.s_id,s_name having avg(s_score) >= 85;

– 34、查詢(xún)課程名稱(chēng)為"數(shù)學(xué)"，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù):

select s_name,s_score as mathScore from student
    join (select s_id,s_score
            from score,course
            where score.c_id=course.c_id and c_name='數(shù)學(xué)')tmp
    on tmp.s_score < 60 and student.s_id=tmp.s_id;

– 35竟闪、查詢(xún)所有學(xué)生的課程及分?jǐn)?shù)情況:

select a.s_name,
    SUM(case c.c_name when '語(yǔ)文' then b.s_score else 0 end ) as chainese,
    SUM(case c.c_name when '數(shù)學(xué)' then b.s_score else 0 end ) as math,
    SUM(case c.c_name when '英語(yǔ)' then b.s_score else 0 end ) as english,
    SUM(b.s_score) as sumScore
  from student a
    join score b on a.s_id=b.s_id
    join course c on b.c_id=c.c_id
    group by s_name,a.s_id;

后續(xù)部分參見(jiàn):
https://blog.csdn.net/Thomson617/article/details/83281254

Hive下的SQL經(jīng)驗(yàn)總結(jié)：

(1).不支持非等值連接离福，一般使用left join、right join 或者inner join替代炼蛤。
    ?SQL中對(duì)兩表內(nèi)聯(lián)可以寫(xiě)成：
        select * from dual a,dual b where a.key = b.key;
    ?Hive中應(yīng)為:
        select * from dual a join dual b on a.key = b.key; 
    而不是傳統(tǒng)的格式：
        SELECT t1.a1 as c1, t2.b1 as c2 FROM t1, t2 WHERE t1.a2 = t2.b2 
        
(2).分號(hào)字符:不能智能識(shí)別concat(‘;’,key)妖爷，只會(huì)將‘；’當(dāng)做SQL結(jié)束符號(hào)理朋。
    ?分號(hào)是SQL語(yǔ)句結(jié)束標(biāo)記絮识，在HiveQL中也是，但是在HiveQL中嗽上，對(duì)分號(hào)的識(shí)別沒(méi)有那么智慧次舌，例如：
        ?select concat(key,concat(';',key)) from dual;
    ?但HiveQL在解析語(yǔ)句時(shí)提示：
        FAILED: Parse Error: line 0:-1 mismatched input '<EOF>' expecting ) in function specification
    ?解決的辦法是，使用分號(hào)的八進(jìn)制的ASCII碼進(jìn)行轉(zhuǎn)義兽愤，那么上述語(yǔ)句應(yīng)寫(xiě)成：
        ?select concat(key,concat('\073',key)) from dual;

(3).不支持INSERT INTO 表 Values（）, UPDATE, DELETE等操作.這樣的話彼念，就不要很復(fù)雜的鎖機(jī)制來(lái)讀寫(xiě)數(shù)據(jù)。
    INSERT INTO syntax is only available starting in version 0.8浅萧。INSERT INTO就是在表或分區(qū)中追加數(shù)據(jù)逐沙。

(4).HiveQL中String類(lèi)型的字段若是空(empty)字符串, 即長(zhǎng)度為0, 那么對(duì)它進(jìn)行IS NULL的判斷結(jié)果是False，使用left join可以進(jìn)行篩選行洼畅。

(5).不支持 ‘< dt <’這種格式的范圍查找吩案，可以用dt in(”,”)或者between替代。

(6).Hive不支持將數(shù)據(jù)插入現(xiàn)有的表或分區(qū)中土思，僅支持覆蓋重寫(xiě)整個(gè)表务热，示例如下：
    INSERT OVERWRITE TABLE t1 SELECT * FROM t2;
    
(7).group by的字段,必須是select后面的字段，select后面的字段不能比group by的字段多.
    如果select后面有聚合函數(shù),則該select語(yǔ)句中必須有g(shù)roup by語(yǔ)句;
    而且group by后面不能使用別名;
    有聚合函數(shù)存在就必須有g(shù)roup by.
    
(8).select , where 及 having 之后不能跟子查詢(xún)語(yǔ)句(一般使用left join己儒、right join 或者inner join替代)

(9).先join(及inner join) 然后left join或right join

(10).hive不支持group_concat方法,可用 concat_ws('|', collect_set(str)) 實(shí)現(xiàn)

(11).not in 和 <> 不起作用,可用left join tmp on tableName.id = tmp.id where tmp.id is null 替代實(shí)現(xiàn)

(12).hive 中‘不等于’不管是用崎岂！ 或者<>符號(hào)實(shí)現(xiàn)，都會(huì)將空值即null過(guò)濾掉闪湾，此時(shí)要用
        where （white_level<>'3' or  white_level is null） 
    或者 where (white_level!='3' or white_level is null )  來(lái)保留null 的情況冲甘。

(13).union all 后面的表不加括號(hào),不然執(zhí)行報(bào)錯(cuò);
    hive也不支持頂層的union all，使用子查詢(xún)來(lái)解決;
    union all 之前不能有DISTRIBUTE BY | SORT BY| ORDER BY | LIMIT 等查詢(xún)條件

– 36途样、查詢(xún)?nèi)魏我婚T(mén)課程成績(jī)?cè)?0分以上的學(xué)生姓名江醇、課程名稱(chēng)和分?jǐn)?shù):

select student.s_id,s_name,c_name,s_score from student
  join (select sc.* from score sc
        left join(select s_id from score where s_score < 70 group by s_id)tmp
        on sc.s_id=tmp.s_id where tmp.s_id is null)tmp2
    on student.s_id=tmp2.s_id
  join course on tmp2.c_id=course.c_id
order by s_id;


**-- 查詢(xún)?nèi)考案竦男畔?*
select sc.* from score sc
  left join(select s_id from score where s_score < 60 group by s_id)tmp
    on sc.s_id=tmp.s_id
where  tmp.s_id is  null;
**-- 或(效率低)**
select sc.* from score sc
where sc.s_id not in (select s_id from score where s_score < 60 group by s_id);

– 37、查詢(xún)課程不及格的學(xué)生:

select s_name,c_name as courseName,tmp.s_score
from student
join (select s_id,s_score,c_name
      from score,course
      where score.c_id=course.c_id and s_score < 60)tmp
on student.s_id=tmp.s_id;

–38何暇、查詢(xún)課程編號(hào)為01且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名:

select student.s_id,s_name,s_score as score_01
from student
join score on student.s_id=score.s_id
where c_id='01' and s_score >= 80;

– 39陶夜、求每門(mén)課程的學(xué)生人數(shù):

select course.c_id,course.c_name,count(1)as selectNum
from course
join score on course.c_id=score.c_id
group by course.c_id,course.c_name;

– 40、查詢(xún)選修"張三"老師所授課程的學(xué)生中裆站，成績(jī)最高的學(xué)生信息及其成績(jī):

select student.*,tmp3.c_name,tmp3.maxScore
from (select s_id,c_name,max(s_score)as maxScore from score
      join (select course.c_id,c_name from course join
                  (select t_id,t_name from teacher where t_name='張三')tmp
            on course.t_id=tmp.t_id)tmp2
      on score.c_id=tmp2.c_id group by score.s_id,c_name
      order by maxScore desc limit 1)tmp3
join student
on student.s_id=tmp3.s_id;

– 41条辟、查詢(xún)不同課程成績(jī)相同的學(xué)生的學(xué)生編號(hào)黔夭、課程編號(hào)、學(xué)生成績(jī):

select distinct a.s_id,a.c_id,a.s_score from score a,score b
    where a.c_id <> b.c_id and a.s_score=b.s_score;

– 42羽嫡、查詢(xún)每門(mén)課程成績(jī)最好的前三名:

select tmp1.* from
  (select *,row_number()over(order by s_score desc) ranking
      from score  where c_id ='01')tmp1
where tmp1.ranking <= 3
union all
select tmp2.* from
  (select *,row_number()over(order by s_score desc) ranking
      from score where c_id ='02')tmp2
where tmp2.ranking <= 3
union all
select tmp3.* from
  (select *,row_number()over(order by s_score desc) ranking
      from score where c_id ='03')tmp3
where tmp3.ranking <= 3;

– 43本姥、統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)（超過(guò)5人的課程才統(tǒng)計(jì)）:
– 要求輸出課程號(hào)和選修人數(shù)，查詢(xún)結(jié)果按人數(shù)降序排列杭棵，若人數(shù)相同婚惫，按課程號(hào)升序排列

select distinct course.c_id,tmp.num from course
    join (select c_id,count(1) as num from score group by c_id)tmp
    where tmp.num>=5 order by tmp.num desc ,course.c_id asc;

– 44、檢索至少選修兩門(mén)課程的學(xué)生學(xué)號(hào):

select s_id,count(c_id) as totalCourse
from score
group by s_id
having count(c_id) >= 2;

– 45魂爪、查詢(xún)選修了全部課程的學(xué)生信息:

select student.* 
from student,
     (select s_id,count(c_id) as totalCourse 
      from score group by s_id)tmp
where student.s_id=tmp.s_id and totalCourse=3;

–46先舷、查詢(xún)各學(xué)生的年齡(周歲):
– 按照出生日期來(lái)算，當(dāng)前月日 < 出生年月的月日則滓侍，年齡減一

select s_name,s_birth,(year(CURRENT_DATE)-year(s_birth)-
    (case when month(CURRENT_DATE) > month(s_birth) then 0
          when day(CURRENT_DATE) > day(s_birth) then
          1 else 0 end)) as age
    from student;

– 47密浑、查詢(xún)本周過(guò)生日的學(xué)生:
–方法1

select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);

–方法2

select s_name,s_sex,s_birth from student
    where substring(s_birth,6,2)='10'
    and substring(s_birth,9,2)=14;

– 48、查詢(xún)下周過(guò)生日的學(xué)生:
–方法1

select * from student where weekofyear(CURRENT_DATE)+1 =weekofyear(s_birth);

–方法2

select s_name,s_sex,s_birth from student
    where substring(s_birth,6,2)='10'
    and substring(s_birth,9,2)>=15
    and substring(s_birth,9,2)<=21;

– 49粗井、查詢(xún)本月過(guò)生日的學(xué)生:
–方法1

select * from student where MONTH(CURRENT_DATE)+1 =MONTH(s_birth);

–方法2

select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='10';

– 50、查詢(xún)12月份過(guò)生日的學(xué)生:

select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='12';

所有代碼親測(cè)有效!
如果因?yàn)閔ive版本及測(cè)試環(huán)境造成無(wú)法運(yùn)行的還請(qǐng)自行修正!

hive sql中的部分方法總結(jié):

1.case when ... then ... else ... end

2.length(string)

3.cast(string as bigint)

4.rand()       返回一個(gè)0到1范圍內(nèi)的隨機(jī)數(shù)

5.ceiling(double)    向上取整

6.substr(string A, int start, int len)

7.collect_set(col)函數(shù)只接受基本數(shù)據(jù)類(lèi)型街图，它的主要作用是將某字段的值進(jìn)行去重匯總浇衬，產(chǎn)生array類(lèi)型字段

8.concat()函數(shù)
    1、功能：將多個(gè)字符串連接成一個(gè)字符串餐济。
    2耘擂、語(yǔ)法：concat(str1, str2,...)
    返回結(jié)果為連接參數(shù)產(chǎn)生的字符串，如果有任何一個(gè)參數(shù)為null絮姆，則返回值為null醉冤。

    9.concat_ws()函數(shù)
    1、功能：和concat()一樣篙悯，將多個(gè)字符串連接成一個(gè)字符串蚁阳，但是可以一次性指定分隔符～（concat_ws就是concat with separator）
    2、語(yǔ)法：concat_ws(separator, str1, str2, ...)
    說(shuō)明：第一個(gè)參數(shù)指定分隔符鸽照。需要注意的是分隔符不能為null螺捐，如果為null，則返回結(jié)果為null矮燎。

    10.nvl(expr1, expr2)：空值轉(zhuǎn)換函數(shù)  nvl(x,y)    Returns y if x is null else return x

11.if(boolean testCondition, T valueTrue, T valueFalse)

12.row_number()over()分組排序功能,over()里頭的分組以及排序的執(zhí)行晚于 where group by  order by 的執(zhí)行定血。

13.獲取年、月诞外、日澜沟、小時(shí)、分鐘峡谊、秒茫虽、當(dāng)年第幾周
    select 
        year('2018-02-27 10:00:00')       as year
        ,month('2018-02-27 10:00:00')      as month
        ,day('2018-02-27 10:00:00')        as day
        ,hour('2018-02-27 10:00:00')       as hour
        ,minute('2018-02-27 10:00:00')     as minute
        ,second('2018-02-27 10:00:00')     as second
        ,weekofyear('2018-02-27 10:00:00') as weekofyear
  獲取當(dāng)前時(shí)間:
        1).current_timestamp
        2).unix_timestamp()
        3).from_unixtime(unix_timestamp())
        4).CURRENT_DATE