SQL初心者の私が、早速やらかしました。
今回のQiita Tech Festa 2025 でSQLあるじゃん というノリでお題考えて使ってみたはいいものの、私が書いていたのはSQLiteでMySQLやPostgreSQLではなかった。。
切り替えようとは思ったもののあと2日
間に合うわけもなく、いまSQLiteのネタを書いてます。。。
🌀日本の市区町村名の面白パターン🌀
データベースの力で、市区町村の名前をちょっと違った視点で見てみたら…
春夏秋冬が入っている、ながーい名前、県をまたぐ街。SQLでいろいろ掘ってみました🇯🇵🌸
1.「春夏秋冬」が入っている地区
「季節感あふれる地名ってどれくらいあるの？」と思って探してみました。
district に「春」「夏」「秋」「冬」のいずれかが含まれる地名をSQLで抽出↓
SQL
"① 「春夏秋冬」が入っている地区":"""
SELECT prefecture, district
FROM districts
WHERE district LIKE '%春%'
OR district LIKE '%夏%'
OR district LIKE '%秋%'
OR district LIKE '%冬%'
ORDER BY district;
"""
👑 Ranking👑
| prefecture | district | |
|---|---|---|
| 1 | 秋田県 | 北秋田市 |
| 2 | 秋田県 | 北秋田郡上小阿仁村 |
| 3 | 秋田県 | 南秋田郡五城目町 |
| 4 | 秋田県 | 南秋田郡井川町 |
| 5 | 秋田県 | 南秋田郡八郎潟町 |
| 6 | 秋田県 | 南秋田郡大潟村 |
| 7 | 高知県 | 吾川郡春野町 |
| 8 | 新潟県 | 新潟市秋葉区 |
| 9 | 愛知県 | 春日井市 |
| 10 | 福岡県 | 春日市 |
| 11 | 埼玉県 | 春日部市 |
| 12 | 福岡県 | 田川郡香春町 |
| 13 | 福島県 | 田村郡三春町 |
| 14 | 秋田県 | 秋田市 |
| 15 | 山口県 | 美祢郡秋芳町 |
| 16 | 愛知県 | 西春日井郡春日町 |
| 17 | 愛知県 | 西春日井郡豊山町 |
2. 地区名の長さTOP10
「地名の長さ」を文字数でソートしてみました。
山梨の「南都留郡富士河口湖町」、見ただけで息切れするボリューム感。
SQL
"② 地区名の長さTOP10": """
SELECT prefecture, district, LENGTH(district) as len
FROM districts
ORDER BY len DESC
LIMIT 10;
""",
👑 Ranking👑
| rank | prefecture | district | len |
|---|---|---|---|
| 1 | 山梨県 | 南都留郡富士河口湖町 | 10 |
| 2 | 秋田県 | 北秋田郡上小阿仁村 | 9 |
| 3 | 山梨県 | 西八代郡市川三郷町 | 9 |
| 4 | 長野県 | 下高井郡野沢温泉村 | 9 |
| 5 | 大阪府 | 南河内郡千早赤阪村 | 9 |
| 6 | 和歌山県 | 東牟婁郡那智勝浦町 | 9 |
| 7 | 香川県 | 仲多度郡まんのう町 | 9 |
| 8 | 愛媛県 | 上浮穴郡久万高原町 | 9 |
| 9 | 長崎県 | 南松浦郡新上五島町 | 9 |
| 10 | 北海道 | 樺戸郡新十津川町 | 8 |
3. 都道府県別の地区数TOP5
シンプルに「市区町村の多い県は？」を調べました。
北海道がダントツです（広すぎるので当然…？）
SQL
"③ 都道府県別の地区数TOP5": """
SELECT prefecture, COUNT(DISTINCT district) as district_count
FROM districts
GROUP BY prefecture
ORDER BY district_count DESC
LIMIT 5;
""",
👑 Ranking👑
| rank | prefecture | district_count |
|---|---|---|
| 1 | 北海道 | 189 |
| 2 | 長野県 | 81 |
| 3 | 愛知県 | 81 |
| 4 | 福岡県 | 80 |
| 5 | 埼玉県 | 80 |
4. 都道府県をまたぐ同じ地区名
意外とある「同じ名前の市」。実際に都道府県をまたいで存在する地名を探すとどうなるか。
SQL
"④ 都道府県をまたぐ同じ地区名": """
SELECT district, GROUP_CONCAT(DISTINCT prefecture) as prefectures, COUNT(DISTINCT prefecture) as prefecture_count
FROM districts
GROUP BY district
HAVING prefecture_count > 1;
"""
👑 Ranking👑
| rank | district | prefectures | prefecture_count |
|---|---|---|---|
| 1 | 伊達市 | 北海道,福島県 | 2 |
| 2 | 府中市 | 東京都,広島県 | 2 |
最後に
「なんとなく見てた市区町村名にも、こんな面白さがあるんだ」と思える発見でした。
SQLの勉強がちょっと楽しくなる、教材としてもおすすめです✨
ーー使用データと環境ーー
・使用環境：Google Colab + SQLite + pandas
・使用データ：
全体コードはこちら↓
python
import pandas as pd
import sqlite3
from collections import Counter
from google.colab import files
# 日本語カラム名を英語にリネーム
rename_dict = {
"地区": "district",
"コード": "code",
"都道府県": "prefecture",
"都道府県フリ": "prefecture_kana"
}
df = pd.read_csv("都道府県市区町村data.csv")
df.rename(columns=rename_dict, inplace=True)
# districtカラムが文字型でない場合に備えて型変換
df["district"] = df["district"].astype(str)
conn = sqlite3.connect(":memory:")
df.to_sql("districts", conn, index=False, if_exists="replace")
queries = {
"① 「春夏秋冬」が入っている地区":"""
SELECT prefecture, district
FROM districts
WHERE district LIKE '%春%'
OR district LIKE '%夏%'
OR district LIKE '%秋%'
OR district LIKE '%冬%'
ORDER BY district;
""",
"② 地区名の長さTOP10": """
SELECT prefecture, district, LENGTH(district) as len
FROM districts
ORDER BY len DESC
LIMIT 10;
""",
"③ 都道府県別の地区数TOP5": """
SELECT prefecture, COUNT(DISTINCT district) as district_count
FROM districts
GROUP BY prefecture
ORDER BY district_count DESC
LIMIT 5;
""",
"④ 都道府県をまたぐ同じ地区名": """
SELECT district, GROUP_CONCAT(DISTINCT prefecture) as prefectures, COUNT(DISTINCT prefecture) as prefecture_count
FROM districts
GROUP BY district
HAVING prefecture_count > 1;
"""
}
for title, query in queries.items():
print(f"== {title} ==")
result_df = pd.read_sql(query, conn)
result_df.index = result_df.index + 1 # インデックスを1からにする
display(result_df)