【SQLite初心者向け】重複なし！最初の文字でレコードを抽出するSELECTクエリ

2024-06-22

SQLiteで重複する「最初の文字」がないSELECTクエリ

問題の理解

まず、どのような状況でこのクエリが必要なのか理解する必要があります。例えば、以下のようなケースが考えられます。

顧客リストから、苗字の最初の文字が重複しない顧客のみを抽出したい。

GROUP BY句とDISTINCTキーワード

この問題を解決するには、GROUP BY句とDISTINCTキーワードを組み合わせます。

GROUP BY句は、SELECT句で指定した列に基づいて結果をグループ化します。各グループは、その列の値が同じレコードで構成されます。

DISTINCTキーワードは、SELECT句で指定した列の値が重複しないレコードのみを選択します。

クエリ例

SELECT DISTINCT
  first_name,
  last_name
FROM customers
GROUP BY LEFT(last_name, 1);

このクエリは、以下の処理を実行します。

customersテーブルからすべてのレコードを選択します。
last_name列の最初の1文字に基づいて結果をグループ化します。
各グループから、重複しないfirst_nameとlast_name列の値を選択します。

演習

上記を参考に、以下のケースでそれぞれクエリを作成してみましょう。

補足

上記のクエリは、最初の文字のみを比較します。2文字目以降も比較したい場合は、LEFT(last_name, 2)のように文字数を調整してください。
大文字と小文字を区別しない比較が必要な場合は、LOWER()関数を使用して文字列を小文字に変換してから比較してください。

商品リストから商品名の最初の文字が重複しない商品のみを抽出するクエリ

SELECT DISTINCT
  product_name
FROM products
GROUP BY LEFT(product_name, 1);

言語リストから言語名の最初の文字が重複しない言語のみを抽出するクエリ

SELECT DISTINCT
  language_name
FROM languages
GROUP BY LEFT(language_name, 1);

このクエリは、languagesテーブルからすべてのレコードを選択し、language_name列の最初の1文字に基づいて結果をグループ化します。そして、各グループから重複しないlanguage_name列の値を選択します。

SQLiteで重複する「最初の文字」がないSELECTクエリを作成するその他の方法

ウィンドウ関数を使った方法

SQLiteには、ウィンドウ関数と呼ばれる特殊な関数が用意されています。ウィンドウ関数は、現在の行だけでなく、その周辺の行を参照して値を算出することができます。

この方法では、ROW_NUMBER()関数とPARTITION BY句を使用して、各グループ内の行に番号を割り当てます。そして、MIN()関数を使用して、各グループ内で最初の行の「最初の文字」を取得します。

SELECT product_name
FROM products
WHERE product_name IN (
  SELECT product_name
  FROM products
  ORDER BY product_name
  WINDOW (
    PARTITION BY LEFT(product_name, 1)
    ORDER BY ROW_NUMBER() OVER (PARTITION BY LEFT(product_name, 1)) ASC
    ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
  )
);

product_name列の最初の1文字ごとにレコードをパーティショニングします。
各パーティション内で、product_name列を昇順に並べ替えます。
ROW_NUMBER()関数を使用して、各パーティション内の行に番号を割り当てます。
MIN()関数を使用して、各パーティション内で最初の行のproduct_name列の値を取得します。

サブクエリを使用する方法もあります。サブクエリは、別のクエリをSELECTクエリの中で実行する機能です。

この方法では、まずサブクエリを使用して、各グループ内で最初の行の「最初の文字」を取得します。そして、親クエリでその結果を参照して、重複しないレコードのみを選択します。

SELECT product_name
FROM products
WHERE product_name IN (
  SELECT product_name
  FROM products AS p
  WHERE NOT EXISTS (
    SELECT 1
    FROM products AS q
    WHERE q.product_name < p.product_name
      AND LEFT(q.product_name, 1) = LEFT(p.product_name, 1)
  )
);

productsテーブルをpというエイリアスで2回参照します。
qテーブルから、p.product_nameよりも小さいproduct_name列を持つレコードかつ、LEFT(p.product_name, 1)とLEFT(q.product_name, 1)が一致するレコードが存在しないことを確認します。

上記以外にも、CTE（Common Table Expressions）や結合クエリなどを利用した方法も考えられます。

どの方法を選択するかは、状況やデータ量、パフォーマンスなどを考慮して決定する必要があります。

sqlite