【SQL初心者必見】SQLiteでグループ化と集計をマスター！最大値、最小値、平均値などを簡単に取得

SQLiteでグループごとの最大値を持つ行を取得する方法

方法1：サブクエリを使用する

1. 最大値を含むサブクエリを作成します。 このサブクエリは、各グループの最大値を max_value という列に含むようにします。
2. メインクエリでサブクエリと結合します。 メインクエリは、max_value 列と一致する行のみを選択するようにします。

例：

-- テーブル `customers` の例
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  order_amount REAL
);

-- 各都市の最大注文金額を取得する
SELECT c.id, c.name, c.city, c.order_amount
FROM customers AS c
JOIN (
  SELECT city, MAX(order_amount) AS max_value
  FROM customers
  GROUP BY city
) AS sub
ON c.city = sub.city AND c.order_amount = sub.max_value;

方法2：ウィンドウ関数を使用する

SQLiteバージョン3.25以降では、ウィンドウ関数を使用して、グループごとの最大値を直接計算できます。

-- 各都市の最大注文金額を取得する
SELECT id, name, city, order_amount
FROM customers
ORDER BY city
WINDOW (
  PARTITION BY city
  ORDER BY order_amount DESC
  ROWS BETWEEN PRECEDING 0 AND CURRENT ROW
);

どちらの方法が適しているか

どちらの方法を使用するかは、状況によって異なります。

• サブクエリの方が、より古いバージョンのSQLiteで使用できます。 また、複雑な条件が必要な場合にも適しています。
• ウィンドウ関数は、より簡潔で読みやすいコードを作成できます。 また、パフォーマンスが優れている場合もあります。

• 複数の列でグループ化したい場合は、GROUP BY 句に複数の列を指定します。
• 複数の最大値を取得したい場合は、ROW_NUMBER() 関数と組み合わせて使用できます。

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  order_amount REAL
);

データの挿入

INSERT INTO customers (name, city, order_amount)
VALUES
  ('Alice', 'Tokyo', 100.00),
  ('Bob', 'Osaka', 80.00),
  ('Charlie', 'Tokyo', 120.00),
  ('David', 'Osaka', 90.00),
  ('Eve', 'Tokyo', 150.00);

-- 各都市の最大注文金額を取得する
SELECT c.id, c.name, c.city, c.order_amount
FROM customers AS c
JOIN (
  SELECT city, MAX(order_amount) AS max_value
  FROM customers
  GROUP BY city
) AS sub
ON c.city = sub.city AND c.order_amount = sub.max_value;

結果

id | name   | city | order_amount
----+-------+------+-------------
5  | Eve   | Tokyo | 150.00
3  | Charlie | Tokyo | 120.00
4  | David | Osaka  | 90.00

-- 各都市の最大注文金額を取得する
SELECT id, name, city, order_amount
FROM customers
ORDER BY city
WINDOW (
  PARTITION BY city
  ORDER BY order_amount DESC
  ROWS BETWEEN PRECEDING 0 AND CURRENT ROW
);

id | name   | city | order_amount
----+-------+------+-------------
5  | Eve   | Tokyo | 150.00
3  | Charlie | Tokyo | 120.00
4  | David | Osaka  | 90.00

説明

• 方法1:
  • まず、city 列でグループ化し、各グループの最大 order_amount を max_value という列に含むサブクエリを作成します。
  • 次に、メインクエリで customers テーブルとサブクエリを結合し、city と order_amount が一致する行のみを選択します。
• 方法2:
  • ORDER BY city 句を使用して、顧客情報を都市ごとにソートします。
  • WINDOW 句を使用して、各都市の order_amount の最大値を計算します。
  • ROWS BETWEEN PRECEDING 0 AND CURRENT ROW 句を使用して、現在の行のみを結果に含めます。

CTEを使用すると、複雑なクエリをより読みやすく、わかりやすく分割できます。

-- 各都市の最大注文金額を取得する
WITH max_orders AS (
  SELECT city, MAX(order_amount) AS max_value
  FROM customers
  GROUP BY city
)
SELECT c.id, c.name, c.city, c.order_amount
FROM customers AS c
JOIN max_orders AS m
ON c.city = m.city AND c.order_amount = m.max_value;

方法4：副問い合わせを使用する

副問い合わせを使用すると、クエリ内で別のクエリを実行できます。

-- 各都市の最大注文金額を取得する
SELECT id, name, city, order_amount
FROM customers AS c
WHERE order_amount = (
  SELECT MAX(order_amount)
  FROM customers
  WHERE city = c.city
);

方法5：GROUP BY句とHAVING句を組み合わせて使用する

HAVING 句を使用すると、GROUP BY 句の結果をさらに絞り込むことができます。

-- 各都市の最大注文金額を持つ顧客情報を取得する
SELECT id, name, city, order_amount
FROM customers
GROUP BY city
HAVING order_amount = (
  SELECT MAX(order_amount)
  FROM customers
  WHERE city = c.city
);

どの方法を選択するべきか

• 可読性 が重要であれば、CTE または副問い合わせを使用するとよいでしょう。
• パフォーマンス が重要であれば、ウィンドウ関数を使用するとよいでしょう。
• クエリ が複雑な場合は、GROUP BY 句と HAVING 句を組み合わせて使用するとよいでしょう。

• NULL値 を処理する必要がある場合は、CASE 式を使用する必要があります。