京东-供应链研发部-数据库开发岗面试题：
1.oracle分析函数：
语法：function(参数) over（partition/order by/windowing）;
Corr() over() –表记录相关系数
Stddev() over()-样本标准差
Stddev_samp() over() –样本标准差
Stddev_pop() over()-总体标准差
Variance() over()-样本方差
Var_samp() over()-样本方差
Var_pop() over()-总体方差
Covar_samp over()-表记录样本协方差
Covar_pop over() –表记录总体协方差
Percentile_disc(0.5) within group(order by salary) over(partition by job) – 各工种的平均工资
Cume_dist() over(partition by job order by salary)—各工作下薪资排名比。
Percent_rank() over(partition by job order by salary)-各工作下薪资排名号/该组行数
Ratio_to_report(salary) over(partition by job )-各工作下该薪资占该工作总薪资的占比
Lag(eage,1) over(partition by esex order by salary)-各esex下按薪资排序去前1行
Lead(eage,1) over(partition by esex order by salary)-各esex下按薪资排序取后1行
Max(salary) keep(dense_rank first order by salary desc)-取薪资排名的第一行
Max(salary) keep（dense_rank last order by salary desc）-取薪资排名的最后一行
First_value(salary) over(partition by job order by salary desc)-取各工作下薪资排名第一的薪资
Last_value(ename) over(partition by job order by salary desc)-取各工作下薪资排名最后的人名
Ntile(3) over(partition by job order by salary desc)-将各工作下的记录平均分成3份，多出的按顺序分给前面的组
Dense_rank() over(partition by job order by age)-各工作下按年龄连续排序
Rank() over(partition by job order by age)-各工作下按年龄跳跃排序
Max(salary) over(parition by job order by age)-各工作下按年龄排序取最大薪资
Min(salary) over(partition by job order by age)-各工作下按年龄排序取最小薪资
Avg(salary) over(partition by job order by age)-各工作下按年龄排序取平均工资
Sum(salary) over(partition by job order by age)-各工作下按年龄排序取总工资
Count(*) over(partition by job order by age) –各工作下按年龄排序取总记录数

2.oracle聚合函数：
Avg()
Round()
Sum()
Count()
Min()
Max()

3.oracle字符串函数：
LOWER() –转小写
UPPER()-转大写
LTRIM()-左删除空格
RTRIM()-右删除空格
CONCAT()-连接
REPLACE()-更新内容
LEN()-长度
4.oracle表关联：
Outer join –外连接
Self join –自连接 表A 关联表A，找出自表里字段的内在关系，例如求科目字段里：语文>数学的人，本表中父子节点的对应输出
Cross join –交叉连接-（A表）n*（B表）m=nm条记录 ，笛卡尔积
Full join –全连接
Right join 右连接
Left join 左连接
Inner join 内连接
Natural join 自然连接 –表A和表B相同字段等值连接
5.oracle行列转换方法：

name 学科 成绩
a     语文  90
b    数学  90
b    英语   100

name  yu     shu    wai
a      90
b             90       100

select * from pivot_data 
pivot(sum(成绩)  for 学科 in（‘语文’，‘ 数学‘，‘ 英语’）)；-先提取出所有学科，再动态拼接。

Hive行列转换：

Select name,
Info[‘语文‘]  as yu,
Info[‘数学‘]  as shu,
Info[‘英语‘]  as wai
From (  select
Name,
Str_to_map(concat_ws(‘，‘，Collect_set(Concat_ws(‘：‘，学科,Cast(成绩 as string)))  ))  info  --[数学: 90],[ 英语  :100 ]
From pivot_data
Group by name) a

6.hive内表外表：
内表：无external,保存在warehouse下，
创建过程和数据加载过程：数据-移动到-数据仓库目录中，访问数据仓库目录中的数据
删除时：  hdfs文件数据和元数据（这里指数据库元数据：表，sql语法，存储过程，连接功能等）被同时删除,
元数据：数据库元数据，参数元数据，结果集元数据。
外表：
创建过程和数据加载：数据不移动到数据仓库目录中，只与外部数据建立一个连接。
删除时：只删除连接，只删除metastore的元数据，不删除hdfs的表数据。-客户端-metastore服务-mysql存取元数据。

7.hive动态静态分区：

```sql
静态分区：指定分区-常用
创建静态分区表：
Create table a() 
partition by (month_id string)
row format delimited fields terminated by ‘，’；
静态分区插入数据：
insert into table a  partition (month_id=’201911’)
select * from b;

创建动态分区表：
create table c (name string)
partition by (user_id)
row format delimited fields terminated by ‘，’；
动态分区插入数据
Set  hive.exec.dynamic.partition.mode=nonstrict ；–设置动态分区模式为非严格模式
insert into table c partition (user_id)
select  name,
         user_id 
from d;--根据user_id 的值来分区

结果:
Select * from c;
Wangsan 10
Zhanger 20

8.Hive:RCFILE,ORCFILE,SEQUENCEFILE,TEXTFILE,等使用原因：
Textfile:默认格式，存储方式-行存储。Gzip压缩算法，压缩后文件不支持split（tab键字符串分割）。
在反序列化中，必须逐个字符判断是否为分隔符和行结束符，因此反序列化开销会比 SqueueceFile高几十倍。（序列化：对象转-XML（字符串）.json（字符串）,二进制流，）表user –json [{“id”:”1”,”user”:”name1”}],反序列化：字符串，二进制流-转对象
Sequeuefile:可分割文件格式，将KEY-VALUE转化成二进制写到文件中，行存储，支持none,block >record三种压缩。和MApfile是相互兼容的。
RCfile:同一行数据位于同一个节点，列维度数据压缩，跳过不必要的列读取。不支持数据写操作，仅支持追加。-hdfs文件仅支持数据追加文件尾部。行组4MB做为行组大小。

ORCfile：按行分块，每块列存储。压缩快，效率>rcfile,是rcfile的改良版。

           存储方式             占用磁盘大小      运行时间大小
textfile   行存储                5                      5
Sequecefile  行存储              4                      4
Rcfile      列存储               3                      3
ORCfile     列存储               2                      2

9.Hive不支持子查询的原因：

支持 not  in (1,2,3)
不支持 not in（select ID from A）  --1
遇到要用1的情况改用为：
Not exists  (select Id FROM a)
LEFT  JOIN   a ON   …  where a.id is null

10.Hivesql优化的步骤：
一、Hive参数调优
1.设置自动mapjoin为false
Set hive.auto.convert.join=false;
Set hive.ignore.mapjoin.hint=false;
2.关闭严格模式
Set hive.exec.dynamic.partition.mode=nonstrict;
Set hive.mapred.mode=nonstrict;
3.设置压缩格式-gzip
Set hive.exec.compress.output=true;
Set mapred.output.compress=true;
Set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
Set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec;
4.设置压缩格式-lzo
Set hive.exec.compress.output=true;
Set mapred.output.compress=true;
Set mapred.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;
Set io.compression.codecs=com.hadoop.compression.lzo.LzoCodec;
5．数据倾斜
Set hive.map.aggr=true; --map端聚合
Set hive.optimize.skewjoin=true;
Set hive.groupby.skewindata=true;
5.排序内存溢出问题
Set mapreduce.task.io.sort.mb=10;
6.join 内存溢出设置
set mapred.child.java.opts=-Xmx512m;
7.设置任务数
set mapred.reduce.tasks=10;
8.元数据：嵌套SQL并行执行优化：
Set hive.exec.parallel=true;
Set hive.exec.parallel.thread.number=16;
Mapreduce.reduce.shuffle.memory.limit.percent:0.25-0.1
Set mapred.max.split.size=1000000;

二、Hivesql优化：
1.大表变小：
横向减：减字段
纵向减：-长尾： key的1个值（user_id=01）上的数据记录分到1个instance上处理，导致数据记录大的运行时间长，名为长尾；
A.热点长尾：key的1个值（user_id=01）的记录数很大（例如>10000），这个01是热点值，它造成的长尾叫热点长尾。
优化方法：热点值记录和非热点值记录分开统计。
B．空值长尾：key中的空值（user_id=null）对应的记录会集中到1个reduce上，导致跑的慢，造成空值长尾。
优化方法：空值变随机数：coalesce(user_id,rand()*9999)
Coalesce(,Rand()*999) coalesce(,rand()*999) coalesce(,rand()999)
C.map长尾：splitsize将数据分片，大小不均时，有的map任务处理数据量大，导致map长尾。
优化方法：调节splitsize，分片更多更小。
D.用distinct 时,将数据都传到reduce端，当key的值对应的数据量不均匀时，导致跑的慢，导致reduce长尾。
优化方法：select a,count(distinct b) from c group by a;
Select a, sum(case when num>0 then 1 else 0 end ) as num1 from(Select a,b ,count() as num from c group by a,b) d group by a;
Distinct 变group by
2.从表小-left join 的右表，right join 的左表
3.子查询临时表化
4.函数性能调优，group by 替代 distinct

三、Hive 业务逻辑调优：
1.数据流程优化 ：网络带宽-采集-切片-落地-加载-生成-并行-压缩-解压-传输
2.并行程序、拉链表、增量表、全量表、明细表汇总生成结果 表，维度表，逻辑归类处理

11.Linux:Grep ,sed,awk应用实例：

ls | grep ‘.*/.sp’
sed ‘s/abc/ABC/’file_input  --替换
cat input.txt |awk –f read.awk