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

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

 

转载于:https://www.cnblogs.com/frank413/p/10418574.html