以下是我整理的京东供应链数据仓库开发面试题1

京东-供应链研发部-数据库开发岗面试题：

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()-长度