MariaDB - DELETE all values that are not the max of one column with a group by on another column
2024-04-02
MariaDBでグループ化による列の最大値ではないすべての値を削除する
この解説では、MariaDBでグループ化された列の最大値ではないすべての値を削除する方法について説明します。
問題
table というテーブルがあり、column1 と column2 という2つの列があります。
column1はグループ化する列です。column2は最大値を取得したい列です。
目的
column1 ごとに column2 の最大値ではないすべての値を削除します。
解決策
以下のSQLクエリを使用します。
DELETE t
FROM table t
INNER JOIN (
SELECT column1, MAX(column2) AS max_value
FROM table
GROUP BY column1
) AS g ON g.column1 = t.column1 AND g.max_value != t.column2;
解説
tableテーブルをtというエイリアスで結合します。- サブクエリで
column1ごとのcolumn2の最大値 (max_value) を取得します。 - サブクエリと
tテーブルをcolumn1で結合します。 column2がmax_valueと等しくない行を削除します。
例
以下の例では、table テーブルに以下のデータがあるとします。
column1 | column2
------- | --------
a | 10
a | 20
b | 30
b | 40
c | 50
c | 60
上記のSQLクエリを実行すると、以下の結果になります。
column1 | column2
------- | --------
a | 20
b | 40
c | 60
column1 | max_value
------- | --------
a | 20
b | 40
c | 60
column1 | column2
------- | --------
a | 10
b | 30
c | 50
これらの行は削除されます。
補足
- このクエリは、
column2にNULL値が含まれている場合、NULL値が最大値とみなされることに注意してください。 - NULL値を最大値とみなしたくない場合は、サブクエリで
MAX()関数にIGNORE NULLSオプションを指定する必要があります。
SELECT column1, MAX(column2) AS max_value
FROM table
GROUP BY column1
IGNORE NULLS;
-- テーブル作成
CREATE TABLE table (
column1 VARCHAR(255) NOT NULL,
column2 INT NOT NULL
);
-- データ挿入
INSERT INTO table (column1, column2) VALUES ('a', 10);
INSERT INTO table (column1, column2) VALUES ('a', 20);
INSERT INTO table (column1, column2) VALUES ('b', 30);
INSERT INTO table (column1, column2) VALUES ('b', 40);
INSERT INTO table (column1, column2) VALUES ('c', 50);
INSERT INTO table (column1, column2) VALUES ('c', 60);
-- 削除処理
DELETE t
FROM table t
INNER JOIN (
SELECT column1, MAX(column2) AS max_value
FROM table
GROUP BY column1
) AS g ON g.column1 = t.column1 AND g.max_value != t.column2;
-- 結果確認
SELECT * FROM table;
column1 | column2
------- | --------
a | 20
b | 40
c | 60
他の方法
方法1: CASE 式を使用する
DELETE FROM table
WHERE CASE
WHEN column2 < MAX(column2) OVER (PARTITION BY column1)
THEN TRUE
ELSE FALSE
END;
CASE式を使用して、column2がcolumn1ごとの最大値よりも小さいかどうかを判断します。- 条件が真の場合、行は削除されます。
方法2: ウィンドウ関数を使用する
DELETE FROM table
WHERE column2 < ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC);
ROW_NUMBER()ウィンドウ関数を使用して、column1ごとのcolumn2の順位を取得します。- 順位が1位 (最大値) ではない行は削除されます。
方法3: 一時テーブルを使用する
-- 一時テーブル作成
CREATE TEMPORARY TABLE tmp (
column1 VARCHAR(255) NOT NULL,
column2 INT NOT NULL
);
-- データ挿入
INSERT INTO tmp (column1, column2)
SELECT column1, column2
FROM table;
-- 最大値取得
UPDATE tmp t
INNER JOIN (
SELECT column1, MAX(column2) AS max_value
FROM tmp
GROUP BY column1
) AS g ON g.column1 = t.column1
SET t.max_value = g.max_value;
-- 削除処理
DELETE t
FROM table t
INNER JOIN tmp ON tmp.column1 = t.column1 AND tmp.column2 != tmp.max_value;
-- 一時テーブル削除
DROP TEMPORARY TABLE tmp;
- 一時テーブル
tmpを作成し、tableテーブルのデータをコピーします。 tmpテーブルとtableテーブルを結合し、column2がmax_valueと等しくない行を削除します。- 一時テーブル
tmpを削除します。
- 方法1はシンプルですが、パフォーマンスが低下する可能性があります。
- 方法2はパフォーマンスが優れていますが、複雑です。
- 方法3は柔軟性がありますが、処理が重くなります。
mariadb
