Hive手写SQL案例

create external table student_info.student(
id string comment ‘学生id‘,
name string comment ‘学生姓名‘,
gender string comment ‘学生性别‘
) comment "学生信息表"
row format delimited fields terminated by ‘\t‘
line terminated by ‘\n‘
stored as textfile
location "/user/root/student";

load data local inpath ‘/root/student.txt‘ into table student_info.student  location "/user/root/student" ;

create external table test.employee_info(
id string comment ‘员工id‘,
name string comment ‘员工姓名‘,
indentity_card string comment ‘身份证号‘,
gender string comment ‘性别‘,
department string comment ‘所属部门‘,
post string comment ‘岗位‘,
hire_date string comment ‘入职时间‘,
departure_date string comment ‘离职时间‘
) comment "员工基本信息表"
partitioned by (day string comment "员工入职时间")
row format delimited fields terminated by ‘\t‘
lines terminated by  ‘\n‘
stored as textfile 
location ‘/user/root/employee‘;

create external table test.income_info(
id string comment ‘员工id‘,
name string comment ‘员工姓名‘,
income_data string comment ‘收入‘,
income_month string comment ‘收入所属月份‘,
income_type string comment ‘收入类型‘,
income_datetime string comment ‘收入薪水时间‘
) comment ‘员工收入表‘
partitioned by (day string comment "员工发放薪水时间")
row format delimited fields terminated by ‘\t‘
lines terminated by  ‘\n‘
stored as textfile 
location ‘/user/root/income‘;

select 
    income_year,(income_data-(nvl(penalty_data,0))) as company_cost
from
(
    -- 统计员工收入金额和罚款金额，输出 2019 500 10
    select 
        income_year,
        sum(case when income_type!=‘罚款‘ then data_total else 0 end) as income_data,
        sum(case when income_type=‘罚款‘ then data_total else 0 end) as penalty_data
    from
    (
    -- 按照年份、收入类型求收入金额
    select 
        year(to_date(income_datetime)) as income_year,
        income_type,
        sum(income_data) as data_total
    from
        test.income_info
    group by 
        year(to_date(income_datetime)) ,income_type
    ) tmp_a
    group by  tmp_a.income_year
) as  temp
order by income_year desc;

--根据id关联得出department,和消费类型
select 
    income_year,department,
    (sum(case when income_type!=‘罚款‘ then income_data else 0 end) - sum(case when income_type=‘罚款‘ then income_data else 0 end) ) as department_cost
from
(
    -- 先对员工进行薪资类别的聚合统计
    select 
        id,year(to_date(income_datetime)) as income_year,income_type,sum(income_data) as income_data
    from 
        test.income_info
    group by 
    year(to_date(income_datetime)),id,income_type
) temp_a
inner join
    test.employee_info b
on
    temp_a.id=b.id
group by
    department,income_year
order by income_year desc , department_cost asc;

select department,department_cost,dense_rank() over(order by department_cost desc) as cost_rank
from
(
--根据id关联得出department,和消费类型
select 
    department,
    (sum(case when income_type!=‘罚款‘ then income_data else 0 end) - sum(case when income_type=‘罚款‘ then income_data else 0 end) ) as department_cost
from
(
    -- 先对员工进行薪资类别的聚合统计
    select 
        id,income_type,sum(income_data) as income_data
    from 
        test.income_info
    group by 
    id,income_type
) temp_a
inner join
    test.employee_info b
on
    temp_a.id=b.id
group by
    department
) tmp_c ;

create external table test.income_dynamic(
id string comment ‘员工id‘,
name string comment ‘员工姓名‘,
income_data_current string comment ‘本月收入‘,
income_datetime_current string comment 本月‘收入薪水时间‘,
income_data_last   string comment ‘上月收入‘,
income_datetime_last string comment ‘上月收入薪水时间‘,
) comment ‘员工收入动态表‘
partitioned by (day string comment "员工本月发放薪水时间")
row format delimited fields terminated by ‘\t‘
lines terminated by  ‘\n‘
stored as textfile 
location ‘/user/root/income‘;
-- ------------------------------------------------------------------------------
-- 动态分区插入
-- 插入语句
-- 采用full join
insert into table test.income_dynamic partition(day)
select 
    (case when id_a is not null then id_a else id_b end ) as id,
    (case when name_a is not null then name_a else name_b end )  as name ,
    income_data,income_datetime,income_data_b,income_datetime_b,day
from
    (
    -- 选出表中所有的数据
    select
        id as id_a,name as name_a,income_data,income_datetime,day,concat(year(to_date(day)),month(to_date(day))) as day_flag
    from 
        test.income_info
    where 
        income_type=‘薪资‘ ) tmp_a
full outer join
    (
    -- 将表中的收到薪水的日期整体加一个月
    select
        id as id_b,name as name_b,income_data as income_data_b,income_datetime as  income_datetime_b,concat(year(add_months(to_date(day),1)),month(add_months(to_date(day),1))) as   month_flag
    from 
        test.income_info
    where 
        income_type=‘薪资‘
    ) tmp_b
    on 
        tmp_a.day_flag=tmp_b.month_flag
    and 
        tmp_a.id_a=tmp_b.id_b
where day is not null
;

１、问题
hive如何将
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6
变为：
a       b       1,2,3
c       d       4,5,6
-------------------------------------------------------------------------------------------
２、数据
test.txt
a       b       1 
a       b       2 
a       b       3 
c       d       4 
c       d       5 
c       d       6
------------------------------------------------------------------------------------------- 
３、答案
1.建表
drop table tmp_jiangzl_test;
create table tmp_jiangzl_test
(
col1 string,
col2 string,
col3 string
)
row format delimited fields terminated by ‘\t‘
stored as textfile;
-- 加载数据
load data local inpath ‘/home/jiangzl/shell/test.txt‘ into table tmp_jiangzl_test;
2.处理
select col1,col2,concat_ws(‘,‘,collect_set(col3)) 
from tmp_jiangzl_test  
group by col1,col2;
---------------------------------------------------------------------------------------
collect_set/concat_ws语法参考链接：https://blog.csdn.net/waiwai3/article/details/79071544
https://blog.csdn.net/yeweiouyang/article/details/41286469   [Hive]用concat_w实现将多行记录合并成一行
---------------------------------------------------------------------------------------
二、列转行
１、问题
hive如何将
a       b       1,2,3
c       d       4,5,6
变为：
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6
---------------------------------------------------------------------------------------------
2、答案
1.建表

drop table tmp_jiangzl_test;
create table tmp_jiangzl_test
(
col1 string,
col2 string,
col3 string
)
row format delimited fields terminated by ‘\t‘
stored as textfile;
处理：
select col1, col2, col5
from tmp_jiangzl_test a 
lateral  view explode(split(col3,‘,‘)) b AS col5;
---------------------------------------------------------------------------------------
lateral  view 语法参考链接：
https://blog.csdn.net/clerk0324/article/details/58600284

1.创建数据库
create database wordcount;
2.创建外部表
create external table word_data(line string) row format delimited fields terminated by ‘,‘ location ‘/home/hadoop/worddata‘;
3.映射数据表
load data inpath ‘/home/hadoop/worddata‘ into table word_data;
4.这里假设我们的数据存放在hadoop下，路径为：/home/hadoop/worddata，里面主要是一些单词文件，内容大概为：
hello man
what are you doing now
my running
hello
kevin
hi man
执行了上述hql就会创建一张表src_wordcount，内容是这些文件的每行数据，每行数据存在字段line中，select * from word_data;就可以看到这些数据

5.根据MapReduce的规则，我们需要进行拆分，把每行数据拆分成单词，这里需要用到一个hive的内置表生成函数（UDTF）：explode(array)，参数是array，其实就是行变多列：

create table words(word string);
insert into table words select explode(split(line, " ")) as word from word_data;

6.查看words表内容
OK
hello
man
what
are
you
doing
now
my
running
hello
kevin
hi
man
split是拆分函数，跟java的split功能一样，这里是按照空格拆分，所以执行完hql语句，words表里面就全部保存的单个单词
7.group by统计单词
    select word, count(*) from wordcount.words group by word;
wordcount.words 库名称.表名称，group by word这个word是create table words(word string) 命令创建的word string

结果：
are     1
doing   1
hello   2
hi      1
kevin   1
man     2
my      1
now     1
running 1
what    1
you     1

select buyer_id
from
(
select buyer_id,sum(case when order_id=‘面粉‘ then 0 else 1 end) as flag
from order
) as tmp
where flag=0;

select count(*)/2 as weibo_relation_number
from
(
  (select concat(id,keep_id) as flag from weibo_relation)
  union all  --全部合并到一起，不能提前去重
  (select concat(keep_id,id) as flag from weibo_relation)
) as tmp
having count(flag) =2
group by flag;

select count(distinct keep_id) as total_keep_id
from weibo_relation
where id
  in
(select id from weibo_relation where keep_id=‘c‘)