sql语法（mysql,sqlserver,hivesql）
一．mysql 与sqlserver与语法
万事皆备于我，我心即使宇宙。
不定时补充笔记。
参考文档
sqlserver文档
https://l e arn.microsoft.com/zh-cn/sql/?view=sql-server-2017
sql server 2019 从入门到精通
顺序
编码问题
--COLLATE Chinese_PRC_CI_AI_WS
--COLLATE SQL_Latin1_General_CP1_CI_AS
Transact-SQL语法
如何给标识符起名
在创建或引用数据库实例，如表、索引、约束等时，必须遵守 SQL Server 的命名规则，否则可能发生一些难以预测和检测的错误。
1. 标识符分类
2. 规则标识符
3. 界定标识符
4. 标识符规则
5. 对象命名规则
6. 实例的命名规则
常量与变量
数字常量
integer 常量由没有用引号括起来，并且不包含小数点的数字字符串来表示。integer 常量必须全部为数字，它们不能包含小数。
decimal 常量由没有用引号括起来，并且包含小数点的数字字符串来表示。
float 和 real 常量使用科学记数法来表示。
money 常量以前缀为可选的小数点和可选的货币符号的数字字符串来表示。money 常量不使用引号括起来。
字符串常量
字符串常量括在单引号内并包含字母和数字字符（a~z、A~Z 和 0~9）以及特殊字符，如感叹号（!）、at 符（@）和数字号（#）。
Unicode 字符串
Unicode 字符串的格式与普通字符串相似，但它前面有一个 N 标识符（N 代表 SQL92 标准中的区域语言）。N 前缀必须是大写字母。
日期和时间常量
日期和时间常量使用特定格式的字符日期值来表示，并用单引号括起来。
符号常量
1分隔符
在 T-SQL 中，双引号有两层意思。除了引用字符串之外，双引号还能够用来做分隔符，也就是所谓的定界表示符（delimited identifier）。分割标识符是标识的一种特殊类型，通常将保留当作标识符并且用数据库对象的名称命名空间。
2. 标识符
在 T-SQL 中，标识符用于识别数据库对象如数据库、表和索引。它们通过字符串表示出来，这些字符串的长度可以达到 128 个字符，还包含字母、数字或者下面的字符：“_”、“@”、“#”和“$”。
全局变量
全局变量是 SQL Server 系统提供的内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。
全局变量通常存储一些 SQL Server 的配置设定值和统计数据。
用户可以在程序中用全局变量来测试系统的设定值或者是 Transact-SQL 命令执行后的状态值。
局部变量
局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。
在批处理和脚本中变量可以有如下用途：作为计数器计算循环执行的次数或控制循环执行的次数，保存数据值供控制流语句测试以及保存由存储过程代码返回的数据值或者函数返回值。
局部变量被引用时要在其名称前加上标志“@”，而且必须先用 DECLARE 命令声明后才可以使用。
批处理和脚本
批处理是同时从应用程序发送到SQL Server 并得以执行的一组单条或多条Transact-SQL语句。这些语句为了达到一个整体的目标而同时执行。GO 命令表示批处理的结束。
如果Transact-SQL 脚本中没有 GO 命令，那么它将被作为单个批处理来执行。
SQL Server 将批处理中的语句作为一个整体，编译为一个执行计划，因此批处理中的语句是一起提交给服务器的，所以可以节省系统开销。
运算符和表达式
运算符是一些符号，它们能够用于执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。
在 SQL Server 2019 中，运算符主要有以下 6 大类：算术运算符、赋值运算符、比较运算符、逻辑运算符、连接运算符以及按位运算符。
表达式在 SQL Server 2019 中也有非常重要的作用，SQL 语言中的许多重要操作也都需要使用表达式来完成
算术运算符
比较运算符
比较运算符用来比较两个表达式的大小，表达式可以是字符、数字或日期数据，其比较结果是布尔值。
比较运算符测试两个表达式是否相同。除了 text、ntext 或 image 数据类型的表达式外，比较运算符可以用于所有的表达式。
逻辑运算符
逻辑运算符可以把多个逻辑表达式连接起来测试，以获得其真实情况。返回带有 TRUE、FALSE 或 UNKNOWN 值的 Boolean 数据类型。
连接运算符
加号（+）是字符串串联运算符，可以将两个或两个以上字符串合并成一个字符串。其他所有字符串操作都使用字符串函数（如 SUBSTRING）进行处理。
默认情况下，对于 varchar 数据类型的数据，在 INSERT 或赋值语句中，空的字符串将被解释为空字符串。在串联 varchar、char 或 text 数据类型的数据时，空的字符串被解释为空字符串。
按位运算符
按位运算符在两个表达式之间执行位操作，这两个表达式可以为整数数据类型类别中的任何数据类型。
运算符的优先级
当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的顺序可能严重地影响所得到的值。
表达式
表达式是指用运算符和圆括号把变量、常量和函数等运算成分连接起来的有意义的式子，即使单个的常量、变量和函数也可以看成是一个表达式。
表达式有多方面的用途，如执行计算、提供查询记录条件等。
根据连接表达式的运算符进行分类，可以将表达式分为算术表达式、比较表达式、逻辑表达式、按位表达式和混合表达式等；
根据表达式的作用进行分类，可以将表达式分为字段名表达式、目标表达式和条件表达式。
通配符
查询时，有时无法指定一个清楚的查询条件，此时可以使用 SQL 通配符，通配符用来代替一个或多个字符，在使用通配符时，要与LIKE运算符一起使用。
标识符
@ 参数，变量
@@ 全局变量
# 临时表 存储过程
## 全局的临时表，存储过程
Float另一种写法 –0.5E-2
Money 数据类型￥12
中文：unicode N’
日期 ：单引号’时间’： ‘December 5,1985’ / ‘5 December ,1985’ /’12/5/85’/’1985-12-5’/’851205’
常量
日期 用单引号 '2019-10-10'
中文 加N N'我i'
单引号 前面再加一个' select ''''
打印print 'hell word! '
注释
单行 --
多行/* */
逗号写在字段前面
注意换行
每行尽量以核心语句开头
核心语句后的内容能写在一行尽量写在一行
where条件过长时，每个都要换行
数据类型
数值
整数 integer 一般默认用integer integer(10)
浮点数 float /real/double 一般默认 5舍6入
固定位小数 demical(4,2), 默认demical(10,0) 没有补0
字符串，文本：
可变长字符串 varchar(255)
定长 char(10) 不足补空格
时间：date 日期 time 时间 datetime 日期+时间
整数类型
保存人的年龄（1-100） ：tinyint
保存某个状态值（0、1）：tinyint
sqlserver数据类型
常用
int,float,nvchar，date,time,datetime
其他
tinyint 0-255
smallnt 2字节 16位 1位+-号 15位长度 -2^15 - 2^15-1
int 4字节 32位
bigint 8字节 63位
datretime2 比datetime 范围更大
datetimeoffset 标准世界时间utc 加时区时间偏移
bit 位数据 0/1
binary 二进制
rowversion 计数器 插入或者更新时自动加1且唯一
timestamp 计时器 插入或者更新时自动插入且唯一
uniqueidentifier 全球唯一标识符
cursor 游标 一行数据
table 数据集 表
xml
可自定义数据类型
数据操控 DML（对表中数据操控）
数据操作语句（Data Manipulation Language）是使用户能够查询数据库及操作已有数据库中数据的语句，其中包括数据库插入语句、数据更改语句、数据删除语句和数据查询语句等。
通配符
‘[xz]‘ 匹配[]内任意字符（x或z）
‘[!xz]‘ /‘[^xz]‘ 匹配没有 []内任意字符（没有x，z）
隐形符号char
字段内容之间有空格，但是使用 like '% %' 却无法查询出相应的数据，此时就有需要使用到这三个特殊字符
char(9) 水平制表符
char(10)换行键
char(13)回车键
直接在sql的命令框中输入以下字符可查看结果：
print 'A'+char(10)+'B'
print 'A'+char(9)+'B'
print 'A'+char(13)+'B'
数据库中查询是否存在这几个特殊字符：
SELECT * FROM 表名WHERE CHARINDEX(字段, CHAR(9)) > 0
SELECT * FROM 表名WHERE CHARINDEX(字段, CHAR(10)) > 0
SELECT * FROM 表名WHERE CHARINDEX(字段, CHAR(13)) > 0
查询 SELECT
单列查询，多列查询，所有列查询
查询列：别名 AS，常数添加，字符
中文别名：用中文时需要双引号（””）
四则运算： 列与列 有空值行过滤， 列与数字
拼接 concat（列1，列2）有空值的，结果为空值 concat_ws(‘拼接符’，列1，列2，) 空值自动跳过
去重 distinct列名
排序 ORDER BY 列名
默认ASC升序 DESC降序 空值最小
单列排序，多列排序，列位置排序（从1开始计算位置）
汉字按a-z排序 order by convert（列名 USING gbk）;
limit
limit 3 选取前三行
limit3,2 跳过三行，再取两行。
过滤WHERE 列名 比较
计算符： = ，！=，<>,<=,>=
四则运算，列与数字，列与列
空值替换 IFNULL（列名，0）
数值两者之间： BETWEEN 5 AND 10；两端都包含。
取空值 WHERE 列名 IS NULL
取非空值 WHERE 列名 IS NOT NULL；
模糊查询 WHERE 列名LIKE ’......-....%’
_ 代替一个 ,%代替0个或者多个
转义\_ ,\%
多重过滤
and, or , ，not 条件
列条件AND 列条件 OR；
多个过滤，从左到右，依次运算，多使用（）
在，不在过滤
in(，，，)
not in()
in /not in(内部只能用一条子查询)
子查询
标量子查询 ：子查询是常数
普通子查询 ： 子查询当表
关联子查询 分组进行比较
在细分的组内进行比较时，需要使用关联子查询
分组比较 where =
group by product_type 可省略
选取出办公用品、衣服和厨房用具三类商品中高于该类
商品的平均销售单价的商品
select name from classes where id in (select cls_id from students where cls_id is not null);
多条件
select * from students where (age, height) = (select max(age), max(height) from students);
子查询关键字
Any,some
子查询中常用子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS。
ANY 和 SOME 关键字是同义词，表示满足其中任一条件。它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
all
ALL 关键字与 ANY 和 SOME 不同，使用 ALL 时需要同时满足所有内层查询的条件。例如，修改前面的例子，用 ALL 操作符替换 ANY 操作符。ALL 关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为 TRUE，则返回TRUE。
EXISTS
EXISTS 关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么 EXISTS 的结果为 TRUE，此时外层查询语句将进行查询；
如果子查询没有返回任何行，那么 EXISTS 返回的结果是 FALSE，此时外层语句将不进行查询。
查询所有选修了“C1”课程的学生名
in
select StudentName from 学生表
where StudentNo in (select StudentNo from 选课表 where CourseNo=‘C1’)
exists
select StudentName from 学生表
where exists (select 1 from 选课表 where 选课表.StudentNo=学生表.StudentNo and 选课表.CourseNo='C1')
如果子查询与主表有关联，则返回，否则过滤掉
先在外层查询中取“学生表”的第一行记录，利用该记录的相关属性值（在exists子查询 的where子句中用到的列）处理内层查询，若外层的where子句返回“true”，则本条记录放入结果表中。然后再取下一行记录，重复上述过程直到外层表遍历完毕。
Exists语句不关心子查询返回的具体内容，因此用“exists(select 1 from)”来判断子查询是否返回记录。
Exists(select)：若子查询的结果集非空时，exists()表达式返回true；子查询的结果集为空时，exists()表达式返回false。
Not Exists(select)：若子查询的结果集非空时，not exists()表达式返回false；子查询的结果集为空时，not exists()表达式返回true。
查询没所有选修“C1”课程的学生名
select StudentName from 学生表
where not exists (select 1 from 选课表 where 学生表.StudentNo=选课表.StudentNo and CourseNo=‘C1’)
https://blog.csdn.net/xiaouncle/article/details/88084889
聚合函数
计数：
表计数 以最大的为准
COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
计数列count（列） 空值忽略
计数不同 count(distinct 列)
列中最大值，最小值： max(列) min(列) 忽略空值
求和 可以四则运算 sum(列-列) 忽略空值
求均值 avg(列*2) 分母不对空值计数
有空值用 avg(ifnull(列名，0))
分组 group by
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
说明:
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果
分组聚合：聚合键 聚合
select m.聚合键 ，聚合函数 from 表as m where 条件 group by m.聚合键
空值单独一组，聚合键不要取别名
分组再过滤 having group by 后面 必须select 中有的字段
select gender,count(*) from students group by gender having count(*)>2;
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
select gender,count(*) from students group by gender with rollup;
group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
select gender,group_concat(name) from students group by gender;
表联结 可以加条件
where联结(=inner join ) from.... where a = b and 内部连接
select a.列... b.列...(全部：a.*，b.*)
from 表1 as a，表2 as b
where a.列a=b.列2 and 条件
内部联结 inner join（join） on and 表连接前过滤
select a.列... b.列...(全部：a.*，b.*)
from 表1 as a inner join表2 as b
on a.列a=b.列2 and 条件过滤(等于where)
自联结
外部联结
左外联结 left outer join( left join)... on... and
右外联结 right outer join (right join) ... on.. and
全外联结 full outer join (full join)... on and..
交叉联结 笛卡尔积
交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积
from cross join ,
FROM 表a,表b
多表联结
表1 inner join 表2 on 条件 innerjoin 表3 on 条件 2
表连接cross apply和outer apply
常用于，左表一条匹配右表多条，对右表数据进行限制。
右表可以是有条件的跟左表的记录匹配，而条件的值可以来至于左表。
cross apply 类似（inner join，当匹配不到的时候，过滤）
CROSS APPLY 的意思是“交叉应用”，在查询时首先查询左表，然后右表的每一条记录跟左表的当前记录进行匹配。匹配成功则将左表与右表的记录合并为一条记录输出；匹配失败则抛弃左表与右表的记录。（与 INNER JOIN 类似）
查询每个学生最近两次的考试成绩
SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1
CROSS APPLY(
SELECT TOP 2 * FROM Score AS T
WHERE T1.StudentNo = T.StudentNo
ORDER BY T.ExamDate DESC
) AS T2
outer apply 类似 （left outer join 当匹配不到的时候，右表返回空）
OUTER APPLY 的意思是“外部应用”，与 CROSS APPLY 的原理一致，只是在匹配失败时，左表与右表也将合并为一条记录输出，不过右表的输出字段为 null。（与 LEFT OUTER JOIN 类似）
查询每个学生最近两次的考试成绩,没有参加考试的同学成绩补 null
SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1
OUTER APPLY(
SELECT TOP 2 * FROM Score AS T
WHERE T1.StudentNo = T.StudentNo --连接条件写在里面
ORDER BY T.ExamDate DESC
) AS T2
组合查询 上下连接 行相加 union
seleect from
union
select from group by
.....
自动过滤重复行，第一条给字段取名 ，最后一条 from 之后语句 作用所有结果
union ALL 不过滤重复行
增-插入行 insert into ....values（）
插入完整行：一般不用，要求一一对应，容易出错
insert into 表名 values（1，2，‘我’）
插入部分行；没插入的为空值
insert into 表名（列1，列3，列2） values（1，2，‘我‘）
多行插入：插入检索数据
insert into 表名（列1，列3，列2） values（1，2，‘我‘）,
(),();
insert into 表名（列1，列3，列2）
select.....
插入数据，如果中已经存在相同的记录，则忽略当前新数据
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");
改-更新 update.. set ..where
列全部更新
update 表名
set 列名= ，列名2=.....
Set 列名=null 可以为空值
列部分更新
update 表
set 列名= ，列名2=.....
where
ORDER BY 子句; --可选
LIMIT 子句; --可选
联结外表更新
update 表 inner join 表2
set 列名= ，列名2=.....
where 联结条件 and
删 delete from.. where.
筛选删
delete from 表名 where。。。。。
全删,清空表
truncate table b表名
不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录
应该先select,再命名表，再查询全部，再删
delete from titles_test
where id not in(
select * from
(select min(id)from titles_test group by emp_no)as t
)
数据定义 DDL 对表，数据库操作
数据定义语句 DDL，是用于描述数据库中要存储的现实世界实体的语言。
作为数据库管理系统的一部分，DDL 用于定义数据库的所有特性和属性，例如行布局、字段定义、文件位置，常见的数据定义语句有：CREATE DATABASE、CREATE TABLE、CREATE VIEW、DROP VIEW、ALTER TABLE 等。
数据库操作
SHOW DATABASES
CREATE DATABASES 库名
USE 库名
创建表
create table 表名（
字段名 数据类型 列约束，
sex char（1），
birt date null,
death date not null，
该表的约束 ，
PRIMARY KEY (birt ) ）
创建表 不启用压缩
create table test
(
id int not null identity(1,1),
t_id int not null,
t_desc char(200) not null, -----行压缩主要是为数值类型和固定长度的类型，使用可变长度的存储，所以用char，突出效果
constraint pk_tb primary key(id)
)with (data_compression=none)
----data_compression就是数据压缩的关键字，其中有none(不启用数据压缩),row(行压缩),page(页压缩)
对字段约束
SQL Server 2019 中有 5 种约束，分别是：主键约束（primary key constraint）、唯一性约束（unique constraint）、检查约束（check constraint）、默认约束（default constraint）和外键约束（foreign key constraint）。
约束是用来保证数据库完整性的一种方法，设计表时，需要定义列的有效值并通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性，约束是独立于表结构的，它作为数据库定义的一部分在创建表时声明，可以通过对象资源管理器或者 ALTER TABLE 语句添加或删除。
not null
primary key 主键
当一列重复时，可以使用两列 PRIMARY KEY (shop_id, product_id)
外键
foreign key 列名 references 其他表名（其他表主键）
cheack(字段名>5)
check(字段名（‘m’,’f’）)
default 0 默认0 列约束
创建表添加约束
直接添加 birt date null primary key
尾部添加 primary key(birt)
直接添加外键约束
birt date not null refererences 其他表名（其他表主键）
修改表添加约束
alter table 表名 add constraint unique(列名)
alter table 表名 add constraint foreign key(列名) references 其他表名（其他表主键）
复制表
create table 表名 as 其他表（可用 select from 获取）；
标准sql
select 字段 into 表 from
修改表
加单个
ALTER TABLE 表名ADD 字段名 类型 属性
加多个
ALTER TABLE <表名> ADD （字段名 类型 属性，……）；
删
ALTER TABLE 表名 drop 字段名，
ALTER TABLE 表名drop columns 字段名，
修改
ALTER TABLE 表名 modify columns 字段名 属性 类型；
删除表
drop table 表名
如果存在，删除表
--只能用一次，如果存在，先删，在创建
IF OBJECT_ID('tempdb..#TSDIPRMT_prm') IS NOT NULL
DROP TABLE #TSDIPRMT_prm
重命名（可多个）
rename table 表名1 to 表名 2，rename table 表名2 to 表名 3；
Merge 同时Insert,Update,Delete
将Insert,Update,Delete简单的并为一句，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。
MERGE INTO table_name AS TARGETtable --目标表
USING (table|view|sub_query) AS SOURCEtable --源表 可以是视图，查询，表
ON（TARGETtable.[PolicyNo] = SOURCEtable.[PolicyNo]) --连接
WHEN MATCHED and 条件 THEN ---当键一样时，更新
UPDATE
SET targettable.name = sourcetable.name,
WHEN NOT MATCHED and 条件 THEN --当目标没有，源表有，插入
INSERT (column_list) VALUES (column_values);
when NOT MATCHED by Source THEN --当目标有，源表没有，删除，不常用
delete
OUTPUT $ACTION as ACTION --数据变动显示
inserted.id ---插入的id 输出变更
delete.id --删除的id
数据控制 DCL 对进程，权限操作
DCL 控制语句用来设置、更改用户或角色权限，包括 GRANT、DENY、REVOKE 等语句。
GRANT 语句用来对用户授予权限，REVOKE 语句可用于删除已授予的权限，DENY 语句可用于防止主体通过 GRANT 获得特定权限。
默认状态下，只有 sysadmin、dbcreater、db_owner、db_securityadmin 等成员有权执行数据控制语言。
授予权限操作——GRANT
拒绝权限操作——DENY
收回权限操作——REVOKE
声明，赋值，打印
数据声明——DECLARE
数据声明语句可以声明局部变量、游标变量、函数和存储过程等，除非在声明中提供值，否则声明之后所有变量将初始化为 NULL。可以使用 SET 或 SELECT 语句对声明的变量赋值。
DECLARE 语句声明变量的基本语法格式：
数据赋值——SET
SET 命令用于对局部变量进行赋值，也可以用于用户执行 SQL 命令时设定 SQL Server 中的系统处理选项，SET 赋值语句的语法格式如下：
数据输出——PRINT
PRINT 语句可以向客户端返回用户定义信息，可以显示局部或全局变量的字符串值。其语法格式：
局部变量
declare @myname int --创建 默认null
varchar(10) 字符串
set/select @myname=12 --赋值
select @myname --使用
打印中引用局部变量
print convert(varchar(10),@myname) +'hell word!'
--声明的变量 初始为null
Declare @username varchar(20)
--变量赋值,可以是表达式 set/select
Set @username=‘newadmin’
Select @username=‘newadmin’
取一列数据最后一条记录
declare @a int
select @a=[product_cd] FROM [Travel_China].[dbo].[产品名称对应表]
select @a as lastScore
print ‘my name’
print @name+‘my name’
流程控制语句
到现在为止，介绍的 T-SQL 代码都是按从上到下的顺序执行，但是通过 T-SQL 中的流程控制语句，可以根据业务的需要改变代码的执行顺序。
T-SQL 中可以用来编写流程控制模块的语句有：BEGIN…END 语句、IF…ELSE 语句、CASE 语句、WHILE 语句、GOTO 语句、BREAKE 语句、WAITFOR 语句和 RETURN 语句。
BEGIN…END 语句块
语句块是多条 Transact-SQL 语句组成的代码段，从而可以执行一组 Transact-SQL 语句。
BEGIN 和 END 是控制流语言的关键字。BEGIN…END 语句块通常包含在其他控制流程中，用来完成不同流程中有差异的代码功能。例如，对于 IF…ELSE 语句或执行重复语句的 WHILE 语句，如果不是有语句块，这些语句中只能包含一条语句，但是实际的情况可能需要复杂的处理过程。
BEGIN...END 语句块允许嵌套。
Begin
语句块
End
If.. Else 单条件
IF…ELSE 语句用于在执行一组代码之前进行条件判断，根据判断的结果执行不同的代码。
IF...ELSE 语句对布尔表达式进行判断，如果布尔表达式返回 TRUE，则执行 IF 关键字后面的语句块；如果布尔表达式返回 FALSE，则执行 ELSE 关键字后面的语句块。
Declare @ age int
Select @age=40
If @ <30
Begin
Xxxxx
End
Else
Begin
Xxxxx
End
case when 多条件
CASE 是多条件分支语句，相比 IF…ELSE 语句，CASE 语句进行分支流程控制可以使代码更加清晰，易于理解。
CASE 语句也根据表达式逻辑值的真假来决定执行的代码流程，CASE 语句有两种格式。
case when then else end as name
while 循环
WHILE 语句根据条件重复执行一条或多条 T-SQL 代码，只要条件表达式为真，就循环执行语句。
在 WHILE 语句中可以通过 CONTINUE 或者 BREAK 语句跳出循环。
While…break…continue
declare @myname int
select @myname=12
while @myname< 20
begin
print '1'
select @myname= @myname+1
end
return
从查询或过程中退出
print '1'
goto xx 跳转
GOTO 语句表示将执行流更改到标签处。跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。
GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。
goto jump --跳转到 标签jump
xxxxx
jump :
xxxx
goto 标签名
标签名：
print '1'
goto a1
print '2'
print '4'
print '4'
a1:
print '5'
waitfor 暂停
WAITFOR 语句用来暂时停止程序的执行，直到所设定的等待时间已过或所设定的时刻快到，才继续往下执行。
延迟时间和时刻的格式为“HH:MM:SS”。在 WAITFOR 语句中不能指定日期，并且时间长度不能超过 24 小时。
waitfor delay ’00:0010’ --等待10秒
waitfor time ’09:00:00’ --指定9点·运行
暂停10s
waitfor delay '00:00:10'
print '1'
RETURN语句
RETURN 表示从查询或过程中无条件退出。
RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。RETURN 之后的语句是不执行的。
批处理语句
批处理是从应用程序发送到 SQL Server 并得以执行的一条或多条 T-SQL 语句。
使用批处理时，会有一些注意事项需要大家重视。
索引
create index 索引名 on 表名（列名）
创建唯一索引
create unique index 索引名 on 表名（列名）
创建表时，添加索引
CREATE TABLE t(
INDEX (列名1,列名2) );
使用索引
SELECT * FROM table_name
USE INDEX(index_list)
WHERE condition;
使用强制索引
SELECT * FROM 表名
FORCE INDEX (index_list)
WHERE condition;
删除索引
DROP INDEX index_name ON table_name
SSMS中创建索引
查看表中的索引sp_helpindex
execute sp_helpindex @objname = 'TravelPolicy';
--TravelPolicy 表名
go
删除索引
drop index 表名.索引名
drop index Employee.PK__Employee__3214EC277D95E615;
go
创建普通索引
create index [index_mode] on [cn_name]([car_mode]);
index_mode自定义索引名
cn_name表名
car_mode列名
创建单个普通索引
SQL CREATE INDEX 语法 在表上创建一个简单的索引。允许使用重复的值
CREATE INDEX index_name
ON table_name (column_name);
创建组合索引索引
多给列用逗号隔开
CREATE INDEX PersonIndex
ON Person (LastName, FirstName);
如果存在，删除已有索引
create index ix_com_Employee_IDName on Employee (ID,Name)with (drop_existing = on);
创建唯一索引
SQL CREATE UNIQUE INDEX 语法 在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
create unique index index_name on table_name (column ASC|DESC[,.....])with (drop_existing = on);
在创建表上的索引前，我会删除表上的所有索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
创建主键时，创建索引
创建主键的时候，指定为普通索引
ALTER TABLE table_name ADD CONSTRAINT cons_name priamry KEY(columnname ASC|DESC,[.....]) WITH (drop_existing = ON);
创建主键的时候，指定为聚集索引
ALTER TABLE employee ADD CONSTRAINT pk_for_employee PRIMARY KEY CLUSTERED (id);
通过此方式创建的索引无法删除
创建聚集索引
create clustered index ix_name on table_name(columnName ASC|DESC[,......]) with (drop_existing = on);
create clustered index ix_clu_for_employee_ID on Employee(ID);
go
视图=表 保存的是查询语句
可以在视图上，再创建视图，但尽量使用单一视图
不好更新
创建视图
视图名中加上view :view_user，便于认识
CREATE VIEW 视图名称(视图列名1, 视图列名2, ) AS
<SELECT语句>
定义视图时不能使用ORDER BY子句:数据行都是没有顺序的
修改视图
ALTER VIEW <视图名> AS <SELECT语句>
更新视图：原表数据也会更新，不要用
UPDATE productsum
SET sale_price = '5000'
WHERE product_type = '办公用品';
删除视图
不用之后要删除
drop view 视图名
触发器
创建触发器
before | after （insert | update|delete）on 表名
create trigger 触发器名（由时间-表-动作组成）
触发时间 触发动作 on 表名
for each row
begin
执行语句；（分号结尾）
end；（分号结尾）
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被删除的原数据，NEW 用来表示将要或已经插入的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： new.columnName （columnName 为相应数据表某一列名
create trigger after_employees_test
after insert on employees_test
for each row
begin
insert into audit values(new.ID,new.NAME);
end
事务管理
1不自动提交
set autocommit=0
开始一个事务
START TRANSACTION;
begin;
提交
commit
回滚
rollback;
设置保留点
savepoint 名1；
回滚保留点1
rollback to 名1；
删除保留点
release savepoint 名1；
过程（固化）
创建(navicat)
create definer=’root’@’localhost’ procedure’过程名’（in ‘变量’ int)
begin
end
in/out/inout 输入输出类型
命令行创建
create procedure 过程名（）
sql语句；
执行
call procedure 过程名（）；
删除
drop procedure 过程名
二．函数
自定义函数
https://www.yiibai.com/sqlserver
CREATE FUNCTION xuefen(@inputxf int) RETURNS nvarchar(10)
BEGIN
declare @retrunstr nvarchar(10)
If @inputxf >=50
set @retrunstr='取得学分'
else
set @retrunstr='未取得学分'
return @retrunstr
END
分组函数
分组连接
group_concat（列名，Y） ，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。
SELECT dept_no,group_concat(emp_no) employees
FROM dept_emp GROUP BY dept_no
with ROLLUP - 计算合计及小计
SELECT product_type
,regist_date
,SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type, regist_date WITH ROLLUP
列转行，添加逗号
方法1
STRING_AGG()
等于mysql GROUP_CONCAT()
a,b,c
SELECT id,STRING_AGG(Genre, ',') from data
group by id，
1) 喂猫 2) 水犬 3) 喂花园 4)
SELECT STRING_AGG(CONCAT(TaskId, ') ', TaskName), ' ')
FROM data
空值替换成N/A
SELECT STRING_AGG(ISNULL(TaskCode, 'N/A'), ', ')
对结果进行排序，通过WITHIN GROUP
SELECT ar.ArtistName,
STRING_AGG(al.AlbumName, ', ') WITHIN GROUP (ORDER BY al.AlbumName DESC)
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ArtistName;
方法2 较复杂
https://blog.csdn.net/qq_35972907/article/details/115670367
select id,
stuff((select distinct ','+uid from t1 where a.id=id for xml path('')),1,1,'') AS items
from t1 as a
group by id
逻辑判断函数
any() /some()
ANY/SOME 可以用MIN/MAX代替
where(Having) 条件 > any(10,20,30)
# 大于其中任何一个值就可以，那么，他与使用Min有什么区别呢？
where(Having) 条件 > min(10,20,30)
where(Having) 条件 < any(10,20,30)
# 小于其中任何一个值就可以，那么，他与使用Max有什么区别呢？
where(Having) 条件 < max(10,20,30) # 使用max替换了
all
all（列），all(19，10)
也可以使用min和max代替
ALL运算符是一个逻辑运算符，它将单个值与子查询返回的单列值集进行比较。ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询、
含有关键字exists，not exist查找 等于表连接过滤
要在子查询中添加联结条件
区别 in not in 先查询子查询
exists, not exists 先查询主表 ，适合主表较小，子表较大
查找未分配具体部门的员工的所有信息
select * from employees as e
where not exit (
select emp_no from dept_emp as d
where e.emp_no !=d.emp_no)
流程控制函数
if 函数
SELECT *,IF(销售数量>200,'优秀','一般') FROM `销售表`
case when可以用来行转列
CASE 语句始终位于 SELECT 条件中。
CASE 必须包含以下几个部分：WHEN、THEN和 END。ELSE 是可选组成部分，用来包含不符合上述任一 CASE 条件的情况。
你可以在 WHEN 和 THEN之间使用任何条件运算符编写任何条件语句（例如 WHERE），包括使用 AND 和 OR 连接多个条件语句。
第一种 条件
SELECT `店号`,`商品编码`,`销售数量`,
CASE
WHEN `销售数量`>250 THEN '优'
WHEN `销售数量`>150 THEN '良'
WHEN `销售数量`>100 THEN '中'
ELSE '差'
END AS 评价
FROM `销售表`;
第二种 常量 不好用 不用
SELECT `商品名称`,`进价`,`售价`,
CASE 大类编码
WHEN 01 THEN 售价*1.1
WHEN 02 THEN 售价*1.2
WHEN 03 THEN 售价*1.05
ELSE 售价
END AS 新售价
FROM `商品表`;
行转列
SELECT name,
MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
FROM score
GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文 | 数学 | 外语 |
| 李四 | 语文 | 数学 | 外语 |
转换函数
cast 类型转换
CAST（转换前的值 AS 想要转换的数据类型）
类型为系统的数据类型
select cast(GETDATE() as nvarchar)
select cast(GETDATE() as DATE)
select cast(GETDATE() as TIME)
convert 类型转换
select convert(datetime,'2015-12-1')
TRY_PARSE、TRY_CAST和TRY_CONVERT 类型转换
即便转换失败也不会造成整个语句报错，只会在无法转换的情况下输出NULL值；
TRY_CAST和TRY_CONVERT 用法同cast,convert
TRY_PARSE
用于将字符串类型的数据转换成时间或者数值类型的数据，但数字cast比较好，时间的话，对于源数据的数据格式支持比较广或者要求比较宽松，而TRY_CAST和TRY_CONVERT则要求比较严格。
TRY_PARSE在将纯数字转为DATETIME的情况下会失效
try_parse('20211201' as datetime) #失败
select try_parse('Thursday, 19 Nov 2015' as datetime) 成功
TRY_PARSE支持带有时区的时间格式而其他两个不支持
select try_parse('2015/09/01 14:14:45+001' as datetime)
空值替换 coalesce，将NULL转换为其他值
如何筛选出缺失值，即使用WHERE加上IS NULL或者IS NOT NULL，ifnull()当只有2个表达式的时候，两者的用法可以互换 ，当有多个参数判断是否null时，只能使用COALESCE。
左侧开始第 1个不是NULL的值
COALESCE(col_1, col2, ...., 1)
返回表达式中第一个非空表达式
SELECT COALESCE(NULL,NULL,3,4,5) FROM dual
其返回结果为：3
如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。
开窗函数
hive中能不用窗口函数与order by 排序。尽量不用。
开窗函数与其他函数的区别是,它不是关联其他表查询,而是在一张表内根据我们的想法自定义的规则分组后对我们组内的数据进行检索和计算。我们自定义的规则所分的组，就如同整张表的一个个小窗口，因此我们开出一个个小窗口并对这些小窗口进行的操作就叫做开窗函数。
开窗函数分为排序函数和聚合函数
开窗函数之排名函数
1 row_number() over(partition by...order by......) as 列名
ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，
相同的数字，依次加序号,不能用做于学生成绩的排名，一般多用于分页查询
2.RANK() over(order by......) 列名
定义：Rrank函数会把排序的值相同的归为一组且每组序号一样，排序不会连续
RANK()是跳跃的排名
3 dense_rank()
dense_rank排序是连续的，也会把相同的值分为一组且每组排序号一样
4 ntile(n组) over ( order by 列）
NTILE()的功能是进行均分分组，其参数是我们要分组的数量，比如，我们把学生平均分为四组，分为几个区，一个区会有多少个。
获取前20%的时间数据(先分组，再取)
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) as sorted
from business
) as t
where sorted = 1;
partition by 分组排序
partition by col1 order by col2
表示根据COL1分区，在分区内部根据 COL2排序，而此函数计算的值就表示每个分区内部排序后的顺序编号（组内连续的唯一的)
开窗函数之累计聚合
sum /av/min/max/ (列) over（partition by order by）
取列名moving_avg
rows n preceding：截止到之前n行 加上自身 n+1行
rows n following：往后n行 加上自身 n+1行
rows between 3 preceding and 4 following 自身加前三+后四行
指定order by,不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有值累加;
如果使用over()
max(列) over() 生成一列全是最大值的列
无界限
current row：当前行
unbounded 无限，
unbounded preceding 到前面的起点
unbounded following 表示到后面的终点
例子：
SUM(pv) OVER (partition by cookieid ORDER BY createtime)
默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid)
分组内所有行
sum(pv) over (partition by cookieid order by createtime
rows between 3 preceding and 4 following )
分组内前3行+当前行+后四行
累计求和错误：order by 列重复
select id ,sum(id) over(order by id) from num;
当遇到 id 相同时，窗口函数认为，窗口大小一样，取最后一个。
开窗函数之位移函数 用于环比 lag lead
从前往后
lag（列名，偏移几行，超过窗口时值） ：向下位移 自身落后
lead（列名，偏移几行，超过窗口时值） ：向上位移， 自身超前
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值。举例如下：
select *,lag(item_brand,1,0) over (order by item_id) from items
参与位移运算
(confirmed -LAG(confirmed,1) OVER (PARTITION BY name ORDER BY whn)) AS New
顾客上次,上上次购买时间
没有的，用原来时间填充，或者指定一个不可能时间
select name,
lag(orderdate,1,orderdate) over(partition by name order by orderdate ) as time1,
lag(orderdate,2，'1900-01-01') over (partition by name order by orderdate) as time2
from business;
顾客下次购买时间
select name,
lead(orderdate,1,orderdate) over(partition by name order by orderdate ) as time1
from business;
FIRST_VALUE（）、 LAST _VALUE（）
可以查看分区内排序后，第一个值和最后一个值
可以指定 rows bwtween ,
注意指定order by 是到当前行，last 是到当前行最后一个值 也就是当前行。
时间函数
DATEPART() 时间的某部分
DECLARE @d DATETIME = '2022-03-04 15:30:18';
SELECT DATEPART(year, @d) year,
DATEPART(quarter, @d) quarter,
DATEPART(month, @d) month,
DATEPART(day, @d) day,
DATEPART(hour, @d) hour,
DATEPART(minute, @d) minute,
DATEPART(second, @d) second;
date_part 缩写形式
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz ISO_WEEK isowk, isoww
DATEPART()函数有两个参数：
• date_part是要提取的日期的一部分(参见下表中的有效日期部分)。
• input_date是提取日期部分的日期。
当前时间 今天
GETDATE()
当前时间去除日期
时间格式转换CONVERT
时间转换 120格式 VARCHAR(10) 前10位
select CONVERT(VARCHAR(10), GETDATE() - 1, 120)
select CONVERT(VARCHAR(10), GETDATE(),120)
0开头的月：202306，202312
202204
select substring(convert(varchar,GETDATE(),112),1,6) as [Month]
季度
12-1 为第一季度
select DATEPART(quarter, DATEADD(month,1,GETDATE())) as Quarter
上个月
budgetMONTH 202009
convert(VARCHAR(6),dateadd(month,-1,cast (budgetMonth+'01' as date)),112)
DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0)
当前月初
select DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
当前月往前移三个月月初
select DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0)
去年12月
211201
select right(convert(nvarchar, year(dateadd(month, 1, getdate()-1))-1), 2) + '1201'
20211201
select convert(nvarchar, year(dateadd(month, 1, getdate()))-1)+ '1201'
year
202112-202211月
year(dateadd(month,1,[Report_Time])) as [Year]
常用时间函数
当前
'9999-01-01' 表示当前
当前日期时间now()
当前日期 curdate()
当前时间curtime()
CURRENT_TIMESTAMP – 当前日期和时间
GETDATE()
Sqlserver获取系统当前日期的函数
UTCDATE()
返回UTC日期,当前 UTC（世界标准时间）日期值。
年月日
获取天数的函数DAY(d)
获取月份的函数MONTH(d)
获取年份的函数YEAR(d)
获取月份名称 monthname()
dayname() 工作日名词 ‘周四’
DAYOFMONTH(date) 对应的该月日期，范围是从 1到31
时分秒
获取时间中 时hour() 分 minute 秒 second()
周
DAYOFWEEK(date)
返回date (1 = 周日, 2 = 周一, ..., 7 = 周六)对应的工作日索引。这些索引值符合 ODBC标准。
WEEKDAY(date)：返回日期星期索引 (0 = Monday, 1 = Tuesday, . 6 = Sunday)
获取日期中指定部分字符串值的函数DATENAME(dp,d)
截取日期
EXTRACT(日期元素 FROM 日期列)
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
DATEPART(dp,d)
获取日期中指定部分的整数值的函数DATEPART(dp,d)
select datepart(Year,GETDATE())
DATENAME(dp,d)
获取日期中指定部分字符串值的函数
dp参数缩写：
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns
dp 其他可以取的值有：quarter、dayofyear、day、week、weekday、hour、minute、second 等
时间间隔
datadiff()
datediff(日期1, 日期2) 默认最小单位
如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。
select a.ID, a.date
from weather as a cross join weather as b
on datediff(a.date, b.date) = 1
where a.temp > b.temp;
优点 不需要提前做时间格式转换
DATEDIFF(格式,startdate,enddate) =enddate-startdate
|
格式 |
缩写 |
|
年 |
yy, yyyy |
|
季度 |
qq, q |
|
月 |
mm, m |
|
年中的日 |
dy, y |
|
日 |
dd, d |
|
周 |
wk, ww |
|
星期 |
dw, w |
|
小时 |
hh |
|
分钟 |
mi, n |
|
秒 |
ss, s |
|
毫秒 |
ms |
|
微妙 |
mcs |
|
纳秒 |
ns |
timestampdiff(时间类型, 日期1, 日期2)
= 日期1-日期2 与datadiff相反
时间类型：“day”, “hour”, “second”
select a.ID, a.date
from weather as a cross join weather as b
on timestampdiff(day, a.date, b.date) = -1
where a.temp > b.temp;
时间加减DateAdd()
时间增加 date_add(‘20191010’，interval 1 month)
时间减少 data_sub(‘20191010’，interval 1 year)
dateadd(datepart,number,date)
Ddatepart参数缩写：
年 yy, yyyy
季度 qq, q
月 mm, m
年中的日 dy, y
日 dd, d
周 wk, ww
星期 dw, w
小时 hh
分钟 mi, n
秒 ss, s
毫秒 ms
微妙 mcs
纳秒 ns
select dateadd(day,1,GETDATE())
select dateadd(d,1,GETDATE())
select dateadd(month,1,GETDATE())
select dateadd(m,1,GETDATE())
时间格式 date() str_to_date() date_format()
DATE()
返回日期或日期时间表达式的日期部分
STR_TO_DATE（）
STR_TO_DATE(列,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值。
DATE_FORMAT(date,format)函数则是把数据库的日期转换为对应的字符串格式
SELECT user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
FROM user_info
date_format 时间格式
date_format(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式
例：data_fromat(date,’%Y-%m-%d %H:%i:%s’)
|
格式符 |
功能 |
例如 |
|
%Y |
四位年份 |
2020 |
|
%y |
2位数年份 |
20 |
|
%m |
月份 |
01,02,03… |
|
%c |
月份 |
1,2,3… |
|
%d |
日 |
01,02,03 |
|
%H |
24小时制 |
|
|
%h |
12小时制 |
|
|
%i |
分钟 |
00,01,…59 |
|
%s |
秒 |
00,01,…59 |
|
格式 |
描述 |
|
%a |
缩写星期名 |
|
%b |
缩写月名 |
|
%D |
带有英文前缀的月中的天 |
|
%e |
月的天，数值(0-31) |
|
%f |
微秒 |
|
%I |
小时 (01-12) |
|
%i |
分钟，数值(00-59) |
|
%j |
年的天 (001-366) |
|
%k |
小时 (0-23) |
|
%l |
小时 (1-12) |
|
%M |
月名 |
|
%p |
AM 或 PM |
|
%r |
时间，12-小时（hh:mm:ss AM 或 PM） |
|
%S |
秒(00-59) |
|
%T |
时间, 24-小时 (hh:mm:ss) |
|
%U |
周 (00-53) 星期日是一周的第一天 |
|
%u |
周 (00-53) 星期一是一周的第一天 |
|
%V |
周 (01-53) 星期日是一周的第一天，与 %X 使用 |
|
%v |
周 (01-53) 星期一是一周的第一天，与 %x 使用 |
|
%W |
星期名 |
|
%w |
周的天 （0=星期日, 6=星期六） |
|
%X |
年，其中的星期日是周的第一天，4 位，与 %V 使用 |
|
%x |
年，其中的星期一是周的第一天，4 位，与 %v 使用 |
日志时间转换北京时间：
select from_unixtime(time)
毫秒计时的日志时间。提取前10位
select from_unixtime(cast(substr(time,1,10) as int))
cast( A as int)
把一种数据类型 转换为另一种数据类型
CHAR, DATE, TIME , DATETIME , DECIMAL ,整数 : SIGNED ,无符号整数 : UNSIGNED
hive常用日期函数
时间戳 秒10位 毫秒 13位，日期格式必须是 ’2018-12-12 12：12：12’ 这样。
如果是时间错误 10-35 会识别为11月4号。
unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp();
返回当前时间戳 到秒,淘汰了。
select unix_timestamp(时间类型);
select unix_timestamp("2020-10-28",'yyyy-MM-dd');
非时间类型，需要指定时间格式。
from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);
默认到秒
select from_unixtime(1603843200，’yyyy-MM-dd’);
自定义格式
current_date：当前日期
select current_date;
current_timestamp：当前的日期加时间，到毫秒
select current_timestamp;
to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');
year：获取年
select year('2020-10-28 12:12:12');
month：获取月
select month('2020-10-28 12:12:12');
day：获取日
select day('2020-10-28 12:12:12');
hour：获取时
select hour('2020-10-28 12:12:12');
minute：获取分
select minute('2020-10-28 12:12:12');
second：获取秒
select second('2020-10-28 12:12:12');
weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');
dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');
months_between： 两个日期间的相差的月份
select months_between('2020-04-01','2020-10-28');
没什么用，前面-后面 而且带余数 
add_months：日期加减月
select add_months('2020-10-28',-3);
-3 表示减3.
datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');
前减后，返回的是天数
date_add：日期加减天数
select date_add('2020-10-28',4);
-3 表示-3天
date_sub：日期减天数
select date_sub('2020-10-28',-4);
-4 表示加4天
last_day：日期的当月的最后一天的日期
select last_day('2020-02-30');
返回2020-03-31
date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
支持输入 时间类型，时间戳，字符串
yyyyy MM dd HH mm ss 可以任意组合。
字符串函数
字符串转换
去除右边空值， rtrim(),
去除左边空值 ltrim()
去除左右空格RTRIM()
select trim(" atguigu ");
lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');
g 为填补字符 返回 atguigugg
rpad： 向右补齐，到指定长度
select rpad('atguigu',9,'g');
转换大写， upper(),
转换小写 lower()
连接：concat(),concat_ws()
类型转换
ASCII()函数
ASCII(character_expression)函数用于返回字符串表达式中最左侧字符的 ASCII 代码值。
参数 character_expression 必须是一个 char 或 varchar 类型的字符串表达式
CHAR() 函数
CHAR(integer_expression) 函 数 将 整 数 类 型 的 ASCII 值 转 换 为 对 应 的 字 符 ，integer_expression 是一个介于 0 和 255 之间的整数。
如果该整数表达式不在此范围内，将返回NULL 值。
STR()函数
转换为字符类型
STR ( float_expression [ , length [ , decimal ] ] )函数用于将数值数据转换为字符数据。
float_expression 是一个带小数点的近似数字（float）数据类型的表达式。
length 表示总长度。它包括小数点、符号、数字以及空格。默认值为 10。
decimal 指定小数点后的位数。decimal 必须小于或等于 16。如果 decimal 大于 16，则会截断结果，使其保持为小数点后有 16 位。
字符串逆序的函数REVERSE(s)
REVERSE(s)将字符串 s 反转，返回的字符串的顺序和 s 字符顺序相反。
字符部分替换函数
列1=replace(列1，找到字符串，替换字符串)
直接替换全部
update titles_test
set emp_no=replace(emp_no,10001,10005)
where id=5
替换其中部分字符
SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');
把时间中的/ 替换为-
截取字符函数
left(列，几个) 从左取
right(列，几个)从右取
substring（被截取字段，从第几位开始截取，截取长度）从1开始数
=substr() 完全一样
select substring('apple.com', 4, 4)
le.c
substring（被截取字段，从第几位开始截取） 截取到结束位
select substring('apple.com', 4);
le.com
支持倒着取，函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。
从字符串的第 5 个字符位置（倒数）开始取，直到结束
substring('apple.com', -5)
e.com
从字符串的第 5 个字符位置（倒数）开始取，取 4 个字符
select substring('apple.com', -5, 4)
用分隔符截取
SUBSTRING_INDEX (原始字符串， 分隔符，n)
该函数用来获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。
获取分隔符之前数据
SUBSTRING_INDEX('www.mysql.com', '.', 2)
www.mysql
获取分隔符之后数据
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
mysql.com
获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法。
SUBSTRING_INDEX('www.mysql.com', '.', 1)
www
SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
字符串查询
LEN(str)
计算字符串长度的函数LEN(str)
返回字符表达式中的字符数。
如果字符串中包含前导空格和尾随空格，则函数会将它们包含在计数内。
LEN 对相同的单字节和双字节字符串返回相同的值。
字节数 1汉字=3字节，1字母1字节 length（）
字符长度 char_length() ：汉字，数字，字母都算一个字符
查找字符串'10,A,B' 中逗号','出现的次数
select char_length('10,A,B')-char_length(replace('10,A,B',',',''))
CHARINDEX匹配子串开始位置的函数
CHARINDEX(str1,str,[start]) 函数返回子字符串 str1 在字符串 str 中的开始位置，start 为搜索的开始位置，如果指定 start 参数，则从指定位置开始搜索；
如果不指定 start 参数或者指定为 0或者为负值，则从字符串开始位置搜索。
自定义列排序（‘字符串列排序’）
INSTR（’字符串，字符串’，列名）
查找一个字符串在另一个字符串中的位置，和FIND不同的是，当找不到相关值时，结果返回0，而非错误值。
中文列排序（例如字段名为月份：值包含一月、二月、三月、四月、五月）
SELECT 字段名 FROM 表名 ORDER BY INSTR('五月,四月,三月,二月,一月',月份)
数值函数
常用函数
绝对值 abs()
平方根：sqrt()
指数exp()
四舍五入：round(,小数点保留位数)
圆周律：pi()
求余 MOD( 被除数，除数 )
幂运算函数POWER(x,y)
POWER(x,y)函数返回 x 的 y 次乘方的结果值
SQUARE (x)
EXP(x)
对数运算函数LOG(x)和LOG10(x)
LOG(x)返回 x 的自然对数，x 相对于基数 e 的对数。
角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)
RADIANS(x)将参数 x 由角度转化为弧度。
正弦函数SIN(x)和反正弦函数ASIN(x)
SIN(x)返回 x 的正弦，其中 x 为弧度值。
余弦函数COS(x)和反余弦函数ACOS(x)
COS(x)返回 x 的余弦，其中 x 为弧度值
正切函数、反正切函数和余切函数
TAN(x)返回 x 的正切，其中 x 为给定的弧度值。
随机数RAND()和RAND(x)
RAND(x)返回一个随机浮点值 v，范围在 0 到 1 之间（即 0 ≤ v ≤ 1.0）。
若指定一个整数参数x，则它被用作种子值，使用相同的种子数将产生重复序列。
如果用同一种子值多次调用 RAND 函数，它将返回同一生成值。
判断正负SIGN(x)
SIGN(x)返回参数的符号，x 的值为负、零或正时，返回结果依次为-1、0 或 1。
取整函数
向上取整
ceil(1.2)=2
向下取整CEILING(x)和FLOOR(x)
CEILING(x)返回不小于 x 的最小整数值
floor(1.2)=1
四舍五入函round（数值，保留小数点后位数）
ROUND(x,y)返回最接近于参数 x 的数，其值保留到小数点后面 y 位，若 y 为负值，则将保留x 值到小数点左边 y 位。
round(1.2)=1
。
取最大值：对字符型数字，自动改为数值型
select max(a+0)
文本和图像函数
TEXTPTR函数
TEXTPTR(column)函数用于返回对应 varbinary 格式的 text、ntext 或者 image 字段的文本指针值。
查找到的文本指针值可应用于 READTEXT、WRITETEXT 和 UPDATETEXT 语句。
其中参数 column 是一个数据类型为 text、ntext 或者 image 的字段列。
TEXTVALID函数
TEXTVALID('table.column' ,text_ptr)函数用于检查特定文本指针是否为有效的 text、ntext 或image 函数。
table.column 为指定数据表和字段，text_ptr 为要检查的文本指针。
Sqlserver系统函数
查询脚本是否运行成功
不成功则报错
DECLARE @FLAG INT
SELECT TOP 1 @FLAG = run_status
FROM [msdb].[dbo].[sysjobhistory] ja
JOIN msdb.dbo.sysjobs j
ON j.job_id = ja.job_id
WHERE j.name = 'GOALDDataExtraction_China'
AND step_id = 0
and run_date>=CONVERT(VARCHAR(10), GETDATE(), 112) --限定为今天的脚本
ORDER BY run_date DESC, run_time DESC
IF ISNULL(@FLAG, 0)=0
BEGIN
RAISERROR('Previous job not yet successfully completed',16,1)
END
判断当天的数据源是否更新
DECLARE @FLAG INT,@premium int,@value nvarchar
--脚本是否运行
SELECT TOP 1 @FLAG = run_status
FROM [msdb].[dbo].[sysjobhistory] ja
JOIN msdb.dbo.sysjobs j
ON j.job_id = ja.job_id
--WHERE j.name = 'GOALDDataExtraction_China'
WHERE j.name = 'SIT data upate daily'
AND step_id = 0
and run_date>=CONVERT(VARCHAR(10), GETDATE(), 112) --限定为今天的脚本
ORDER BY run_date DESC, run_time DESC
--当天是否有保费
SELECT @premium =sum([Premium])
FROM [FARS].[dbo].[View_SIT_AH_Daily]
where Trans_Date=CONVERT(VARCHAR(10), dateadd(day,-1,GETDATE()), 112)
--SELECT sum([Premium])
--FROM [FARS].[dbo].[View_SIT_AH_Daily]
--where Trans_Date=CONVERT(VARCHAR(10), dateadd(day,-1,GETDATE()), 112)
IF ISNULL(@FLAG, 0)=1 and @premium>0
begin
set @value=N'是'
end
else
begin
set @value=N'否'
end
select @value as value
数据查询表是否存在
1 OBJECT_ID（’表名’） 表的唯一id
字段参考文档
查询表信息
SELECT * from sys.objects
WHERE object_id = OBJECT_ID(N'[ah_premium_production]')
如果存在，删除表
IF OBJECT_ID('[FARS].[dbo].[ah_premium_production]') IS NOT NULL
BEGIN
DROP TABLE [FARS].[dbo].[ah_premium_production]
END
如果存在 删除表2
--u表示表
IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[_StagingTravelLibertyPolicy_Certificate_ID]') AND type IN (N'U'))
DROP TABLE [_StagingTravelLibertyPolicy_Certificate_ID]
GO
其他
COL_LENGTH(table,column)
返回表中指定字段的长度值。其返回值为 INT 类型。
table为要确定其列长度信息的表的名称，是 nvarchar 类型的表达式。
column 为要确定其长度的列的名称,是 nvarchar 类型的表达式。
DATALENGTH (expression)
返回数据表达式的数据的实际长度，即字节数。其返回值类型为 INT。
NULL 的长度为 NULL。
expression 可以是任何数据类型的表达式。
COL_NAME (table_id，column_id)
返回表中指定字段的名称。
table_id 是表的标识号，column_id 是列的标识号，类型为 int。
DB_ID(database_name)
返回数据库的编号。其返回值为 SMALLINT 类型。
如果没有指定 database_name，则返回当前数据库的编号。
DB_NAME (database_id)
返回数据库的名称。其返回值类型为 nvarchar(128)。
database_id 是 smallint 类型的数据。
如果没有指定 database_id，则返回当前数据库的名称。
GETANSINULL() (database_name)
返回当前数据库默认的 NULL 值，其返回值类型为int。
GETANSINULL()函数对 ANSI 空值 NULL 返回 1；如果没有定义 ANSI 空值，则返回 0。
HOST_ID()
返回服务器端计算机的标识号。其返回值类型为 char(10)。
HOST_NAME()
返回服务器端计算机的名称，其返回值类型为 nvarchar(128)。
OBJECT_ID(database_name.schema_name.object_name，object_type)
返回数据库对象的编号。其返回值类型为 int。
object_name 为要使用的对象，它的数据类型为 varchar 或nvarchar。
如果 object_name 的数据类型为 varchar，则它将隐式转换为 nvarchar。可以选择是否指定数据库和架构名称。object_type 指定架构范围的对象类型。
SUSER_SID (login_name)
根据用户登录名返回用户的 SID（Security Identification Number，安全标识号）。其返回值类型为 int。
如果不指定 login_name，则返回当前用户的 SID。
SUSER_SNAME ([server_user_sid])
返回与安全标识号（SID）关联的登录名。
如果没有指定 server_user_sid，则返回当前用户的登录名。其返回值类型为 nvarchar(128)。
OBJECT_NAME (object_id [, database_id ])
返回数据库对象的名称。
database_id 要在其中查找对象的数据库的 ID，数据类型为 int。
object_id 为要使用的对象的 ID，数据类型为 int。
假定为指定数据库对象，如果不指定 database_id，则假定为当前数据库上下文中的架构范围内的对象。其返回值类型为 sysname。
USER_ID(user)函数
根据用户名返回数据库用户的 ID。其返回值类型为 int。
如果没有指定user，则返回当前用户的数据库 ID。
USER_NAME(id)函数
根据与数据库用户关联的 ID 号返回数据库用户名。
其返回值类型为nvarchar(256)。
如果没有指定 id，则返回当前数据库的用户名。
复杂sql
中位数
1当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数
2 floor(((select sum(number) from class_grade)+1)/2) as median1,
floor(((select sum(number) from class_grade)+2)/2) as median2
求和计数只对其列某类
sum(case type when "no_completed" then 1 else 0 end)
多个查询过滤
where (user_id,date)
in (select user_id,DATE_ADD(min(date),INTERVAL 1 DAY) from login group by user_id);
分组筛选第一，第二数据
select t.user_id,
max((case when number=1 then date else 0 end)),
max((case when number=2 then date else 0 end))
,t.cnt
from
(select user_id,date,
count(*)over(partition by user_id) as cnt,
row_number()over(partition by user_id order by date) as number
from order_info
where t.cnt>=2
group by t.user_id
有筛选条件的统计数量问题
select sum(
case when <判断表达式> then 1
else 0
end
) as 数量
from 信息表；
平均成绩大于80分的人数占比
select a.班级,
sum(
case when b.平均成绩>80 then 1
else 0 end) as 人数,
sum(
case when b.平均成绩>80 then 1
else 0 end)/count(a.学号) as 人数占比
from 学生表 as a left join(
select 学号,avg(分数) as 平均成绩
from 成绩表
group by 学号
) as b
on a.学号=b.学号
group by 班级
真题
交换相邻数据
解析
1 计算是奇数 ，还是偶数
mod(n,m) ，返回n除以m的余数
2奇数加1 偶数减1 交换位置
代码
select
(case
when mod(座位号, 2) != 0 then 座位号 + 1
when mod(座位号, 2) = 0 then 座位号 - 1
end) as '交换后座位号',
姓名
from 学生表;
3 列为奇数 ，最后一个不交换
select
(case
# 当座位号是奇数并且不是不是最后一个座位号时
when mod(id, 2) != 0 and counts!= id then id + 1
# 当座位号是奇数并且是最后一个座位号时，座位号不变
when mod(id, 2) != 0 and counts = id then id
# 当座位号是偶数时
else id - 1
end) as id2,student
from seat,(select count(*) as counts from seat);
行列互换
1 group by 过滤0值
2 max(case when) 取非0值
行转列 case when
select A,
-- 第2步，在行列互换结果表中，其他列里的值分别使用case和max来获取
--max 去掉0
max(case B when 'm' then C else 0 end) as 'm',
max(case B when 'n' then C else 0 end) as 'n'
from cook
-- 第1步，在行列互换结果表中按第1列分组
group by A;
查询第N高
1 过滤需要的列，用desc 降序
2 distinct去重
3 limit 取第n高
4 ifnull 如过没用，返回null
select ifNull(
(select distinct salary
from Employee
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;
连续出现N次
第一种 row_number 连续序号法 快 复杂
连续登录5天用户数
1 用户 id 登录日期substr， 升序 group by去重
2假设连续登录 ，给出 用户 row_number() 序号
3 日期列-序号 如果是连续 , 等于减去序号 登录的一天日期相同
4过滤出第一天登录日期>=5
5 用户去重，统计
select count(distinct usr_id)
from(
select usr_id,base_dt,count(1) c1
from(
select *,date_sub(load_dt,interval rnk day) base_dt
from(
select usr_id,load_dt,row_number() over(partition by usr_id order by load_dt) rnk
from(
select usr_id,load_dt
from td_load_rcd
where substr(load_dt,1,7)='2020-06'
group by usr_id,load_dt
)t
)t
)t
group by usr_id,base_dt
having c1>=7
) t
第二种 自联结法 简单慢
1 n次 自联结n次
2 减1相等 a.学号 = b.学号 - 1 b.学号 = c.学号 - 1
连续出现三次
select distinct a.成绩 as 最终答案
from score as a,
score as b,
score as c;
where a.学号 = b.学号 - 1
and b.学号 = c.学号 - 1
and a.成绩 = b.成绩
and b.成绩 = c.成绩;
七日留存率-n日留存,n月留存
第一种 连续计数法
解析
1 获取user_id和用户登录时间login_time，str_to_date将登陆时间转化为日期
2 group by 去重 user_id，login_time
3 增加一列first_day，此列存着每个用户最早登录时间。
3再增加一列by_day，这一列是用login_time - first_day （datediff），得到0，1，2，3，4，5......，这就得到了某一天登录离第一次登录有多长时间。
4 case when 分类求和不同天留存人数
数据
表 user_info
列 user_id和用户登录时间login_time
代码
SELECT
first_day,
# 4 列转行 计算 一日到七日留存人数
sum(case when by_day = 0 then 1 else 0 end) day_0,
sum(case when by_day = 1 then 1 else 0 end) day_1,
sum(case when by_day = 2 then 1 else 0 end) day_2,
sum(case when by_day = 3 then 1 else 0 end) day_3,
sum(case when by_day = 4 then 1 else 0 end) day_4,
sum(case when by_day = 5 then 1 else 0 end) day_5,
sum(case when by_day = 6 then 1 else 0 end) day_6,
sum(case when by_day >= 7 then 1 else 0 end) day_7plus
FROM
# 2 获取user_id 时间间隔
(SELECT user_id,login_time,first_day,
DATEDIFF(login_time,first_day) as by_day
FROM
(SELECT b.user_id, b.login_time,c.first_day
FROM
#0提取 user_id，登录日期
(SELECT user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
FROM user_info
GROUP BY 1,2) b
LEFT JOIN
#1 获取第一次登录时间
(SELECT user_id, min(login_time) first_day
FROM(select user_id,
str_to_date(login_time,'%Y/%m/%d') login_time
FROM user_info
group by 1,2) a
group by 1) c
on b.user_id = c.user_id
order by 1,2) e
order by 1,2) f
group by 1
order by 1
第二种较简单
1 表自联结两次 等到 两个登录时间
2 timestampdiff 计算时间间隔 可以month,day
3用case when 与 count(distinct) 计算人数 与留存率
select a.登陆时间,count(distinct a.用户id) as 活跃用户数,
count(case when 时间间隔=1 then 用户id else null end) as 次日留存数,
count(case when 时间间隔=1 then 用户id else null end) as 次日留存数 / count(distinct a.用户id) as 次日留存率,
count(case when 时间间隔=3 then 用户id else null end) as 三日留存数,
count(case when 时间间隔=3 then 用户id else null end) as 三日留存数 / count(distinct a.用户id) as 三日留存率,
count(case when 时间间隔=7 then 用户id else null end) as 七日留存数,
count(case when 时间间隔=7 then 用户id else null end) as 七日留存数 / count(distinct a.用户id) as 七日留存率
from
(select *,timestampdiff(day,a.登陆时间,b.登陆时间) as 时间间隔
from
(select a.用户id,a.登陆时间,b.登陆时间
from 用户行为信息表 as a
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机') as c
) as d
group by a.登陆时间;
2019年1-4月产生订单的用户，以及在次月的留存用户数
select a.用户id,
count(case when timestampdiff(month,b.时间,a.时间)=1 then a.用户id else null end) as 次月留存用户数
from 订单表 as a
inner join 订单表 as b
on a.用户id = b.用户id
where a.时间 between '2019-01-01' and '2019-04-30'
group by a.用户id
每个月的活跃用户
select substr(load_dt,1,7) load_month ,
count(distinct usr_id) cst_cnt
from td_load_rcd
group by substr(load_dt,1,7)
7月份工作日期间，各时间段的月活分布，通勤（7：00-9：00、18：00-20：00），午休（11：00-13：00）,临睡（22：00-1：00），哪段时间的活跃用户数最高？
select
case when hour(load_dt) between 7 and 8 or hour(load_dt) between 18 and 19 then 'commute'
when hour(load_dt) between 11 and 12 then 'lunch'
when hour(load_dt) in (22,23,0) then 'before_sleep'
end as time_prd
,count(distinct usr_id) cnt
from td_load_rcd
where substr(load_dt,6,2)=7 and #七月
date_format(load_dt,'%W') not in('Saturday','Sunday') #工作日
group by #通勤，午休，临睡
case when hour(load_dt) between 7 and 8 or hour(load_dt) between 18 and 19 then 'commute'
when hour(load_dt) between 11 and 12 then 'lunch'
when hour(load_dt) in (22,23,0) then 'before_sleep'
end
如何查找前20%的数据
1 row_number 排序
2排名<=最大的排名值 * 20%,就是前20%的数据。
获取前20%
Select *
from
(select *,
row_number() over(order by 访问量 desc) as 排名
from 用户访问次数表) as a
where 排名< (select max(排名) from a) * 0.2;
剔除前20
Select *
from
(select *,
row_number() over(order by 访问量 desc) as 排名
from 用户访问次数表) as a
where 排名 > (select max(排名) from a) * 0.2;
top n
row_number()
topN问题 sql模板
select *
from (
select *,
row_number() over (partition by 要分组的列名
order by 要排序的列名 desc) as 排名
from 表名) as a
where 排名 <= N;
前n名
1 dense_rank()
select DepartmentId,Name,Salary
from (
select *,
dense_rank() over (partition by DepartmentId
order by Salary desc) as ranking
from Employee) as a
where ranking <= 3;
三 Hivesql
改基本不用（新建一张表改），用创建，查询，删除。
启动hive
cd /opt/module/hive/bin/hive
#hive: bin/hive #hive文件夹中
“-e”不进入 hive 的交互窗口执行 sql 语句
bin/hive -e "select id from student;"
执行文件中的 sql 语句
hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql
执行文件中的 sql 语句并将结果写入文件中
[ hive]$ bin/hive -f /opt/module/hive/datas/hivef.sql >
/opt/module/datas/hive_result.txt
退出 hive 窗口
hive(default)>exit;
hive(default)>quit;
文件夹网页地址：
hadoop102:9870 
后面目录是表名
Linux命令
https://blog.csdn.net/qinfuan2017/article/details/79728906
当前目录：pwd
目录下内容：ll
写txt 1 vim name.txt
2 插入模式 a 插入
3切换命令esc
4 保存 : wq
u: 撤销上一步的操作。
hive数据类型
数据
数字 INT ，bigint,,float ,double ,
布尔BOOLEAN
时间类型：TIMESTAMP yyyy-mm-dd 自动转换
字符：string 等于mysql varchar
集合类型
map a[‘z’]
arrary a[1]
struct 使用.访问 a.Z
创建集合表
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string> )
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP， STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP 中的 key 与 value 的分隔符
lines terminated by '\n'; -- 行分隔符
查询三种集合文件
select friends[1],children['xiao song'],address.city from
test
类型转换 CAST
只允许向上，向高级类型修改，不允许向低级修改：如float 改成 int.
select '1'+2, cast('1'as int) + 2
失败 返回空值 NULL。
隐式类型转换规则如下
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成
INT，INT 可以转换成 BIGINT。
（2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
（3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
（4）BOOLEAN 类型不可以转换为任何其它的类型。
DDL 数据定义：表，库
创建库
create database if not exists 库名;
为了防止报错 添加if not exists
默认存储路径是/user/hive/warehouse/*.db
指定数据库在 HDFS 上存放的位置
create database db_hive2 location '/db_hive2.db';
展示库
模糊查询 show databases like 'db_hive*';
展示库信息 desc database 库名;
详细信息 desc database extended 库名;
删除库
drop database db_hive2;
drop database if exists db_hive2;
数据库不为空，强制删除
drop database db_hive cascade;
创建表
创建普通表
create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';
创建外部表
外部表：EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实
际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据
create external table learn(id string);
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
根据查询结果创建表
create table if not exists student2 as select id, name from student;
展示表详细信息
show cerate table test;
表文件夹位置
内外部表互相修改
managed 管理 表示内部
查看表类型 desc formatted student2;
修改成外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
修改成内部表 alter table student2 set tblproperties('EXTERNAL'='FALSE');
注意：：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！
创建表，指定字段分隔符
row format delimited fields terminated by ','
默认 
\001 作为分隔符
create external table if not exists dept(
deptno int,
dname string, )
row format delimited fields terminated by '\t';
修改表 可以只用一个replace columns
查询表 desc dept;
重命名 ALTER TABLE 旧表名 RENAME TO 表名。
修改 alter
替换一列 alter table 表名 change column 旧列名 新列名 数据类型;
添加列 alter table dept add columns(deptdesc string);
ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)
replace columns 则是表示替换表中所有字段
替换所有列
alter table dept replace columns(deptno string, dname string, loc string)
删除列 （没被替换的删除）
alter table dept replace columns(deptno string,, loc string)
利用 replace 可以添加多列
DML 数据操作-重点
导入数据
1 load data 加载数据 重点
创建数据文件
vim 文件名.txt
excel,csv 转txt
文件另存为txt
或者直接导入.csv 要求：表分隔符， csv格式utf-8
更改编码格式：文件路径上执行 piconv -f gb2312 -t UTF-8 a.txt > c.txt （解决中文乱码）
上传文件到表目录 (少用 put,元数据库识别不到改变)
hadoop fs -put test.txt /user/hive/warehouse/test(上传后文件所在目录)
load data [local] inpath '路径/文件名' [overwrite] into table 表名 [partition (partcol1=val1,…)];
load data:表示加载数据
local:表示从本地加载数据到 hive表；否则从HDFS 加载数据到 hive 表 （3）inpath:表示加载数据的路径
overwrite:表示覆盖表中已有数据（先清空表，再写入），否则表示追加
into table:表示加载到哪张表
partition:表示上传到指定分区
从本地加载txt到表
load data local inpath
'/opt/module/hive/datas/student.txt（相对路径./student.txt)’ into table student;
先上传hdfs，再加载到表
hadoop fs -put /opt/module/hive/data/student.txt
/user/atguigu/hive;
load data inpath '/user/atguigu/hive/student.txt' into
table 库名.表名;
覆盖写
load data inpath '/user/atguigu/hive/student.txt'
overwrite into table 库名.表名;
2 insert 从另一张表不支持部分插入 走mr ,很少用
基本插入数据,(慢不用)
insert into table 表名
values(1,'wangwu'),(2,'zhaoliu');
先查询，再插入
insert into：以追加数据的方式插入到表或分区，原有数据不会删除
insert overwrite：会覆盖表中已存在的数据
注意：insert 不支持插入部分字段。
insert overwrite/into table student_par
select id, name from student where month='201709';
多个查询，分区插入
一张表查询，插入两张表
from student
insert overwrite table student1 partition(month='201707')
select id, name where month='201709'
insert overwrite table student2 partition(month='201706')
select id, name where month='201709';
查询结果直接创建表
create table if not exists 表名
as select id, name from student;
创建表时通过 Location 指定加载数据路径
先上传数据
hadoop fs -put /opt/module/datas/student.txt /student;
创建表
create external table if not exists student5(
id int, name string )
row format delimited fields terminated by '\t'
location '/student;
import 数据到指定 Hive 表中
注意：只有先用 export 导出后，再将数据导入。
import table student2
from '/user/hive/warehouse/export/student';
数据导出
1 hadoop102:9870 文件下载
2 insert 导出 走mr慢
insert 将查询的结果导出到本地
insert overwrite local directory
'/opt/module/hive/data/export/student'
row format delimited fields terminated by ’,’
select * from student;
将查询的结果导出到 HDFS 上(没有 local)
insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
Hadoop 命令导出到本地 重点
dfs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;
下载路径必须存在
Hive Shell 命令导出 （不好用，列名中带表名)
bin/hive -e 'select * from default.student;' >
/opt/module/hive/data/export/student4.txt;
（> 覆盖 >>追加写）
Export 导出到 HDFS 上
export table default.student #default可省略
to '/user/hive/warehouse/export/student';
export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移.
包括元数据信息。
sqoop导出 -重点
后面讲
清除表中数据（Truncate）
truncate table student;
Truncate 只能删除管理表，不能删除外部表中数据
查询
文档网址
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
查询 comm 为空的所有员工信息
select * from emp where comm is null;
RLIKE 子句
可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件
查找名字中带有 A 的员工信息
select * from emp where ename RLIKE '[A]';
表连接
outer 可以省略
join = near join
left join right join
全外连接 full join 没有符合条件的值的话，那么就使 NULL 值替代。
笛卡尔积select empno, dname from emp, dept;
左边独有select ... from a left join b on a.key=b.key where b.key is null
右边独有select ... from a left join b on a.key=b.key where a.key is null
左边独有+右边独有 select ... from a full join b on a.key=b.key where b.key is null or a.key is null
排序
order by 最好不用 用加limit, 因为全局排序，只有一个 Reducer 。
对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排
序，此时可以使用 sort by。如top n.
sort by
每个 reduce 内部排序sort by。
设置 reduce 个数
set mapreduce.job.reduces=3;
查看设置 reduce 个数
set mapreduce.job.reduces;
各区内部有序，但每个区数据随机分配。与distribute by 一起用
select * from emp sort by deptno desc;
distribute By
先按照部门编号分区，再按照员工编号降序排序
set mapreduce.job.reduces=3; #同时几个mr跑任务。
insert overwrite local directory
'/opt/module/data/distribute-result'
select * from emp distribute by deptno sort by empno desc;
distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一个分区里面去。
cluster By
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序
排序，不能指定排序规则为 ASC 或者 DESC。
select * from emp cluster by deptno;
=select * from emp distribute by deptno sort by deptno;
等价相同
分区表 重要
不分区：表数据都存放在一个文件夹里面，一张表，就是一个文件夹。
分区表，表数据可以分开来放在表文件夹里面子文件夹。需要时可以只查询部分子文件夹。
1分区基本操作
创建分区表
create table dept_partition(deptno int, dname string, loc string )
partitioned by (day string)
row format delimited fields terminated by '\t';
注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列
加载数据到分区
load data local inpath
'/opt/module/hive/datas/dept_20200401.txt' into table dept_partition
partition(day='20200401');
注意：分区表加载数据时，必须指定分区
查询分区表中数据
select * from dept_partition where day='20200401';
多分区联合查询
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';
或者
select * from dept_partition where day='20200401' or
day='20200402' or day='20200403';
增加分区（不加载数据）
alter table dept_partition add partition(day='20200404');
同时创建多个分区（同级）
alter table dept_partition add partition(day='20200405') partition(day='20200406');
空格分隔分区
删除分区
alter table dept_partition drop partition (day='20200406');
删除多个分区
alter table 表名 drop partition(day=’20200404’)，partition（day=’20200405’）;
逗号分隔分区
查看分区表有多少分区
show partitons 表名；
查看分区表结构
desc formatted 表名；
2 二级分区 加逗号
一般按天day分区，二级分区hour
创建二级分区表
create table dept_partition2( deptno int, dname string, loc string )
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
加载数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401', hour='12');
查询数据
select * from dept_partition2 where day='20200401' and
hour='12';
数据直接上传到分区目录，让分区表和数据产生关联的三种方式
创建分区文件夹
dfs -mkdir -p
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
上传数据
dfs -put /opt/module/datas/dept_20200401.log
/user/hive/warehouse/mydb.db/dept_partition2/day=20200401/hour=13;
1修复分区
msck repair table 表名;
2添加分区
alter table dept_partition2 add partition(day='201709',hour='14');
3load数据到分区
创建文件夹后，用load 数据到分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour='15');
导入数据时没设置分区
1出错，找不到文件 2自动创建一个默认分区如下：
3 insert 静态分区与动态分区
动态分区默认最后一个字段为分区，二级分区，最后两个字段
动态分区：对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据
插入到相应的分区中
开启动态分区
hadoop 3 不用修改
hadoop2 set hive.exec.dynamic.partition.mode=nonstrict
其他参数，很少用到
默认一次最大可创建1000个分区
hive.exec.max.dynamic.partitions=1000
默认一次每个节点可创建100个分区
hive.exec.max.dynamic.partitions.pernode=100
静态分区
insert into table 表名 partition(loc=’20’)
select deptno, dname from dept;
动态分区
默认最后一个字段为分区，二级分区，最后两个字段
insert into table 表名
select deptno, dname, loc from dept;
或者
insert into table 表名 partition(loc)
select deptno, dname, loc from dept;
分桶表 不重要
把一个文件夹（表，分区）里面的单个文件，分成几份。
分区针对的是数据的存储路径；分桶针对的是数据文件。
分桶规则：
根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方
式决定该条记录存放在哪个桶当中
创建分桶表
create table stu_buck(id int, name string)
clustered by(id) #根据什么字段分桶
into 4 buckets 分成几桶
row format delimited fields terminated by '\t';
查看表结构
desc formatted stu_buck;
导入数据到分桶表中
注意
从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
1reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个
数设置为大于等于分桶表的桶数
不要使用本地模式
load data inpath '/student.txt' into table stu_buck;
抽样查询
select * from 表名 tablesample(bucket 1 out of 4 on
列名);
bucket 1 out of 4 on 列名 从某列把数据随机分成四份，抽取第一份。
hive函数
查看函数信息
查看系统自带函数
show functions;
显示自带的函数的用法
desc function 函数名;
详细显示自带的函数的用法
desc function extended upper;
函数分类
常见函数
nvl（列，常数/或者替换列） 空值null替换
if(a,b,c) 如果 a为true 返回b,否则c
聚合拼接 concat_ws(‘’,collect_set(列))
concat_ws()
会跳过分隔符参数后的任何 NULL 和空字符串，CONCAT_WS 必须是 "string or array<string>
collect_list(列) 多行 聚合成一行 arrary类型
collect_set(列)： 多行去重 聚合成一行 arrary
聚合函数，可用于group by
例
SELECT t1.con_blood, CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT NAME, CONCAT_WS(',',constellation,blood_type) con_blood
FROM person_info ) t1
GROUP BY t1.con_blood
先聚合，再拼接
一行转多行
split(列，分割符) 将字符串分割成arrary结构。结果仍然是一列，不是多列。
select split(category,’,’) from move_info
原来 ‘悬疑，动作，科幻，剧情’
变成 [‘悬疑’，’动作’，’科幻’，’剧情’]
EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
select explode(friends) from test3
lateral view :针对原表整体行数做炸裂（增）
用法：LATERAL VIEW 函数 表别名 AS 列名
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此
基础上可以对拆分后的数据进行聚合。
例
（因为原表行数不变）
SELECT movie, category_name
FROM movie_info
lateral view explode(split(category,",")) movie_info_tmp AS category_name;
一行变多行，只有拆分列一列
select explode(split(列,’,’)) from 表
集合操作
size： 集合中元素的个数
select size(friends) from test3;
map_keys： 返回map中的key
select map_keys(children) from test3;
map_values: 返回map中的value
select map_values(children) from test3;
array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;
sort_array： 将array中的元素排序
select sort_array(friends) from test3;
grouping sets:多维分析
同时分多组，行连接
select a,b,sum(c) from tab1 group by a,b grouping sets((a,b),a,b,());
自定义函数(没学)
官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
编程步骤
（1）继承 Hive 提供的类
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
（2）实现类中的抽象方法
（3）在 hive 的命令行窗口创建函数
添加 jar
add jar linux_jar_path
创建 function
create [temporary] function [dbname.]function_name AS class_name;
temporary :临时的
（4）在 hive 的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;
hive压缩与存储
mr支持的压缩编码
主要用.snappy
解码器，可以在hdf浏览器直接看
开启 Map 输出Reduc 压缩 不用
开启 map 输出阶段压缩可以减少 job 中 map 和 Reduce task 间数据传输量。具体配置如
下：
开启 hive 中间传输数据压缩功能
>set hive.exec.compress.intermediate=true;
开启 mapreduce 中 map 输出压缩功能
>set mapreduce.map.output.compress=true;
设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec=
org.apache.hadoop.io.compress.SnappyCodec;
开启 Reduce 输到表 压缩 不重要，无法直接打开
当 Hive 将 输 出 写 入 到 表 中 时 ， 输出内容同样可以进行压缩。
默认hive.exec.compress.output=false。为文本，非压缩。
开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;
开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
设置 mapreduce 最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec =
org.apache.hadoop.io.compress.SnappyCodec;
设置 mapreduce 最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
测试一下输出结果是否是压缩文件
insert overwrite local directory
'/opt/module/data/distribute-result' select * from emp distribute by
deptno sort by empno desc;
文件存储格式
Hive 支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。
csv ,分隔 tsv /t分隔
行存储：一行一行存储 TEXTFILE 、SEQUENCEFILE。
列存储 一列一列存储 TEXTFILE 、SEQUENCEFILE。
建表时设置存储格式与压缩格式 优化重要
默认txt
create table log_orc_zlib( track_time string, url string)
row format delimited fields terminated by '\t'
设置其他格式
create table log_orc_zlib( track_time string, url string)
row format delimited fields terminated by '\t'
stored as orc #设置存储格式为orc
tblproperties("orc.compress"="SNAPPY"); #设置压缩格式为 snappy.
设置存储格式
stored as orc
stored as parquet
设置压缩格式
tblproperties("orc.compress"="SNAPPY");
tblproperties("orc.compress"="SNAPPY");
ORC , Parquet ,textFile 对比
文件大小：ORC < Parquet < textFile ocr最优
查询速度相近 ，txt最快
总结 优化时 使用ocr存储格式
存储方式和压缩总结
在实际的项目开发当中，hive 表的数据存储格式一般选择：orc 或 parquet。压缩方式一
般选择 snappy，lzo。
hive优化
1查看执行计划（Explain）
explain select deptno, avg(sal) avg_sal from emp group by
deptno;
结果 关键字
Explain
STAGE DEPENDENCIES:
Stage-1 is a root stage #步骤1
Stage-0 depends on stages: Stage-1 #步骤2
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree: map 步骤
TableScan
alias: emp #表
Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: sal (type: double), deptno (type: int)
outputColumnNames: sal, deptno #输出列
Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE
Column stats: NONE
Group By Operator
aggregations: sum(sal), count(sal)
keys: deptno (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 1 Data size: 7020 Basic stats:
COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: int)
sort order: +
Map-reduce partition columns: _col0 (type: int)
Statistics: Num rows: 1 Data size: 7020 Basic stats:
COMPLETE Column stats: NONE
value expressions: _col1 (type: double), _col2 (type:
bigint)
Execution mode: vectorized
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0), count(VALUE._col1)
keys: KEY._col0 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE
Column stats: NONE
Select Operator
expressions: _col0 (type: int), (_col1 / _col2) (type: double)
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE
Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 7020 Basic stats: COMPLETE
Column stats: NONE
table:
input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
2表优化
数据倾斜
什么叫数据倾斜？
某个节点被分配过多数据，导致比其他节点需要更多的执行时间，或者内存溢出，无法查询。
数据倾斜原因
1，数据在节点上分布不均
2，key分布不均(key中存在个别值数据量比较大，比如NULL,那么join时就会容易发生数据倾斜)
3，count(disctinct key)，在数据两比较大的时候容易发生数据倾斜，因为count（distinct）是按照group by字段进行分组的
4，group by的使用容易造成数据倾斜
5，业务数据本身的特性
6，建表时考虑不周
7，某些SQL语句本身就有数据倾斜
2.1表连接
2.11小表连接大表
1 小表在左，先进内存
2 hive 可以启用mapjoin 设置
新版的 hive 已经对小表 JOIN 大表和大表 JOIN 小表进行了优化。小表放
在左边和右边已经没有区别。
参数：
hive.auto.convert.join=true 默认值为true，自动开启MAPJOIN优化。
hive.mapjoin.smalltable.filesize=2500000 默认值为2500000(25M)
注意：使用默认启动该优化的方式如果出现莫名其妙的BUG(比如MAPJOIN并不起作用)，就将以下两个属性置为fase手动使用MAPJOIN标记来启动该优化:
hive.auto.convert.join=false (关闭自动MAPJOIN转换操作)
hive.ignore.mapjoin.hint=false (不忽略MAPJOIN标记)
select /* +mapjoin(a) */ a.id , a.name, b.age from a join b on a.id = b.id;
将表放到Map端内存时，如果节点的内存很大，但还是出现内存溢出的情况，我们可以通过这个参数 mapreduce.map.memory.mb 调节Map端内存的大小。
3小表不小不大，怎么用 map join 解决倾斜问题
#先用大表的键值，过滤一遍中表，再连接中表
表 log ,users
select /*+mapjoin(x)*/* from log a
left outer join (
select /*+mapjoin(c)*/ d.*
from ( select distinct user_id from log ) c
join users d
on c.user_id = d.user_id
) x
on a.user_id = b.user_id;
2.12 大表连接大表
非inner join 时 空值过滤与空值替换：
null 不做处理的话，会会划分到一个reducer里面，会出现数据倾斜。
数据倾斜：某些 reducer 的资源消耗远大于其他 reducer。
inner join 自动过滤空值
空值过滤
1非inner join 2空值不需要时
select n.* from (select
* from nullidtable where id is not null) n left join bigtable o on n.id = o.id;
#只有左表需要全部值，过滤左表即可。
空值替换
select n.* from nullidtable n full join bigtable o on
nvl(n.id,rand()) = o.id
#使用一个字符 加 rand() 随机整数，或者随机选择
空值的 key 变成一个字符串加上随机数
case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end
大表连接大表 使用分桶表
表连接时，相同字段根据哈希值分到同一个桶。
参数设置，启动分桶连接
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
设置 6 个 reduce 个数
set mapreduce.job.reduces = 6；
创建两个分桶表，根据连接字段，桶的个数不要超过可用 CPU 的核数
create table bigtable_buck2( id bigint, t bigint, uid string, keyword string, click_num int)
clustered by(id)
sorted by(id)
into 6 buckets
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table
bigtable_buck2;
普通连接
insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable_buck1 s
join bigtable_buck2 b
on b.id = s.id;
join 不用where 使用 on
执行 JOIN 查询的时候,使用 ON 语句而是不使用 where。
2.2 Group By
默认情况下，Map 阶段同一 Key 数据分发给一个 reduce，当一个 key 数据过大时就倾斜
了。
如果是group by，就可以用两阶段聚合法，
将group by a 改成 group by a,b ，然后再group by a
或者增加一个随机数x，将a通过concat(x,a)改成b,将group by a 改成 group by b, 然后再聚合一次去掉x后的b，group by substr(b,length(x))
1开启map端聚合
生成的查询计划会有两个 MR。
第一个 MR 中，Map 的输出结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作。
第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中
参数：
是否在 Map 端进行聚合，默认为 True
set hive.map.aggr = true
在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000
有数据倾斜的时候进行负载均衡（默认是 false）
set hive.groupby.skewindata = true
同时过滤空值，增加分组
Count(Distinct) 去重统计
据量大的情况下，由于 COUNT DISTINCT 操作只有一个 Reduce 处理
一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换,但是需要注意 group by 造成 的数据倾斜问题.
2.4避免笛卡尔积
尽量避免笛卡尔积，join 的时候不加 on 条件，或者无效的 on 条件，Hive 只能使用 1 个
reducer 来完成笛卡尔积。当需要笛卡尔积时，临时生效一下。
2.5行列过滤
列：少使用select * 只取需要的列，如果有分区，尽量使用分区过滤，
行：先过滤行，再连接其他表。
通过子查询后，再关联表
select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;
2.6 使用分区表，分桶表
2.7 排序 order by 后面加limit 使用sorted by
使用了 order by 语句的查询，要 求必须使用 limit 语句,不然不给用
2.8不可拆分大文件引发的数据倾斜
只能将使用GZIP压缩等不支持文件分割的文件转为bzip和zip等支持文件分割的压缩方式。
2.9 key不同数据类型
表b中key字段既有string类型也有int类型，所有的string类型都被分配成同一个id，
改int 为string
把数字类型转换成字符串类型
on a.usr_id = cast(b.user_id as string)
with rollup 导致的数据倾斜
select a，b，c，count(1)from log group by a，b，c with rollup;
2.10特殊值导致侵斜
1分开处理法
某一个特殊值，特别多 如 未登录用户users=0 单独过滤出来连接
select *
from (select * from logs where user_id = 0) a
join (select * from users where user_id = 0) b
on a.user_id = b.user_id
union all
select *
from logs a join users b
on a.user_id <> 0 and a.user_id = b.user_id;
2启用skew join
其原理把这种user_id = 0的特殊值先不在reduce端计算掉，而是先写入hdfs，然后启动一轮map join专门做这个特殊值的计算，期望能提高计算这部分值的处理速度
参数：
启动
hive.optimize.skewjoin = true;
数量
hive.skewjoin.key=10000
3随机数分配法
特殊值比较多，最常见的倾斜是因为数据分布本身就具有长尾性质，比如我们将日志表和商品表关联，分配到热门商品的reducer就会很慢。
加一个随机数，随机数的取值范围n相当于将item给分散到n个reducer：
select a.*, b.*
from (select *, cast(rand() * 10 as int) as r_id from logs)a
join (select *, r_id from items
lateral view explode(range_list(1,10)) rl as r_id)b
on a.item_id = b.item_id and a.r_id = b.r_id
确实无法减少数据量引发的数据倾斜
导致内存溢出，无法运行
调整reduce的内存大小使用mapreduce.reduce.memory.mb这个配置。
总结
数据倾斜是由于某个task被分配过多数据，而比其他task需要更多的执行时间(如几十倍，几百倍)，导致其他task执行完进入漫长等待的一种现象。
数据倾斜只会发生在多对多或一对多的数据分发的过程中，如spakr的shuffle操作中，在MapReduce中的reduce阶段，
常见的算子类型为：join，group by 和窗口函数如row_number 。
这是因为这些算子会进行shuffle操作，产生一个key值，如group by的字段，join的on字段，
为了利用多台机器的并发能力，会按这个key值取数范围进行均衡的分发，每台机器尽量分到相同长度的取值范围的key，
然后将这些有key值的数据的数据传输过去。
这时如果某个key范围内的数据量大大多于其他范围的数据量，就会发生数据倾斜。
解决办法：
解决数据倾斜的思路在于，先找到产生数据倾斜的算子操作，然后针对具体的算子，解决它单个key范围被分到过多的数据的问题，
按key的类型，由简便到复杂依次有以下几种解决思路：
1.直接消灭倾斜的key。
2.直接避免shuffle操作，没有了shuffle操作也就没有了数据倾斜
3.通过增多task的数量，减小单个task内的数据量，这个方法适用于某个key范围的数据多的情况。
4.通过特殊处理key值，减小单个task内的数据量，这个方法适用于某些特定的key值的数据过多的情况
第一个解决思路比较简单，找到倾斜的key，直接过滤掉。就没有倾斜问题了。这种操作的适用范围很窄。比如一些空字符串，一些缺省值等等，本身在业务上能接受它们不参与操作。
如果发现造成倾斜的key是这些，就可以直接过滤，非常简单粗暴，性价比最高。
如果该key不能被过滤，就考虑能否将shuffle操作避免掉。
比如join的时候使用广播的方式，将其中一张表广播到所有的机器节点上，这样一个shuffle操作就变成了一个map操作。
广播的方式(map join)适用于join的时候某一张表的数据量比较小的时候，如果两张表都很大，则不适用这种方式。
如果不能避免shuffle操作也不能过滤倾斜的key值，那么我们就要从key值的类型入手，如果倾斜的key值是连续的，不是由单个key值引起的，就可以增大task的数量，
比如，修改shuffle产生的partition参数为更大，就可以使同一个范围内的key值分到不同机器上，
或者使key值重新排列，倒排或者其他方式，使他们不再连续，分配到不同的机器上，就可以防止倾斜。
上述3个操作都比较简单高效，但是应用的场景有限，如果该key不能被过滤，也不能避免shuffle，而且是1个到多个不连续的key引起的，就需要做比较复杂的操作了。
如果是group by，就可以用两阶段聚合法，
将group by a 改成 group by a,b ，然后再group by a
或者增加一个随机数x，将a通过concat(x,a)改成b,将group by a 改成 group by b, 然后再聚合一次去掉x后的b，group by substr(b,length(x))
如果是join操作，就需要分开join，将倾斜的数据和不倾斜的数据分成两部分。
然后两站表不倾斜的部分join得到第一张表。
倾斜的数据，第一张较大的表：增加一个随机数1-x，随机数取决于你想把数据切成几份。
得到 concat(x,a).
另一张较小的表将每一行复制到x份(总共增加x-1份)，然后按顺序标上序号1到x，如下所示：
源数据，倾斜的key值为a和b，
大表：aaaaa bbbbb
小表：aaaa bbbb
原来的join最后得到40条数据. 每个key分到20条
处理过key的表，
大表：1a 2a 2a 1a 2a 1b 2b 3b 3b 2b (增加一个随机数前缀1-3)
小表：
1a 2a 3a 1a 2a 3a 1a 2a 3a 1a 2a 3a 这n条数据都按顺序附加一个1~x的前缀
1b 2b 3b 1b 2b 3b 1b 2b 3b 1b 2b 3b
生成40条数据，每个key平均分到6.66条.
6.66条的计算公式是5/3*4, 5是大表的key的条数，有5个，增加随机数之后，被分成了3份，得到了5/3 条，小表虽然也加了随机数但是复制了x份，
所以小表的key还是4个，所以是：5/3*4
最后将倾斜部分的数据和不倾斜的部分的数据分别join之后再union起来就可以了。
当然，解决数据倾斜的不止这些方法，这些方法只是常用的，本质还是打散集中在某台机器，某个task的的数据量。只要能达到这个目的，就可以。
hive实战
创建 orc 存储格式带 snappy 压缩的表
create table gulivideo_orc( videoId string, uploader string,
age int, category array<string>,
length int, relatedId array<string>)
stored as orc
tblproperties("orc.compress"="SNAPPY");
top n
统计视频观看数 Top10
SELECT videoId, views
FROM gulivideo_orc
ORDER BY views DESC LIMIT 10;
统计视频类别热度 Top10
SELECT t1.category_name , COUNT(t1.videoId) hot
FROM (
SELECT videoId, category_name
FROM gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
) t1
GROUP BY t1.category_name
ORDER BY hot DESC LIMIT 10;
统计出视频观看数最高的 20 个视频的所属类别以及类别包含Top20 视频的个数
思路：
（1）先找到观看数最高的 20 个视频所属条目的所有信息，降序排列
（2）把这 20 条信息中的 category 分裂出来(列转行)
（3）最后查询视频分类名称和该分类下有多少个 Top20 的视频
SELECT t2.category_name, COUNT(t2.videoId) video_sum
FROM
( SELECT t1.videoId, category_name
FROM
( SELECT videoId, views , category
FROM gulivideo_orc ORDER BY views DESC LIMIT 20
) t1
lateral VIEW explode(t1.category) t1_tmp AS category_name
) t2
GROUP BY t2.category_name
统计视频观看数 Top50 所关联视频的所属类别排序
SELECT t6.category_name, t6.video_sum,
rank() over(ORDER BY t6.video_sum DESC ) rk
FROM
(
SELECT t5.category_name, COUNT(t5.relatedid_id) video_sum
FROM
(
SELECT t4.relatedid_id, category_name
FROM
( SELECT t2.relatedid_id , t3.category
FROM
( SELECT relatedid_id FROM
( SELECT videoId, views, relatedid
FROM gulivideo_orc ORDER BY views DESC LIMIT 50
)t1
lateral VIEW explode(t1.relatedid) t1_tmp AS relatedid_id
)t2
JOIN gulivideo_orc t3
ON
t2.relatedid_id = t3.videoId
) t4
lateral VIEW explode(t4.category) t4_tmp AS category_name
) t5
GROUP BY
t5.category_name
ORDER BY
video_sum
DESC
) t6
统计每个类别中的视频热度 Top10，以 Music 为例
思路：
（1）要想统计 Music 类别中的视频热度 Top10，需要先找到 Music 类别，那么就需要将
category 展开，所以可以创建一张表用于存放 categoryId 展开的数据。
（2）向 category 展开的表中插入数据。
（3）统计对应类别（Music）中的视频热度。
SELECT
t1.videoId,
t1.views,
t1.category_name
FROM
(
SELECT
videoId,
views,
category_name
FROM gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
)t1
WHERE
t1.category_name = "Music"
ORDER BY
t1.views
DESC
LIMIT 10
统计每个类别视频观看数 Top10
SELECT
t2.videoId,
t2.views,
t2.category_name,
t2.rk
FROM
(
SELECT
t1.videoId,
t1.views,
t1.category_name,
rank() over(PARTITION BY t1.category_name ORDER BY t1.views DESC ) rk
FROM
(
SELECT
videoId,
views,
category_name
FROM gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
)t1
)t2
WHERE t2.rk <=10
统计上传视频最多的用户 Top10以及他们上传的视频
观看次数在前 20 的视频
思路：
（1）求出上传视频最多的 10 个用户
（2）关联 gulivideo_orc 表，求出这 10 个用户上传的所有的视频，按照观看数取前 20
SELECT
t2.videoId,
t2.views,
t2.uploader
FROM
(
SELECT
uploader,
videos
FROM gulivideo_user_orc
ORDER BY
videos
DESC
LIMIT 10
) t1
JOIN gulivideo_orc t2
ON t1.uploader = t2.uploader
ORDER BY
t2.views
DESC
- 点赞
- 收藏
- 关注作者
评论（0）