SQLウィンドウ関数と集約式

SQLウィンドウ関数と集約式
以下はSQLでよく使われる2種類の分析関数の使用、ウィンドウ関数と集約式を整理する.ここではグループ計算、並べ替え、抽出、関数後にover窓を開く必要がある分析関数をウィンドウ関数とし、集約関数を用いたグループ、シーケンスなどの文を集約式と呼ぶ(いくつかの集約式の後にもoverで範囲を画定することができ、また、統計などの機能に用いられるいくつかの集約関数、例えばcorr、stddevなどは、使用方法が集約関数とほぼ同じであり、この編では整理されていないため)は、学習慣習に基づいて分類され、実際の使用ではこれらの関数を「分析関数」と呼ぶことが多い.これらの関数は、異なるデータベースでサポートされている場合が異なります.ここでは、主にHiveおよびPostgreSQLの関連関数を参照します.
1.ウィンドウ関数
ウィンドウ関数(Window Function)は主にパケット集約計算やソートに用いられ、「ウィンドウ」は「範囲」を意味し、ある列に従って先に絞り込み、関数の機能に基づいてソートなどを計算する.
ウィンドウ関数の基本形式

#    over   
<    > over (partition by <       >
                order by <       >)
                <          >

overの後ろの部分は「窓を開ける」ために使用されます.範囲と順序を指定します.固定された組み合わせであるover(distribute by…sort by…)とover(partition by…order by…)の2つの方法があります.order byまたはsort byの後ろには窓句があります.一般的にはrows betweenで始まり、選択行の範囲を説明するために使用されます.order byの後ろにしか現れません.主に:

PRECEDING:前

FOLLOWING:以降

CURRENT ROW:現在の行

UNBOUNDED:始点、UNBOUNDED PRECEDINGは前からの始点、UNBOUNDED FOLLOWING:は後への終点

例えば、rows between 3 PRECEDING and 1 FOLLOWINGは前取3行及び後取1行を示し、rows between CURRENT ROW and UNBOUNDED FOLLOWINGは現在行から後のすべての行を示す.
1.1基本集約関数のウィンドウ計算
ウィンドウ関数と基本的な集約計算(集約関数+group by)の最大の違いは、count()関数を例に、ウィンドウ関数が元のテーブルに記録された数(行数)を変更しないことです.

--          group by，              ，      
select company, count(staff_id) from com_info where c_time = '2020-02-29' group by company;

--         ，                  
select company, count(staff_id)  over （partition by company order by district）
from com_info where c_time = '2020-02-29';

1.2並べ替え機能のウィンドウ関数rank()、dense_rank()、およびrow_number()は、ウィンドウ句を書くことができない順序付けのためのウィンドウ関数である.

row_number()は、1から順に、パケット内に記録するシリアル番号を生成し、row_number()値は重複せず、並べ替え値が同じである場合、表に記録された順に並べ替える.

rank()は、パケット内のデータ項目の順位を生成し、順位が等しいと次の順位の位置を占有する(1,2,2,4など).

dence_rank()は、パケット内のデータ項目の順位を生成し、順位が等しい次の順位の位置を占有しない(順位:1,2,2,3)

.

select staff_id, rank() over(partition by department order by staff_his_perf) as perf_rank
from stf_pr_info where c_time = '2020-02-01_2020-02-29'

1つの例では、ユーザーログインログテーブルがuser_id,log_id,session_id,platがsqlで30日近く毎日平均ログインユーザー数をクエリーし、30日近く連続アクセス7日以上のユーザー数をsqlでクエリーします.

select user_id,max(count_date_on)
from(
(select user_id, count(date_on) count_date_on
from 
(select user_id,log_id,row_number() over(partition by user_id order by log_id desc) rnk,log_id-(max(log_id)-rnk) date_on
from TB
group by user_id ) A
group by user_id,date_on))B
group by user_id
having max(count_date_on)>=7

別の例では、ユーザーの最長連続ログイン日数を計算します.

select UID,max(cnt) as cnt
from (
select UID,Grp_No,count(*) as cnt
from (
select UID,LoadTime,(Day(LoadTime)-ROW_NUMBER() OVER (Partition By UID Order By UID,LoadTime)) as Grp_No 
from #Tmp_Data) a
group by UID,Grp_No) b 
group by UID

overでは、Hiveで集約関数を使用できます.

select staff_id, rank() over (order by sum(stf_perf_cnt)) as staff_his_od
from TB
group by staff_id;

1.3バケツ関数overntile順序付けされたパーティションの行は、指定された数のほぼ等しいバケツに割り当てられます.バケツ番号は、1つのグループから1つのバケツ番号が割り当てられます.グループの各行について、ntile関数は、その行が属するグループを示すバケツ番号を割り当てます.ほぼ等しいということは、グループ結果の分散ができるだけ小さくなり、すべてのバケツのレコードが同じになるか、あるバケツから記録の少ないバケツの開始後のすべての突き刺しの記録数は、そのバケツの記録数と同じである.例えば、53のデータが5つのグループに分けられ、11、11、11、10、10であり、11、11、11、11、11、9ではない.1つの例では、1000店舗の価格データである.価格ランキング上位30%と下位70%の店舗の平均価格を計算する.

-- 1            10 
drop table if exists test_dp_price_rk;
create table test_dp_price_rk
select id, price, NTILE(10) over (order by price desc) as rn
from test_dp_price;

-- 2    30% 70%，       
select new_rn,
max(case when new_rn=1 then 'avg_price_first_30%' when new_rn=2 then 'avg_price_last_70%' end) 
as avg_price_name,
avg(price) avg_price
from 
(select id, price, rn,
case when rn in (1,2,3) then 1 else 2 end as new_rn
from test_dp_price_rk)a group by new_rn;

1.4オフセット関数ntileおよびlag()関数は、同じフィールドの前のN行のデータ(lag)または後のN行のデータ(lead)を独立した列として同じクエリから取り出すことができ、基本構文は

lag(col,n,DEFAULT) --     n   ，     n    （null）  DEFAULT，n   1，DEFAULT   null
lead(col,n,DEFAULT) --     n   ，     n    （null）  DEFAULT，n   1，DEFAULT   null

1つの例では、過去2回の消費金額を消費記録から取り出す

select sal_time, cust_id, sal, lag(sal,1,0) over (partition by cust_id order by sal_time) as lest_sal
from cust_info_base where sal_time between '2020-02-01' and '2020-02-29'

1.5 lead()およびfirst_valuelast_valueはウィンドウ内の第1の記録を取り、first_valueはウィンドウ内の最後の記録を取り、2つのパラメータlast_valueがある.例えば、部門の入社時間が最も早く、最も遅い従業員を取り出す.

select dep_id, first_value(join_time) over (partition by dep_id order by join_time) as ld_time,
last_value(join_time) over (partition by dep_id order by join_time) as yn_time
from staff_info_base
where c_time = '2020-02-29'

1.6(columnName, isSkipNull=false)およびcume_distpercent_rankは、ウィンドウ内の現在値以下の行数とグループ内の合計行数との比を計算するために使用されます.すなわち、統計値のウィンドウ範囲内の位置、例えば、従業員の収入が全社および部門内の位置を計算するために使用されます.

select part_id, staff_name, compen, cume_dist() over (order by compen) as pct_company,
cume_dist() over (partition by part_id order by compen) as pct_part 
from staff_payment_his where c_time = '2020-02-29'
order by part_id, compen

24,5679142は、0から始まる24,5679142のパーセンテージ・ランキングを計算するために使用されます.1つの例では、従業員の収入のパーセンテージ・ランキングと全体の収入の位置との比較を計算します.

select part_id, staff_name, compen,
percent_rank() over (partition by part_id order by compen) as pct_rank_part,
precentile_cont(0) within group(order by compen desc) over(partition by part_id) max_com,
precentile_cont(0.25) within group(order by compen desc) over(partition by part_id) b75_com,
precentile_cont(0.5) within group(order by compen desc) over(partition by part_id) medium_com,
precentile_cont(0.75) within group(order by compen desc) over(partition by part_id) b25_com
from staff_payment_his where c_time = '2020-02-29'
order by part_id, compen

2.集約式
2.1秩序化されたセットの集約
秩序化配列の重合は、cume_dist、percent_rank、(x - 1) / (the number of rows in the window or partition - 1)、percentile_cont、percentile_discを含む統計的位置値および衆数に主に用いられる.(modeのうちHiveとpercentilepercentile_approxの機能と使い方はpercentileのpgと類似しており、percentile_contは近似度を設定することができ、公式ドキュメントのヒント、Use PERCENTILE_APPROX if your input is non-integral.)

--          /   
select staff_rank, precentile_cont(0.5) within group(order by staff_perf_cnt) as perf_medium 
from stf_pr_info where c_time = '2020-02-01_2020-02-29'

percentile_approxとpercentile_discのわずかな違いは、ある求取ビット値に対応するレコードがない場合、percentile_contは最も近い2つのレコードの平均値を計算し、percentile_contは最も近い1つの値(昇順配列のように上向き)をソートすることである.
次の例では、各部門の在職時間の長さとその数を計算します.集約関数の間にネストは許可されていないため、連表で処理する必要があります.

select t1.part_id as de_part,
count(t1.staff_id) as stf_cnt,
count(case when t1.on_wtime = t2.mode_tm then t1.staff_id else null end) as mode_cnt
from
(select part_id, staff_id, on_wtime
from public.dt_link_agent
where c_time = '2020-02-29') t1
left join
(select part_id,
mode() within group(order by on_wtime) as mode_tm
from public.dt_link_agent
where c_time = '2020-02-29'
group by part_id) t2
on t1.part_id = t2.part_id
group by de_part

2.2接合機能の集約percentile_discは、文字列の結合操作を行うことができます.たとえば、中心の大きい部門の下にあるすべての2次部門を並べ替えて結合します.

select org_id, string_agg(part_id,',' order by part_id) as dep
from public.dt_link_agent
where c_time = '2020-02-29'
group by org_id

string_aggは1つのリスト(array_aggでpgで表される)に結合することができるが、{}を用いて重合し、この結合もgroup byで重量を除去する必要がある.

select org_id, array_agg(distinct part_id) as dep
from public.dt_link_agent
where c_time = '2020-02-29'
group by org_id

リストを文字列に変換する必要がある場合は関数distinctが必要であり、またarray_to_stringにはpg、json_agg、json_object_agg、xmlagg等があり、各種データ構造を処理する.
2.3選別と濾過pgでは、filterを使用してフィルタリングがサポートされています.たとえば、統計センター組織の下で、従業員数が200人以上の部門のリストです.

select t.org_id as org_id, string_agg(t.part_id, ',' order by t.part_id) 
filter (where t.staff_cnt > 200)
from 
(select org_id, part_id, count(staff_id) as staff_cnt
from public.dt_link_agent
where c_time = '2020-02-29'
group by org_id, part_id) t 
group by org_id

参考資料:
Hive Operators and User-Defined Functions(UDFs)9.20.Aggregate Functions PostgreSQL集約式FILTER,order,within group用法SQL Server Nutile()関数SQL 2005の4つのランキング関数(row_number,rank,dense_rank,ntile)の比較PERCENT_RANKウィンドウ関数Oracleのすべての分析関数