SQLでWHERE句とGROUP BY句を使ってデータをフィルタリングする方法

2024-04-07

SQLクエリ、カウント、グループ化

準備

以下の環境を用意する必要があります。

データベース (MySQL、PostgreSQL、SQLiteなど)
SQL クエリを実行できるツール (MySQL Workbench、pgAdmin、DB Browser for SQLiteなど)

データ

このチュートリアルでは、以下のサンプルデータを使用します。

CREATE TABLE customers (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  country VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO customers (name, country, age) VALUES
  ('John Doe', 'USA', 30),
  ('Jane Doe', 'USA', 25),
  ('Alice Smith', 'UK', 20),
  ('Bob Smith', 'UK', 22),
  ('Tom Hanks', 'USA', 55),
  ('Sally Field', 'USA', 50);

COUNT()関数は、テーブル内のレコード数をカウントします。

SELECT COUNT(*) FROM customers;

このクエリは、customersテーブル内のすべてのレコード数を返し、6という結果になります。

GROUP BY句は、1つ以上の列に基づいてデータをグループ化します。

SELECT country, COUNT(*)
FROM customers
GROUP BY country;

このクエリは、customersテーブルを国別にグループ化し、各国の顧客数を表示します。

country | COUNT(*)
------- | --------
USA     | 4
UK      | 2

WHERE句は、条件に基づいてレコードをフィルタリングします。

SELECT country, COUNT(*)
FROM customers
WHERE age > 30
GROUP BY country;

country | COUNT(*)
------- | --------
USA     | 2

HAVING句は、グループ化されたデータに対して条件を指定します。

SELECT country, COUNT(*)
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

このクエリは、顧客数が2人以上の国を表示します。

country | COUNT(*)
------- | --------
USA     | 4

このチュートリアルでは、SQLクエリでデータをどのようにカウントしてグループ化できるかを学びました。

これらの機能を組み合わせることで、さまざまなデータ分析を行うことができます。

練習問題

各国の顧客の平均年齢を求めましょう。
20歳以上の顧客の割合を求めましょう。
各国の顧客数が多い順にリストアップしましょう。

各国の顧客の平均年齢

SELECT country, AVG(age)
FROM customers
GROUP BY country;

country | AVG(age)
------- | --------
USA     | 42.5
UK      | 21

20歳以上の顧客の割合

SELECT COUNT(*) AS total_customers,
  COUNT(CASE WHEN age >= 20 THEN 1 END) AS adult_customers,
  ROUND(COUNT(CASE WHEN age >= 20 THEN 1 END) / COUNT(*) * 100, 2) AS percentage
FROM customers;

このクエリは、20歳以上の顧客の割合を表示します。

total_customers | adult_customers | percentage
--------------- | --------------- | --------
6              | 5              | 83.33%

各国の顧客数が多い順にリストアップ

SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC;

country | customer_count
------- | --------
USA     | 4
UK      | 2

データをカウントしてグループ化する他の方法

ウィンドウ関数は、グループ内のデータに対して計算を行う関数です。

SELECT name, country,
  COUNT(*) OVER (PARTITION BY country) AS customer_count
FROM customers;

サブクエリは、別のクエリの結果を返すクエリです。

SELECT country, (
  SELECT COUNT(*)
  FROM customers
  WHERE country = c.country
) AS customer_count
FROM customers AS c
GROUP BY country;

結合は、複数のテーブルを結合する操作です。

SELECT c.country, COUNT(*) AS customer_count
FROM customers AS c
INNER JOIN (
  SELECT country
  FROM customers
  GROUP BY country
  HAVING COUNT(*) > 1
) AS g ON c.country = g.country;