[Oracle]分析函数详述(五)
_gsc
6 from lw_sales where goods_type='G01' group by dept_id, to_char(sale_date,'YYYY-MM'))
7 select a.sale_month,
8 min(case when goods_sale_cnt=max_gsc then dept_id end) max_dept_id,
9 min(case when goods_sale_cnt=min_gsc then dept_id end) min_dept_id
10 from t a where (goods_sale_cnt=min_gsc or goods_sale_cnt=max_gsc ) group by a.sale_month;
SALE_MO MAX_DE MIN_DE
------- ------ ------
2013-03 S01 S00
2013-04 S01 S00
例2：查出货物G03在销售当天及前十二天的最大销售额（按部门和整个公司分别求出）
[sql]
SQL> select dept_id， sale_date，sale_cnt，
2 max(sale_cnt)over(partition by dept_id order by sale_date range interval '12' day preceding) max_dept_sale_cnt,
3 max(sale_cnt)over(order by sale_date range numtodsinterval(12, 'DAY') preceding) max_sale_cnt
4 from lw_sales where goods_type='G03';
DEPT_I SALE_DATE SALE_CNT MAX_DEPT_SALE_CNT MAX_SALE_CNT
------ -------------- ---------- ----------------- ------------
S00 06-3月 -13 800 800 800
S01 17-3月 -13 600 600 800
S00 21-3月 -13 200 200 600
S01 03-4月 -13 200 200 200
S02 05-4月 -13 800 800 800
S02 22-4月 -13 300 300 300
已选择6行。
相关统计分析函数--FIRST_VALUE和LAST_VALUE
例：查出所有雇员的雇员职位、编号、姓名、薪水以及同职位上薪水最高和最低的雇员的雇员编号（薪水相同的，按雇员编号倒序排列）
[sql]
SQL> select job, empno, ename, sal,
2 last_value(empno)over(partition by job order by sal, empno desc rows between unbounded preceding and unbounded following) lv,
3 first_value(empno)over(partition by job order by sal, empno desc rows between unbounded preceding and unbounded following) fv
4 from emp;
JOB EMPNO ENAME SAL LV FV
--------- ---------- ---------- ---------- ---------- ----------
ANALYST 7902 FORD 7788 7902
ANALYST 7788 SCOTT 7788 7902
CLERK 7900 JAMES 950 7369 7900
CLERK 7934 MILLER 7369 7900
CLERK 7876 ADAMS 7369 7900
CLERK 7369 SMITH 7369 7900
MANAGER 7698 BLAKE 2850 7566 7698
MANAGER 7782 CLARK 7566 7698
MANAGER 7566 JONES 7566 7698
PRESIDENT 7839 KING 7839 7839
SALESMAN 7654 MARTIN 1250 7499 7654
SALESMAN 7521 WARD 1250 7499 7654
SALESMAN 7844 TURNER 1500 7499 7654
SALESMAN 7499 ALLEN 1600 7499 7654
已选择14行。
统计分析函数要点
可以有partition by、order by和range/rows子句（此即windowing子句）
以上子句都不是必须出现的，但若出现windowing子句则其前必须出现order by子句
没有order by就是分组统计，有order by就是分组累计
FIRST/LAST这两个分析函数后面只能跟partition by子句
FIRST_VALUE和LAST_VALUE用于获取一组有序的数据中的第一个和最后一个值，和MAX/MIN以及FIRST/LAST函数很像，但要注意区别
COUNT也可以作为分析函数，这和SUM/MAX等差不多，另外一个函数叫RATIO_TO_REPORT，在做报表或饼状图的时候很有用！