SQL文をもっとよくしたい

Question

質問があるのですが、
以下のSQLはどのような書き方にすればもっとよくなるでしょうか？

ちなみにORマッパーはDomaを使っています

要件は以下です。
「ログインユーザーが所属する店舗(store_mst)あるいは、そのチェーン店(groupstore_mst)に紐づく商品分類タグ(tag_mst)で、その商品分類タグのWEB表示(tag_mst.display_flg = 1)件数が合わせて５件を超える店舗IDを取得する」

スキーマ構成としては以下です。

商品分類タグマスタ(tag_mst)
・tag_id(PK)
・store_id
・groupstore_id
・display_flg

店舗マスタ(store_mst)
・store_id(PK)
・groupstore_id
・store_name

チェーン店マスタ(groupstore_mst)
・groupstore_id(PK)
・groupstore_name

今SQLで以下ようなクエリを作成しています。Domaの書き方になっています。引数はログインユーザーのstoreIdです。

SELECT
   T5.store_id
FROM
  (
  SELECT
    T1.store_id,
    (
    SELECT COUNT(*)
    FROM
      tag_mst T2
    WHERE
      T2.store_id = T1.store_id AND T2.display_flg = 1
    GROUP BY
      T2.store_id
  ) +(
  SELECT COUNT(*)
  FROM
    tag_mst T3
  WHERE
    T3.groupstore_id =(
    SELECT
      T4.groupstore_id
    FROM
      store_mst T4
    WHERE
      T4.store_id = T1.store_id
  ) AND T3.display_flg = 1
) AS cnt
FROM
  tag_mst T1
WHERE
  store_id = /*storeId*/101
GROUP BY
  T1.store_id
) T5
WHERE
  cnt > 5
  

もう少し良い書き方がしたいのですが、アドバイス頂けないでしょうか？
よろしくお願い致します。

---

補足

CREATE TABLE `tag_mst` (
  `tag_id` int(11) NOT NULL,
  `store_id` int(11) DEFAULT NULL,
  `groupstore_id` int(11) DEFAULT NULL,
  `display_flg` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `store_mst` (
  `store_id` int(11) NOT NULL,
  `groupstore_id` int(11) DEFAULT NULL,
  `store_name` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('1', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('2', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('3', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('4', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('5', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('6', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('11', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('12', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('13', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('14', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('15', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('16', NULL, '201', '1');

INSERT INTO `store_mst` (`store_id`, `groupstore_id`, `store_name`) VALUES ('101', '201', 'name');

ALTER TABLE `store_mst` ADD UNIQUE(`store_id`);

---

補足2

伝わりづらいようなので要件の説明を追加します。

チェーン店で設定した商品分類タグはそれに紐づく店舗でも共通して画面表示されるので、チェーン店の共通タグと店舗個別のタグを足した合計が5件を超えるstore_idを取得したい

というものになります。回答者様のコメント欄にも詳しく記載してますので併せてご覧下さい。

Comments

[退会済みユーザー]

各テーブルのCREATE文とサンプルデータのINSERT文を提示してください。

[sazi]

そもそも動作するSQLじゃないし、要件にあるgroupstore_mstも出現していない。

[sazi]

失礼。groupstore_mstは不要そう

[k499778]

groupstore_mstは話の流れ上書きました。DDL,DML作成しました。

[sazi]

質問にあるSQLはエラーにはならないのですか？ from ()+()なんて記述は初めて見たのですが。

[k499778]

()+()の書き方は問題なく動きます

[sazi]

サブクエリーで件数加算というなら分かるけど from ()+()って・・・()の数もあって無いし、どこか記述が漏れてませんか？

[k499778]

数えましたが()の数が４つで漏れていないと思います

[退会済みユーザー]

提示されたCREATE,INSERTで提示されたそもそものSQLがエラーで動きません。正確な情報を提示してください。

[sazi]

()の数は合いましたが文法的にエラーですので、もう一度確認してください。
エラーのSQLだと、後学者に取ってこの記事自体が成り立たないので

[m.ts10806]

「よくする」の落としどころが明確になってないと永久に解決しない気がします。

[k499778]

スッキリしたSQL文にしたい。パフォーマンスチューニングをしたいですね。

[k499778]

今要件がうまく伝わっておらず、想定した結果が返ってきていないのでそれでBAつけあぐねています。saziさんのアドバイスの方向性でかなり満足しています。あとは想定した結果さえ取れれば。saziさんのコメントに悩みんでいるところの部分は記載しています。

[m.ts10806]

コメント部分だけだと共有されないので要件が変わってきている部分、修正事項については質問本文に反映しておくようにしてください(既にされているかもしれませんが、念のため）　もっといえば、タイトルは変えたほうがいいかも。

[arasi]

質問とはかんけいないが　InnoDB DEFAULT CHARSET=latin1　にしていて文字化けしないのか?

Answer 1

質問のSQLを分析します

SELECT T5.store_id
FROM (
  SELECT  T1.store_id
        , (
            SELECT COUNT(*) FROM tag_mst T2 
            WHERE T2.display_flg = 1 AND T2.store_id=T1.store_id  GROUP BY T2.store_id
          ) + (
            SELECT COUNT(*) FROM tag_mst T3 
            WHERE T3.display_flg = 1 AND T3.groupstore_id=(
              SELECT T4.groupstore_id FROM store_mst T4 WHERE T4.store_id = T1.store_id
            )
          ) 
        AS cnt
  FROM tag_mst T1
  WHERE store_id = /*storeId*/101
  GROUP BY T1.store_id
) T5
WHERE cnt > 5

指定した店舗の件数が>5の結果しか返却しないので、これでは要件満たせていませんね。

店舗マスタと商品分類マスタを結合(or条件がポイント）し、店舗別の共通分類を含めた一覧としてそれを集計する

select  str.store_id
from  store_mst str inner join tag_mst tag 
      on  str.groupstore_id=tag.groupstore_id or str.store_id=tag.store_id
where tag.display_flg = 1
  and str.groupstore_id in (
        select groupstore_id from store_mst where store_id=/*storeId*/101
      )
group by str.store_id
having count(*)>5

質問のSQLが要件を満たしている場合、指定した店舗のみの状況は件数のみで判断できるので、以下のようにすると簡潔になります。

select  count(*)
from  store_mst str inner join tag_mst tag 
      on  str.groupstore_id=tag.groupstore_id or str.store_id=tag.store_id
where tag.display_flg = 1
  and str.store_id=/*storeId*/101

Comments

[k499778]

回答ありがとうございます。またこちらでもいろいろ試してから返答致します。

[k499778]

一行目のstore_idをtag_mstから取得しているのでnullで返ってきてしまう場合があります。（groupstore_idで条件があった場合）
それに対してうまく対処する方法はないでしょうか？

[sazi]

データ確認していなかったのでちょっと待って下さい。

[k499778]

はい。ありがとうございます。
あと誤解を生んでしまったかもしれませんが、orのイメージでした。
(前)ログインユーザーが所属する店舗(store_mst)"と"そのチェーン店(groupstore_mst)に紐づく
(今)ログインユーザーが所属する店舗(store_mst)"あるいは"、そのチェーン店(groupstore_mst)に紐づく
少しわかりやすく書き換えました。ご配慮いただけると助かります。

[k499778]

パターン１に関しては現行のままだとエラーになり、以下のようにすると動きました。
ただ想定される101が返されず,nullが返されました。
ANYをつけました。
select store_id from tag_mst
where display_flg = 1
and groupstore_id = ANY(select groupstore_id from store_mst
where store_id=101)
group by store_id
having count(*)>5

[sazi]

要件として店舗IDを求めるとありますが、店舗IDは指定の条件なので、ややこしくなるだけなので、件数を返却するものとしました。その件数で判定すれば良いでしょうし。

[k499778]

phpMyAdminで実行しているのですがエラーになってしまいます。（#1242 - Subquery returns more than 1 row）
なのでANYをサブクエリの前に入れて実行しました。
想定通りの結果が返ってきています。
他のデータも入れて試してみます。

select store_id from tag_mst
where display_flg = 1
and store_id in (
select store_id from store_mst
where groupstore_id = ANY(
select groupstore_id from store_mst
where store_id=/*storeId*/5100
)
)
group by store_id
having count(*)>5

[sazi]

確認しているデータが違うからですね。
select groupstore_id from store_mst where store_id=/*storeId*/5100
これが複数件返却されるのですか？
店舗マスタで店舗IDでユニークじゃないって・・・

[sazi]

しかも、SQL変わってて、ORじゃなくなってるし・・・

[sazi]

ログインユーザーが所属する店舗が含まれるチェーン店のうち、商品件数が>5の店舗ということ？

[sazi]

コメントを見るとそのようなので、修正しました。

[k499778]

ALTER TABLE `store_mst` ADD UNIQUE(`store_id`);にしてデータを整理すると,ANYをつけなくてもエラーが起きなくなりました。質問の投稿にも追加しておきます。

ありがとうございます。

[k499778]

SQL文の方試してみたのですが、
想定の結果がやはり返ってきませんでした。
ログインユーザーの店舗IDに紐づくチェーン店IDのタグ(where tag_mst.groupstore_id= 201)が5件超える場合もその店舗IDを取得したいです。

例えば
where tag_mst.groupstore_id= 201 のものが6件、where tag_mst.store_id= 101 のものが0件 だとしても合計5件を超えるのでstore_id101を取得します。

今だと絞りに絞っているので、そういった場合取れてきません。
そこも考慮に入れて頂けると助かります。

[sazi]

tag_mstのstore_idが設定されているもの
・店舗ごとの商品分類
tag_mstのgroupstore_idが設定されているもの
・チェーン店の共通商品分類
に対して、指定した店舗の属するチェーン店で上記分類が>5の条件の店舗
（チェーン店共通商品分類＞５なら結局そのチェーン店店舗全て）
という仕様ですか？
上記仕様を確認する為にも、質問のSQLをエラーが無い状態にしてと言っているんですが

[k499778]

失礼しました。SQLを修正致しましたのでご確認頂ければと思います。

[k499778]

そうですね。仕様としてもそれで合っています。なのでチェーン店が5件超えていればそれに紐づく店舗IDは全て取得ですね。

チェーン店で設定した商品分類タグはそれに紐づく店舗でも共通して画面表示されるので、チェーン店の共通タグと店舗個別のタグを足した合計が5件を超えるstore_idを取得したいです。お手数ですがお願いできたらと思います。

[sazi]

全面的に回答修正

[k499778]

ありがとうございます。すごく助かりました。最後までお付き合い頂きありがとうございました。感謝しています

[sazi]

性能はどのくらい改善されました？

[k499778]

性能としてあまり大きな変化は見られましんでしたが、コードが圧倒的にスッキリしました。大量データのテストはまだなのでその時また性能を感じたいと思います。

Answer 2

SQLの実行にはSQLの中で使っているテーブルの定義が必要ですから、各テーブルのCREATE TABLE文を、動作確認できるだけの(今回は [件数が合わせて５件を超える店舗ID]) のデータのINSERT文も質問にあった方がコメントが付き易いです。

以下、質問を訂正されては？

2カ所全角の空白が入っています。

□T5.store_id
store_id = /storeId/□5100

同じテーブルを何度もSELECTするのなるべく避けた方がパフォーマンスが良いです。

今回の抽出条件に

store_id = 5100

とありますが、これは最初(インランビューの中で)に対象データをしぼった方がパフォーマンスが良いです。

メンテの為にもテーブルの別名であろうとどのテーブルであるかわかり易く

 tag_mst T2

          ↓

 tag_mst TM

などとされては？

Comments

[k499778]

回答ありがとうございます。またこちらでもいろいろ試してから返答差し上げたいと思います。

[Orlofsky]

全角の空白がもう1つ残っています。
> □T5.store_id
メモ帳でコードを書いているならサクラエディタを使っては？
http://monaski.hatenablog.com/entry/2015/06/06/232751

> `display_flg` int(11) DEFAULT '0'
数値はシングルクォートで囲まないでください。
INSERT文も直してください。

[k499778]

修正しました。ご指摘ありがとうございます。

[Orlofsky]

ちゃんと読んでください。
INSERT文が直っていません。