每周完成一个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