HIVE查漏补缺+复习

最新推荐文章于 2024-05-23 09:18:37 发布

xiaowei0548

最新推荐文章于 2024-05-23 09:18:37 发布

阅读量462

点赞数

分类专栏： HIVE 文章标签： hive

本文链接：https://blog.csdn.net/xiaowei0548/article/details/107696489

版权

HIVE 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

注：hive、oracle提供开窗函数，mysql8之前版本不提供，但Oracle发布的 MySQL 8.0版本支持窗口函数（over）和公用表表达式（with）这两个重要的功能！

HIVE知识扫盲

建表

drop TABLE IF EXISTS test_chw;
CREATE TABLE IF NOT EXISTS test_chw (
	`id` INT (12) NOT NULL AUTO_INCREMENT,
-- 	id int unsigned not null primary key auto_increment comment '主键',
	name varchar(20) default null comment '姓名',
	mobile char(11) default null unique comment '手机号码',
	address varchar(100) default null comment '居住地址',
	description text comment '个人简介', -- 当不确定长度时，可以使用text
	sex char(1) default null comment '性别',
	age tinyint default 0 comment '年龄', -- 年龄可以使用tinyint，范围为-128~127
	idno char(18) default null comment '身份证号码',
	`create_time` datetime NOT NULL DEFAULT '1990-01-01' COMMENT '创建时间',
	`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
	PRIMARY KEY (`id`),
  key telephone(mobile), -- 添加索引，提高查询效率
	UNIQUE KEY `uniq` (`idno`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'test';

介绍

事务

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

MySQL 字符串函数

合并多个字符串，并添加分隔符：

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;

格式化数字 "#,###.##" 形式：

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56

从字符串第一个位置开始的 6 个字符替换为 runoob：

SELECT INSERT("google.com", 1, 6, "runnob");  -- 输出：runoob.com

返回字符串 abc 中 b 的位置：

SELECT LOCATE('b', 'abc') -- 2

返回字符串 runoob 中的前两个字符：

SELECT LEFT('runoob',2) -- ru

比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1
SELECT STRCMP("runoob", "runoob");  -- 0
SELECT STRCMP("aunoob", "runoob");  -- -1
SELECT STRCMP("zunoob", "runoob");  -- 1

返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c
SELECT SUBSTRING_INDEX('a*b*c*d*e','*',3);  -- a*b*c

MySQL 数字函数

返回大于或等于 x 的最小整数　	
SELECT CEIL(1.5) -- 返回2

SELECT 10/3;  -- 3.3333 
SELECT 10%3;  -- 1 取余
SELECT MOD(10,3);  -- 1 取余
SELECT 10 DIV 3;  -- 3 取整
SELECT CEIL(1.8); -- 2 向上取整
SELECT FLOOR(1.8); -- 1 向下取整

	
返回以下数字列表中的最大值：

SELECT GREATEST(3, 12, 34, 8, 25); -- 34


返回以下数字列表中的最小值：

SELECT least(1,2,3,4,-1); -- -1

返回 x 的 y 次方　	
2 的 3 次方：

SELECT POW(2,3) -- 8

返回 0 到 1 的随机数　　	
SELECT RAND() --0.93099315644334
返回离 x 最近的整数	
SELECT ROUND(1.23456) --1
返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	
SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL 日期函数

按表达式 f的要求显示日期 d	
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -- 2011-11-11 11:11:11 AM
SELECT DATE_FORMAT('2011-11-11 11:22:33','%Y-%m-%d %H-%m-%s'); -- 2011-11-11 11-11-33
SELECT DATE_FORMAT('2011-11-11 11:22:33','%Y-%m-%d %s'); -- 2011-11-11 33

返回日期 d 是星期几，如 Monday,Tuesday	
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday

MySQL 高级函数

返回参数中的第一个非空表达式（从左向右）	
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com

Hive 分析函数（排名函数）、开窗函数

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

介绍：

开窗函数

开窗函数一般就是说的是over（）函数，其窗口是由一个 OVER 子句定义的多行记录，其作用就如同它的名字，就是限定出一个窗口。

分析函数

分析函数种类较多，指的是对数据进行各种处理，分析的函数，大体上包括但不仅限于：聚合函数，排名函数，取一些特定位置范围的数值的函数等等。

分析函数和聚合函数的关系

聚合函数的作用域是由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口，。聚合函数对其所作用的每一组记录输出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果，简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。

开窗函数与分析函数的关系

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化
可以理解为一个分析函数必然要搭配一个窗口函数，以指定原数据的规则，便于分析函数实现。

开窗函数

over（）开窗函数，其括号内部主要有两种形式，固定搭配，不能更改：
over(distribute by…sort by…) 和 over(partition by…order by…)

两种开窗方式区别

patition by是按照一个一个reduce去处理数据的，所以要使用全局排序order by
distribute by是按照多个reduce去处理数据的，所以对应的排序是局部排序sort by

开窗函数的窗口大小问题

窗口大小：hive的窗口大小默认是从起始行到当前行的
窗口大小的设置（也叫window子句）

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：无边界，
UNBOUNDED PRECEDING 表示从最前面的起点开始，
UNBOUNDED FOLLOWING：表示到最后面的终点
–其他AVG，MIN，MAX，和SUM用法一样

默认窗口大小是从起始行到当前行
partition by …order by…rows between unbounded preceding and current row
窗口大小为从起始行得到当前行。
partition by …order by… rows between 3 preceding and current row
窗口大小为从当前行到之前三行
partition by …order by… rows between 3 preceding and 1 following
窗口大小为当前行的前三行到之后的一行
partition by …order by… rows between 3 preceding and unbounded following
窗口大小为当前行的前三行到之后的所有行

排名函数

注意：排名函数不支持window子句，即不支持自定义窗口大小

row_number():

没有并列，相同名次顺序排序

rank():

有并列，相同名次空位(即类似于1 1 3)

dense_rank():

有并列，相同名次不空位（即类似于1 1 2）

注意：窗口函数的别名位置是在over()子句之后

lag() lead() first_value() last_value()

lag()

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上n行之内，若当某一行为NULL时候，取默认值，如不指定，则为NULL）

lead()

与LAG相反 LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

first_value()

取分组内排序后，截止到当前行，第一个值. 如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

last_value()

取分组内排序后，截止到当前行，最后一个值如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

1. 基础结构：

分析函数（如:sum(),max(),row_number()…） + 窗口子句（over函数）

2. over函数写法：

over（partition by id order by cnt）先根据id 字段分区，相同的id 分为一区，每个分区内根据createtime字段排序（默认升序）
　　
注：不加 partition by 的话则把整个数据集当作一个分区，不加 order by的话会对某些函数统计结果产生影响，如sum()

窗口示例hql：

SELECT 
 id,cnt
,sum(cnt) over(PARTITION BY id ) as n0
,sum(cnt) over(PARTITION BY id order by cnt ) as n1
,sum(cnt) over(PARTITION BY id order by cnt rows between unbounded preceding and current row) as n2 
,sum(cnt) over(PARTITION BY id order by cnt desc rows between unbounded preceding and current row) as n22
,sum(cnt) over(partition by id order by cnt rows between 1 preceding and current row) as n3 
,sum(cnt) over(partition by id order by cnt rows between 1 preceding and 1 following) as n4
,sum(cnt) over(partition by id order by cnt rows between unbounded preceding and unbounded following) as n5 
,avg(cnt) over(partition by id order by cnt rows between unbounded preceding and current row) as avg1
,min(cnt) over(partition by id order by cnt rows between unbounded preceding and current row) as min1
,min(cnt) over(partition by id order by cnt rows between 1 preceding and current row) as min2
,max(cnt) over(partition by id order by cnt rows between unbounded preceding and current row) as max1
,max(cnt) over(partition by id order by cnt rows between 1 preceding and 1 following) as max2
,ntile(2) over(partition by id order by cnt) as ntile
,row_number() over(partition by id order by cnt desc) as desc_row_number
,rank() over(partition by id order by cnt desc) as desc_rank
,dense_rank() over(partition by id order by cnt desc) as desc_dense_rank
FROM default.testchw_20200731 
order by id,cnt

结果：
在这里插入图片描述

hive group by聚合函数增强

1.grouping sets

grouping sets子句都可以根据UNION连接的多个GROUP BY查询进行逻辑表示

SELECT a，b，SUM（c）FROM tab1 GROUP BY a，b GROUPING SETS（（a，b），a，b，（））

等价于

SELECT a，b，SUM（c）FROM tab1 GROUP BY a，b
union
SELECT a，null，SUM（c）FROM tab1 GROUP BY a，null
union
SELECT null，b，SUM（c）FROM tab1 GROUP BY null，b
union
SELECT null，null，SUM（c）FROM tab1

2.GROUPING__ID

注意是两个下划线相连,说明聚合结果是属于(grouping sets)哪一个子集的的。

SELECT key, value, GROUPING__ID,count(*)
FROM T1
GROUP BY key, value
GROUPING SETS（（key，value），key，value）
;

等价于

SELECT key, value,0,count(*) -- 属于第1个GROUPING SETS子集,即（key，value）
FROM T1
GROUP BY key, value
union 
SELECT key, NULL,1,count(*) -- 属于第2个GROUPING SETS子集,即key
FROM T1
GROUP BY key
union 
SELECT NULL, value,2,count(*) -- 属于第3个GROUPING SETS子集,即value
FROM T1
GROUP BY value

3.WITH CUBE

CUBE是是group by字段的所有组合

GROUP BY a，b，c WITH CUBE

等同于

GROUP BY a,b,c GROUPING SETS（（a,b,c），（a,b）,（b,c）, （a,c）,（a），（b），（c），（））

4.WITH ROLLUP

ROLLUP子句与GROUP BY一起用于计算维度的层次结构级别的聚合。

GROUP BY a，b，c WITH ROLLUP

等同于

GROUP BY a，b，c GROUPING SETS（（a，b，c），（a，b），（a），（））

MySQL 运算符

1、算术运算符

MySQL 支持的算术运算符包括:

运算符	作用
+	加法
-	减法
*	乘法
/ 或 DIV	除法
% 或 MOD	取余

1、除

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+
2、商

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+
3、取余

mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

2、比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号	描述	备注
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配

1、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

3、逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号	作用
XOR	逻辑异或

异或：xor 、 ^

mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

工作中遇到的问题/需求

1、输入该月的每一天

SELECT
 DATE(date_add(DATE_ADD('2020-07-28', INTERVAL - DAY('2020-07-28') + 2 DAY),INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY)) as received_second_create_date 
-- ,round(33000*1.0/RIGHT(LAST_DAY('2020-07-28'),2),2) as warehouse_cunchu_fee
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY ( last_day( '2020-07-28' ) ) 
-- HAVING received_second_create_date='2020-07-28'
ORDER BY
	help_topic_id
;
结果：
received_second_create_date
2020-07-01
2020-07-02
2020-07-03
2020-07-04
2020-07-05
2020-07-06
2020-07-07
2020-07-08
2020-07-09
2020-07-10
2020-07-11
2020-07-12
2020-07-13
2020-07-14
2020-07-15
2020-07-16
2020-07-17
2020-07-18
2020-07-19
2020-07-20
2020-07-21
2020-07-22
2020-07-23
2020-07-24
2020-07-25
2020-07-26
2020-07-27
2020-07-28
2020-07-29
2020-07-30
2020-07-31

2、列转行

SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.name, ',', b.help_topic_id + 1 ), ',',- 1 ) AS tmp_optimizer_name
FROM
    (SELECT 'aaa,bbb,ccc,ddd,eee' as name) AS a
    JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( a.name ) - length( REPLACE ( a.name, ',', '' ) ) + 1 ) 
;
结果：
tmp_optimizer_name
aaa
bbb
ccc
ddd
eee

Mysql笔记

xiaowei0548

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
HIVE查漏补缺+复习

HIVE查漏补缺HIVE知识扫盲建表介绍事务MySQL 字符串函数MySQL 数字函数MySQL 日期函数MySQL 高级函数Hive开窗函数介绍：1. 基础结构：2. over函数写法：MySQL 运算符1、算术运算符2、比较运算符3、逻辑运算符工作中遇到的问题/需求1、输入该月的每一天2、列转行Mysql笔记注：hive、oracle提供开窗函数，mysql8之前版本不提供，但Oracle发布的 MySQL 8.0版本支持窗口函数（over）和公用表表达式（with）这两个重要的功能！HIVE知识扫
复制链接

扫一扫