枣庄手机网站建设公司,个人简历制作视频教程,网站内容如何编辑软件,网站群建设标准一、Hive的查询DQL
1. 查询的语法
关键字作用distinct如果有多个字段#xff0c;根据组合进行去重all默认情况就是查询所有#xff0c;可以省略select 1 from table相当于给表增加了一列where分组前过滤having分组后过滤order by全局有序#xff0c;按照关键字去重分组,re…一、Hive的查询DQL
1. 查询的语法
关键字作用distinct如果有多个字段根据组合进行去重all默认情况就是查询所有可以省略select 1 from table相当于给表增加了一列where分组前过滤having分组后过滤order by全局有序按照关键字去重分组,reduce置为1distribute by结合sort by使用指明按照什么进行分区sort by底层根据reduce个数随机分成对应个有序小组cluster bydistribute by 和sort by 关键字一样时可以用它代替没啥用鸡肋join左右相连union上下相连limit n offset m限制查询个数, 从第m个开始数
2. 执行顺序
from join on where group by having select distinct union order by limit 知道执行顺序的非常重要建议书写SQL时也可以按照这个顺序来书写。不过select 建议在group by后即可书写了因为必须先确定字段后才能进行分组后的排序。
3. 语法细节
比较null是否等于null null nullnull is null limit n, m: 从n1个元素开始数数m个使用rlike代替like来进行字符串匹配上限比较高,可以使用正则表达式。
4. 聚合函数
所有聚合函数都是忽略null值的count(1)来统计元素的个数时会统计null值聚合函数不能和普通列一起查询需要普通列和聚合函数一起查询需要将普通列放在group by中
5. group by关键字
当select查询的字段和group by后的字段一样时本质上就是去重。如果不想缺失数据时建议不要使用group by 关键字来进行分组。
6. join关键字
如果不写on条件join操作就是两个表进行逐行拼接即笛卡尔积。join可以使用等值连接或者非等值连接。join默认是内连接只会取符合on条件的交集。
满外连接 select * from emp e full join dept d on e.deptno d.deptno;
7. union和union all的区别
union all 是上下两表连接不去重union会默认去重。
8. join多表联查的底层原理
优化前先启动一个MR将a表和b表进行连接之后将将连接结果再用一个MR和c表进行连接。 优化后默认是开启优化的会使用map join将b表和c表都作为小表读入内存后直接一个map过程完成连接操作。
9.四个排序关键字
order by deptno, salary 基本上等价于distribute by deptno sort by salary 不过distribute by sort by 效率更高并且设置多个reduce时可以将数据分到不同的文件中。
二、函数
查询函数功能 show functions;desc function 函数名desc function extended 函数名 单行函数 单行函数指每行数据处理一次一进一出。数值函数 round(,2)四舍五入,保留2位小数ceil: 数轴右边那个数floor数轴左边那个数 字符串函数 substring(“atguigu”, -2):倒数后面2个都要replace(): 一般用于日期格式替换regexp_replace(str, regx, rep): 正则替换nvl(A, B): 如果A为null, 则赋值为Bregexp() : 正则匹配get_json_object(): $.属性名$.[0].属性名 日期函数 unix_timestamp(): 转出时间戳from_unixtime()转成日期current_date(): 打印当前时间current_timestamp(): 打印时间精确到毫秒datediff(date1, date2) : 计算日期的差值date_add(): 日期的加减date_format():日期格式转换, 一般用于日期的截取 流程控制函数 case语句if语句 if(a,b,c),如果a为真则执行b否则执行c
casewhen sal 2500 then 小康when sal 1500 and sal 2500 then 温饱when sal 1500 then 贫穷
end 聚合函数 多行函数指多行数据处理一次多进一出。collect_list(job): 收集分组后多行数据合并成一个数组, 结果不去重collect_set(job): 收集分组后的去重数据集array_contains(arr, num): 判断数组中是否包含num元素 炸裂函数 炸裂函数指一行数据返回多个结果一进多出。适用于两个表都是多对多关系时将数组分割为多行数据udtf([a,b,c]) : 炸裂函数 explode(arr): 使用频率最高用来炸数组posexplode() : 带了的下标的炸裂函数inline(): 用来炸裂结构体 Lateral View侧写: 一般搭配炸裂函数使用
select *
from movie
lateral view explode(movies) temp as movie;窗口函数
概念窗口函数是高阶函数mysql8.0才有。窗口函数的执行时间在group by之后having之前。分类 窗口 lead, lag, first_value,last_value聚合count,sum,min,max,avg分析rank,row_number,dense_rank,ntile 语法和含义 函数()over([partition by][order by ][窗口子句])窗口就是你当前查询结果的所有数据并且默认窗口最大从第一行到最后一行函数表示计算逻辑over表示开窗控制函数的计算范围 (1) 窗口子句rows between表示 显示限定范围,默认是上无边界下无边界 unbounded preceding 上无边界current row 当前行unbounded following下无边界1 preceding 上一行1 following 下一行默认情况如果没有order by窗口子句默认是上无边界下无边界如果有order by, 窗口子句默认是上无边界到当前行。
select user_name,order_date,order_amount,count(*)over(rows between unbounded preceding and unbounded following) cnt
from order(2) partition by 属性名… 开窗后再划分子窗口每行再单独计算。可以有多个属性名来通过组合来划分的更细。 (3) order by 属性名…, 对细分子窗口进行排序。
select user_name,order_date,order_amount,sum(order_amount)over(partition by user_name order by order_date)from order_info;常用窗口函数
6.1 跨行取数据
函数名作用lag(字段名, 偏移量, 默认值)从当前位置向上偏移几行后取值默认偏移量是1默认值默认是nulllead(字段名, 偏移量, 默认值)从当前位置向下偏移几行取值, 默认偏移量是1默认值是默认是null
6.2 取第一个和最后一个数据
函数名作用first_value(字段名, false)取第一个数据第二个参数表示是否忽略nulllast_value(字段名, false)取最后一个数据一般第二个参数均为false
6.3 排名函数
函数名作用rank排名相同时会重复有重复时下一个名次会消失最后一个排名等于元素个数dense_rank排名相同时会重复有重复时下一个名次不会消失row_number行号
6.4 分组分析
函数名作用ntile(组的个数)将数据打上组名可以通过组名取出对应组的数据
select user_name,order_date,order_amount,ntile(5)over(order by order_date) ntfrom order_info;注意 ①如果你需要结果有序那一定要排序否则会正进反出MR的环形缓冲区的反向溢写导致的 ② 如果在累加求和时排序有重复值累加的不是一行而是一段 三、子查询
子查询分类
标量子查询一个常量单列值列子查询一个竖列行子查询一个子表
子查询出现的位置
位置子查询类别select标量子查询where标量子查询列子查询from行子查询, 列子查询
