【SQL入門】SQLを用いてデータを集計する

なぜ集計が必要か

SQLの集計関数を使用すると、テーブルに保存されている数値データの合計や最大値、平均を求めることができます。今回は紹介しませんが、分散や標準偏差なども出すことができます。

テーブルを眺めるだけではデータの分布や特徴を掴むことはできません。例えば、あるクラス40人の英語の点数テーブルに対し、平均値、最小値・最大値を求めることで、そのクラスの点数データの特徴がわかります。

【SQL入門】データをつなげるでは、売上に商品情報をつなげたテーブルを作成しました。このテーブルの各商品の売上の合計や、1回の注文で平均いくつ売れたかなどを集計すれば、どのようなデータなのかが見えてきて、分析の方針を定めることができます。

集計とは何か

集計とは分類を行ったデータに対して集計処理を行うことを指しています

データの分類：データを商品ごとや顧客IDごとなどで区別・識別すること
集計処理：（各分類における）合計・平均といった集計値を出す

データの分類(GROUP BY)

GROUP BYを使用することで、データをグループ化することができます。指定したカラムで、完全に同一のデータを持つレコードどうしが同じグループとなります。下記のコードでは、同じ「product_id」を持つレコードはグループ化されています。
また、GROUP BYの注意点として、SELECTで使えるのはGROUP BYに指定しているカラム名と、集計関数のみです。下記のコードの場合、SELECTで集計関数を使用していないので,「product_id」ごとに集計された値を取得できません。

【誤ったSQLの例】
SELECT 
    price, <= 集計関数を使用していない
    product_id　
FROM 
    sales
GROUP BY product_id;

データの処理(集計関数)

ここで紹介するのはSQLで使用できる関数のごく一部です。実際にサンプルSQLで各関数を使用してみます。

SUM
- 数値の合計を計算する場合は、SUMを用います。指定したカラムに保存されたデータの合計を計算することが可能です。
AVG
- 数値の平均を計算する場合は、AVGを用います。指定したカラムに保存されたデータの平均を計算することが可能です。
MAX・MIN
- MAX・MIN関数は、指定したカラムのデータの最大・最小値を求めることができます。

実際に集計してみる

準備

【パイプライン入門】spreaad sheet to BQを参考にデータを準備
【SQL入門】データをつなげるを参考に売上に商品情報をつなげたSQLを準備
- with句で中間テーブルとして持っておいてください。また、Salesカラムも以下のように作成してください。

with test_table as(
SELECT 
    sales.date,
    sales.order_id,
    sales.user_id,
    sales.product_id,
    sales.count,
    sales.tax,
    sales.count*products.profit AS Sales,
    products.name,
    products.profit,
    products.price,
    products.cost,
    products.calories
FROM 
    manabiba.sales 
JOIN 
    manabiba.products 
    ON sales.product_id = products.product_id
)

サンプルSQL

SUM関数

「SUM(カラム名)」のようにすることで、指定したカラムのデータの合計が計算できます。SUM関数を使い、データ全体の売上の計算をしてみます。

SELECT 
    SUM(test_table.Sales) AS Sales
FROM 
    test_table;

このように、データ全体の売上の合計を出すことができます。次に月ごとの売上を見てみましょう。

SELECT 
    concat(substr(cast(test_table.date as string),1,7)) as month,
    SUM(test_table.Sales) AS Sales
FROM 
    test_table
GROUP BY 
    month;

2月の方が売上が多いことがわかりますね。

GROUP BYで商品ごとに分類することで、各商品の売上を見ることもできます。

SELECT 
    test_table.name,SUM(test_table.Sales) AS Sales
FROM 
    test_table
GROUP BY 
    test_table.name;

炭酸ジュースの売上が多いことがわかりますね。

AVG関数

「AVG(カラム名)」のようにすることで、指定したカラムのデータの平均が計算できます。AVG関数を使い、各商品ごとの販売個数の平均を計算をしてみます。小数点以下の数値を切り捨てるためにFLOOR関数を使用してください。

SELECT 
    test_table.name,
    FLOOR(AVG(test_table.count)) AS Unitsales
FROM 
    test_table
GROUP BY test_table.name;

各商品の平均の売上個数がわかります。また、以下のようにWHERE句を使用することで指定のレコードの平均を計算できます。

SELECT 
    FLOOR(AVG(test_table.count)) AS Unitsales
FROM 
    test_table
WHERE 
    test_table.name = 'パン';

MAX・MIN関数

「MAX( カラム名 )」「MIN( カラム名 )」とすることで、指定したカラムのデータの最大値、最小値を取得します。一番売上の多い日を見てみます。

SELECT 
    * 
FROM 
    test_table 
WHERE 
    test_table.Sales = (SELECT MAX(test_table.Sales) FROM test_table);

売上の1番多い日がわかりました。MAXをMINに書き換えれば、売上の1番少ない日がわかります。

SELECT 
    * 
FROM 
    test_table 
WHERE 
    test_table.Sales = (SELECT MIN(test_table.Sales) FROM test_table);

特殊な集計方法

分析関数(WINDOW関数)

WINDOW関数は、SUM・AVG・MAX・MINと同様の計算をしますが、行のグループに対して1つの結果を返すのではなく、各行に対して1つの結果を返すのが集計関数と異なる点です。また、集計対象となる行の範囲を指定できます。これはサンプルSQLで詳しく取り扱います。
左は同じuser_idごとに分類して、集計関数を使用したテーブルです。各ユーザーの注文金額の合計を、合計金額として返しています。
右はWINDOW関数を使用して、各ユーザーの注文金額の平均を、平均金額として返しています。集約関数とは違い、一行にはまとまらず、それぞれの行に結果を表示します。

集計対象行の範囲指定ができるフレーム句もありますが、今回は説明を割愛しておきます。

WINDOW関数の基本構文

WINDOW関数の基本構文は以下の通りです。

WINDOW関数 OVER(
    PARTITION BY カラム名 
    ORDER BY カラム名)

PARTITION BY

PARTITION BY句で、指定したカラムをグループ化します。集計関数でのGROUP BYのような動きをします。

ここでは、商品名ごとにグループ化します。

集計関数で商品ごとの売上個数を集計した場合のSQL

SELECT
    test_table.name,
    COUNT(*) AS Unitsales
FROM 
    test_table
GROUP BY test_table.name;

分析関数で同じ商品ごとの売上個数を集計結果を出す場合のSQL

SELECT
    test_table.order_id,
    test_table.name,
    COUNT(*) OVER(PARTITION BY test_table.name) AS Unitsales
FROM 
    test_table;

このようにWINDOW関数は各行に対して結果を返します。GROUP BY付きの集計関数がそれぞれの行で実行されたようなイメージですね。

ORDER BY

ORDER BYで、指定したカラムを基準にレコードをソートします。分析関数のORDER BYは行を順番に並べた上で、最初の行から現在の行までのみを集計の対象にする性質があります。

FIRST_VALUEを使って、商品が最初に売れた日を出してみます。

SELECT
    test_table.name,
    FIRST_VALUE(test_table.date) OVER(PARTITION BY test_table.name  ORDER BY test_table.date ASC) AS FIRST_VALUE
FROM 
    test_table;