statistics.md

这件事情的最终目的还是想从二手房的一些宏观的统计数据上分析整体的走势，因此设计一些合理的科学的统计指标并且随着时间的推移观察指标的变化很有意义。下面是当前想到的一些指标，后面还会不断补充。

数据统计指标

均价（平均房价/平均面积）

这个指标无法准确判断调价的走势，因为每次都有新房源插入，算是一个比较宏观粗略的统计，一定程度上反映单价均价的走势，排除了一些不感兴趣的豪宅（没钱）

统计口径：

select date_format(now(),'%y-%m-%d') as time,count(id) as house_num, avg(total_price), avg(total_square), avg(total_price+price_diff)/avg(total_square) as cur_avg_price from lj_house where total_price<800 and total_square<200;

统计结果：

+----------+-----------+------------------+-------------------+---------------+
| time     | house_num | avg(total_price) | avg(total_square) | cur_avg_price |
+----------+-----------+------------------+-------------------+---------------+
| 17-11-26 |      6522 |         336.8329 |           88.9405 |    3.78517765 |
+----------+-----------+------------------+-------------------+---------------+
| 17-11-27 |      6617 |         336.2847 |           88.8688 |    3.78178881 |
+----------+-----------+------------------+-------------------+---------------+
| 17-11-28 |      6700 |         336.5161 |           88.8549 |    3.78492864 |
+----------+-----------+------------------+-------------------+---------------+
| 17-11-29 |      6907 |         335.7475 |           88.7398 |    3.78074352 |
+----------+-----------+------------------+-------------------+---------------+
| 17-11-30 |      7070 |         335.4209 |           88.6362 |    3.78078314 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-01 |      7189 |         335.0300 |           88.5010 |    3.78177054 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-02 |      7260 |         334.9988 |           88.4811 |    3.78216706 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-03 |      7312 |         335.0729 |           88.4483 |    3.78367923 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-04 |      7362 |         335.0687 |           88.4465 |    3.78342392 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-06 |      7603 |         334.5679 |           88.2901 |    3.78325860 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-08 |      7867 |         333.9583 |           88.0678 |    3.78485456 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-10 |      7975 |         334.0636 |           88.0939 |    3.78379871 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-16 |      8455 |         334.0391 |           87.8891 |    3.79049444 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-23 |      9073 |         332.8165 |           87.5568 |    3.78762417 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-27 |      9374 |         332.9133 |           87.4899 |    3.79043322 |
+----------+-----------+------------------+-------------------+---------------+
| 17-12-30 |      9693 |         332.3667 |           87.3863 |    3.78807605 |
+----------+-----------+------------------+-------------------+---------------+
| 18-01-06 |     10175 |         332.1026 |           87.3262 |    3.78577876 |
+----------+-----------+------------------+-------------------+---------------+

调价均值（只统计调过价的房子）

这个指标一定程度上能反映卖家的心理预期，可以适当排除一些调价幅度过大（大于50）且挂牌时间超过半年的业主，感觉这部分业主不诚心卖，有扰乱市场之嫌

统计口径：

select date_format(now(),'%y-%m-%d') as time, count(id) as house_num,avg(price_diff),avg(total_square), avg(TIMESTAMPDIFF(DAY,sale_date,now())) as avg_sale_days from lj_house where abs(price_diff)>0 and abs(price_diff)<50 and TIMESTAMPDIFF(DAY,sale_date,now())<180;

统计结果：

+----------+-----------+-----------------+-------------------+---------------+
| time     | house_num | avg(price_diff) | avg(total_square) | avg_sale_days |
+----------+-----------+-----------------+-------------------+---------------+
| 17-11-26 |       317 |         -5.1767 |           88.1767 |       55.4700 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-11-27 |       391 |         -5.1483 |           87.7749 |       55.8926 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-11-28 |       426 |         -5.4601 |           88.8474 |       56.1901 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-11-29 |       488 |         -5.6516 |           89.2480 |       56.0492 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-11-30 |       565 |         -5.9274 |           89.7469 |       55.2832 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-01 |       605 |         -6.1835 |           89.5025 |       56.6347 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-02 |       657 |         -5.9224 |           88.9848 |       56.0183 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-03 |       731 |         -5.9658 |           88.4802 |       55.9056 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-04 |       776 |         -5.7784 |           89.0000 |       56.9253 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-06 |       881 |         -5.9376 |           88.6935 |       58.0942 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-08 |      1020 |         -5.9314 |           88.1735 |       58.8382 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-10 |      1137 |         -6.3668 |           87.9894 |       60.4072 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-16 |      1358 |         -6.6421 |           87.7040 |       64.3078 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-23 |      1718 |         -6.9604 |           86.9779 |       68.3423 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-27 |      1901 |         -7.0058 |           86.8096 |       69.7512 |
+----------+-----------+-----------------+-------------------+---------------+
| 17-12-30 |      2046 |         -7.0010 |           87.0728 |       71.4951 |
+----------+-----------+-----------------+-------------------+---------------+
| 18-01-06 |      2288 |         -7.1525 |           86.6849 |       75.7937 |
+----------+-----------+-----------------+-------------------+---------------+

每日数据变化

这个指标包含每天新增房源个数、每天调价房源个数（分为上调和下调）、每天调价均值，可以粗略反映每日的趋势

+----------+-----------+----------------+-------------------+---------------+
| time     |insert_num |  diff_up_num   |   diff_down_num   | avg_diff_price|
+----------+-----------+----------------+-------------------+---------------+
| 17-11-28 |      87   |        13      |         34        |      7.5      |
+----------+-----------+----------------+-------------------+---------------+
| 17-11-29 |     220   |        14      |         60        |     -7.4      |
+----------+-----------+----------------+-------------------+---------------+
| 17-11-30 |     170   |        21      |         73        |     -6.0      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-01 |     126   |         9      |         44        |     -6.2      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-02 |      75   |        21      |         37        |     -1.4      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-03 |      55   |        30      |         72        |     -4.0      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-04 |      59   |        15      |         43        |     -6.0      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-06 |     254   |        40      |         103       |     -5.6      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-08 |     272   |        45      |         135       |     -4.6      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-10 |     112   |        21      |         131       |     -7.3      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-16 |     498   |        55      |         251       |     -7.9      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-23 |     651   |        120     |         436       |     -6.9      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-27 |     317   |        54      |         232       |     -6.6      |
+----------+-----------+----------------+-------------------+---------------+
| 17-12-30 |     334   |        56      |         188       |     -5.9      |
+----------+-----------+----------------+-------------------+---------------+
| 18-01-06 |     502   |        90      |         336       |     -7.2      |
+----------+-----------+----------------+-------------------+---------------+

各区域调价排行

反映不同区域的变化情况，同样排除了一些数据

select date_format(now(),'%y-%m-%d') as time, area, count(id), avg(price_diff)  from lj_house where abs(price_diff)>0 and abs(price_diff)<50 and TIMESTAMPDIFF(DAY,sale_date,now())<180  group by area order by avg(price_diff);

+----------+--------+-----------+-----------------+
| time     | area   | count(id) | avg(price_diff) |
+----------+--------+-----------+-----------------+
| 17-11-30 | 拱墅   |        74 |         -7.4730 |
| 17-11-30 | 西湖   |       137 |         -6.9270 |
| 17-11-30 | 下城   |        74 |         -6.0405 |
| 17-11-30 | 江干   |       134 |         -5.5075 |
| 17-11-30 | 上城   |        37 |         -4.9189 |
| 17-11-30 | 下沙   |        71 |         -4.7606 |
| 17-11-30 | 滨江   |        38 |         -3.7368 |
+----------+--------+-----------+-----------------+

+----------+--------+-----------+-----------------+
| time     | area   | count(id) | avg(price_diff) |
+----------+--------+-----------+-----------------+
| 17-12-10 | 西湖   |       251 |         -7.5378 |
| 17-12-10 | 江干   |       223 |         -6.5650 |
| 17-12-10 | 下城   |       176 |         -6.2273 |
| 17-12-10 | 下沙   |       148 |         -6.0811 |
| 17-12-10 | 拱墅   |       174 |         -6.0632 |
| 17-12-10 | 滨江   |        92 |         -5.1413 |
| 17-12-10 | 上城   |        73 |         -4.9178 |
+----------+--------+-----------+-----------------+