[SQL]カラムの部分一致を条件としたGROUP BY を行いたい

前提

以下のようなデータが存在するとします

Users

ID	name	age	point	zip_coe	prefecture	address
1	太郎	10	100	163-8001	東京都	新宿区西新宿
2	次郎	9	150	105-8511	東京都	港区芝
3	三郎	11	200	104-8404	東京都	中央区築地
4	四郎	12	250	192-8501	東京都	八王子市元本郷町
5	五郎	8	10	160-0022	東京都	新宿区新宿

実現したいこと

pointの合計値をaddressの区または市単位でGROUP BYして集計したいです
- イメージは以下の形です
  
  sum_point address
  110 新宿区
  150 港区
  200 中央区
  250 八王子市

sum_point	address
110	新宿区
150	港区
200	中央区
250	八王子市

試したこと

例えばprefectureを条件にする場合であれば簡単でした

sql
1  select sum(point) as sum_point, prefecture from `users` group by prefecture;

しかし、「addressの区または市単位でGROUP BY」する方法が知識が足りずわかりませんでした
- なんとなく、like句で「市または区を区切り文字にした前方一致」にてどうにかできるのかな？というアタリはあるのですが・・
そもそもこのような集計がSQLの技術的に可能であるのかすら不明なのですが、どなたか助力をお願いしたく投稿いたしました

補足情報（FW/ツールのバージョンなど）

MySQL 5.7

matukeso

2022/12/18 18:58

東京都西多摩郡檜原村とかは存在しない前提なの？

退会済みユーザー

2022/12/18 19:40 編集

タイトルが「[SQL]カラムの部分一致を条件としたGROUP BY を行いたい」なので、質問者さんにしか分かりませんが、地方自治体データを提示しているのは、例え話であって、目的は、カラムの文字列の一部をもとに GROUP 化したいという事かも知れません。本当に住所、郵便番号、市区町村、地方自治体などで GROUP BY したいのであれば、日本郵便のサイトからデータを取得して、郵便番号と地方公共団体コードを結び付けて集計すれば済む話なので・・・。 https://www.post.japanpost.jp/zipcode/dl/readme.html https://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

KOZ6.0

2022/12/18 19:33

「郵便番号データダウンロード」 https://www.post.japanpost.jp/zipcode/download.html 東京都のCSVデータをダウンロードすると、市、区の入った列があります。これと郵便番号を突き合わせるのが楽かなと思います。 # 郡は西多摩郡瑞穂町、西多摩郡日の出町という具合に町や村などが入っていますが、これはどう扱うのか決めてください。 address の中から「市」や「区」という文字を検索して・・・なんてことも考えられますが、「市川市」(東京都ではないですが)などの例外を考慮すると物凄く面倒なことになりそうです。

退会済みユーザー

2022/12/18 19:39

それと、下記のケースを考えると、住所を文字列ベースで考えるのは最適ではないと思います。 ※市が連続して存在するケース　野々市市・四日市市・静市市原町 ※町が連続して存在するケース　岩手県花巻市東和町町井・宮城県登米市豊里町町浦・秋田県能代市二ツ井町町尻上記は連続して存在しているケースだけであり、連続せずに複数存在するケースもあります。

KOZ6.0

2022/12/18 19:59

SQLでちゃちゃっと出来そうではないですね。「addressの区または市単位」で集計するのはあきらめたほうが良さそうですよ。＞yomigi さん