MySQL レコード単位で検索条件を変える副問い合わせ

はじめに

業務でレコード毎に異なる期間で取得したい要望があって対応しました。対応内容を簡略化して紹介します。

要件等

ユーザと要件

• ユーザ
  • A 県にある B 社は食品を販売している会社です。
• 要件
  • 月毎に各店舗の売り上げをリアルタイムに算出したい
  • ただし、期間限定出店している店舗は出店日から該当月末までの売り上げを集計したい
  • 店舗と出店期間は以下である
    • 本店： 常設
    • ECサイト：常設
    • Cデパート物産展：期間限定（2022/4/29～2022/5/10）
• 表示イメージ（5月の売り上げ）を表1に示す

テーブル構成

ER 図を図1に示す。

対応

日時売り上げで取得する範囲が店舗に行って異なることがめんどくさいですが、副問い合わせを用いてレコード単位で条件を変えてしまいましょう。

SELECT name, shop_type, open_date, close_date, SUM(proceeds) AS proceeds
FROM shops
INNER JOIN daily_results ON daily_results.shop_id = shops.id
WHERE date <= "2022-5-31"
AND date >= (
  SELECT
    CASE
      WHEN open_date IS NULL THEN "2022-5-1"
      ELSE open_date
    END
  FROM shops
)
GROUP BY ships.id

副問い合わせで各 shops ごとに開始日を取得し、その値を使って日時 daily_results.date をレコード毎に検索する感じになります。

おわりに

今回は簡略化した構成で記載しているので簡易ですが、実際は図2のような構成でエリア単位で集計したいとか結構複雑なテーブル構成です。

そもそも常設と期間限定を同じ期間レンジ扱いで処理しようとしているのが根本ではあるんですが、業務上いるといわれたらどうしようもないですし