大数据随笔——当我们在谈论HiveSql时我们在谈论什么
【摘要】 Apache Hive™ 数据仓库软件使用SQL帮助读取、写入和管理驻留在分布式存储中的大型数据集。结构可以投影到已存储的数据上。提供了一个命令行工具和JDBC驱动程序来将用户连接到配置单元。
Hive 随笔
Apache Hive™ 数据仓库软件使用SQL帮助读取、写入和管理驻留在分布式存储中的大型数据集。结构可以投影到已存储的数据上。提供了一个命令行工具和JDBC驱动程序来将用户连接到配置单元。
顾名思义Hive本质时一个存放数据的工具，那么迈出的第一步也应当是建库建表。
Hive 的 DDL 语法
数据库操作
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name --DATABASE|SCHEMA 是等价的
[COMMENT database_comment] --数据库注释
[LOCATION hdfs_path] --存储在 HDFS 上的位置
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性
-- 建库
create database myhive;
-- 修改库
alter database myhive set dbproperties('createtime'='20220612');
-- 查看数据库
desc database myhive;
-- 删除数据库
drop database myhive;
数据表操作
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --表名
[(col_name data_type [COMMENT col_comment],
... [constraint_specification])] --列名 列数据类型
[COMMENT table_comment] --表描述
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --分区表分区规则
[
CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS
] --分桶表分桶规则
[SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
] --指定倾斜列和值
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
] -- 指定行分隔符、存储文件格式或采用自定义存储格式
[LOCATION hdfs_path] -- 指定表的存储位置
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement]; --从查询结果创建表
-- 基本建表语句
-- 建表后无数据
create table tableName(id int,name string);
-- 查询建表 as
-- 建表后有数据，数据为查询结果
-- 并不会带原表的分区（分区丢失）包括一些字段的约束等
create table tableName as select * from tableA;
-- 结构建表 like
-- 建表后无数据，表结构从 tableA 中复制
create table tableName like tableA;
-- CTE
-- 公用表表达式
-- 创建一个临时结果集
with tableName as (select * from tableB)
select * from tableName;
-- 表格式约束
row format delimited fields terminated by '\t' 指定字段分隔符，默认分隔符为 '\001'
stored as 指定存储格式
location 指定存储位置
Hive 的 DML 语法
表建好了，后面便是将数据导入。
数据加载
注意：
LOCAL关键字代表从本地文件系统加载文件，省略则代表从 HDFS 上加载文件：
-
从本地文件系统加载文件时，
filepath可以是绝对路径也可以是相对路径 (建议使用绝对路径)； -
从 HDFS 加载文件时候，
filepath为文件完整的 URL 地址：如hdfs://namenode:port/user/hive/project/ data1
-
filepath可以是文件路径 (在这种情况下 Hive 会将文件移动到表中)，也可以目录路径 (在这种情况下，Hive 会将该目录中的所有文件移动到表中)； -
如果使用 OVERWRITE 关键字，则将删除目标表（或分区）的内容，使用新的数据填充；不使用此关键字，则数据以追加的方式加入；
-
加载的目标可以是表或分区。如果是分区表，则必须指定加载数据的分区；
-
加载文件的格式必须与建表时使用
STORED AS指定的存储格式相同。
使用建议：
不论是本地路径还是 URL 都建议使用完整的。虽然可以使用不完整的 URL 地址，此时 Hive 将使用 hadoop 中的 fs.default.name 配置来推断地址，但是为避免不必要的错误，建议使用完整的本地路径或 URL 地址；
加载对象是分区表时建议显示指定分区。在 Hive 3.0 之后，内部将加载 (LOAD) 重写为 INSERT AS SELECT，此时如果不指定分区，INSERT AS SELECT 将假设最后一组列是分区列，如果该列不是表定义的分区，它将抛出错误。为避免错误，还是建议显示指定分区。
load data inpath '/hivedatas/techer.csv' into table techer;
-- 追加操作
load data local inpath '/export/servers/hivedatas/student.csv' into table student;
-- 覆盖操作
load data local inpath '/export/servers/hivedatas/student.csv' overwrite into table student;
-- 加载数据到指定分区
load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);
查询结果插入到表
-- 普通查询插入
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]]
select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement1 FROM from_statement;
-- 动态插入分区
-- 创建动态分区表
create table t(c1 string,c2 string)PARTITIONED BY (p_c1 string) row format delimited FIELDS terminated by ',' STORED AS TEXTFILE;
-- 插入数据
insert into table t_p partition(p_c1)
select t.*,c1 as p_c1 from t
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...)
select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...)
select_statement FROM from_statement;
注意：Hive 0.9.0 之前的版本动态分区插入是默认禁用的，而 0.9.0 之后的版本则默认启用。需要调整以下设置。
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
修改和删除
更新和删除的语法比较简单，和关系型数据库一致。需要注意的是这两个操作都只能在支持 ACID 的表，也就是事务表上才能执行。
-- 更新
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
--删除
DELETE FROM tablename [WHERE expression]
查询结果导出
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format]
SELECT ... FROM ...
-- 示例
INSERT OVERWRITE LOCAL DIRECTORY '/usr/file/ouput'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
SELECT * FROM emp_ptn;
数据查询
按以下hql建表
CREATE TABLE emp(
empno INT, -- 员工表编号
ename STRING, -- 员工姓名
job STRING, -- 职位类型
mgr INT,
hiredate TIMESTAMP, --雇佣日期
sal DECIMAL(7,2), --工资
comm DECIMAL(7,2),
deptno INT) --部门编号
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
-- 建表语句
CREATE TABLE dept(
deptno INT, --部门编号
dname STRING, --部门名称
loc STRING --部门所在的城市
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
CREATE EXTERNAL TABLE emp_ptn(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
PARTITIONED BY (deptno INT) -- 按照部门编号进行分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
单表查询
-- select
-- 查询表中全部数据
SELECT * FROM emp;
-- where
-- 查询 10 号部门中员工编号大于 7782 的员工信息
SELECT * FROM emp WHERE empno > 7782 AND deptno = 10;
-- DISTINCT
-- 查询所有工作类型
SELECT DISTINCT job FROM emp;
-- 分区查询
-- 查询分区表中部门编号在[20,40]之间的员工
SELECT emp_ptn.* FROM emp_ptn
WHERE emp_ptn.deptno >= 20 AND emp_ptn.deptno <= 40;
-- limit
-- 查询薪资最高的 5 名员工
SELECT * FROM emp ORDER BY sal DESC LIMIT 5;
-- group by
-- 查询各个部门薪酬综合
-- hive.map.aggr 控制程序如何进行聚合。默认值为 false。如果设置为 true，Hive 会在 map 阶段就执行一次聚合。这可以提高聚合效率，但需要消耗更多内存。
set hive.map.aggr=true;
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- order and sort
-- 使用 ORDER BY 时会有一个 Reducer 对全部查询结果进行排序，可以保证数据的全局有序性；
-- 使用 SORT BY 时只会在每个 Reducer 中进行排序，这可以保证每个 Reducer 的输出数据是有序的，但不能保证全局有序。
-- 查询员工工资，结果按照部门升序，按照工资降序排列
SELECT empno, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
-- having
-- 查询工资总和大于 9000 的所有部门
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
-- DISTRIBUTE BY
-- 如果想要把具有相同 Key 值的数据分发到同一个 Reducer 进行处理，这可以使用 DISTRIBUTE BY 字句。
-- 将数据按照部门分发到对应的 Reducer 上处理
SELECT empno, deptno, sal FROM emp DISTRIBUTE BY deptno SORT BY deptno ASC;
-- 如果 SORT BY 和 DISTRIBUTE BY 指定的是相同字段，且 SORT BY 排序规则是 ASC，此时可以使用 CLUSTER BY 进行替换，同时 CLUSTER BY 可以保证数据在全局是有序的。
多表查询
Hive 支持内连接，外连接，左外连接，右外连接，笛卡尔连接，这和传统数据库中的概念是一致的。
需要特别强调：JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定，否则就会先做笛卡尔积，再过滤，这会导致你得不到预期的结果。
-- INNER JOIN
-- 查询员工编号为 7369 的员工的详细信息
SELECT e.*,d.* FROM
emp e JOIN dept d
ON e.deptno = d.deptno
WHERE empno=7369;
--如果是三表或者更多表连接，语法如下
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
-- LEFT OUTER JOIN
-- 左连接
SELECT e.*,d.*
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
-- RIGHT OUTER JOIN
-- 右连接
SELECT e.*,d.*
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
-- FULL OUTER JOIN
SELECT e.*,d.*
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;
-- LEFT SEMI JOIN
-- LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。
-- 查询在纽约办公的所有员工信息
SELECT emp.*
FROM emp LEFT SEMI JOIN dept
ON emp.deptno = dept.deptno AND dept.loc="NEW YORK";
--上面的语句就等价于
SELECT emp.* FROM emp
WHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc="NEW YORK");
-- join
-- 笛卡尔积连接
SELECT * FROM emp JOIN dept;
本地模式
在上面演示的语句中，大多数都会触发 MapReduce, 少部分不会触发，比如 select * from emp limit 5 就不会触发 MR，此时 Hive 只是简单的读取数据文件中的内容，然后格式化后进行输出。在需要执行 MapReduce 的查询中，你会发现执行时间可能会很长，这时候你可以选择开启本地模式。
--本地模式默认关闭，需要手动开启此功能
SET hive.exec.mode.local.auto=true;
启用后，Hive 将分析查询中每个 map-reduce 作业的大小，如果满足以下条件，则可以在本地运行它：
- 作业的总输入大小低于：hive.exec.mode.local.auto.inputbytes.max（默认为 128MB）；
- map-tasks 的总数小于：hive.exec.mode.local.auto.tasks.max（默认为 4）；
- 所需的 reduce 任务总数为 1 或 0。
因为我们测试的数据集很小，所以你再次去执行上面涉及 MR 操作的查询，你会发现速度会有显著的提升。
Hive 的函数
hive 提供了多种函数，能够实现不同的功能可以使用以下命令查询内部函数
-- 查看内置函数
SHOW FUNCTIONS;
-- 查看month相关的函数
SHOW FUNCTIONS LIKE '*month*';
-- 查看函数用法
DESC FUNCTION function_name;
-- 查看 add_months 函数的详细说明并举例
DESC FUNCTION EXTENDED add_months;
时间转换函数
语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
hive> select unix_timestamp() from tableName;
1616906976
语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;
20210328
语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
hive> select unix_timestamp('2021-03-08 14:21:15') from tableName;
1615184475
语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
hive> select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;
1615184475
语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
hive> select to_date('2021-03-28 14:03:01') from tableName;
2021-03-28
语法: year(string date)
返回值: int
说明: 返回日期中的年。
hive> select year('2021-03-28 10:03:01') from tableName;
2021
hive> select year('2021-03-28') from tableName;
2021
日期函数
语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
hive> select datediff('2020-12-08','2012-05-09') from tableName;
213
语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
hive> select date_add('2020-12-08',10) from tableName;
2020-12-18
语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
hive> select date_sub('2020-12-08',10) from tableName;
2020-11-28
窗口函数
窗口聚合函数
-- 共4种用法
sum(...) over() -- 相当于对全表进行求和
sum(...) over(order by ...) -- 连续累积求和，排序，不分组
sum(...) over(partition by ...) -- 同组内所行求和，只分组不排序
sum(...) over(partition by ... order by ...) -- 在每个分组内，连续累积求和，分组+排序
窗口表达式
窗口表达式提供了一种控制行范围的能力如往前，往后n行
可以写多个列
-- 默认从第一行到当前行
sum(...) over(partition by ... order by ...)
sum(...) over(partition by ... order by ... , ...) -- 按多列分区
sum(...) over(partition by ... order by ... rows between unbounded preceding and current row)
-- 向前3行至当前行
sum(...) over(partition by ... order by ... rows between 3 preceding and current row)
-- 向前3行,向后1行
sum(...) over(partition by ... order by ... rows between 3 preceding and 1 following)
-- 当前行到最后一行
sum(...) over(partition by ... order by ... rows between current row and unbounded following)
-- 第一行至最后一行，即分组内所有行
sum(...) over(partition by ... order by ... rows between unbounded preceding and unbounded following)
窗口排序函数
row_number -- row_number家族，在每个分组中，为每行分配一个从1开始的唯一序号，不考虑重复
rank -- 考虑重复，挤占后续位置
dense_rank -- 考虑重复，不挤占后续位置
-- ntile将每个分组内的数据分为指定的若干个桶内，并且为每一个桶分配一个桶编号
select cookieid,createtime,pv
ntile(3) over(partition by cookieid order by createtime)
from website_pv_info
order by cookieid,createtime;
抽样函数
随机抽样 random
随机，但是速度慢
select * from website_pv_info order by rand() limit 2;
select * from website_pv_info distribute by rand() sort by rand() limit 2;
block 抽样
速度快，但是不随机
-- 按行数抽样
select * from website_pv_info tablesample (1 rows);
-- 根据数据大小百分比抽样
select * from website_pv_info tablesample (50 percent);
-- 根据数据大小抽样，支持b,k,m,g
select * from website_pv_info tablesample (1B);
bucket table 基于分桶表抽样
速度快+随机
-- 根据整行数据进行抽样
select * from website_pv_info tablesample ( bucket 1 out of 2 on rand());
类型转换
-- CAST可以转换的类型
-- 1．隐式类型转换规则如下
-- （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。
-- （2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
-- （3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
-- （4）BOOLEAN 类型不可以转换为任何其它的类型。
CAST('1' AS INT) -- 可以正常执行
CAST('X' AS INT) -- 执行失败返回 NULL
concat 函数
-- CONCAT 函数用于将多个字符串连接成一个字符串。
-- CONCAT(str1,str2,…)
SELECT CONCAT(id,',',name) AS con FROM info LIMIT 1;返回结果为
+----------+
| con |
+----------+
| 1,BioCyc |
+----------+
-- CONCAT_WS() 指定参数之间的分隔符
SELECT CONCAT_WS('_',id,name) AS con_ws FROM info LIMIT 1;
+----------+
| con_ws |
+----------+
| 1_BioCyc |
+----------+
case when
-- 简单 case
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- 搜索 case
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
UDTF（explode）和侧视图
-- explode
-- 接收 array 和 map 转换成多行
select explode(`array`(1,2,3,4,5));
-- Result:
-- col
-- 1
-- 2
-- 3
-- 4
-- 5
select explode(`map`("name","xxx",'sex','男','age','13'));
-- Result:
-- key value
-- name xxx
-- sex 男
-- age 13
-- 侧视图结合 explode
-- 侧视图语法
select ... from tableA lateral view UDTF(xxx) 表别名 as 列别名1,列别名2,列别名3;
行列转换
-- 多行转多列
-- 利用case when
-- 原表
-- students.id students.name students.subject students.score
-- 1 小明 语文 87
-- 2 张三 语文 27
-- 3 王五 语文 69
-- 4 李四 语文 99
-- 5 小明 数学 86
-- 6 马六 数学 33
-- 7 李四 数学 44
-- 8 小红 数学 50
-- 转换后
-- name chinese math
-- 小明 87 86
-- 小红 0 50
-- 张三 27 0
-- 李四 99 44
-- 王五 69 0
-- 马六 0 33
select name,
max(case subject when '语文' then score else 0 end) chinese,
max(case subject when '数学' then score else 0 end) math
from students group by name;
-- 多行转单列
-- 转换前
-- cookie.cookieid cookie.createtime cookie.pv
-- cookie1 2015-04-11 5
-- cookie1 2015-04-12 7
-- cookie1 2015-04-10 1
-- cookie1 2015-04-13 3
-- cookie1 2015-04-14 2
-- cookie1 2015-04-15 4
-- cookie1 2015-04-16 4
-- cookie2 2015-04-10 2
-- cookie2 2015-04-11 3
-- cookie2 2015-04-12 5
-- cookie2 2015-04-13 6
-- cookie2 2015-04-14 3
-- cookie2 2015-04-15 9
-- cookie2 2015-04-16 7
-- 转换后
-- cookieid createtimes pvs
-- cookie1 ["2015-04-10","2015-04-11","2015-04-12","2015-04-13","2015-04-14","2015-04-15","2015-04-16"] [1,5,7,3,2,4,4]
-- cookie2 ["2015-04-10","2015-04-11","2015-04-12","2015-04-13","2015-04-14","2015-04-15","2015-04-16"] [2,3,5,6,3,9,7]
select cookieid,collect_list(createtime) createtimes,collect_list(pv) pvs from cookie group by cookieid;
-- 其他函数
COLLECT_LIST(col) -- 多行合并成一行，不去重
COLLECT_SET(col) -- 多行合并成一行，将某字段的值进行去重汇总
CONCAT([string|col],[string|col],[string|col],....) -- 返回输入字符串连接后的结果，支持任意个输入字符串
CONCAT_WS('分隔符',[array|map]) -- 指定分隔符
-- 多列转多行
-- 主要使用 union 关键字
-- 转换前
-- rows2cols.col1 rows2cols.col2 rows2cols.col3 rows2cols.col4
-- a 1 2 3
-- b 4 5 6
-- 转换后
-- _u1.col1 _u1.col2 _u1.col3
-- a c 1
-- a d 1
-- a e 1
-- b c 4
-- b d 4
-- b e 4
select col1,'c' as col2,col2 as col3 from rows2cols
union all
select col1,'d' as col2,col2 as col3 from rows2cols
union all
select col1,'e' as col2,col2 as col3 from rows2cols;
-- 单列转多行
-- 转换前
-- aihao.id aihao.name aihao.age aihao.favors
-- 1 huangbo 45 a-c-d-f
-- 2 xuzheng 36 b-c-d-e
-- 3 huanglei 41 c-d-e
-- 4 liushishi 22 a-d-e
-- 5 liudehua 39 e-f-d
-- 6 liuyifei 35 a-d-e
-- 转换后
-- id name age favorslist
-- 1 huangbo 45 a
-- 1 huangbo 45 c
-- 1 huangbo 45 d
-- 1 huangbo 45 f
-- 2 xuzheng 36 b
-- 2 xuzheng 36 c
-- 2 xuzheng 36 d
-- 2 xuzheng 36 e
-- 3 huanglei 41 c
-- 3 huanglei 41 d
-- 3 huanglei 41 e
-- 4 liushishi 22 a
-- 4 liushishi 22 d
-- 4 liushishi 22 e
-- 5 liudehua 39 e
-- 5 liudehua 39 f
-- 5 liudehua 39 d
-- 6 liuyifei 35 a
-- 6 liuyifei 35 d
-- 6 liuyifei 35 e
select id,name,age,t.col1 as favorsList from aihao lateral VIEW explode(split(favors,'-')) t as col1;
-- lateral VIEW + explode
常见案例
查看前百分之 N
/*
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型
ntile(n)和where sorter = m 构成 n/m，如：ntile（2）和where sorted = 1 构成显示所有列的1/2
20% = 1/5 ==> ntile(5),where sorted = 1
*/
SELECT *
FROM (
SELECT name, orderdate, cost, NTILE(5) OVER (ORDER BY orderdate DESC) sorted
FROM business
) t
WHERE sorted = 1;
TOPN 问题
select * from (
select ROW_NUMBER() over(partition by ... order by ... DESC) as rank from tableName
) t where t.rank < 3;
与前一天相比
LAG(列名,前几行,'默认值')
LAG(name, 1)
LAG(name, 1,'default')
-- 1代表取前几条 1代表取q
-- 表示取前一条记录的name的值。
-- LEAD()函数与此类似，不过它是查询某字段的后N条记录的值。
连续登录/连续问题
-- 原数据
1,2020-01-01
1,2020-01-02
1,2020-01-07
1,2020-01-08
1,2020-01-09
1,2020-01-10
2,2020-01-01
2,2020-01-02
2,2020-01-03
2,2020-01-04
3,2020-01-02
3,2020-01-03
3,2020-01-04
-- 两种方案
-- 1.自连接，构建笛卡尔积
-- 2. 窗口函数
-- 自连接
-- 两天的连续登录问题使用自连接比较好做，大于2天不适合用自连接
-- 1.自连接
select a.user_id a_id,a.login_date a_dt,
b.user_id b_id,b.login_date b_dt
from login_log a,login_log b;
-- 2.查找用户id相同并且时间差为1的记录
select *
from (
select a.user_id a_id,a.login_date a_dt,
b.user_id b_id,b.login_date b_dt
from login_log a,login_log b
) t where t.a_id=t.b_id and datediff(t.a_dt,t.b_dt)=1;
-- 提取id，并去重
select distinct t1.a_id
from (
select *
from (
select a.user_id a_id,a.login_date a_dt,
b.user_id b_id,b.login_date b_dt
from login_log a,login_log b
) t where t.a_id=t.b_id and datediff(t.a_dt,t.b_dt)=1) t1;
-- 窗口函数
-- 适合于连续N天的问题
select distinct user_id from (
select user_id,login_date,
date_add(login_date,4-1) as nextday,
lead(login_date,4-1) over (partition by user_id order by login_date) as nextlogin
from login_log
) t where nextday = nextlogin;
-- nextday，手动算出 N 天后的时间
-- nextlogin，向下取 N 行进行验证
级联/累加求和
-- 编写Hive的HQL语句求出每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
-- 原表
usr,mon,num
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11
-- 两种方法
-- 1.group by 加自连接
-- 2.窗口函数
-- 1.group by 加自连接
-- 过于复杂，不写了
-- https://www.bilibili.com/video/BV1L5411u7ae?p=126
-- 方法2
-- 窗口函数
-- 关键点是
sum(num) over (partition by usr order by mon) accPV
with t as (
select usr,mon,num,
row_number() over (partition by usr,mon order by num DESC) top,
sum(num) over (partition by usr order by mon) accPV
from fangwen
)select usr,mon,num,accPV from t where top=1;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）