鏈家廣州二手房數據 2023

還記得在2019年的夏天曾經用 R 爬過一份廣州在 lianjia.com 放盤數據 (博客1，博客2，博客3)。翻看當時的記錄：我稚嫩地驚嘆著廣州二手房放盤量已經超過50,000套了。爾後，疫情襲來，三年封鎖。這個夏天當我用 Python 再次爬 lianjia.com 廣州的放盤數據，卻坦然地接受超120,000套巨量放盤數量。

我分別在5月初和6月初各爬了一次，方便比較二手房數據的變化。如果以後還有時間和精力會繼續每月一更數據。

簡單地清洗了數據，其他分析很還沒做，就暫時分享一下 SQL 能看到的數字和趨勢吧。

全市平均總價和均價

SELECT 
strftime('%Y-%m', date) as Month
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price, 
ROUND(AVG(unit_price), 2) as Avg_Unit_Price
FROM gz
GROUP BY date

全市各區平均總價和均價

SELECT 
strftime('%Y-%m', date) as Month,
district as District,
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price, 
ROUND(AVG(unit_price), 2) as Avg_Unit_Price
FROM gz
GROUP BY district, date
ORDER BY Avg_Unit_Price DESC;

熱門區域平均總價和均價

SELECT 
strftime('%Y-%m', date) as Month,
position as Location,
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price,
ROUND(AVG(unit_price), 2) as Avg_Unit_Price,
MIN(total_price) as Min_Total_Price,
MIN(unit_price) as Min_Unit_Price
FROM gz 
WHERE position LIKE '珠江新城%' 
GROUP BY position, date

熱門小區平均總價和均價

SELECT 
strftime('%Y-%m', date) as Month,
region as Region,
COUNT(total_price) as Count, 
ROUND(AVG(total_price), 2) as Avg_Total_Price,
ROUND(AVG(unit_price), 2) as Avg_Unit_Price,
MIN(total_price) as Min_Total_Price,
MIN(unit_price) as Min_Unit_Price
FROM gz 
WHERE region LIKE '中海花城灣%'
GROUP BY date

小結

廣州各區的放盤量維持增長趨勢。強勢區（天河與越秀）二手房總體均價微漲，其餘各區二手房價格呈下跌趨勢。廣州二手房風向標區域珠江新城放盤量增加但價格下跌。網紅小區中海花城灣平均總價下降約250萬。總之，6月的房子比5月更不好賣了。

如果有需要數據的可以自取。如果覺得有用的請星標。 GitHub Link