# 分析 (窗口) 函数

``function(args) OVER(partition_by_clause order_by_clause [window_clause])    partition_by_clause ::= PARTITION BY expr [, expr ...]    order_by_clause ::= ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]``

## PARTITION BY 从句​

Partition By 从句和 Group By 类似。它把输入行按照指定的一列或多列分组，相同值的行会被分到一组。

## ORDER BY 从句​

Order By 从句和外层的 Order By 基本一致。它定义了输入行的排列顺序，如果指定了 Partition By，则 Order By 定义了每个 Partition 分组内的顺序。与外层 Order By 的唯一不同点是，OVER 从句中的 Order By n（n 是正整数）相当于不做任何操作，而外层的 Order By n 表示按照第 n 列排序。

``SELECT   row_number() OVER (ORDER BY date_and_time) AS id,   c1, c2, c3, c4   FROM events;``

## Window 从句​

Window 从句用来为分析函数指定一个运算范围，以当前行为准，前后若干行作为分析函数运算的对象。Window 从句支持的方法有：AVG(), COUNT(), FIRST_VALUE(), LAST_VALUE() 和 SUM()。对于 MAX() 和 MIN(), window 从句可以指定开始范围 UNBOUNDED PRECEDING

``ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]``

## 举例​

``create table stock_ticker (stock_symbol string, closing_price decimal(8,2), closing_date timestamp);    ...load some data...    select * from stock_ticker order by stock_symbol, closing_date | stock_symbol | closing_price | closing_date        | |--------------|---------------|---------------------| | JDR          | 12.86         | 2014-10-02 00:00:00 | | JDR          | 12.89         | 2014-10-03 00:00:00 | | JDR          | 12.94         | 2014-10-04 00:00:00 | | JDR          | 12.55         | 2014-10-05 00:00:00 | | JDR          | 14.03         | 2014-10-06 00:00:00 | | JDR          | 14.75         | 2014-10-07 00:00:00 | | JDR          | 13.98         | 2014-10-08 00:00:00 |``

``select stock_symbol, closing_date, closing_price,    avg(closing_price) over (partition by stock_symbol order by closing_date    rows between 1 preceding and 1 following) as moving_average    from stock_ticker; | stock_symbol | closing_date        | closing_price | moving_average | |--------------|---------------------|---------------|----------------| | JDR          | 2014-10-02 00:00:00 | 12.86         | 12.87          | | JDR          | 2014-10-03 00:00:00 | 12.89         | 12.89          | | JDR          | 2014-10-04 00:00:00 | 12.94         | 12.79          | | JDR          | 2014-10-05 00:00:00 | 12.55         | 13.17          | | JDR          | 2014-10-06 00:00:00 | 14.03         | 13.77          | | JDR          | 2014-10-07 00:00:00 | 14.75         | 14.25          | | JDR          | 2014-10-08 00:00:00 | 13.98         | 14.36          |``