ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 常用的分析函数

常用的分析函数

原创 Linux操作系统 作者:hjgluguo 时间:2010-01-18 15:20:23 0 删除 编辑

--计算employees表中相同经理下的每一雇员和雇佣日期正好位于雇员正前后的雇员的平均薪水:
SELECT manager_id, last_name, salary, AVG(salary) over(PARTITION BY manager_id ORDER BY hire_date rows BETWEEN 1 preceding AND 1 following) AS c_mavg FROM hr.employees;

--计算employees表每个雇员与雇员自己薪水相差在50与150之间的雇员的个数
SELECT last_name, salary, COUNT(*) over(ORDER BY salary RANGE BETWEEN 50 preceding AND 150 following) AS mov_count FROM hr.employees;

--返回employees表中每个部门佣金最少雇员的最低薪水以及佣金最高雇员的最高薪水

SELECT department_id, MIN(salary) keep(dense_rank FIRST ORDER BY commission_pct) "Worst",  
  MAX(salary) keep(dense_rank LAST ORDER BY commission_pct) "Best" FROM hr.employees GROUP BY department_id;


--计算hr.employees表中薪水为$15500和佣金为5%的雇员的排名:
SELECT rank(15500, .05) within GROUP ( ORDER BY salary, commission_pct) "Rank" FROM hr.employees;

--employees表中部门为80,以薪水和佣金排名。相同薪水值有相同的排名,并且导致排名的不连续。比较下面例子

SELECT department_id,
last_name,
salary,
commission_pct,
rank() over(PARTITION BY department_id ORDER BY salary DESC, commission_pct) "Rank"
FROM hr.employees WHERE department_id = 80;

--在employees表中,各部门按雇员雇佣日期排序后赋予每一行一个数

SELECT department_id, last_name, employee_id,
row_number() over(PARTITION BY department_id ORDER BY employee_id) AS emp_id FROM hr.employees;

--选择employees的所有行,按first_name排序,但结果集只返回51至100行

SELECT name FROM (SELECT first_name || ' ' || last_name name, row_number() over(ORDER BY first_name) r FROM hr.employees)
WHERE r BETWEEN 51 AND 100;
--等同于
SELECT name FROM (
  SELECT name, rownum r FROM
    (SELECT first_name || ' ' || last_name name FROM hr.employees ORDER BY first_name )
      WHERE rownum <= 100
)
WHERE r > 50 ;
--比较如下三个查询:
SELECT first_name || ' ' || last_name name, rownum FROM hr.employees;
SELECT first_name || ' ' || last_name name, rownum FROM hr.employees order by first_name;
SELECT name, rownum FROM (SELECT first_name || ' ' || last_name name FROM hr.employees ORDER BY first_name) ;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7865774/viewspace-625349/,如需转载,请注明出处,否则将追究法律责任。

下一篇: grep的一些例子
请登录后发表评论 登录
全部评论

注册时间:2008-07-11

  • 博文量
    97
  • 访问量
    182034