ポイント: スノーフレークウィンドウ関数は、集約なしで行を横断して複雑な計算を可能にします。このチュートリアルでは、OVER()、PARTITION BY、およびORDER BYのような重要な機能を説明し、累積合計やランキングなどの計算における使用を示します。
- ウィンドウ関数は従来のSQLのGROUP BYとは異なります。
- 計算のためのウィンドウを定義するためにOVER()を使用します。
スノーフレークはウィンドウ関数をサポートしています。ウィンドウ関数は、行のサブセットに対して実行されるものと考えてください。ただし、結果はすべての行を返します。これは、各グループに1つの結果がある従来のSQLのグループ化とは異なります。
ウィンドウ関数は、以下のような例で役立つ可能性があります：
- 累積合計
- いくつかの前の行の平均値
- すべての行の中での各行のパーセンタイルランキング。
スノーフレークにおけるウィンドウ関数のトピックは広範で複雑です。このチュートリアルは簡単な概要を提供し、さらなるチュートリアルを引き続き開発していきます。
スノーフレークの定義
スノーフレークはウィンドウを関連する行のグループと定義します。これはover()ステートメントによって定義されます。over()ステートメントは、従来のSQL関数の代わりにウィンドウ関数を使用したいことをスノーフレークに知らせます。いくつかの関数は両方のコンテキストで動作します。
ウィンドウフレームはウィンドウのサブグループです。ウィンドウフレームにはorder byステートメントが必要です。行は既知の順序でなければなりません。
ウィンドウフレームは累積的またはスライディングであり、これはorder byステートメントの拡張です。累積的はウィンドウフレーム全体を意味します。スライディングは、例えば+- n行のオフセットを追加することを意味します。
ウィンドウにはパーティションステートメントも含めることができます。パーティションは行のグループであり、従来のグループ化ステートメントのようです。
ウィンドウと通常のSQL
たとえば、製品ごとに売上をグループ化し、テーブルに4行がある場合、結果には2行が含まれている可能性があります：
ウィンドウ関数
順序
通常のSQLのグループ化
select count(*) from sales group by product:
10 product A
20 product B
ウィンドウ関数を使用すると、2つのグループをまたいでカウントが得られますが、データベース内の4つの行すべてが表示され、合計は全体のグループのものになります。これにより、パーティションステートメントを使用すると、状況が異なります。
count 10 product A
count 10 product A
count 20 product B
count 20 product B
サンプルデータを作成
これを学ぶために、まずこれらの2つのテーブルを作成します。
CREATE TABLE customers
(
customernumber varchar(100) PRIMARY KEY,
customername varchar(50),
phonenumber varchar(50),
postalcode varchar(50),
locale varchar(10),
datecreated date,
email varchar(50)
);
CREATE TABLE orders
(
customernumber varchar(100) PRIMARY KEY,
ordernumber varchar(100),
comments varchar(200),
orderdate date,
ordertype varchar(10),
shipdate date,
discount number,
quantity int,
productnumber varchar(50)
)
次に、このSQLデータを貼り付けます。データの最上部は次のようになります：
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('440','tiqthogsjwsedifisiir','3077854','vdew','','2020-09-27','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('440','402','swgstdhmju','2020-09-27','sale','2020-10-01','0.7005950240358919','61','BB111');
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('802','hrdngzutwelfhgwcyznt','1606845','rnmk','','2020-09-27','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('802','829','jybwzvoyzb','2020-09-27','sale','2020-10-06','0.3702248922841853','75','FF4444');
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('199','ogvaevvhhqtjcqggafnv','8452159','hyxm','','2020-09-27','[email protected]');
パーティション
パーティションはウィンドウ内のサブセットを作成します。ここでは、製品ごとの数量の合計があります。
select customernumber, ordernumber, productnumber,quantity,
sum(quantity) over (partition by productnumber) as prodqty
from orders
order by ordernumber
これは、注文テーブルが自分自身と結合されるこのSQLステートメントと同じ結果を生み出します：
select customernumber,
ordernumber,
productnumber,quantity,
(select sum(quantity) from orders as o2 where o1.productnumber = o2.productnumber) as prodqty
from orders as o1
order by ordernumber
sum()関数は、ウィンドウ関数には適していません。なぜなら、それはグループのためのものであって、順序付けられたセットではないからです。しかし、スノーフレークはウィンドウフレームでsumを使用することを許可しています。つまり、order()ステートメントを含むステートメントで、解釈が難しい結果を生じます。
ここで、ランキングに関連するrank関数を見てみましょう。ここでは、ウィンドウ関数を使用して最も価値のある顧客をランク付けします。これらは最も大きな購入をした顧客です。
rank()関数は引数を取らず、ウィンドウは数量の降順で並べられます。出力を10に制限して、以下のページに収まるようにします。
select customernumber, quantity, rank() over (order by quantity desc) from orders limit 10
ここが出力です。最も多く購入した顧客が最初に表示されます。
追加リソース
このようなチュートリアルをもっと探すには、これらのリソースを探ってください：
スノーフレークガイド
これらの投稿は私自身の意見であり、必ずしもBMCの立場、戦略、または意見を代表するものではありません。