arts-week1

每周完成一个ARTS:

每周至少做一个LeetCode的算法题,阅读和点评至少一篇文本技术文章,学习至少一个技术技巧,分享一篇有观点和思考的技术文章(也就是algorithm ,preview ,tip,share 简称ARTS)需要坚持至少一年。

Algorithm

整数反转,先从简单的题目入手

 Preview

也从简单入手,在扇贝阅读了一篇《Alipay announces service charge on credit card payment》

 Tip

记录几个oracle窗口函数

SELECT 
t.full_name,
t.salary,
t.manager_id,
row_number() over(partition by t.manager_id order by t.salary desc) as 部门排行,
rownum row_number, --行号
round((rownum + 1) / 4) page_number, --每4行一页
ntile(2) over(ORDER BY t.salary DESC) page_number_nt, --平均分成两类

avg(t.salary) over(partition by t.manager_id) as 部门薪水均值,
sum(t.salary) over(partition by t.manager_id) as 部门薪水总额,
count(t.id) over(partition by t.manager_id) as 部门总人数,
dense_rank() over(partition by t.manager_id order by t.salary desc) as 部门薪水排行,
dense_rank() over(order by t.salary desc) as 公司薪水排行,

min(t.salary) over(partition by t.manager_id) as 部门的最低薪水1,
MIN(t.salary) keep(dense_rank FIRST ORDER BY t.salary) over(PARTITION BY t.manager_id) as 部门的最低薪水2,
first_value(t.salary) over(PARTITION BY t.manager_id ORDER BY t.salary) as 部门的最低薪水3,

max(t.salary) over(partition by t.manager_id) as 部门的最高薪水1,
Max(t.salary) keep(dense_rank Last ORDER BY t.salary) over(PARTITION BY t.manager_id) as 部门的最高薪水2,
last_value(t.salary) over(PARTITION BY t.manager_id ORDER BY t.salary) as 部门的最高薪水3,

lag(t.full_name, 1, '00') over(ORDER BY t.salary DESC) last_persion, --薪水在自己前一位的人
lead(t.full_name, 1, '00') over(ORDER BY t.salary DESC) next_persion --薪水在自己后一位的人
FROM 
EMPLOYEES t
ORDER BY t.salary DESC

 

Share

经典面试:当你输入一个网址后回车,实际会发生什么?

 

上一篇:20165324 《网络对抗技术》week1 Kali的安装与配置


下一篇:20165203 《网络对抗技术》week1 Kali的安装与配置