SQLでMIN()、MAX()、AVG()などの集計関数を使ってグループ内の統計値を求める方法
2024-07-27
SQLで2つのフィールドを使ってグループ化とカウントを行う方法
SQLにおけるGROUP BY句とCOUNT関数は、データを様々な基準でグループ化し、各グループ内のレコード数を集計するのに役立ちます。この機能は、顧客の購入傾向を分析したり、ウェブサイトのトラフィックを調査したりするなど、様々な場面で活用できます。
本記事では、2つのフィールドを使ってグループ化とカウントを行うSQLクエリをわかりやすく解説します。
例
以下は、架空の「orders」テーブルを持つデータベースを例として使用します。このテーブルには、注文ID、顧客ID、商品ID、注文個数などの情報が含まれています。
orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT
);
このテーブルを使って、以下のクエリを実行すると、各顧客が購入した商品の個数をカウントできます。
SELECT customer_id, product_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, product_id;
このクエリは以下の結果を返します。
customer_id | product_id | order_count
------------+------------+------------
1 | 1 | 10
1 | 2 | 15
2 | 1 | 5
2 | 3 | 7
この結果から、顧客1は商品1を10個、商品2を15個、顧客2は商品1を5個、商品3を7個購入したことがわかります。
解説
上記のクエリは以下の通り分解できます。
SELECT customer_id, product_id, COUNT(*) AS order_count: この部分は、取得したいカラムを指定します。ここでは、customer_id、product_id、そして各グループの注文個数（order_countというエイリアスで指定）を取得します。FROM orders: この部分は、クエリ対象のテーブルを指定します。ここでは、ordersテーブルを参照します。GROUP BY customer_id, product_id: この部分は、データをグループ化するフィールドを指定します。ここでは、customer_idとproduct_idでグループ化するため、これらのフィールドがGROUP BY句に指定されています。COUNT(*): この部分は、各グループ内のレコード数をカウントする集計関数です。ここでは、すべてのレコード（*）をカウントするため、COUNT(*)が使用されています。
2つのフィールドでグループ化とカウントを行う際のポイント
GROUP BY句で指定するフィールドは、集計を行いたいフィールドと同じである必要はありません。- 複数の集計関数を使用することもできます。
WHERE句を使用して、グループ化対象となるレコードを絞り込むことができます。HAVING句を使用して、グループ化結果に対して条件を指定することができます。
SQLにおけるGROUP BY句とCOUNT関数は、データを効果的に分析するのに役立つ強力なツールです。2つのフィールドを使ってグループ化とカウントを行うことで、より詳細な分析が可能になります。
SELECT customer_id,
SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id;
customer_id | total_quantity
------------+---------------
1 | 25
2 | 12
この結果から、顧客1は合計25個の商品を注文し、顧客2は合計12個の商品を注文したことがわかります。
説明
SELECT customer_id, SUM(quantity) AS total_quantity: この部分は、取得したいカラムを指定します。ここでは、customer_idと、各顧客が注文した商品の合計個数（total_quantityというエイリアスで指定）を取得します。SUM(quantity): この部分は、quantityフィールドの値を合計する集計関数です。
- 各商品がどの顧客に最も多く販売されているのかを確認する:
SELECT product_id, customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY product_id, customer_id
ORDER BY order_count DESC;
- 各顧客がどの曜日に最も多くの注文を行っているのかを確認する:
SELECT customer_id, DAYOFWEEK(order_date) AS order_day, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, DAYOFWEEK(order_date);
WITH order_counts AS (
SELECT customer_id, product_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, product_id
)
SELECT *
FROM order_counts
ORDER BY customer_id, product_id;
customer_id | product_id | order_count
------------+------------+------------
1 | 1 | 10
1 | 2 | 15
2 | 1 | 5
2 | 3 | 7
サブクエリを使用した方法
サブクエリを使用して、集計結果を別のクエリで使用することができます。以下は、サブクエリを使用して2つのフィールドでグループ化とカウントを行う例です。
SELECT customer_id, product_id, (
SELECT COUNT(*)
FROM orders
WHERE customer_id = o.customer_id
AND product_id = o.product_id
) AS order_count
FROM orders AS o;
customer_id | product_id | order_count
------------+------------+------------
1 | 1 | 10
1 | 2 | 15
2 | 1 | 5
2 | 3 | 7
集計関数以外の方法
集計関数以外にも、グループ化とカウントを行う方法はいくつかあります。以下は、その例です。
DISTINCT句を使用して、各グループ内の重複するレコードを削除する。COUNT(DISTINCT field)を使用して、各グループ内の個別レコード数をカウントする。MIN()、MAX()、AVG()などの集計関数を使用して、グループ内の最小値、最大値、平均値などを求める。
mysql sql
