链家广州二手房数据 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