1、Over函数（分析统计函数）
1.1语法
sum/avg/count() over(partition by ..)
- over()在什么条件之上;
- partition by 按哪个字段划分组；
1.2示例
SELECT E.ENAME,E.JOB,E.SAL,E.DEPTNO,SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) SUM_SAL, --统计某组中的总计值AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG_SAL, --统计某组中的平均值COUNT(E.SAL) OVER(PARTITION BY E.DEPTNO) COUNT_SAL --按某列分组，并统计该组中记录数量FROM EMP E;
2、Pivot函数（行转列函数）
2.1语法
Pivot函数是将多行聚合并转置为列，每列代表不同范围的聚合数据。新语法的概述如下：
SELECT ...
FROM ...
PIVOT [XML]( pivot_clausepivot_for_clausepivot_in_clause )
WHERE ...
- pivot_clause：定义要聚合的列（pivot 是聚合操作）；
- pivot_for_clause：定义要分组和旋转的列；
- pivot_in_clause：为 pivot_for_clause中的列定义过滤器（即限制结果的值范围）。pivot_in_clause
中每个值的聚合将被转置到单独的列中（在适当的情况下）
2.2例子
以Emp表为例，按部门和工作对薪水求和，但将每个部门的总和转移到自己的列中。在我们调整工资之前，我们将检查基础数据，如下所示：
SQL> SELECT job2 , deptno3 , SUM(sal) AS sum_sal4 FROM emp5 GROUP BY6 job7 , deptno8 ORDER BY9 job10 , deptno;JOB DEPTNO SUM_SAL
--------- ---------- ----------
ANALYST 20 6600
CLERK 10 1430
CLERK 20 2090
CLERK 30 1045
MANAGER 10 2695
MANAGER 20 3272.5
MANAGER 30 3135
PRESIDENT 10 5500
SALESMAN 30 61609 rows selected.
对于每个职位，我们把部门进行行转列，让所有部门变成了列，显示工资总额，这样的数据相比较上面的数据更加简洁直观，如下所示：
SQL> WITH pivot_data AS (2 SELECT deptno, job, sal3 FROM emp4 )5 SELECT *6 FROM pivot_data7 PIVOT (8 SUM(sal) --<-- pivot_clause9 FOR deptno --<-- pivot_for_clause10 IN (10,20,30,40) --<-- pivot_in_clause11 );JOB 10 20 30 40
--------- ---------- ---------- ---------- ----------
CLERK 1430 2090 1045
SALESMAN 6160
PRESIDENT 5500
MANAGER 2695 3272.5 3135
ANALYST 66005 rows selected.
3、综合运用
说明：根据列的特定值条件，统计符合条件数量.
3.1报表需求（如下）：
3.2原始数据（如下）：
select csm.short_name 经销商简称,csm.code 经销商编码,asa.product_time 生产日期, --生产日期adp.pickup_date 提货日期, --提货日期aso.orders_type, --订单类型 0：正单，1：改补单aso.order_number 订单号, --订单数apdi.tag_number 包号, --包数apdi.quantity 件数 --件数from aba --批次表inner join asa --班次表on asa.is_phantom = '0'and asa.id = aba.shifts_audit_idinner join apmi --装箱单信息on apmi.batch_audit_id = aba.idand apmi.is_phantom = '0'inner join apdi --装箱单明细on apdi.is_phantom = '0'and apdi.package_main_info_id = apmi.idinner join aso --销售订单on aso.is_phantom = '0'and aso.id = apmi.sales_order_idinner join adp --提货计划on adp.is_phantom = '0'and adp.sale_order_id = aso.idinner join csmon csm.id = aso.dealer_idand csm.is_phantom = '0'where asa.product_time >= to_date('2022-03-01', 'yyyy-mm-dd')AND asa.product_time <= to_date('2022-03-02', 'yyyy-mm-dd')
3.3综合运用（最终效果）：
分析：根据上述报表和原始数据我们可以知道，一个订单包含了很多个包，每一行数据就是一包，我们需要按生产日期统计订单数，包数，件数，但是其中又根据订单类型进一步拆分了正单和改补单，因此我们可以把订单类型也加入到分组列当中，作为行转列的一个数据源列。
select 经销商简称,经销商编码,生产日期,提货日期,包数,nvl(正单_订单数,0),nvl(正单_板件数,0),sum(正单_订单数) over (partition by 生产日期) 正单_订单小计,sum(正单_板件数) over (partition by 生产日期) 正单_板件小计,nvl(改补_订单数,0),nvl(改补_板件数,0),sum(改补_订单数) over (partition by 生产日期) 改补_订单小计,sum(改补_板件数) over (partition by 生产日期) 改补_板件小计,sum(nvl(正单_订单数,0) + nvl(改补_订单数,0)) over (partition by 生产日期) 订单合计,sum(nvl(正单_板件数,0)+ nvl(改补_板件数,0)) over (partition by 生产日期) 板件合计from (select csm.short_name 经销商简称,csm.code 经销商编码,asa.product_time 生产日期, --生产日期adp.pickup_date 提货日期, --提货日期decode(aso.orders_type, 0, 0, 1) orderType, --订单类型0：正单，1：改补单count(distinct aso.order_number) orders, --订单数count(distinct apdi.tag_number) 包数, --包数sum(apdi.quantity) qty --件数from aba --批次表inner join asa --班次表on asa.is_phantom = '0'and asa.id = aba.shifts_audit_idinner join apmi --装箱单信息on apmi.batch_audit_id = aba.idand apmi.is_phantom = '0'inner join apdi --装箱单明细on apdi.is_phantom = '0'and apdi.package_main_info_id = apmi.idinner join aso --销售订单on aso.is_phantom = '0'and aso.id = apmi.sales_order_idinner join adp --提货计划on adp.is_phantom = '0'and adp.sale_order_id = aso.idinner join csm_dealer csmon csm.id = aso.dealer_idand csm.is_phantom = '0'where asa.product_time >= to_date('2022-03-01', 'yyyy-mm-dd')AND asa.product_time <= to_date('2022-03-02', 'yyyy-mm-dd')group by csm.short_name,csm.code,asa.product_time, --生产日期adp.pickup_date, --提货日期aso.orders_type)
pivot(sum(orders) as 订单数, sum(qty) 板件数FOR orderType IN(0 AS 正单, 1 AS 改补))
