SQLite DISTINCTで重複行を除外するSELECT文の使い方

DISTINCT で重複行を除外する

SELECT は通常、条件に一致する行をすべて返すため、重複もそのまま出てきます。そこで DISTINCT を付けると、選択したカラムの値がまったく同じ行はひとつにまとめられ、ユニークな組み合わせだけが結果に現れます。

CREATE TABLE orders (id INTEGER, customer TEXT, country TEXT);
INSERT INTO orders VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Ada',   'UK'),
    (4, 'Cleo',  'US'),
    (5, 'Boris', 'FR');

SELECT DISTINCT customer FROM orders;

5行から3行へ。SQLite は customer カラムを見て重複を取り除き、ユニークな値ごとに1行ずつ返してくれました。並び順は保証されないので、順番が大事なら ORDER BY を付けましょう。

DISTINCT は SELECT リスト全体に効く

ここでハマる人が多いです。DISTINCT は特定のカラムだけを重複削除するわけではなく、SELECT したすべてのカラムを見て、行全体として重複を判定します。

CREATE TABLE orders (id INTEGER, customer TEXT, country TEXT);
INSERT INTO orders VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Ada',   'UK'),
    (4, 'Cleo',  'US'),
    (5, 'Boris', 'FR');

SELECT DISTINCT customer, country FROM orders;

(customer, country) の組み合わせがユニークな行が、それぞれ1回ずつ現れます。同じ顧客が異なる国で2回登場していれば、両方の行が出力されます。SQLite から見れば、それらは重複ではないからです。

ちなみに、他のカラムを無視して DISTINCT(customer) のように書く構文は存在しません。括弧を付けたくなる気持ちはわかりますが、SELECT DISTINCT(customer), country は SELECT DISTINCT customer, country とまったく同じ意味に解釈されます。括弧は単に式をグループ化しているだけです。顧客ごとに1行ずつ、なんらかの国を1つ選んで取り出したい場合は、GROUP BY と集約関数の出番です。

COUNT(DISTINCT col) で一意な値の数を数える

実務でよくあるのが「あるカラムには ユニークな 値がいくつあるのか?」という疑問です。COUNT(*) は行数を、COUNT(col) は NULL 以外の値の数を、そして COUNT(DISTINCT col) は NULL を除いたユニークな値の数を返します。

CREATE TABLE orders (id INTEGER, customer TEXT, country TEXT);
INSERT INTO orders VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Ada',   'UK'),
    (4, 'Cleo',  'US'),
    (5, 'Boris', 'FR');

SELECT
    COUNT(*)                  AS total_orders,
    COUNT(DISTINCT customer)  AS unique_customers,
    COUNT(DISTINCT country)   AS unique_countries
FROM orders;

注文は5件、ユニークな顧客は3人、ユニークな国は3つ。COUNT(DISTINCT ...) は DISTINCT の集計用途として一番出番が多い形で、「何種類のものが登場したか」を数えたいときには必ずと言っていいほど使います。

ただし、SQLite の COUNT(DISTINCT ...) の中に書けるカラムは1つだけです。複数カラムの組み合わせでユニーク数を数えたいときは、サブクエリでくるんでやります: SELECT COUNT(*) FROM (SELECT DISTINCT a, b FROM t)。

DISTINCT と NULL の扱い

SQL における NULL はちょっとクセのある存在で、NULL = NULL の結果は TRUE ではなく NULL になります。ところが DISTINCT はここだけ特別扱いで、重複排除の判定においては NULL 同士をすべて「等しい」とみなします。

CREATE TABLE contacts (name TEXT, email TEXT);
INSERT INTO contacts VALUES
    ('Ada',   'ada@example.com'),
    ('Boris', NULL),
    ('Cleo',  NULL),
    ('Dan',   'dan@example.com'),
    ('Eve',   NULL);

SELECT DISTINCT email FROM contacts;

返ってくる行は3つ、'ada@example.com'、'dan@example.com'、そしてNULLが1つだけです。3件あったNULLのメールが1つにまとめられたわけですね。このルールはGROUP BYやUNIONなどの集合演算でも同じように働きます。「なんでNULL行が3回じゃなくて1回しか出てこないの?」とハマったときに思い出すと役立ちます。

DISTINCTはORDER BYやLIMITより先に評価される

SELECT文の各句には論理的な評価順があります。FROM → WHERE → GROUP BY → HAVING → SELECT/DISTINCT → ORDER BY → LIMITの順ですね。つまり、まずDISTINCTで重複を取り除き、次にORDER BYで残った行を並べ替え、最後にLIMITで件数を絞り込む、という流れになります。

CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO orders VALUES
    (1, 'Ada',   12.0),
    (2, 'Boris', 30.0),
    (3, 'Ada',   45.0),
    (4, 'Cleo',  20.0),
    (5, 'Boris', 30.0);

SELECT DISTINCT customer
FROM orders
WHERE amount > 15
ORDER BY customer
LIMIT 2;

WHERE で4行に絞り込み、DISTINCT で Boris の重複をまとめ、ORDER BY でアルファベット順に並べ替え、LIMIT で先頭2行を取り出す、という流れです。一度自分で順を追って確認しておくと安心です。結果の並び順で混乱するときは、たいていどのステップがいつ実行されるかを忘れているのが原因です。

DISTINCT と GROUP BY の違い

単純に重複を除外するだけなら、次の2つのクエリは同じ行を返します。

CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO orders VALUES
    (1, 'Ada',   12.0),
    (2, 'Boris', 30.0),
    (3, 'Ada',   45.0),
    (4, 'Cleo',  20.0);

SELECT DISTINCT customer FROM orders;
SELECT customer FROM orders GROUP BY customer;

結果は同じです。違うのは、その後に何ができるかです。

• DISTINCT は「重複を除いた行が欲しいだけ」のとき専用。
• GROUP BY は「行をまとめてグループごとに何かを計算したい」とき用。たとえば COUNT(*)、SUM(amount)、MAX(created_at) などですね。

DISTINCT を書こうとして「やっぱり顧客ごとの合計も欲しいな」と気づいたら、それは GROUP BY に切り替えるサインです。

CREATE TABLE orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO orders VALUES
    (1, 'Ada',   12.0),
    (2, 'Boris', 30.0),
    (3, 'Ada',   45.0),
    (4, 'Cleo',  20.0);

SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer
ORDER BY total DESC;

顧客ごとに1行、しかも欲しかった集計値付き、という結果が得られます。DISTINCTではこれは無理です。「グループごとに1行、さらに 合計も一緒に」という意図を表現する手段がないからです。

注意しておきたいポイント

• パフォーマンス。 DISTINCTを使うと、SQLiteは重複を探すために内部でソートやハッシュ化を行うのが普通です。結果セットが大きいときは、重複削除の対象となるカラムにインデックスを張っておくと効きます。横に広いテーブルで全カラムに対してSELECT DISTINCTをかけているなら、本当に全カラム必要なのか一度立ち止まって考えてみてください。
• DISTINCT *はほぼ使わない。 文法的には正しく、SELECT DISTINCT * FROM tで行全体の重複を除外できます。ただし主キーがあるテーブルなら各行はすでに一意なので、書いても意味がありません。
• UNIQUEと混同しないこと。 UNIQUEはテーブル側の制約で、そもそも重複した値が挿入されないようにするためのものです。一方のDISTINCTはクエリ実行時のフィルターで、結果から重複を隠すだけです。役割がまったく違います。

次は:CASE式

SELECT、WHERE、ORDER BY、そしてDISTINCTで結果の形を整えられるようになったら、次のステップはクエリの 中 で条件分岐を書くことです。CASE式を使うと、条件に応じて異なる値を返せます。SQLにおけるif/elseのようなもので、次のページで詳しく扱います。