窗口函数之分析函数（读书笔记一）

来源：互联网发布：朋友圈链接制作软件编辑：程序博客网时间：2024/06/06 09:19

窗口函数建实验表语句

CREATE TABLE employeeinfo(emp_id NUMBER(7),                          emp_name VARCHAR2(40),                          dept_id NUMBER(7),                          hire_date DATE,                          salary NUMBER(9,2));INSERT INTO employeeinfo VALUES(100,'wang john',10,to_date('1990-01-01','yyyy-mm-dd'),20000);INSERT INTO employeeinfo VALUES(101,'kochhar neena',90,to_date('1989-09-21','yyyy-mm-dd'),17000);INSERT INTO employeeinfo VALUES(102,'de haan lex',90,to_date('1993-01-13','yyyy-mm-dd'),17000);INSERT INTO employeeinfo VALUES(103,'hunold alexander',60,to_date('1990-01-03','yyyy-mm-dd'),9000);INSERT INTO employeeinfo VALUES(104,'ernst bruce',60,to_date('1991-05-21','yyyy-mm-dd'),6000);INSERT INTO employeeinfo VALUES(105,'austin david',60,to_date('1997-06-25','yyyy-mm-dd'),4800);INSERT INTO employeeinfo VALUES(106,'pataballa valli',60,to_date('1998-02-05','yyyy-mm-dd'),48000);INSERT INTO employeeinfo VALUES(107,'lorentz diana',60,to_date('1999-02-07','yyyy-mm-dd'),42000);INSERT INTO employeeinfo VALUES(108,'greenberg nancy',100,to_date('1994-08-17','yyyy-mm-dd'),12000);INSERT INTO employeeinfo VALUES(109,'faviet daniel',100,to_date('1994-08-16','yyyy-mm-dd'),9000);INSERT INTO employeeinfo VALUES(110,'chen john',100,to_date('1997-09-28','yyyy-mm-dd'),8200);INSERT INTO employeeinfo VALUES(111,'sciarra ismael',100,to_date('1997-09-30','yyyy-mm-dd'),7700);INSERT INTO employeeinfo VALUES(112,'urman jose manuel',100,to_date('1998-03-07','yyyy-mm-dd'),7800);INSERT INTO employeeinfo VALUES(113,'popp luis',100,to_date('1999-12-07','yyyy-mm-dd'),6900);INSERT INTO employeeinfo VALUES(114,'raphaely den',30,to_date('1994-12-07','yyyy-mm-dd'),11000);INSERT INTO employeeinfo VALUES(120,'weiss matthew',50,to_date('1996-07-18','yyyy-mm-dd'),8000);INSERT INTO employeeinfo VALUES(121,'fripp adam',50,to_date('1997-04-10','yyyy-mm-dd'),8200);INSERT INTO employeeinfo VALUES(122,'kaufling payam',50,to_date('1995-05-01','yyyy-mm-dd'),7900);INSERT INTO employeeinfo VALUES(123,'vollman shanta',50,to_date('1997-10-10','yyyy-mm-dd'),6500);INSERT INTO employeeinfo VALUES(124,'mourgos kevin',50,to_date('1999-11-16','yyyy-mm-dd'),4800);INSERT INTO employeeinfo VALUES(145,'russell john',80,to_date('1996-10-01','yyyy-mm-dd'),14000);INSERT INTO employeeinfo VALUES(146,'partners karen',80,to_date('1997-01-05','yyyy-mm-dd'),13500);INSERT INTO employeeinfo VALUES(147,'errazuriz alberto',80,to_date('1997-03-10','yyyy-mm-dd'),12000);INSERT INTO employeeinfo VALUES(148,'cambrault gerald',80,to_date('1999-10-15','yyyy-mm-dd'),11000);INSERT INTO employeeinfo VALUES(149,'zlotkey eleni',80,to_date('2000-01-29','yyyy-mm-dd'),10500);COMMIT;

CREATE TABLE sales(country VARCHAR2(20),                   sale_month DATE,                   sales_number NUMBER(7),                   sales_value  NUMBER(9,2));INSERT INTO sales VALUES('USA',to_date('2008-1-1','yyyy-mm-dd'),1200,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-2-1','yyyy-mm-dd'),1150,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-3-1','yyyy-mm-dd'),1300,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-4-1','yyyy-mm-dd'),1280,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-5-1','yyyy-mm-dd'),1350,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-6-1','yyyy-mm-dd'),1400,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-7-1','yyyy-mm-dd'),1300,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-8-1','yyyy-mm-dd'),1250,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-9-1','yyyy-mm-dd'),1400,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-10-1','yyyy-mm-dd'),1380,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-11-1','yyyy-mm-dd'),1450,500000.00);INSERT INTO sales VALUES('USA',to_date('2008-12-1','yyyy-mm-dd'),1500,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-1-1','yyyy-mm-dd'),1600,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-2-1','yyyy-mm-dd'),1390,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-3-1','yyyy-mm-dd'),1730,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-4-1','yyyy-mm-dd'),1900,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-5-1','yyyy-mm-dd'),1850,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-6-1','yyyy-mm-dd'),3800,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-7-1','yyyy-mm-dd'),1700,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-8-1','yyyy-mm-dd'),1490,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-9-1','yyyy-mm-dd'),1830,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-10-1','yyyy-mm-dd'),2000,500000.00);INSERT INTO sales VALUES('USA',to_date('2009-11-1','yyyy-mm-dd'),1950,500000.00);INSERT INTO sales VALUES('USA',to_date('2000-12-1','yyyy-mm-dd'),1900,500000.00);COMMIT;

--求某部门按入职日期排序的平均工资、总工资、最高工资、最低工资，以及相关排序

SELECT emp_id,emp_name,dept_id,hire_date,salary,AVG(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) avg_salary,SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) sum_salary,MAX(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) max_salary,MIN(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) min_salary,COUNT(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) count_salaryFROM employeeinfoWHERE dept_id IN (10,30,50,60);

这是实验的脚本，简单的窗口函数使用场景

0 0