hiveウィンドウ関数の適用シーンと実戦の詳細

23143 ワード

hiveのウィンドウ関数は、非常に強力で、使用も便利で、私たちのクエリーにかなりの遍歴を提供することができます.次に、具体的なニーズと例を結びつけて、ウィンドウ関数の使い方を説明します.

1.データ準備

まずhiveデータベースにテーブルを作成します.テーブルの構造は次のとおりです.

hive (tmp)> desc phone_test;
OK
calling_num             string
called_num              string

テストファイルの準備:

ファイルputをhdfsのhiveテーブルに対応する場所に移動します.

hadoop fs -put phone /data/hive/warehouse/tmp.db/phone_test

これで、データの準備が完了しました.

2.row_num()メソッド、最も役に立つウィンドウ関数は、一つもないかもしれません。

表phone_testの第1列は主呼電話、第2列は被呼電話であり、同じ主呼電話について、通話回数の多少(すなわち被呼電話)でソートする必要があることが多い.この時row_num()メソッドが役に立ちます.以下を参照してください.

select a.calling_num,called_num,count,
row_number() OVER (distribute BY calling_num sort BY count DESC) rn
from
(select calling_num,called_num,count(*) as count
from phone_test group by calling_num,called_num)a

130 131 6   1
130 133 2   2
130 132 2   3
130 134 1   4
132 130 5   1
132 133 3   2
132 131 3   3
134 135 4   1
134 136 3   2
138 137 3   1
138 136 2   2
135 130 4   1
135 132 2   2

最後の列は、同じ呼び出し電話の中で、呼び出し電話と呼び出し電話の通話回数のソートです.どうですか、便利でしょう.最も多くの通話を取るには、その上で少し変更します.

select b.calling_num,called_num,count
from
(select a.calling_num,called_num,count,
row_number() OVER (distribute BY calling_num sort BY count DESC) rn
from
(select calling_num,called_num,count(*) as count
from phone_test group by calling_num,called_num)a)b
where b.rn = 1

130 131 6
132 130 5
134 135 4
138 137 3
135 130 4

これで各グループのrn=1の行を選択した.
書き方を変えて、こう書くこともできます.

select b.calling_num,called_num,count
from
(select a.calling_num,called_num,count,
row_number() OVER (partition BY calling_num order BY count DESC) rn
from
(select calling_num,called_num,count(*) as count
from phone_test group by calling_num,called_num)a)b
where b.rn = 1

130 131 6
132 130 5
134 135 4
138 137 3
135 130 4

効果は前と同じ!

3.rank() dense_rank()

rank、その名の通り、ソートです.これはソートよりも上位にランクされているのは、返されるデータ項目がグループ内の順位であり、順位が等しいと名詞に対応する空席が残ります.そしてdense_rankとrankの唯一の違いは、ランキングが等しいときに対応する空席を残さないことです.例を見てみましょう.

select a.calling_num,called_num,count,
rank() over (partition by calling_num order by count desc) rank,
dense_rank() over (partition by calling_num order by count desc) drank,
row_number() OVER (distribute BY calling_num sort BY count DESC) rn
from
(select calling_num,called_num,count(*) as count
from phone_test group by calling_num,called_num)a

 ：calling_num called_num count rank drank rn
130 131 6   1   1   1
130 133 2   2   2   2
130 132 2   2   2   3
130 134 1   4   3   4
132 130 5   1   1   1
132 133 3   2   2   2
132 131 3   2   2   3
134 135 4   1   1   1
134 136 3   2   2   2
138 137 3   1   1   1
138 136 2   2   2   2
135 130 4   1   1   1
135 132 2   2   2   2

賢いあなたは、この2つの関数の使い方が分かったのではないでしょうか.

4.ntile()

ntileは階層別クエリーです.その役割は、データをいくつかの部分に分けることです.例えば、データを全部で10部に分け、上位10%を分析したいと思っています.以下を参照してください.

select calling_num,called_num,count(*),ntile(5) over(order by count(*) desc) til
from phone_test group by calling_num,called_num
130 131 6   1
132 130 5   1
135 130 4   1
134 135 4   2
138 137 3   2
132 133 3   2
132 131 3   3
134 136 3   3
138 136 2   3
130 132 2   4
135 132 2   4
130 133 2   5
130 134 1   5

私たちはまずすべてのデータを5つの部分に分けます.一部のみを表示する場合は、次の手順に従います.

select a.calling_num,a.called_num,count,til from
(select calling_num,called_num,count(*) count,ntile(5) over(order by count(*) desc) til
from phone_test group by calling_num,called_num)a
where til = 1

130 131 6   1
132 130 5   1
135 130 4   1

5.一定範囲の重合

実際の応用シーンでは,様々なデータ集約の要件があり,一般的な計算よりも複雑である.例えば、電子商取引にとって、当月と今年までのすべての月の累計注文量を見ることが多い.販売者にとっても、当月と今年の前または今四半期の前の累計売上高を見ることが多い.本明細書の例では、現在の番号の通話合計と、以前のすべての番号の通話合計との累計を計算したい.

select calling_num,sum(num),
sum(sum(num)) over(order by calling_num ROWS between unbounded preceding and current row) as cumulative_sum
from
(select calling_num,called_num,count(*) as num
from phone_test group by calling_num,called_num)a
group by calling_num

130 11  11
132 11  22
134 7   29
135 6   35
138 5   40

sum(sum(num))という書き方では,中のsum(num)は累積する必要がある和を表す.overの内容を重点的に見てみましょう:order by calling_numは、ROWS between unbounded preceding and current rowが、集約の開始位置と終了位置を示すことを示す.unbounded precedingは起点であり、最初の行から始まることを示す.current rowはデフォルト値で、現在のローに表示されます.以下の書き方は、同じ効果を達成できます.

select calling_num,sum(num),
sum( sum(num)) over(order by calling_num ROWS unbounded preceding) as cumulative_sum
from
(select calling_num,called_num,count(*) as num
from phone_test group by calling_num,called_num)a
group by calling_num

130 11  11
132 11  22
134 7   29
135 6   35
138 5   40

現在の行の前の2行のみを集約する場合は、前の2行+現在の行=3行の値を合計して計算します.

select calling_num,sum(num),
sum( sum(num)) over(order by calling_num ROWS between 2 preceding and current row) as cumulative_sum
from
(select calling_num,called_num,count(*) as num
from phone_test group by calling_num,called_num)a
group by calling_num

130 11  11
132 11  22
134 7   29
135 6   24
138 5   18

次のように書くこともできます.

select calling_num,sum(num),
sum( sum(num)) over(order by calling_num ROWS 2 preceding) as cumulative_sum
from
(select calling_num,called_num,count(*) as num
from phone_test group by calling_num,called_num)a
group by calling_num

130 11  11
132 11  22
134 7   29
135 6   24
138 5   18

前の行の後の行を集約する場合は、前の行+現在の行+後の行=3行の計算結果を合計します.

select calling_num,sum(num),
sum(sum(num)) over(order by calling_num ROWS between 1 preceding and 1 following) as cumulative_sum
from
(select calling_num,called_num,count(*) as num
from phone_test group by calling_num,called_num)a
group by calling_num

130 11  22
132 11  29
134 7   24
135 6   18
138 5   11

DataCastle[猫と犬の大戦]——Yinjh試合のすべてのコード

pthreadマルチスレッド加速例(大型マトリクス乗算):Blocking,1024スレッド^^;