import duckdb
import pandas as pd
from sklearn.datasets import fetch_california_housing
# データロード
data = fetch_california_housing(as_frame=True)
df = data.frame
# DuckDBに接続
con = duckdb.connect()
# DataFrameを登録
con.register("housing", df)
データの準備が整ったら DuckDBでSQLを実行してみましょう。
1.平均住宅価格
sql = """
SELECT
AVG(MedHouseVal) as avg_price
FROM housing
"""
print(con.sql(sql).df())
2.住宅価格ランキング
sql = """
SELECT
MedHouseVal
FROM housing
GROUP BY MedHouseVal
ORDER BY MedHouseVal DESC
LIMIT 10
"""
print(con.sql(sql).df())
3.地域ごとの住宅価格
sql = """
SELECT
ROUND(Latitude,1) as lat,
ROUND(Longitude,1) as lon,
AVG(MedHouseVal) as avg_price,
COUNT(*) as houses
FROM housing
GROUP BY lat, lon
ORDER BY avg_price DESC, houses DESC
LIMIT 15
"""
print(con.sql(sql).df())
4.収入と住宅価格
sql = """
SELECT
CASE
WHEN MedInc < 2 THEN 'low'
WHEN MedInc < 5 THEN 'middle'
ELSE 'high'
END as income_level,
AVG(MedHouseVal) as avg_price,
COUNT(*) as cnt
FROM housing
GROUP BY income_level
ORDER BY avg_price DESC
"""
print(con.sql(sql).df())
5.家の広さと価格
sql = """
SELECT
ROUND(AveRooms,0) as rooms,
AVG(MedHouseVal) as price,
COUNT(*) as n
FROM housing
GROUP BY rooms
HAVING n > 50
ORDER BY rooms
"""
print(con.sql(sql).df())
応用編
海沿い住宅は高いのかどうか
カリフォルニアは 西側が海なので
経度(Longitude) で判定
sql = """
SELECT
CASE
WHEN Longitude < -121 THEN 'coastal'
ELSE 'inland'
END AS location_type,
AVG(MedHouseVal) AS avg_house_price,
COUNT(*) AS houses
FROM housing
GROUP BY location_type
"""
result = con.sql(sql).df()
print("海沿い住宅(coastal) vs 内陸住宅(inland)")
print(result)
