hiveのウィンドウ関数の詳細

コンセプト:
sqlにはsum()、avg()、max()などの集約関数と呼ばれる関数があり、これらの関数は複数行のデータを規則的に1行に集約することができ、一般的に集約後の行数は集約前の行数より少ないことが知られています.しかし,集計前のデータも集計後のデータも表示したい場合があり,ウィンドウ関数を導入した.
ウィンドウ関数と分析関数
適用シーン:

1.      
2.  GROUP BY
3.TOP N
4.    
5.    

ウィンドウ関数:
関数＃カンスウ＃
戻りタイプ
説明
row_number()
bigint
パーティション内の現在の行番号、1から
rank()
bigint
間隔のある現在のローのランキング.最初の行と同じrow_number同じ
dense_rank()
bigint
現在の行の間隔がありません.この関数はピアグループをカウントします.
percent_rank()
double precision
現在のローの相対順位:(rank-1)/(合計ロー数-1)
cume_dist()
double precision
現在のローの相対順位:(前のローまたは現在のローと同じロー数)/(合計ロー数)
ntile(num_buckets integer)
integer
1からパラメータ値までの整数の範囲で、できるだけ等しいパーティションを分割します.
lag(value any [, offset integer [, default any ]])
タイプとvalue
パーティションの現在の行の前offset行を計算し、valueを返します.このようなローがない場合は、default置換を返します.offsetとdefaultは、現在のロー計算の結果です.無視するとoffsetのデフォルトは1、defaultのデフォルトはnullです.
lead(value* any [, offset integer [, default any ]])
タイプとvalue
パーティションの現在の行の後offset行を計算し、valueを返します.このようなローがない場合は、default置換を返します.offsetとdefaultは、現在のロー計算の結果です.無視するとoffsetのデフォルトは1、defaultのデフォルトはnullです.
first_value(value any)
タイプとvalue
ウィンドウの最初の行の計算value値を返します.
last_value(value any)
タイプとvalue
ウィンドウの最後の行の計算value値を返します.
nth_value(value any, nth integer)
タイプとvalue
ウィンドウのnth行目の計算value値を返します(行は1から数えます).このような行がなければnullを返します.
partition by句

Over            Partition By，Partition By     
        ，     Group By，            ， Over
               ，       ，        。

order by句

     ,       cost       。      order by  。

order by             （       ，     SQL         ，
     SQL          ）。Order By      Row_Number()，Lead()
，LAG()       ，        ，              。
      Order By  ， Count()，Min()               。

サブクエリ文:

window  

           partition by             .             ，      window   。

           :
-      partition by  ,   order by  ,            。
-    order by  ,   window      ,         。

    select            ,            .             .

window  ：
- PRECEDING：  
- FOLLOWING：  
- CURRENT ROW：   
- UNBOUNDED：  ，UNBOUNDED PRECEDING         ， UNBOUNDED FOLLOWING：        

例:
まずテーブルを用意します.

hive (d6_hive)> select * from full
OK
full.user	full.month	full.count
A	2018-01	5
B	2018-01	18
C	2018-02	10
A	2018-04	21
B	2018-03	25
C	2018-04	3
A	2018-02	33
B	2018-02	15
C	2018-03	25
A	2018-05	18
A	2018-03	2
C	2018-01	10
C	2018-05	28

ウィンドウ関数による累積クエリー:

hive (d6_hive)> select 
              >   user,month,count, 
              >    sum(count) over (partition by user order by month rows between unbounded preceding and current row) as pv1, 
              >    sum(count) over (partition by user order by month) as pv2, 
              >    sum(count) over (partition by user) as pv3, 
              >    sum(count) over (partition by user order by month rows between 3 preceding and current row) as pv4, 
              >    sum(count) over (partition by user order by month rows between 3 preceding and 1 following) as pv5, 
              >    sum(count) over (partition by user order by month rows between current row and unbounded following) as pv6 
              > from full

クエリの結果:

user	month	count	pv1	pv2	pv3	pv4	pv5	pv6
A	2018-02		33	38	38	79	38	40	74
A	2018-01		5	5	5	79	5	38	79
A	2018-03		2	40	40	79	40	61	41
A	2018-04		21	61	61	79	61	79	39
A	2018-05		18	79	79	79	74	74	18
B	2018-02		15	33	33	58	33	58	40
B	2018-03		25	58	58	58	58	58	25
B	2018-01		18	18	18	58	18	33	58
C	2018-01		10	10	10	76	10	20	76
C	2018-02		10	20	20	76	20	45	66
C	2018-03		25	45	45	76	45	48	56
C	2018-04		3	48	48	76	48	76	31
C	2018-05		28	76	76	76	66	66	28

詳細:

pv1:            pv  ， ：A 2  pv1=1  pv+2  pv, 3 =1 +2 +3 
pv2:  pv1
pv3:    (count)   pv  
pv4:       +  3 ， ：2 =1 +2 ， 3 =1 +2 +3 ， 4 =1 +2 +3 +4  
pv5:       +  3 +  1 ， ：4 =1 +2 +3 +4 +5 
pv6:       +     ， ：3 =3 +4 +5 

まずpartition byパーティションを使用し、order byを使用して指定したフィールドで累積するフィールドを選択します.
order byの後に続くフィールドは、このフィールドで累積することを示します.