ポイント: スノーフレーク SQL 集約関数は、行にわたって計算を実行し、カウントや統計分析を含み、テーブル結合は複数のテーブルからデータを組み合わせます。stddev_pop()やlistagg()のような主要な関数は、データの要約と整理を強化します。
- 集約関数は、指定された行にわたって値を計算します。
- テーブルを結合することで、データの取得を組み合わせることができます。
- 統計関数は、平均と偏差を計算します。
この記事では、スノーフレークで集約関数を使用する方法を説明します。
集約関数とは何ですか？
集約関数は、テーブル内のすべての行または行のサブセットに対して計算を実行する関数です。
例えば、最も簡単な集約関数はcount()です。グループやWHERE句なしでcount(*)を使用して、テーブル内のすべての顧客をカウントできます。*はスノーフレークにすべての列を確認するよう指示しますが、1つの列を指定しても同じ意味です。
select count(*) from orders
しかし、特定のサブセットで注文をカウントしたい場合、例えば注文タイプごとに顧客をカウントすることができます:
select ordertype, count(*) from orders
group by ordertype;
サンプルデータを作成する
これらの関数を探求するために、サンプルデータを作成しましょう。スノーフレークにログインし、データベースを作成ボタンをクリックしてinventoryというデータベースを作成します。次に、ワークシートエディタを開き、次の2つのSQLコマンドを貼り付けます:
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)
)
次に、このデータを貼り付けます。データは次のようになります:
insert into customers (customernumber,customername,phonenumber,postalcode,locale,datecreated,email) values ('ee56d97a-fcaa-11ea-ab7a-0ec120e133fc','zopvxqhwocrtsonemrcf','3119110','vqlx','','2020-09-22','[email protected]');
insert into orders (customernumber,ordernumber,comments,orderdate,ordertype,shipdate,discount,quantity,productnumber) values ('ee56d97a-fcaa-11ea-ab7a-0ec120e133fc','ee56d97b-fcaa-11ea-ab7a-0ec120e133fc','shsyuaraxxftdzooafbg','2020-09-22','sale','2020-10-01','0.7751890540939359','40','ee56d97c-fcaa-11ea-ab7a-0ec120e133fc');
テーブルの結合
顧客と注文のテーブルは、注文番号で結びついています。顧客および注文データを一緒に必要なときは、これらを1つのセットにまとめる必要があります。これを結合を使用して一時的に作成します。
顧客番号の列要素で2つのテーブルを結合します。注意してください:
- asを使用して、テーブル名を省略するエイリアスを作成し、入力を簡素化します。
- inner joinの代わりにjoinを使用します。(他のチュートリアルではしばしばinner joinを追加しますが、それは同じことなので混乱を招くだけです。左結合、右結合、または外部結合と対比するために書くこともありますが、それは直積のようなもので、すなわちn個の注文をm人の顧客に付加してn*m行のセットを作成します。)
select c.customernumber, c.customername, o.ordernumber, c.datecreated, o.orderdate, o.shipdate from customers as c
join orders as o on c.customernumber = o.customernumber;
標準偏差
出荷時間の標準偏差を計算しましょう。これを3つのステップで行います:
- 顧客と注文テーブルを結合します
- datediff() 関数を使用して出荷時間を計算します。つまり、顧客が待たなければならない時間です。
- 各外側のクエリは、内部のクエリを括弧[()]でラップして参照します。したがって、クエリは段階的に構築されます。
以下が完全なクエリです。どのように分解されているかは下に示します。
select
avg(shiptime),
stddev_pop(shiptime)
from
(
select
customernumber,
customername,
orderdate,
shipdate,
datediff(days, orderdate, shipdate) as shiptime
from
(
select
c.customernumber,
c.customername,
o.ordernumber,
c.datecreated,
o.orderdate,
o.shipdate
from
customers as c
join orders as o on c.customernumber = o.customernumber
)
order by
shiptime desc
)
私たちは段階的にクエリを構築します。下のクエリ（最も内側のクエリ）から始めて、上に向かって作業します:
- 顧客と注文テーブルを結合し、顧客と注文の詳細を1つのセットでリストできるようにします。(注文テーブルのみを使用しているので、このステップを省略することもできます。)
(
select
c.customernumber,
c.customername,
o.ordernumber,
c.datecreated,
o.orderdate,
o.shipdate
from
customers as c
join orders as o on c.customernumber = o.customernumber
)
- datediff()関数を使用して出荷時間を計算します
select
count(*),
datediff(days, orderdate, shipdate) as shiptime
from
orders
group by
shiptime
order by
shiptime
- 出荷時間の標準偏差を計算します:
select avg(shiptime), stddev_pop(shiptime) from (step b)
こちらが結果です:
こちらが結果です:
こちらが結果です:
AVG(SHIPTIME) STDDEV_POP(SHIPTIME)
8.539063 3.512038155
離散パーセンタイル
95パーセンタイルは、人口の95%を示すことを意味します。これは一般的な統計であり、その範囲外のデータは通常、外れ値と見なされます。
ここでは、25パーセンタイルを計算する方法を示します:
結果は:
select customernumber , PERCENTILE_disc( 0.25 ) within group (order by quantity)
from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
group by customernumber
order by customernumber
結果は:
CUSTOMERNUMBER PERCENTILE_DISC( 0.25 ) WITHIN GROUP (ORDER BY QUANTITY)
5d2b742e-fcaa-11ea-ab7a-0ec120e133fc 9
確認すると、注文数量は92、55、9であることがわかります。したがって、下位25%に該当するのは9だけです。
select quantity from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
order by quantity desc;
こちらが結果です:
QUANTITY
92
55
9
listagg
listagg関数は、顧客ごとに注文を配列としてリストし、要素のリストを要求するWHERE句で使用できる別の形式に変換します。
select listagg(ordernumber, '|')
from orders
where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
こちらが結果です:
LISTAGG(ORDERNUMBER, '|')
5d2b742f-fcaa-11ea-ab7a-0ec120e133fc|5d2b7431-fcaa-11ea-ab7a-0ec120e133fc|5d2b7433-fcaa-11ea-ab7a-0ec120e133fc
クエリを実行する際は、他のクエリと照合して作業を再確認する必要があります。ここでは、顧客番号を上下に列挙しています。
select orderumber from orders
where where customernumber = '5d2b742e-fcaa-11ea-ab7a-0ec120e133fc'
mode
mode()関数は、最も頻繁に出現する値を示します:
結果は:
select mode(quantity)
from orders
結果は:
MODE(QUANTITY)
13
追加リソース
このようなチュートリアルをさらに探るには、次のリソースを参照してください:
スノーフレークガイド
これらの投稿は私自身のものであり、必ずしもBMCの立場、戦略、または意見を表すものではありません。