SQLでデータを取得する際に優先順位をつけて取得条件を絞る方法

はじめに

システム改修中、新しい商品区分の追加に伴い、商品区分ごとの計算表示を変更する必要が生じました。この際、データベースからのデータを優先度を考慮して取得する問題に直面しました。この記事では、その優先度を考慮したデータの取得方法について解説します。

テストデータの作成

以下のようなテーブルを作成します。

上記のテーブルとデータを作成するSQLです。今回はPostgreSQLを使用しています。

-- テーブルに作成
CREATE TABLE PRODUCTS (
  PRODUCT_ID SERIAL NOT NULL,
  PRODUCT_NAME CHARACTER VARYING(255),
  PRICE NUMERIC,
  SPECIAL_OFFER BOOLEAN DEFAULT FALSE,
  POPULARITY_RANK INTEGER,
  ADDED_DATE DATE,
  RECOMMENDATION_SCORE INTEGER,
  STOCK INTEGER,
  PRIMARY KEY (PRODUCT_ID)
);

-- テーブルへのコメント
COMMENT ON TABLE PRODUCTS IS '商品情報テーブル';

-- カラムへのコメント
COMMENT ON COLUMN PRODUCTS.PRODUCT_ID IS '商品ID';
COMMENT ON COLUMN PRODUCTS.PRODUCT_NAME IS '商品名';
COMMENT ON COLUMN PRODUCTS.PRICE IS '価格';
COMMENT ON COLUMN PRODUCTS.SPECIAL_OFFER IS '特価フラグ';
COMMENT ON COLUMN PRODUCTS.POPULARITY_RANK IS '人気ランク';
COMMENT ON COLUMN PRODUCTS.ADDED_DATE IS '追加日';
COMMENT ON COLUMN PRODUCTS.RECOMMENDATION_SCORE IS 'おすすめスコア';
COMMENT ON COLUMN PRODUCTS.STOCK IS '在庫数';

-- データ挿入
INSERT INTO PRODUCTS( 
    PRODUCT_NAME,
    PRICE,
    SPECIAL_OFFER,
    POPULARITY_RANK,
    ADDED_DATE,
    RECOMMENDATION_SCORE,
    STOCK
) 
VALUES 
('りんご', 100, FALSE, 1, '2023-09-01', 90, 10),
('バナナ', 50, FALSE, 2, '2023-09-10', 85, 5),
('さくらんぼ', 200, TRUE, NULL, '2023-09-20', 70, 0),
('オレンジ', 150, FALSE, 3, '2023-10-01', 80, 20),
('ブルーベリー', 300, FALSE, 4, '2023-10-10', 75, 15),
('いちじく', 250, TRUE, NULL, '2023-10-15', 65, 3),
('ぶどう', 350, FALSE, 5, '2023-10-20', 60, 0),
('メロン', 400, FALSE, 6, '2023-10-25', 95, 8);

優先度を考慮したデータ取得の例

上記のテストデータをもとに、以下のようなデータを取得することを想定します。

ショッピングサイトを訪れたときにトップページに表示される商品を想定します。以下の条件でトップページに表示する商品を決めます。

1. 特価商品: 特価商品が存在し、その中に在庫が1つでもあれば、それを表示します。。
2. 新着商品: 特価商品に在庫がなければ、新着商品の上位3つを評価します。この3つの中に在庫が1つでもあれば、それを表示します。
3. 人気商品: 特価商品および新着商品に在庫がなければ、人気商品の上位3つを評価します。この3つの中に在庫が1つでもあれば、それを表示します。
4. おすすめ商品: 前述のカテゴリすべてに在庫がなければ、おすすめ商品の上位3つを評価します。この3つの中に在庫が1つでもあれば、それを表示します。

上記の条件で取得したSQLが以下になります。

WITH special_offer AS ( 
    SELECT
        *
        , '特価商品' AS category 
    FROM
        products 
    WHERE
        special_offer = TRUE 
) 
, new_arrivals AS ( 
    SELECT
        *
        , '新着商品' AS category 
    FROM
        products 
    WHERE
        special_offer = FALSE 
    ORDER BY
        added_date DESC 
    LIMIT 3
) 
, popular_products AS ( 
    SELECT
        *
        , '人気商品' AS category 
    FROM
        products 
    ORDER BY
        popularity_rank ASC 
    LIMIT 3
) 
, recommended_products AS ( 
    SELECT
        *
        , 'おすすめ商品' AS category 
    FROM
        products 
    ORDER BY
        recommendation_score DESC 
    LIMIT 3
) 
SELECT * FROM special_offer 
WHERE stock > 0

UNION ALL 
SELECT * FROM new_arrivals 
WHERE NOT EXISTS (SELECT 1 FROM special_offer WHERE stock > 0)
AND EXISTS (SELECT 1 FROM new_arrivals WHERE stock > 0)

UNION ALL 
SELECT * FROM popular_products 
WHERE NOT EXISTS (SELECT 1 FROM special_offer WHERE stock > 0)
AND NOT EXISTS (SELECT 1 FROM new_arrivals WHERE stock > 0)
AND EXISTS (SELECT 1 FROM popular_products WHERE stock > 0)

UNION ALL 
SELECT * FROM recommended_products
WHERE NOT EXISTS (SELECT 1 FROM special_offer WHERE stock > 0)
AND NOT EXISTS (SELECT 1 FROM new_arrivals WHERE stock > 0)
AND NOT EXISTS (SELECT 1 FROM popular_products WHERE stock > 0)
AND EXISTS (SELECT 1 FROM recommended_products WHERE stock > 0)

現状のデータの場合に上記を実行すると以下のようになります。

優先順位をつけて取得条件を絞るやり方

上記のSQLをもとに特定の条件に基づき、優先順位を設定して取得する方法について説明します。以下のステップでこの方法を実現できます。

1. 条件ごとのSQLの準備
   条件に合致するデータを取得するSQLをそれぞれ作成します。これにより、 それぞれの条件ごとにデータを分割し、管理しやすくします。この際、わかりやすさを重視するため、CTE（Common Table Expressions）の使用するといいでしょう。

   WITH priority1 AS { 
   -- 優先度1の条件で取得するSQL 
   }
   , priority2 AS { 
   -- 優先度2の条件で取得するSQL 
   }
   , priority3 AS { 
   -- 優先度3の条件で取得するSQL 
   }
   ... -- 条件の数だけ作成する

2. 優先順位の設定
   SELECT文とUNION ALLを組み合わせて、データを結合します。この際、SELECT文の順序がそのデータの優先順位を決定します。最初のSELECTで取得したデータが最も優先度が高く、次のSELECTはそれよりも低い優先度となります。

   SELECT * FROM priority1
   UNION ALL
   SELECT * FROM priority2 
   UNION ALL
   SELECT * FROM priority3 
   ... -- 条件の数だけ作成する

3. 上位優先順位データの除外
   各条件に合致するデータを取得する際、NOT EXISTSを利用して、より優先度が高い条件に合致するデータが存在する場合は、それを除外します。

   SELECT * FROM priority1
   UNION ALL
   SELECT * FROM priority2
   WHERE NOT EXISTS (SELECT 1 FROM priority1)
   UNION ALL
   SELECT * FROM priority2
   WHERE NOT EXISTS (SELECT 1 FROM priority1)
   AND NOT EXISTS (SELECT 1 FROM priority2)
   ... -- 条件の数だけ作成する

この方法を利用することで、優先度を考慮したデータの取得を行うことができます。

終わりに

本記事では、システム改修中に直面した優先度を考慮したデータ取得の問題とその解決策について紹介しました。今回の方法が参考になれば幸いです。