データが特殊で、集計作業に手間がかかっている

質問

顧客情報テーブルに対してクエリを流し、「利用している店」ごとの人数を出しています。

ここではテーブル名を「顧客情報テーブル」、「利用している店」のフィールド名を「店キー」とします。

流しているクエリは下記の通りです。

SELECT 店キー, COUNT(*)
FROM 顧客情報テーブル
GROUP BY 店キー;

この「店キー」は、値が入っている場合は、実在する企業名のみ登録されているようなので、値さえ入っていれば、店ごとの利用者がこのクエリで出せます。

しかしながら、私はシステムの中身まで分からないのでどのようになっているのか知りませんが、「店キー」に値がないレコードがかなりあります。
想像ですが、システムリリース初期のデータについてはそういうレコードがあるのと、マイナーな店については「店キー」が入っていないようです。
この場合、「店名」というフィールドの値を参照します。
「店名」は、ユーザーが自分で入力して登録する部分のようです。
基本的には実在する店の名前を入力してくれているようです。（こんな店ある？っと思ったら、ググってみたり）
ただ、お試しで使っているユーザーが「りんご商店」など、架空の店名を入れる場合も多いです。
で、下記のクエリを流して、店キーが空のレコードの、店名を得ています。

SELECT 店名, COUNT(*)
FROM 顧客情報テーブル
WHERE 店キー = ""
GROUP BY 店名;

ここからが大変で、実在する店とそうでない店を自力で分けて、最初のクエリで出した集計結果と合算しています。
実在しないのについては、まとめて「その他」扱いにしています。
また、実在する店の名前でも「○○商店 東京店」、「○○商店 神奈川店」のように、支店名が入力されている場合があるので、目で見て一つの
「○○商店」としてカウントし、最初のクエリ結果と合算しているわけです。

大変手間がかかるし、ミスするので、これを何とか一本のクエリで自動化できないものでしょうか？

CASE式を活用すればなんとかなりますでしょうか。

よろしくお願いします。

回答No.2

t_ohta · ベストアンサー

たぶん例外が多すぎてクエリでは無理でしょう。
プログラムを組んで正規表現を使ってマッチさせながらカウントすれば何とかなるかもしれませんが、根本的にデータ構造が不適切ですから、データ構造の見直しをするべきですね。

お礼

やはり厳しそうですね。
ありがとうございました

回答No.1

ushi2015

こんにちは
目で見て一つの「○○商店」としてカウント
のようなケースをクエリでというのは無理では？
（店名と支店名の間はスペースで、支店名の無い店名にはスペースが
　存在しないとかの判定条件が有れば別ですが。）
「店キー」と「店名」はユニークな組み合わせで全て実在し、
「店キー」が空の場合はまとめて「その他」扱いにして良いのなら、
SELECT IIf(IsNull([店キー]),"その他",[店キー]) AS キー, Count(*) AS 式1
FROM 顧客情報テーブル
GROUP BY IIf(IsNull([店キー]),"その他",[店キー]);
で、出来るかと思いますが、そういう訳ではないようですよね。

お礼

やはり厳しそうですね。
ありがとうございました