聚合透视与窗口
===================


聚合
-----------

.. code-block:: lua

    agg [方式] {统计表达式} by {分组字段} [on viewName]


``[on viewName]`` 在哪一个视图上进行聚合统计。

聚合 ``[方式]`` 有：

=============== ==============================
方式               作用
=============== ==============================
默认(空)           与标准SQL分组统计一致
sets               对分组集中指定的组表达式的每个子集执行分组             
rollup             在指定表达式的每个层次级别创建分组集
cube               为指定表达式集的每个可能组合创建分组集         
=============== ==============================


假定有以下表数据：area，grade，honor，value

.. code-block:: javascript

    ("战争学院区", "王者", "王者1连胜", 25),
    ("战争学院区", "王者", "王者1连胜", 25),
    ("战争学院区", "王者", "王者3连胜", 70),
    ("战争学院区", "王者", "王者12连胜", 300),
    ("战争学院区", "大师", "大师3连胜", 60),
    ("战争学院区", "大师", "大师3连胜", 60),
    ("战争学院区", "大师", "大师6连胜", 120),
    ("战争学院区", "黄金", "黄金1连胜", 15),
    ("战争学院区", "黄金", "黄金1连胜", 15),
    ("战争学院区", "黄金", "黄金3连胜", 45),
    ("战争学院区", "黄金", "黄金12连胜", 180),
    ("裁决之地区", "王者", "王者1连胜", 25),
    ("裁决之地区", "王者", "王者1连胜", 25),
    ("裁决之地区", "大师", "大师3连胜", 60),
    ("裁决之地区", "黄金", "黄金3连胜", 45),
    ("诺克萨斯区", "王者", "王者1连胜", 25),
    ("诺克萨斯区", "王者", "王者1连胜", 25),
    ("诺克萨斯区", "大师", "大师3连胜", 60),
    ("诺克萨斯区", "黄金", "黄金3连胜", 45)

维度分析如下：

1. SQL中的分组统计

主要用来对查询的结果进行分组，相同组合的分组条件在结果集中只显示一行记录。通过添加聚合函数（主要有COUNT()、SUM、MAX()、MIN()等）可以使数据聚合。

.. code-block:: javascript

    agg { area,grade,honor,sum(value) as total_value } 
        by { area,grade,honor }

结果

.. code-block:: html

    +-----+-----+------+-----------+
    | area|grade| honor|total_value|
    +-----+-----+------+-----------+
    |诺克萨斯区|   黄金| 黄金3连胜|         45|
    |战争学院区|   王者|王者12连胜|        300|
    |裁决之地区|   王者| 王者1连胜|         50|
    |战争学院区|   黄金|黄金12连胜|        180|
    |诺克萨斯区|   大师| 大师3连胜|         60|
    |战争学院区|   大师| 大师3连胜|        120|
    |诺克萨斯区|   王者| 王者1连胜|         50|
    |战争学院区|   王者| 王者3连胜|         70|
    |裁决之地区|   黄金| 黄金3连胜|         45|
    |战争学院区|   黄金| 黄金3连胜|         45|
    |战争学院区|   黄金| 黄金1连胜|         30|
    |战争学院区|   大师| 大师6连胜|        120|
    |裁决之地区|   大师| 大师3连胜|         60|
    |战争学院区|   王者| 王者1连胜|         50|
    +-----+-----+------+-----------+

2. Group Sets

能够定义多个数据分组。这样做使聚合更容易, 并且因此使得多维数据分析更容易。

``sets 'A,B'`` 就等价于 group by A union group by B

``sets 'A,C;A,B'`` 就等价于 group by A,C union group by A,B

.. code-block:: javascript

    agg sets 'area,grade,honor' { area,grade,honor,sum(value) as total_value } 
        by { area,grade,honor }

结果

.. code-block:: html

    +-----+-----+------+-----------+
    | area|grade| honor|total_value|
    +-----+-----+------+-----------+
    |战争学院区| null|  null|        915|
    | null| null| 黄金3连胜|        135|
    | null| null| 王者1连胜|        150|
    |诺克萨斯区| null|  null|        155|
    | null| null|王者12连胜|        300|
    | null|   黄金|  null|        345|
    |裁决之地区| null|  null|        155|
    | null| null|黄金12连胜|        180|
    | null|   大师|  null|        360|
    | null| null| 大师6连胜|        120|
    | null| null| 黄金1连胜|         30|
    | null|   王者|  null|        520|
    | null| null| 大师3连胜|        240|
    | null| null| 王者3连胜|         70|
    +-----+-----+------+-----------+

3. Rollup

根据维度在数据结果集中进行的聚合操作。首先会对(A、B、C)进行group by，然后对(A、B)进行group by，然后是(A)进行group by，最后对各个分组结果进行union操作。

.. code-block:: javascript

    agg rollup { area,grade,honor,sum(value) as total_value } 
        by { area,grade,honor }

结果

.. code-block:: html

    | area|grade| honor|total_value|
    +-----+-----+------+-----------+
    |战争学院区|   王者|王者12连胜|        300|
    |战争学院区|   大师|  null|        240|
    |战争学院区| null|  null|        915|
    |诺克萨斯区| null|  null|        155|
    |裁决之地区|   黄金| 黄金3连胜|         45|
    |裁决之地区|   大师|  null|         60|
    |诺克萨斯区|   黄金| 黄金3连胜|         45|
    |诺克萨斯区|   王者| 王者1连胜|         50|
    |裁决之地区| null|  null|        155|
    |战争学院区|   黄金|黄金12连胜|        180|
    |诺克萨斯区|   黄金|  null|         45|
    |裁决之地区|   王者|  null|         50|
    |战争学院区|   黄金| 黄金3连胜|         45|
    |裁决之地区|   黄金|  null|         45|
    |战争学院区|   黄金|  null|        255|
    | null| null|  null|       1225|
    |战争学院区|   王者| 王者3连胜|         70|
    |战争学院区|   大师| 大师6连胜|        120|
    |战争学院区|   王者| 王者1连胜|         50|
    |战争学院区|   大师| 大师3连胜|        120|
    +-----+-----+------+-----------+

4. Cube

首先会对(A、B、C)进行group by，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，( C)，最后对全表进行group by操作。

.. tip:: 

    使用Cube的好处就是可以把海量 (TB 到 PB) 的数据预计算导入到低延迟的分布式数据库中，从而可以实现亚秒级的查询响应。

.. code-block:: javascript

    agg cube { area,grade,honor,sum(value) as total_value } 
        by { area,grade,honor }

.. code-block:: html

    +-----+-----+------+-----------+
    | area|grade| honor|total_value|
    +-----+-----+------+-----------+
    |战争学院区|   王者|王者12连胜|        300|
    |战争学院区|   大师|  null|        240|
    |战争学院区| null|  null|        915|
    |战争学院区| null| 王者1连胜|         50|
    | null| null| 黄金3连胜|        135|
    |战争学院区| null|王者12连胜|        300|
    | null| null| 王者1连胜|        150|
    | null|   王者|王者12连胜|        300|
    |诺克萨斯区| null|  null|        155|
    | null| null|王者12连胜|        300|
    |裁决之地区|   黄金| 黄金3连胜|         45|
    | null|   黄金|  null|        345|
    |裁决之地区|   大师|  null|         60|
    |诺克萨斯区|   黄金| 黄金3连胜|         45|
    |诺克萨斯区|   王者| 王者1连胜|         50|
    |裁决之地区| null|  null|        155|
    |战争学院区| null| 大师6连胜|        120|
    |战争学院区| null| 大师3连胜|        120|
    |战争学院区|   黄金|黄金12连胜|        180|
    |诺克萨斯区|   黄金|  null|         45|
    +-----+-----+------+-----------+

透视
-------------

一种交互式的表，可以自由选择多个字段的不同组合，用于快速汇总、分析大量数据中字段与字段之间的关联关系。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。

.. code-block:: sql

    pivot 列字段1,列字段2,..  by 行字段1,行字段2,.. agg { 统计表达式 } [on viewName]

``[on viewName]`` 表示要在哪一个视图上生成透视视图。

这里的透视与Excel里的数据透视表生成的数据视图是一样的，下面是Excel的透视表示例演示有助于形象理解这里的数据透视概念。

.. image:: ./_static/pivot.gif

假定有如下数据：

.. code-block:: html

    +-------+---+----+
    | 年月| 项目|  收入|
    +-------+---+----+
    |2018-01|项目1| 100|
    |2018-01|项目2| 200|
    |2018-01|项目3| 300|
    |2018-02|项目1|1000|
    |2018-02|项目2|2000|
    |2018-03|项目x| 999|
    +-------+---+----+

.. code-block:: sql

    pivot `项目` by `年月` agg { sum(`收入`) };
    
    -- 输出结果

    +-------+----+----+---+---+
    | 年月| 项目1| 项目2|项目3|项目x|
    +-------+----+----+---+---+
    |2018-03|   0|   0|  0|999|
    |2018-02|1000|2000|  0|  0|
    |2018-01| 100| 200|300|  0|
    +-------+----+----+---+---+

窗口
-------------

对一组行（称为窗口）进行操作，并基于该组行计算每一行的返回值。窗口函数对于处理诸如计算移动平均值、计算累积统计数据或访问给定当前行相对位置的行的值等任务非常有用。

窗口语法
^^^^^^^^^

.. code-block:: sql

    window { 列1,列2..,窗口函数表达式1,窗口函数表达式2,.. } [on viewName]

`请参考完整的窗口函数列表 <http://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#window-functions>`_ 

假定有以下表数据：

.. code-block:: html

    +-----+-----------+------+-----+
    | name|       dept|salary|  age|
    +-----+-----------+------+-----+
    |Chloe|Engineering| 23000|   25|
    | Fred|Engineering| 21000|   28|
    | Paul|Engineering| 29000|   23|
    |Helen|  Marketing| 29000|   40|
    |  Tom|Engineering| 23000|   33|
    | Jane|  Marketing| 29000|   28|
    | Jeff|  Marketing| 35000|   38|
    | Evan|      Sales| 32000|   38|
    | Lisa|      Sales| 10000|   35|
    | Alex|      Sales| 30000|   33|
    +-----+-----------+------+-----+

.. code-block:: html

    window {
        name, salary,
        LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag,
        LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
    }

    运行结果：

    +-----+-----------+------+-----+-----+
    | name|       dept|salary|  lag| lead|
    +-----+-----------+------+-----+-----+
    | Lisa|      Sales| 10000|NULL |30000|
    | Alex|      Sales| 30000|10000|32000|
    | Evan|      Sales| 32000|30000|    0|
    | Fred|Engineering| 21000| NULL|23000|
    |Chloe|Engineering| 23000|21000|23000|
    |  Tom|Engineering| 23000|23000|29000|
    | Paul|Engineering| 29000|23000|    0|
    |Helen|  Marketing| 29000| NULL|29000|
    | Jane|  Marketing| 29000|29000|35000|
    | Jeff|  Marketing| 35000|29000|    0|
    +-----+-----------+------+-----+-----+

    window {
        name, dept, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN
        UNBOUNDED PRECEDING AND CURRENT ROW) AS dense_rank
    }

    运行结果：

    +-----+-----------+------+----------+
    | name|       dept|salary|dense_rank|
    +-----+-----------+------+----------+
    | Lisa|      Sales| 10000|         1|
    | Alex|      Sales| 30000|         2|
    | Evan|      Sales| 32000|         3|
    | Fred|Engineering| 21000|         1|
    |  Tom|Engineering| 23000|         2|
    |Chloe|Engineering| 23000|         2|
    | Paul|Engineering| 29000|         3|
    |Helen|  Marketing| 29000|         1|
    | Jane|  Marketing| 29000|         1|
    | Jeff|  Marketing| 35000|         2|
    +-----+-----------+------+----------+