• 周五. 10月 7th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

Hive sql

admin

11月 28, 2021

hive 架构

hive 数据类型分基本数据类型 和集合数据类型

隐式转换:

hive> select '1.0'+2;  // 数字字符串转double
OK
3.0
hive> select '1111' > 10;
hive> select 1 > 0.8;

显式转换:

hive> select cast('1111s' as int);
OK
NULL
hive> select cast('1111' as int);
OK
1111

集合数据类型:

集合测试:

hive> select array(1,2,3);
OK
[1,2,3]
-- 使用 [] 访问数组元素
hive> select arr[0] from (select array(1,2,3) arr) tmp;
hive> select map('a', 1, 'b', 2, 'c', 3);
OK
{"a":1,"b":2,"c":3}
-- 使用 [] 访问map元素
hive> select mymap["a"] from (select map('a', 1, 'b', 2, 'c',
3) as mymap) tmp;
-- 使用 [] 访问map元素。 key 不存在返回 NULL
hive> select mymap["x"] from (select map('a', 1, 'b', 2, 'c',
3) as mymap) tmp;
NULL
hive> select struct('username1', 7, 1288.68);
OK
{"col1":"username1","col2":7,"col3":1288.68}
-- 给 struct 中的字段命名
hive> select named_struct("name", "username1", "id", 7,
"salary", 12880.68);
OK
{"name":"username1","id":7,"salary":12880.68}
-- 使用 列名.字段名 访问具体信息
hive> select userinfo.id
> from (select named_struct("name", "username1", "id",
7, "salary", 12880.68) userinfo) tmp;
-- union 数据类型
hive> select create_union(0, "zhansan", 19, 8000.88) uinfo;

Hive 默认分隔符:

 Hive  DDL:

创建数据库:

-- 创建数据库,在HDFS上存储路径为 /user/hive/warehouse/*.db
hive (default)> create database mydb;
hive (default)> dfs -ls /user/hive/warehouse;
-- 避免数据库已经存在时报错,使用 if not exists 进行判断【标准写法】
hive (default)> create database if not exists mydb;
-- 创建数据库。添加备注,指定数据库在存放位置
hive (default)> create database if not exists mydb2
comment 'this is mydb2'
location '/user/hive/mydb2.db';

查看数据库:

-- 查看所有数据库
show database;
-- 查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

使用数据库:

use mydb;

删除数据库:

-- 删除一个空数据库
drop database databasename;
-- 如果数据库不为空,使用 cascade 强制删除
drop database databasename cascade;  //慎用,防止删库跑路

创建表:

语法:

create [external] table [IF NOT EXISTS] table_name
[(colName colType [comment 'comment'], ...)]
[comment table_comment]
[partition by (colName colType [comment col_comment], ...)]
[clustered BY (colName, colName, ...)
[sorted by (col_name [ASC|DESC], ...)] into num_buckets
buckets]
[row format row_format]
[stored as file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

字段解释:

1. CREATE TABLE。按给定名称创建表,如果表已经存在则抛出异常。可使用if not exists 规避。
2. EXTERNAL关键字。创建外部表,否则创建的是内部表(管理表)。
删除内部表时,数据和表的定义同时被删除;
删除外部表时,仅仅删除了表的定义,数据保留;
在生产环境中,多使用外部表;
3. comment。表的注释
4. partition by。对表中数据进行分区,指定表的分区字段
5. clustered by。创建分桶表,指定分桶字段
6. sorted by。对桶中的一个或多个列排序,较少使用

存储子句:

ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] | SERDE serde_name
[WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]
7.stored as  TEXTFILE  ORC PARQUET
8.LOCATION。表在HDFS上的存放位置
9.AS。后面可以接查询语句,表示根据后面的查询结果创建表
  create table xxx as select ... 这种方式不会复制分区,分桶
10.like 复制表结构,不复制数据

 内部表 & 外部表:

  •   内部表、管理表  表和数据绑定,一起删除
  •   外部表,删除表不影响数据
-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息,是否转换成功
desc formatted t1;
-- 外部表转内部表。EXTERNAL 大写,false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息,是否转换成功
desc formatted t1;

分区表:

  为了避免全表扫描,将数据存储在不同子目录中,每个子目录对应一个分区

分区表创建与数据加载示例:

-- 创建表
create table if not exists t3(
id int
,name string
,hobby array<string>
,addr map<String,string>
)
partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';
-- 加载数据。
load data local inpath "/home/hadoop/data/t1.dat" into table
t3
partition(dt="2020-06-01");
load data local inpath "/home/hadoop/data/t1.dat" into table
t3
partition(dt="2020-06-02");

备注:分区字段不是表中已经存在的数据,可以将分区字段看成伪列

修复分区:msck repair table 表名 ,先有分区和数据,后有表

查看分区:

show partitions t3;

新增分区并设置数据:

-- 增加一个分区,不加载数据
alter table t3 add partition(dt='2020-06-03');
-- 增加多个分区,不加载数据
alter table t3
add partition(dt='2020-06-05') partition(dt='2020-06-06');
-- 增加多个分区。准备数据
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
/user/hive/warehouse/mydb.db/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
/user/hive/warehouse/mydb.db/t3/dt=2020-06-08
-- 增加多个分区。加载数据
alter table t3 add
partition(dt='2020-06-07') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
partition(dt='2020-06-08') location
'/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';
-- 查询数据
select * from t3

修改分区的hdfs 路径:

alter table t3 partition(dt='2020-06-01') set location
'/user/hive/warehouse/t3/dt=2020-06-03';

删除分区:

-- 可以删除一个或多个分区,用逗号隔开
alter table t3 drop partition(dt='2020-06-03'),
partition(dt='2020-06-04');

分桶表:

  当单个的分区或者表的数据量过大,分区不能更细粒度的划分数据,就需要使用分桶。

  分桶原理:分桶字段.hashCode % 分桶个数

分桶表测试:

create table course(
id int,
name string,
score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by "	";
-- 创建普通表 create table course_common( id int, name string, score int ) row format delimited fields terminated by " "; -- 普通表加载数据 load data local inpath '/home/hadoop/data/course.dat' into table course_common; -- 通过 insert ... select ... 给桶表加载数据 insert into table course select * from course_common; -- 观察分桶数据。数据按照:(分区字段.hashCode) % (分桶数) 进行分区

备注:

  • 分桶规则:分桶字段.hashCode % 分桶数
  • 分桶表加载数据时,使用 insert… select … 方式进行
  • 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true,那是Hive 1.x 以前的版本;Hive 2.x 中,删除了该参数,始终可以分桶;

修改表 & 删除表:

-- 修改表名。rename
alter table course_common
rename to course_common1;
-- 修改列名。change column
alter table course_common1
change column id cid int;
-- 修改字段类型。change column
alter table course_common1
change column cid cid string;
-- The following columns have types incompatible with the
existing columns in their respective positions
-- 修改字段数据类型时,要满足数据类型转换的要求。如int可以转为string,但是
string不能转为int
-- 增加字段。add columns
alter table course_common1
add columns (common string);
-- 删除字段:replace columns
-- 这里仅仅只是在元数据中删除了字段,并没有改动hdfs上的数据文件
alter table course_common1
replace columns(
id string, cname string, score int);
-- 删除表
drop table course_common1;

数据导入:

语法:
LOAD
DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
  • LOCAL:
  1. LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷 贝到Hive表指定的位置
  2. LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定 的位置
  • INPATH:加载数据的路径
  • OVERWRITE:覆盖表中已有数据;否则表示追加数据
  • PARTITION:将数据加载到指定的分区

插入数据:

  常用于从普通表插入数据到分区表,或orc ,parquet 存储格式的表

-- 插入数据
insert into table tabC
partition(month='202001')
values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3,
'zhangsan', 'TJ');
-- 插入查询的结果数据
insert into table tabC partition(month='202002')
select id, name, area from tabC where month='202001';

-- 多表(多分区)插入模式   
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';  // from 提前,这样可以减少hive sql的stage

创建表并插入数据:

-- 根据查询结果创建表
create table if not exists tabD
as select * from tabC;

数据导出:

-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;
-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;
-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;
-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001
/home/hadoop/data/tabC4
-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件 hive -e "select * from tabC" > a.log -- export 导出数据到HDFS。使用export导出数据时,不仅有数还有表的元数据信 息 export table tabC to '/user/hadoop/data/tabC4'; -- export 导出的数据,可以使用 import 命令导入到 Hive 表中 -- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构可能不一致 create table tabE like tabc; import table tabE from ''/user/hadoop/data/tabC4'; -- 截断表,清空数据。(注意:仅能操作内部表) truncate table tabE; -- 以下语句报错,外部表不能执行 truncate 操作 alter table tabC set tblproperties("EXTERNAL"="TRUE"); truncate table tabC;

Hive DQL :

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

基本查询:

-- 省略from子句的查询
select 8*888 ;
select current_date ;
-- 使用列别名
select 8*888 product;
select current_date as currdate;
-- 全表查询
select * from emp;
-- 选择特定列查询
select ename, sal, comm from emp;
-- 使用函数
select count(*) from emp;
-- count(colname) 按字段进行count,不统计NULL
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
-- 使用limit子句限制返回的行数
select * from emp limit 3;

where 子句:

  where 字句中不能使用列的别名

rlike 可以写正则表达式。

group by 字句:

-- 计算emp表每个部门的平均工资
select deptno, avg(sal)
from emp
group by deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal)
from emp
group by deptno, job;
  • where 针对表数据过滤
  • having 针对group by的结果过滤
  • group by 中不能使用别名

表连接:

1. 内连接: [inner] join
2. 外连接 (outer join)
- 左外连接。 left [outer] join,左表的数据全部显示
- 右外连接。 right [outer] join,右表的数据全部显示
- 全外连接。 full [outer] join,两张表的数据都显示

排序字句:

 全局排序 order by :

  1.   order by 子句出现在select语句的结尾;
  2.   order by子句对最终的结果进行排序;
  3.   默认使用升序(ASC);可以使用DESC,跟在字段名之后表示降序;

  ORDER BY执行全局排序,只有一个reduce;

-- 普通排序
select * from emp order by deptno;
-- 按别名排序
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm,
deptno
from emp
order by salcomm desc;
-- 多列排序
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm,
deptno
from emp
order by deptno, salcomm desc;
-- 排序字段要出现在select子句中。以下语句无法执行(因为select子句中缺少
deptno):
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm
from emp
order by deptno, salcomm desc;

  注意排序字段需要出现在select语句中。

分区排序(distribute by):

  结合sort by 使用。 使得结果分区内有序。

-- 启动2个reducer task;先按 deptno 分区,在分区内按 sal+comm 排序
set mapreduce.job.reduces=2;
-- 将结果输出到文件,观察输出结果
insert overwrite local directory '/home/hadoop/output/distBy'
select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
from emp
distribute by deptno
sort by salcomm desc;

当distribute by 与 sort by是同一个字段时,可使用cluster by简化语法;

   cluster by 只能是升序,不能指定排序规则;

-- 语法上是等价的
select * from emp distribute by deptno sort by deptno;
select * from emp cluster by deptno;

Hive 函数:

-- 显示自带函数的用法
desc function upper;
desc function extended upper;

日期相关函数:

-- 当前前日期
select current_date;
select unix_timestamp();
-- 建议使用current_timestamp,有没有括号都可以
select current_timestamp();
-- 时间戳转日期
select from_unixtime(1505456567);
select from_unixtime(1505456567, 'yyyyMMdd');
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');
-- 日期转时间戳
select unix_timestamp('2019-09-15 14:23:00');
-- 计算时间差
select datediff('2020-04-18','2019-11-21');
select datediff('2019-11-21', '2020-04-18');
-- 查询当月第几天
select dayofmonth(current_date);
-- 计算月末:
select last_day(current_date);
-- 当月第1天:
select date_sub(current_date, dayofmonth(current_date)-1)
-- 下个月第1天:
select add_months(date_sub(current_date,
dayofmonth(current_date)-1), 1)
-- 字符串转时间(字符串必须为:yyyy-MM-dd格式)
select to_date('2020-01-01');
select to_date('2020-01-01 12:12:12');
-- 日期、时间戳、字符串类型格式化输出标准时间格式
select date_format(current_timestamp(), 'yyyy-MM-dd
HH:mm:ss');
select date_format(current_date(), 'yyyyMMdd');
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');

-- 计算emp表中,每个人的工龄
select *, round(datediff(current_date, hiredate)/365,1) workingyears from emp;

字符串相关函数:

-- 转小写。lower
select lower("HELLO WORLD");
-- 转大写。upper
select lower(ename), ename from emp;
-- 求字符串长度。length
select length(ename), ename from emp;
-- 字符串拼接。 concat / ||
select empno || " " ||ename idname from emp;
select concat(empno, " " ,ename) idname from emp;
-- 指定分隔符。concat_ws(separator, [string | array(string)]+)
SELECT concat_ws('.', 'www', array('lagou', 'com'));
select concat_ws(" ", ename, job) from emp;
-- 求子串。substr
SELECT substr('www.lagou.com', 5);
SELECT substr('www.lagou.com', -5);
SELECT substr('www.lagou.com', 5, 5);
-- 字符串切分。split,注意 '.' 要转义
select split("www.lagou.com", "\.");

数学相关函数:

-- 四舍五入。round
select round(314.15926);
select round(314.15926, 2);
select round(314.15926, -2);
-- 向上取整。ceil
select ceil(3.1415926);
-- 向下取整。floor
select floor(3.1415926);

条件函数:

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;
-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 将emp表的员工工资等级分类:0-1500、1500-3000、3000以上
select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from
emp;
-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 复杂条件用 case when 更直观
select sal, case when sal<=1500 then 1
when sal<=3000 then 2
else 3 end sallevel
from emp;
-- 以下语句等价
select ename, deptno,
case deptno when 10 then 'accounting'
when 20 then 'research'
when 30 then 'sales'
else 'unknown' end deptname
from emp;
select ename, deptno,
case when deptno=10 then 'accounting'
when deptno=20 then 'research'
when deptno=30 then 'sales'
else 'unknown' end deptname
from emp;

-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值;如果所有值都为
NULL,那么返回NULL
select sal, coalesce(comm, 0) from emp;
-- isnull(a) isnotnull(a)
select * from emp where isnull(comm);
select * from emp where isnotnull(comm);
-- nvl(T value, T default_value)  // 如果value 为null,则赋值 default_value
select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal from emp;
-- nullif(x, y) 相等为空,否则为a
SELECT nullif("b", "b"), nullif("b", "a");

 UDTF 函数:

  一行输 入,多行输出。

-- explode,炸裂函数
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行
select explode(array('A','B','C')) as col;
select explode(map('a', 8, 'b', 88, 'c', 888));
-- UDTF's are not supported outside the SELECT clause, nor
nested in expressions
-- SELECT pageid, explode(adid_list) AS myCol... is not
supported
-- SELECT explode(explode(adid_list)) AS myCol... is not
supported
-- lateral view 常与 表生成函数explode结合使用
-- lateral view 语法:
lateralView: LATERAL VIEW udtf(expression) tableAlias AS
columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
-- lateral view 的基本使用
with t1 as (
select 'OK' cola, split('www.lagou.com', '\.') colb
)
select cola, colc
from t1
lateral view explode(colb) t2 as colc;

记忆:   lateral view explode 表名 as 列名

explode map 案例:

-- 数据准备
lisi|Chinese:90,Math:80,English:70
wangwu|Chinese:88,Math:90,English:96
maliu|Chinese:99,Math:65,English:60

-- 创建表
create table studscore(
name string
,score map<String,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
-- 加载数据
load data local inpath '/home/hadoop/data/score.dat' overwrite
into table studscore;
-- 需求:找到每个学员的最好成绩
-- 第一步,使用 explode 函数将map结构拆分为多行
select explode(score) as (subject, socre) from studscore;
--但是这里缺少了学员姓名,加上学员姓名后出错。下面的语句有是错的
select name, explode(score) as (subject, socre) from
studscore;
-- 第二步:explode常与 lateral view 函数联用,这两个函数结合在一起能关
联其他字段
select name, subject, score1 as score from studscore
lateral view explode(score) t1 as subject, score1;
-- 第三步:找到每个学员的最好成绩
select name, max(mark) maxscore
from (select name, subject, mark
from studscore lateral view explode(score) t1 as
subject, mark) t1
group by name;
with tmp as (
select name, subject, mark
from studscore lateral view explode(score) t1 as subject,
mark
)
select name, max(mark) maxscore
from tmp
group by name;

总结:

  •   将一行数据转换成多行数据,可以用于array和map类型的数据;
  •    lateral view 与 explode 联用,解决 UDTF 不能添加额外列的问题

窗口函数:

  窗口函数相当于把聚合函数在聚合范围的每一行体现

-- 使用窗口函数,查询员工姓名、薪水、薪水总和
select ename, sal, sum(sal) over() salsum,
concat(round(sal / sum(sal) over()*100, 1) || '%')
ratiosal
from emp;

如果over 中没有参数,默认是全部数据集。

partition by子句:

   在over窗口中进行分区,对某一列进行分区统计,窗口的大小就是分区的大小

-- 查询员工姓名、薪水、部门薪水总和
select ename, sal, sum(sal) over(partition by deptno) salsum
from emp;

order by 子句:

  对输入的数据进行排序。

-- 增加了order by子句;sum:从分组的第一行到当前行求和
select ename, sal, deptno, sum(sal) over(partition by deptno
order by sal) salsum
from emp;

注:如果只有partition by 没有order by ,窗口大小是整个分区大小

  如果有order by ,窗口大小是分区第一条数据到当前行

window 子句:

  如果要对窗口的结果做更细粒度的划分,使用window子句,有如下的几个选项:

  • unbounded preceding。组内第一行数据
  • n preceding。组内当前行的前n行数据
  • current row。当前行数据
  • n following。组内当前行的后n行数据
  • unbounded following。组内最后一行数据

示例:

-- rows between ... and ... 子句
-- 等价。组内,第一行到当前行的和
select ename, sal, deptno,
sum(sal) over(partition by deptno order by ename) from
emp;
select ename, sal, deptno,
sum(sal) over(partition by deptno order by ename
rows between unbounded preceding and
current row
)
from emp;
-- 组内,第一行到最后一行的和
select ename, sal, deptno,
sum(sal) over(partition by deptno order by ename
rows between unbounded preceding and
unbounded following
)
from emp;
-- 组内,前一行、当前行、后一行的和
select ename, sal, deptno,
sum(sal) over(partition by deptno order by ename
rows between 1 preceding and 1 following
)
from emp;

排名函数:

  • row_number()。排名顺序增加不会重复;如1、2、3、4、… …
  • RANK()。 排名相等会在名次中留下空位;如1、2、2、4、5、… …
  • DENSE_RANK()。 排名相等会在名次中不会留下空位 ;如1、2、2、3、4、… …
-- 按照班级,使用3种方式对成绩进行排名
select cname, sname, score,
row_number() over (partition by cname order by score
desc) rank1,
rank() over (partition by cname order by score desc)
rank2,
dense_rank() over (partition by cname order by score
desc) rank3
from t2;
-- 求每个班级前3名的学员--前3名的定义是什么--假设使用dense_rank
select cname, sname, score, rank
from (select cname, sname, score,
dense_rank() over (partition by cname order by
score desc) rank
from t2) tmp
where rank <= 3;

序列函数:

  • lag。返回当前数据行的上一行数据
  • lead。返回当前数据行的下一行数据
  • first_value。取分组内排序后,截止到当前行,第一个值
  • last_value。分组内排序后,截止到当前行,最后一个值
  • ntile。将分组的数据按照顺序切分成n片,返回当前切片值
-- lag。返回当前数据行的上一行数据
-- lead。功能上与lag类似
select cid, ctime, pv,
lag(pv) over(partition by cid order by ctime) lagpv,
lead(pv) over(partition by cid order by ctime) leadpv
from userpv;
-- first_value / last_value
select cid, ctime, pv,
first_value(pv) over (partition by cid order by ctime
rows between unbounded preceding and unbounded following) as
firstpv,
last_value(pv) over (partition by cid order by ctime
rows between unbounded preceding and unbounded following) as
lastpv
from userpv;
-- ntile。按照cid进行分组,每组数据分成2份
select cid, ctime, pv,
ntile(2) over(partition by cid order by ctime) ntile
from userpv;

SQL 案例1: 连续7天登陆:

-- 数据。uid dt status(1 正常登录,0 异常)
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 0
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1

数据

-- 建表语句
create table ulogin(
uid int,
dt date,
status int
)
row format delimited fields terminated by ' ';
-- 加载数据
load data local inpath '/home/hadoop/data/ulogin.dat' into
table ulogin;

整体思路:

-- 1、使用 row_number 在组内给数据编号(rownum)
-- 2、某个值 - rownum = gid,得到结果可以作为后面分组计算的依据
-- 3、根据求得的gid,作为分组条件,求最终结果
核心sql: 得到每一个用户 相同的日期(说明连续),对这个值group by 求count ,可以求出连续天数
select
uid, dt, date_sub(dt, row_number() over (partition by uid order by dt)) gid from ulogin where status=1;

最终sql:
select uid, count(*) logincount from (select uid, dt, date_sub(dt, row_number() over (partition by uid order by dt)) gid from ulogin where status=1) t1 group by uid, gid having logincount>=7;

SQL 案例2:编写sql语句实现每班前三名,分数一样并列,同时求出前三名按名次排序的分差

-- 数据。sid 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
-- 待求结果数据如下:
class score rank lagscore
1901 90 1 0
1901 90 1 0
1901 83 2 -7
1901 60 3 -23
1902 99 1 0
1902 87 2 -12
1902 67 3 -20
-- 建表语句
create table stu(
sno int,
class string,
score int
)row format delimited fields terminated by ' ';
-- 加载数据
load data local inpath '/home/hadoop/data/stu.dat' into table
stu;

求解思路:

-- 1、上排名函数,分数一样并列,所以用dense_rank
-- 2、将上一行数据下移,相减即得到分数差
-- 3、处理 NULL
with tmp as (
select sno, class, score,
dense_rank() over (partition by class order by score
desc) as rank
from stu
)
select class, score, rank,
nvl(score - lag(score) over (partition by class order
by score desc), 0) lagscore
from tmp
where rank<=3;

SQL 案例:行<=>列

-- 数据:id course
1 java
1 hadoop
1 hive
1 hbase
2 java
2 hive
2 spark
2 flink
3 java
3 hadoop
3 hive
3 kafka

-- 建表加载数据
create table rowline1(
id string,
course string
)row format delimited fields terminated by ' ';
load data local inpath '/root/data/data1.dat' into table
rowline1;
select id,
sum(case when course="java" then 1 else 0 end) as java,
sum(case when course="hadoop" then 1 else 0 end) as hadoop,
sum(case when course="hive" then 1 else 0 end) as hive,
sum(case when course="hbase" then 1 else 0 end) as hbase,
sum(case when course="spark" then 1 else 0 end) as spark,
sum(case when course="flink" then 1 else 0 end) as flink,
sum(case when course="kafka" then 1 else 0 end) as kafka
from rowline1
group by id;

例2:

-- 数据。id1 id2 flag
a b 2
a b 1
a b 3
c d 6
c d 8
c d 8
-- 编写sql实现如下结果
id1 id2 flag
a b 2|1|3
c d 6|8
-- 创建表 & 加载数据
create table rowline2(
id1 string,
id2 string,
flag int
) row format delimited fields terminated by ' ';
load data local inpath '/root/data/data2.dat' into table
rowline2;
-- 第一步 将元素聚拢
select id1, id2, collect_set(flag) flag from rowline2 group by
id1, id2;
select id1, id2, collect_list(flag) flag from rowline2 group
by id1, id2;
select id1, id2, sort_array(collect_set(flag)) flag from
rowline2 group by id1, id2;
-- 第二步 将元素连接在一起
select id1, id2, concat_ws("|", collect_set(flag)) flag
from rowline2
group by id1, id2;
-- 这里报错,CONCAT_WS must be "string or array<string>"。加一个类
型转换即可
select id1, id2, concat_ws("|", collect_set(cast (flag as
string))) flag
from rowline2
group by id1, id2;
-- 创建表 rowline3
create table rowline3 as
select id1, id2, concat_ws("|", collect_set(cast (flag as
string))) flag
from rowline2
group by id1, id2;
-- 第一步:将复杂的数据展开
select explode(split(flag, "\|")) flat from rowline3;
-- 第二步:lateral view 后与其他字段关联
select id1, id2, newflag
from rowline3 lateral view explode(split(flag, "\|")) t1 as
newflag;

 

  

发表回复

您的电子邮箱地址不会被公开。