目錄
正文
用戶名，月份，訪問次數(shù)
A,2015-01,5A,2015-01,15B,2015-01,5A,2015-01,8B,2015-01,25A,2015-01,5A,2015-02,4A,2015-02,6B,2015-02,10B,2015-02,5A,2015-03,16A,2015-03,22B,2015-03,23B,2015-03,10B,2015-03,1
use myhive;create external table if not exists t_access(uname string comment '用戶名',umonth string comment '月份',ucount int comment '訪問次數(shù)') comment '用戶訪問表' row format delimited fields terminated by "," location "/hive/t_access";
load data local inpath "/home/hadoop/access.txt" into table t_access;
select * from t_access;
現(xiàn)要求出：
每個用戶截止到每月為止的最大單月訪問次數(shù)和累計到該月的總訪問次數(shù)，結(jié)果數(shù)據(jù)格式如下
此結(jié)果需要根據(jù)用戶+月份進行分組
--求當月訪問次數(shù)create table tmp_access(name string,mon string,num int); insert into table tmp_access select uname,umonth,sum(ucount) from t_access t group by t.uname,t.umonth;
select * from tmp_access;
create view tmp_view as select a.name anme,a.mon amon,a.num anum,b.name bname,b.mon bmon,b.num bnum from tmp_access a join tmp_access b on a.name=b.name;select * from tmp_view;
select anme,amon,anum,max(bnum) as max_access,sum(bnum) as sum_access from tmp_view where amon>=bmon group by anme,amon,anum;
use myhive;CREATE TABLE `course` ( `id` int, `sid` int , `course` string, `score` int ) ;
// 插入數(shù)據(jù)// 字段解釋：id, 學(xué)號， 課程， 成績INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);
求：所有數(shù)學(xué)課程成績 大于 語文課程成績的學(xué)生的學(xué)號
create view tmp_course_view asselect sid, case course when "shuxue" then score else 0 end as shuxue, case course when "yuwen" then score else 0 end as yuwen from course; select * from tmp_course_view;
create view tmp_course_view1 asselect aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid; select * from tmp_course_view1;
select * from tmp_course_view1 where shuxue > yuwen;
數(shù)據(jù)格式
2010012325具體數(shù)據(jù)
數(shù)據(jù)解釋
2010012325表示在2010年01月23日的氣溫為25度
比如：2010012325表示在2010年01月23日的氣溫為25度?，F(xiàn)在要求使用hive，計算每一年出現(xiàn)過的最大氣溫的日期+溫度。
要計算出每一年的最大氣溫。我用
select substr(data,1,4),max(substr(data,9,2)) from table2 group by substr(data,1,4);
出來的是 年份 + 溫度 這兩列數(shù)據(jù)例如 2015 99
但是如果我是想select 的是：具體每一年最大氣溫的那一天 + 溫度 。例如 20150109 99
請問該怎么執(zhí)行hive語句。。
group by 只需要substr(data,1,4)，
但是select substr(data,1,8)，又不在group by 的范圍內(nèi)。
是我陷入了思維死角。一直想不出所以然。。求大神指點一下。
在select 如果所需要的。不在group by的條件里。這種情況如何去分析？
create table tmp_weather as select substr(data,1,4) years,substr(data,5,2) months,substr(data,7,2) days,substr(data,9,2) temp from weather;
select * from tmp_weather;
create table tmp_year_weather as select substr(data,1,4) years,max(substr(data,9,2)) max_temp from weather group by substr(data,1,4);
select * from tmp_year_weather;
select * from tmp_year_weather a join tmp_weather b on a.years=b.years and a.max_temp=b.temp;
id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e
表示有id為1,2,3的學(xué)生選修了課程a,b,c,d,e,f中其中幾門。
create table t_course(id int,course string)row format delimited fields terminated by ",";
load data local inpath "/home/hadoop/course/course.txt" into table t_course;
編寫Hive的HQL語句來實現(xiàn)以下結(jié)果：表中的1表示選修，表中的0表示未選修
id a b c d e f1 1 1 1 0 1 02 1 0 1 1 0 13 1 1 1 0 1 0
第一步：
select collect_set(course) as courses from id_course;
第二步：
set hive.strict.checks.cartesian.product=false;create table id_courses as select t1.id as id,t1.course as id_courses,t2.course courses from ( select id as id,collect_set(course) as course from id_course group by id ) t1 join (select collect_set(course) as course from id_course) t2;
啟用嚴格模式：hive.mapred.mode = strict // Deprecated
hive.strict.checks.large.query = true
該設(shè)置會禁用：1. 不指定分頁的orderby
2. 對分區(qū)表不指定分區(qū)進行查詢
3. 和數(shù)據(jù)量無關(guān)，只是一個查詢模式hive.strict.checks.type.safety = true
嚴格類型安全，該屬性不允許以下操作：1. bigint和string之間的比較
2. bigint和double之間的比較hive.strict.checks.cartesian.product = true
該屬性不允許笛卡爾積操作
第三步：得出最終結(jié)果：
思路：
拿出course字段中的每一個元素在id_courses中進行判斷，看是否存在。
select id,case when array_contains(id_courses, courses[0]) then 1 else 0 end as a,case when array_contains(id_courses, courses[1]) then 1 else 0 end as b,case when array_contains(id_courses, courses[2]) then 1 else 0 end as c,case when array_contains(id_courses, courses[3]) then 1 else 0 end as d,case when array_contains(id_courses, courses[4]) then 1 else 0 end as e,case when array_contains(id_courses, courses[5]) then 1 else 0 end as f from id_courses;
a,01,150a,01,200b,01,1000b,01,800c,01,250c,01,220b,01,6000a,02,2000a,02,3000b,02,1000b,02,1500c,02,350c,02,280a,03,350a,03,250
店鋪，月份，金額
use class;create table t_store(name string,months int,money int) row format delimited fields terminated by ",";
load data local inpath "/home/hadoop/store.txt" into table t_store;
編寫Hive的HQL語句求出每個店鋪的當月銷售額和累計到當月的總銷售額
（1）按照商店名稱和月份進行分組統(tǒng)計
create table tmp_store1 as select name,months,sum(money) as money from t_store group by name,months;select * from tmp_store1;
（2）對tmp_store1 表里面的數(shù)據(jù)進行自連接
create table tmp_store2 as select a.name aname,a.months amonths,a.money amoney,b.name bname,b.months bmonths,b.money bmoney from tmp_store1 a join tmp_store1 b on a.name=b.name order by aname,amonths;select * from tmp_store2;
（3）比較統(tǒng)計
select aname,amonths,amoney,sum(bmoney) as total from tmp_store2 where amonths >= bmonths group by aname,amonths,amoney;
