hive数据库posexplode函数

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_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...

• pivot_clause：定义要聚合的列（pivot 是聚合操作）；
• pivot_for_clause：定义要分组和旋转的列；
• pivot_in_clause：为 pivot_for_clause中的列定义过滤器（即限制结果的值范围）。pivot_in_clause
  中每个值的聚合将被转置到单独的列中（在适当的情况下）

2.2例子
以Emp表为例，按部门和工作对薪水求和，但将每个部门的总和转移到自己的列中。在我们调整工资之前，我们将检查基础数据，如下所示：

SQL> SELECT job
  2  ,      deptno
  3  ,      SUM(sal) AS sum_sal
  4  FROM   emp
  5  GROUP  BY
  6         job
  7  ,      deptno
  8  ORDER  BY
  9         job
 10  ,      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       6160

9 rows selected.

对于每个职位，我们把部门进行行转列，让所有部门变成了列，显示工资总额，这样的数据相比较上面的数据更加简洁直观，如下所示：

SQL> WITH pivot_data AS (
  2          SELECT deptno, job, sal
  3          FROM   emp
  4          )
  5  SELECT *
  6  FROM   pivot_data
  7  PIVOT (
  8             SUM(sal)        --<-- pivot_clause
  9         FOR deptno          --<-- pivot_for_clause
 10         IN  (10,20,30,40)   --<-- pivot_in_clause
 11        );

JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK           1430       2090       1045
SALESMAN                              6160
PRESIDENT       5500
MANAGER         2695     3272.5       3135
ANALYST                    6600

5 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_id
         inner join apmi --装箱单信息
            on apmi.batch_audit_id = aba.id
           and apmi.is_phantom = '0'
         inner join  apdi --装箱单明细
            on apdi.is_phantom = '0'
           and apdi.package_main_info_id = apmi.id
         inner join aso --销售订单
            on aso.is_phantom = '0'
           and aso.id = apmi.sales_order_id
         inner join  adp --提货计划
            on adp.is_phantom = '0'
           and adp.sale_order_id = aso.id
         inner join csm
            on csm.id = aso.dealer_id
           and 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_id
         inner join apmi --装箱单信息
            on apmi.batch_audit_id = aba.id
           and apmi.is_phantom = '0'
         inner join apdi --装箱单明细
            on apdi.is_phantom = '0'
           and apdi.package_main_info_id = apmi.id
         inner join aso --销售订单
            on aso.is_phantom = '0'
           and aso.id = apmi.sales_order_id
         inner join adp --提货计划
            on adp.is_phantom = '0'
           and adp.sale_order_id = aso.id
         inner join csm_dealer csm
            on csm.id = aso.dealer_id
           and 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 改补))