Google Cloudの新DBMS、AlloyDB for PostgreSQLを触ってみた Vol.6 (最終回)

SELECT EXTRACT(YEAR FROM transfer_date)  AS year,
       EXTRACT(MONTH FROM transfer_date) AS month,
       property_type,
       ROUND(AVG(price)::numeric, 2)     AS average_price
FROM land_registry_price_paid_uk
WHERE city = 'LONDON' AND transfer_date >= '2013-01-01'
GROUP BY year, month, property_type
ORDER BY year, month;

df1 = pd.read_sql(q1, engine)

# 年月の型変換
df1["year_month"] = df1["year"].astype(int).astype(str) + "-" + df1["month"].astype(int).astype(str)

# グラフ描画
fig1 = px.line(df1, x='year_month', y='average_price', color='property_type',
    title="Average Price Trend in LONDON by Property Types")

WITH maxprices AS (SELECT locality,
                          MAX(price) AS max_price
                   FROM land_registry_price_paid_uk
                   WHERE EXTRACT(YEAR FROM transfer_date) = 2000
                     AND city = 'LONDON'
                   GROUP BY locality)
SELECT t.locality,
       t.street,
       t.price AS max_price
FROM land_registry_price_paid_uk t
         JOIN maxprices m ON t.locality = m.locality AND t.price = m.max_price
WHERE EXTRACT(YEAR FROM t.transfer_date) = 2000

df2 = pd.read_sql(q2, engine1)
df2 = df2.sort_values(by="max_price", ascending=False).head(25)

# グラフ描画
fig2 = px.bar(df2, x="locality", y="max_price",
    title="Top 25 Priced Street in LONDON, Each Locality in 2000",
    text="street", labels={"max_price": "Price"})
fig2.update_layout(showlegend=False, xaxis_title="Locality")