京东-供应链研发部-数据库开发岗面试题： 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()-长度