Hive解析関数とウィンドウ関数

15767 ワード

Hive解析関数とウィンドウ関数
Hive 0.11以降でサポートされるように、複数の入力された行をスキャンして各行の結果を計算します.通常OVER、PARTION BY、ORDER BY、WINDOWINGと併用されます.従来のグループ化結果とは異なり、従来の結果は各グループに1つの結果しかありません.解析関数の結果は複数回現れ、各レコードに出力が接続されます.
構文の形式は次のとおりです.

Function(arg1,....argn) OVER([PARTITION BY<...>] [ORDER BY<...>] [window_clause])

ウィンドウ関数
関数名
説明
FIRST_VALUE
グループ内のソートを取り出し、現在の行の最初の値に切り込みます.
LAST_VALUE
グループ内のソートを取り出した後、現在の行まで、最後の値
LEAD(col, n, DEFAULT)
統計ウィンドウの下のn行目の値に使用します.1番目のパラメータはカラム名、2番目のパラメータは下のn行目(オプション、デフォルトは1)、3番目のパラメータはデフォルト値(下のn行目NULLの場合はデフォルト値)
LAG(col,n,DEFAULT)
リードとは逆に、統計ウィンドウ内の下のn番目の値に使用されます.1番目のパラメータはカラム名、2番目のパラメータは上のn行目(オプション、デフォルトは1)
MOVER従文

標準的な集約関数COUNT,SUM,MIN,MAX,AVG

を使用

PARTION BY文を使用し、1つまたは複数の元のデータ型の列

を使用する

PARTION BYとORDER BY文を使用する、1つまたは複数のデータ型のパーティションまたはビートシーケンス

を使用する.

ウィンドウ仕様を使用して、ウィンドウ仕様はフォーマットをサポートします:

(ROW | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROW | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROW | RANGE) BETWEEN [num] PRECEDING AND (UNBOUNDED | [num]) FOLLOWING

ORDER BYの後ろにウィンドウ従文条件がない場合、ウィンドウ仕様のデフォルトは

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

ORDER BYとウィンドウ従文が欠落している場合、ウィンドウ仕様のデフォルトは次のとおりです.

ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ぶんせきかんすう
関数＃カンスウ＃
説明
ROW_NUMBER()
1から順に、パケット内に記録されたシーケンスが生成され、例えば、pv降順に並び、パケット内の毎日のpv順位が生成され、ROW_NUMBER()のアプリケーションシーンは非常に多く,例えばパケット内で1番目にソートされたレコードを取得し,セッション内の1番目のreferを取得するなどである.
RANK()
グループ内のデータ項目の順位を生成し、順位が等しいと順位に空席が残ります.
DENSE_RANK()
グループ内のデータ項目の順位を生成し、順位が等しいと順位に空席が残らない
CUME_DIST()
現在の値以下の行数をグループ内の合計行数で除算します.たとえば、現在の給与に等しい人数以下の合計人数の割合を統計します.
PERCENT_RANK()
パケット内の現在のローのRANK値-1/パケット内の合計ロー数-1
NTILE(n)
グループ化されたデータを順番にnスライスに分割し、現在のスライス値を返します.スライスが不均一な場合、デフォルトでは最初のスライスの分布が増加します.NTILEでは、NTILE(2)OVER(PARTITION BY Cookieid ORDER BY createtime ROWS BETWEEN 3 PERCEDING AND CURRENT ROW)などのROWS BETWEENはサポートされていません.
Hive2.1.0以降のバージョンではdistinctがサポートされています
集約関数(sum,count,avg)ではdistinctがサポートされていますが、order byまたはウィンドウ制限ではサポートされていません.conut(distinct a) over(partition by c)
Hive2.1.0以降のサポートOVER従文での集約関数のサポート

select rank() over(order by sum(b))

Hive2.2.0ではORDER BYとウィンドウ制限の使用時にdistinctをサポートcount(distinct a) over (partition by c order by d rows between 1 preceding and 1 following)
ウィンドウ関数と解析関数をインスタンスで深く理解する
COUNT、SUM、MIN、MAX、AVGケーススタディ

##      
create table orders(
    user_id string,
    device_id string,
    user_type string,
    price float,
    sales int);

##     orders.txt
zhangsa test1   new     67.1    2
lisi    test2   old     43.32   1
wanger  test3   new     88.88   3
liliu   test4   new     66.0    1
tom     test5   new     54.32   1
tomas   test6   old     77.77   2
tomson  test7   old     88.44   3
tom1    test8   new     56.55   6
tom2    test9   new     88.88   5
tom3    test10  new     66.66   5

##       
select
    user_id,
    user_type,
    sales,
    --              
    sum(sales) over(partition by user_type order by sales asc) as sales_1,
    --             sales_1    
    sum(sales) over(partition by user_type order by sales asc range between unbounded preceding and current row) as sales_2,
    --        ，   sale_1    
    sum(sales) over(partition by user_type order by sales asc rows between unbounded preceding and current row) as sales_3,
    --        3 
    sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and current row) as sales_4,
    --        3 
    sum(sales) over(partition by user_type order by sales asc range between 3 preceding and current row) as sales_5,
    --    +  3 +  1 
    sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and 1 following) as sales_6,
    --
    sum(sales) over(partition by user_type order by sales asc range between 3 preceding and 1 following) as sales_7,
    --    +     
    sum(sales) over(partition by user_type order by sales asc rows between current row and unbounded following) as sales_8,
    --
    sum(sales) over(partition by user_type order by sales asc range between current row and unbounded following) as sales_9,
    --       
    sum(sales) over(partition by user_type) as sales_10
from
    orders
order by
    user_type,
    sales,
    user_id;

##        ：

| user_id  | user_type  | sales  | sales_1  | sales_2  | sales_3  | sales_4  | sales_5  | sales_6  | sales_7  | sales_8  | sales_9  | sales_10  |
|----------|------------|--------|----------|----------|----------|----------|----------|----------|----------|----------|----------|-----------|
| liliu    | new        | 1      | 2        | 2        | 2        | 2        | 2        | 4        | 4        | 22       | 23       | 23        |
| tom      | new        | 1      | 2        | 2        | 1        | 1        | 2        | 2        | 4        | 23       | 23       | 23        |
| zhangsa  | new        | 2      | 4        | 4        | 4        | 4        | 4        | 7        | 7        | 21       | 21       | 23        |
| wanger   | new        | 3      | 7        | 7        | 7        | 7        | 7        | 12       | 7        | 19       | 19       | 23        |
| tom2     | new        | 5      | 17       | 17       | 17       | 15       | 15       | 21       | 21       | 11       | 16       | 23        |
| tom3     | new        | 5      | 17       | 17       | 12       | 11       | 15       | 16       | 21       | 16       | 16       | 23        |
| tom1     | new        | 6      | 23       | 23       | 23       | 19       | 19       | 19       | 19       | 6        | 6        | 23        |
| lisi     | old        | 1      | 1        | 1        | 1        | 1        | 1        | 3        | 3        | 6        | 6        | 6         |
| tomas    | old        | 2      | 3        | 3        | 3        | 3        | 3        | 6        | 6        | 5        | 5        | 6         |
| tomson   | old        | 3      | 6        | 6        | 6        | 6        | 6        | 6        | 6        | 3        | 3        | 6         |

に注意

の結果はORDER BYに関連し、デフォルトは昇順

である.

ROWS BETWEENを指定しない場合、デフォルトは開始から現在の行までです.

ORDER BYを指定しない場合、パケット内のすべての値が加算される.

PRECEDING:前

FOLLOWING:以降

CURRENT ROW:現在の行

UNBOUNDED:境界なし(始点または終点)

UNBOUNDED PRECEDING:前からの始点を示す

UNBOUNDED FOLLOWING:後の終点

まで表示

他のCOUNT、AVG、MIN、MAXはSUMと同じ使い方です.

FIRST_VALUEとLAST_VALUEケーススタディ

select
    user_id,
    user_type,
    sales,
    ROW_NUMBER() OVER(PARTITION BY user_type ORDER BY sales) AS row_num,
    first_value(user_id) over (partition by user_type order by sales desc) as max_sales_user,
    first_value(user_id) over (partition by user_type order by sales asc) as min_sales_user,
    last_value(user_id) over (partition by user_type order by sales desc) as curr_last_min_user,
    last_value(user_id) over (partition by user_type order by sales asc) as curr_last_max_user
from
    orders
order by
    user_type,
    sales;

##        :

| user_id | user_type | sales | row_num | max_sales_user | min_sales_user | curr_last_min_user | curr_last_max_user |
| ------- | --------- | ----- | ------- | -------------- | -------------- | ------------------ | ------------------ |
| tom     | new       | 1     | 1       | tom1           | tom            | tom                | liliu              |
| liliu   | new       | 1     | 2       | tom1           | tom            | tom                | liliu              |
| zhangsa | new       | 2     | 3       | tom1           | tom            | zhangsa            | zhangsa            |
| wanger  | new       | 3     | 4       | tom1           | tom            | wanger             | wanger             |
| tom3    | new       | 5     | 5       | tom1           | tom            | tom3               | tom2               |
| tom2    | new       | 5     | 6       | tom1           | tom            | tom3               | tom2               |
| tom1    | new       | 6     | 7       | tom1           | tom            | tom1               | tom1               |
| lisi    | old       | 1     | 1       | tomson         | lisi           | lisi               | lisi               |
| tomas   | old       | 2     | 2       | tomson         | lisi           | tomas              | tomas              |
| tomson  | old       | 3     | 3       | tomson         | lisi           | tomson             | tomson             |

LEADとLAG

select
    user_id,
    device_id,
    sales,
    ROW_NUMBER() OVER(ORDER BY sales) AS row_num,
    lead(device_id) over (order by sales) as default_after_one_line,
    lag(device_id) over (order by sales) as default_before_one_line,
    lead(device_id,2) over (order by sales) as after_two_line,
    lag(device_id,2,'abc') over (order by sales) as before_two_line
from
    orders
order by
    sales;

        

| user_id  | device_id  | sales  | row_num  | default_after_one_line  | default_before_one_line  | after_two_line  | before_two_line  |
|----------|------------|--------|----------|-------------------------|--------------------------|-----------------|------------------|
| lisi     | test2      | 1      | 3        | test6                   | test4                    | test1           | test5            |
| liliu    | test4      | 1      | 2        | test2                   | test5                    | test6           | abc              |
| tom      | test5      | 1      | 1        | test4                   | NULL                     | test2           | abc              |
| zhangsa  | test1      | 2      | 5        | test7                   | test6                    | test3           | test2            |
| tomas    | test6      | 2      | 4        | test1                   | test2                    | test7           | test4            |
| wanger   | test3      | 3      | 7        | test10                  | test7                    | test9           | test1            |
| tomson   | test7      | 3      | 6        | test3                   | test1                    | test10          | test6            |
| tom2     | test9      | 5      | 9        | test8                   | test10                   | NULL            | test3            |
| tom3     | test10     | 5      | 8        | test9                   | test3                    | test8           | test7            |
| tom1     | test8      | 6      | 10       | NULL                    | test9                    | NULL            | test10           |

RANK、ROW_NUMBER、DENSE_RANK

select
user_id,user_type,sales,
RANK() over (partition by user_type order by sales desc) as r,
ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
DENSE_RANK() over (partition by user_type order by sales desc) as dr
from
orders;

##        

| user_id | user_type | sales |  r  | rn  | dr  |
| ------- | --------- | ----- | --- | --- | --- |
| tom1    | new       | 6     | 1   | 1   | 1   |
| tom3    | new       | 5     | 2   | 2   | 2   |
| tom2    | new       | 5     | 2   | 3   | 2   |
| wanger  | new       | 3     | 4   | 4   | 3   |
| zhangsa | new       | 2     | 5   | 5   | 4   |
| tom     | new       | 1     | 6   | 6   | 5   |
| liliu   | new       | 1     | 6   | 7   | 5   |
| tomson  | old       | 3     | 1   | 1   | 1   |
| tomas   | old       | 2     | 2   | 2   | 2   |
| lisi    | old       | 1     | 3   | 3   | 3   |

NTILE

select
    user_type,sales,
    --        2 
    NTILE(2) OVER(PARTITION BY user_type ORDER BY sales) AS nt2,
    --        3 
    NTILE(3) OVER(PARTITION BY user_type ORDER BY sales) AS nt3,
    --        4 
    NTILE(4) OVER(PARTITION BY user_type ORDER BY sales) AS nt4,
    --       4 
    NTILE(4) OVER(ORDER BY sales) AS all_nt4
from
    orders
order by
    user_type,
    sales;

##        
| user_type | sales | nt2 | nt3 | nt4 | all_nt4 |
| --------- | ----- | --- | --- | --- | ------- |
| new       | 1     | 1   | 1   | 1   | 1       |
| new       | 1     | 1   | 1   | 1   | 1       |
| new       | 2     | 1   | 1   | 2   | 2       |
| new       | 3     | 1   | 2   | 2   | 3       |
| new       | 5     | 2   | 2   | 3   | 4       |
| new       | 5     | 2   | 3   | 3   | 3       |
| new       | 6     | 2   | 3   | 4   | 4       |
| old       | 1     | 1   | 1   | 1   | 1       |
| old       | 2     | 1   | 2   | 2   | 2       |
| old       | 3     | 2   | 3   | 3   | 2       |

saleの前の20%のユーザーIDを取ることを求めます

select
    user_id
from
(
    select
        user_id,
        NTILE(5) OVER(ORDER BY sales desc) AS nt
    from
        orders
)A
where nt=1;
##     
+----------+
| user_id  |
+----------+
| tom1     |
| tom3     |
+----------+

CUME_DIST、PERCENT_RANK

select
    user_id,user_type,sales,
    --  partition,      1 
    CUME_DIST() OVER(ORDER BY sales) AS cd1,
    --  user_type    
    CUME_DIST() OVER(PARTITION BY user_type ORDER BY sales) AS cd2
from
    orders;

##       
+----------+------------+--------+------+----------------------+--+
| user_id  | user_type  | sales  | cd1  |         cd2          |
+----------+------------+--------+------+----------------------+--+
| liliu    | new        | 1      | 0.3  | 0.2857142857142857   |
| tom      | new        | 1      | 0.3  | 0.2857142857142857   |
| zhangsa  | new        | 2      | 0.5  | 0.42857142857142855  |
| wanger   | new        | 3      | 0.7  | 0.5714285714285714   |
| tom2     | new        | 5      | 0.9  | 0.8571428571428571   |
| tom3     | new        | 5      | 0.9  | 0.8571428571428571   |
| tom1     | new        | 6      | 1.0  | 1.0                  |
| lisi     | old        | 1      | 0.3  | 0.3333333333333333   |
| tomas    | old        | 2      | 0.5  | 0.6666666666666666   |
| tomson   | old        | 3      | 0.7  | 1.0                  |
+----------+------------+--------+------+----------------------+--+

select
    user_type,sales,
    --      
    SUM(1) OVER(PARTITION BY user_type) AS s,
    --RANK 
    RANK() OVER(ORDER BY sales) AS r,
    PERCENT_RANK() OVER(ORDER BY sales) AS pr,
    --   
    PERCENT_RANK() OVER(PARTITION BY user_type ORDER BY sales) AS prg
from
    orders;

##       
+------------+--------+----+-----+---------------------+---------------------+--+
| user_type  | sales  | s  |  r  |         pr          |         prg         |
+------------+--------+----+-----+---------------------+---------------------+--+
| new        | 1      | 7  | 1   | 0.0                 | 0.0                 |
| new        | 1      | 7  | 1   | 0.0                 | 0.0                 |
| new        | 2      | 7  | 4   | 0.3333333333333333  | 0.3333333333333333  |
| new        | 3      | 7  | 6   | 0.5555555555555556  | 0.5                 |
| new        | 5      | 7  | 8   | 0.7777777777777778  | 0.6666666666666666  |
| new        | 5      | 7  | 8   | 0.7777777777777778  | 0.6666666666666666  |
| new        | 6      | 7  | 10  | 1.0                 | 1.0                 |
| old        | 1      | 3  | 1   | 0.0                 | 0.0                 |
| old        | 2      | 3  | 4   | 0.3333333333333333  | 0.5                 |
| old        | 3      | 3  | 6   | 0.5555555555555556  | 1.0                 |
+------------+--------+----+-----+---------------------+---------------------+--+

参考博文
http://blog.csdn.net/scgaliguodong123_/article/details/60135385

構成を変更してphpファイルのアップロードサイズ制限の問題を解決します(apache+php)

git-リモートブランチの作成