此前使用Shell Script抓取過中華人民共和國國家統計局公佈的行政區劃代碼 。具體見本人的Blog —— Grab Data About China Administrative Division Code Using Shell Script。此處利用抓取到的數據進行簡單的數據分析。

Overview

數據表中各行政級別的數據量

数据表名 说明 数据量
province 省份 31
city 地级市 346
country 3139
town 乡镇 40053
village 670453

一共5级,每一级都是次一级的外键(通过主键id),如省份地级市的外键,地级市的外键,依次类推。urbanruralCode是城乡分类代码,在village中有一个字段用于标注具体的城乡分类。

以下是SQL查詢

MariaDB [chinaCode]> select count(*) from province;
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.00 sec)

MariaDB [chinaCode]> select count(*) from city;
+----------+
| count(*) |
+----------+
|      346 |
+----------+
1 row in set (0.00 sec)

MariaDB [chinaCode]> select count(*) from country;
+----------+
| count(*) |
+----------+
|     3139 |
+----------+
1 row in set (0.05 sec)

MariaDB [chinaCode]> select count(*) from town;
+----------+
| count(*) |
+----------+
|    40053 |
+----------+
1 row in set (0.08 sec)

MariaDB [chinaCode]> select count(*) from village;
+----------+
| count(*) |
+----------+
|   670453 |
+----------+
1 row in set (0.11 sec)

MariaDB [chinaCode]>

以下是彙總查詢語句

select count(distinct(a.id)) as '省份', count(distinct(b.id)) as '地级市', count(distinct(c.id)) as '县', count(distinct(d.id)) as '乡镇', count(distinct(e.id)) as '村' \
from province a \
left join city b on a.id=b.provinceId \
left join country c on b.id=c.cityId \
left join town d on c.id=d.countryId \
left join village e on d.id=e.townId;

操作过程

MariaDB [chinaCode]> select count(distinct(a.id)) as '省份', count(distinct(b.id)) as '地级市', count(distinct(c.id)) as '县', count(distinct(d.id)) as '乡镇', count(distinct(e.id)) as '村' \
    -> from province a \
    -> left join city b on a.id=b.provinceId \
    -> left join country c on b.id=c.cityId \
    -> left join town d on c.id=d.countryId \
    -> left join village e on d.id=e.townId;
+--------+-----------+------+--------+--------+
| 省份   | 地级市    | 县   | 乡镇   | 村     |
+--------+-----------+------+--------+--------+
|     31 |       346 | 3139 |  40053 | 670453 |
+--------+-----------+------+--------+--------+
1 row in set (0.70 sec)

MariaDB [chinaCode]>

Summary Statistics

按省份分組獲取各級別行政級別數據

select a.name as '省份', count(distinct(b.id)) as '地级市', count(distinct(c.id)) as '县', count(distinct(d.id)) as '乡镇', count(distinct(e.id)) as '村' \
from province a \
left join city b on a.id=b.provinceId \
left join country c on b.id=c.cityId \
left join town d on c.id=d.countryId \
left join village e on d.id=e.townId \
group by a.id;

操作过程

MariaDB [chinaCode]> select a.name as '省份', count(distinct(b.id)) as '地级市', count(distinct(c.id)) as '县', count(distinct(d.id)) as '乡镇', count(distinct(e.id)) as '村'  from province a  left join city b on a.id=b.provinceId  left join country c on b.id=c.cityId  left join town d on c.id=d.countryId  left join village e on d.id=e.townId  group by a.id;
+--------------------------+-----------+-----+--------+-------+
| 省份                     | 地级市    | 县  | 乡镇   | 村    |
+--------------------------+-----------+-----+--------+-------+
| 北京市                   |         2 |  16 |    328 |  6860 |
| 天津市                   |         2 |  16 |    237 |  5354 |
| 河北省                   |        12 | 182 |   2194 | 51094 |
| 山西省                   |        11 | 130 |   1413 | 29933 |
| 内蒙古自治区             |        12 | 111 |    994 | 13165 |
| 辽宁省                   |        14 | 114 |   1526 | 15782 |
| 吉林省                   |         9 |  68 |    880 | 11043 |
| 黑龙江省                 |        13 | 140 |   1283 | 12070 |
| 上海市                   |         2 |  17 |    209 |  5648 |
| 江苏省                   |        13 | 112 |   1208 | 19565 |
| 浙江省                   |        11 | 101 |   1316 | 32257 |
| 安徽省                   |        16 | 121 |   1478 | 17368 |
| 福建省                   |         9 |  94 |   1102 | 16648 |
| 江西省                   |        11 | 111 |   1548 | 19985 |
| 山东省                   |        17 | 154 |   1784 | 79109 |
| 河南省                   |        18 | 176 |   2368 | 50081 |
| 湖北省                   |        14 | 115 |   1234 | 28819 |
| 湖南省                   |        14 | 135 |   2367 | 45292 |
| 广东省                   |        21 | 139 |   1533 | 24773 |
| 广西壮族自治区           |        14 | 124 |   1239 | 16088 |
| 海南省                   |         4 |  29 |    221 |  3044 |
| 重庆市                   |         2 |  38 |   1021 | 11073 |
| 四川省                   |        21 | 201 |   4652 | 53437 |
| 贵州省                   |         9 |  93 |   1387 | 18200 |
| 云南省                   |        16 | 137 |   1391 | 14218 |
| 西藏自治区               |         7 |  75 |    694 |  5464 |
| 陕西省                   |        10 | 117 |   1420 | 28737 |
| 甘肃省                   |        14 |  98 |   1355 | 17138 |
| 青海省                   |         8 |  47 |    406 |  4591 |
| 宁夏回族自治区           |         5 |  27 |    238 |  2721 |
| 新疆维吾尔自治区         |        15 | 101 |   1027 | 10896 |
+--------------------------+-----------+-----+--------+-------+
31 rows in set (0.53 sec)

MariaDB [chinaCode]>

Specific Province

查询某省下辖的各行政级别數據,此处以江苏省为例

select a.name as '省份', count(distinct(b.id)) as '地级市', count(distinct(c.id)) as '县', count(distinct(d.id)) as '乡镇', count(distinct(e.id)) as '村' \
from province a \
left join city b on a.id=b.provinceId \
left join country c on b.id=c.cityId \
left join town d on c.id=d.countryId \
left join village e on d.id=e.townId \
where a.name='江苏省';

操作过程

MariaDB [chinaCode]> select a.name as '省份', count(distinct(b.id)) as '地级市', count(distinct(c.id)) as '县', count(distinct(d.id)) as '乡镇', count(distinct(e.id)) as '村'  from province a  left join city b on a.id=b.provinceId  left join country c on b.id=c.cityId  left join town d on c.id=d.countryId  left join village e on d.id=e.townId  where a.name='江苏省';
+-----------+-----------+-----+--------+-------+
| 省份      | 地级市    | 县  | 乡镇   | 村    |
+-----------+-----------+-----+--------+-------+
| 江苏省    |        13 | 112 |   1208 | 19565 |
+-----------+-----------+-----+--------+-------+
1 row in set (0.02 sec)

MariaDB [chinaCode]>

Specific Town Blongs TO

查询某个乡镇所属的各行政级别,以中国十大经济强镇为例,数据来源:中国中小城市综合实力十强镇公布 江苏4个镇上榜

  1. 江苏昆山市玉山镇
  2. 江苏常熟市虞山镇
  3. 广东佛山市南海区狮山镇
  4. 广东广州市增城区新塘镇
  5. 江苏张家港市杨舍镇
  6. 广东佛山市南海区大沥镇
  7. 江苏苏州市吴江区同里镇
  8. 广东东莞市虎门镇
  9. 广东佛山市顺德区北滘镇
  10. 上海浦东新区金桥镇
select a.name as '省份', b.name as '地级市', c.name as '县', d.name as '乡镇', count(distinct(e.id)) as '下辖村个数' \
from province a \
left join city b on a.id=b.provinceId \
left join country c on b.id=c.cityId \
left join town d on c.id=d.countryId \
left join village e on d.id=e.townId \
where d.name in ('玉山镇','虞山镇','狮山镇','新塘镇','杨舍镇','大沥镇','同里镇','虎门镇','北滘镇','金桥镇') \
group by d.id \
order by d.name;

操作过程

MariaDB [chinaCode]> select a.name as '省份', b.name as '地级市', c.name as '县', d.name as '乡镇', count(distinct(e.id)) as '下辖村个数'  from province a  left join city b on a.id=b.provinceId  left join country c on b.id=c.cityId  left join town d on c.id=d.countryId  left join village e on d.id=e.townId  where d.name in ('玉山镇','虞山镇','狮山镇','新塘镇','杨舍镇','大沥镇','同里镇','虎门镇','北滘镇','金桥镇')  group by d.id  order by d.name;
+-----------+--------------------------------+--------------+-----------+-----------------+
| 省份      | 地级市                         | 县           | 乡镇      | 下辖村个数      |
+-----------+--------------------------------+--------------+-----------+-----------------+
| 广东省    | 佛山市                         | 顺德区       | 北滘镇    |              20 |
| 广东省    | 佛山市                         | 南海区       | 大沥镇    |              38 |
| 广东省    | 潮州市                         | 饶平县       | 新塘镇    |              14 |
| 广东省    | 广州市                         | 增城区       | 新塘镇    |              46 |
| 湖南省    | 衡阳市                         | 衡东县       | 新塘镇    |              34 |
| 江苏省    | 苏州市                         | 张家港市     | 杨舍镇    |              82 |
| 广东省    | 佛山市                         | 南海区       | 狮山镇    |              66 |
| 云南省    | 楚雄彝族自治州                 | 武定县       | 狮山镇    |              28 |
| 河南省    | 驻马店市                       | 遂平县       | 玉山镇    |              15 |
| 福建省    | 南平市                         | 建瓯市       | 玉山镇    |              13 |
| 江苏省    | 苏州市                         | 昆山市       | 玉山镇    |              76 |
| 四川省    | 巴中市                         | 恩阳区       | 玉山镇    |              40 |
| 山东省    | 临沂市                         | 临沭县       | 玉山镇    |              42 |
| 陕西省    | 西安市                         | 蓝田县       | 玉山镇    |              15 |
| 浙江省    | 金华市                         | 磐安县       | 玉山镇    |              22 |
| 贵州省    | 黔南布依族苗族自治州           | 瓮安县       | 玉山镇    |               8 |
| 江苏省    | 苏州市                         | 常熟市       | 虞山镇    |             112 |
| 四川省    | 遂宁市                         | 蓬溪县       | 金桥镇    |               7 |
| 上海市    | 市辖区                         | 浦东新区     | 金桥镇    |              14 |
| 重庆市    | 市辖区                         | 綦江区       | 金桥镇    |               7 |
| 湖南省    | 衡阳市                         | 祁东县       | 金桥镇    |              45 |
| 四川省    | 成都市                         | 双流县       | 金桥镇    |              13 |
+-----------+--------------------------------+--------------+-----------+-----------------+
22 rows in set (0.01 sec)

MariaDB [chinaCode]>

可以看到,在中国大陆,很多地方的乡镇名称是相同的。

将语句改写,各行政级别连在一起组成一条语句:

select concat(a.name,'.',b.name,'.',c.name,'.',d.name,' - ','其下辖行政村个数为: ',count(distinct(e.id))) as '以下是搜索结果'\
from province a \
left join city b on a.id=b.provinceId \
left join country c on b.id=c.cityId \
left join town d on c.id=d.countryId \
left join village e on d.id=e.townId \
where d.name in ('玉山镇','虞山镇','狮山镇','新塘镇','杨舍镇','大沥镇','同里镇','虎门镇','北滘镇','金桥镇') \
group by d.id \
order by d.name;

操作过程

MariaDB [chinaCode]> select concat(a.name,'.',b.name,'.',c.name,'.',d.name,' - ','其下辖行政村个数为: ',count(distinct(e.id))) as '以下是搜索结果'\
    -> from province a \
    -> left join city b on a.id=b.provinceId \
    -> left join country c on b.id=c.cityId \
    -> left join town d on c.id=d.countryId \
    -> left join village e on d.id=e.townId \
    -> where d.name in ('玉山镇','虞山镇','狮山镇','新塘镇','杨舍镇','大沥镇','同里镇','虎门镇','北滘镇','金桥镇') \
    -> group by d.id \
    -> order by d.name;
+-----------------------------------------------------------------------------------------------+
| 以下是搜索结果                                                                                |
+-----------------------------------------------------------------------------------------------+
| 广东省.佛山市.顺德区.北滘镇 - 其下辖行政村个数为: 20                                          |
| 广东省.佛山市.南海区.大沥镇 - 其下辖行政村个数为: 38                                          |
| 广东省.潮州市.饶平县.新塘镇 - 其下辖行政村个数为: 14                                          |
| 湖南省.衡阳市.衡东县.新塘镇 - 其下辖行政村个数为: 34                                          |
| 广东省.广州市.增城区.新塘镇 - 其下辖行政村个数为: 46                                          |
| 江苏省.苏州市.张家港市.杨舍镇 - 其下辖行政村个数为: 82                                        |
| 云南省.楚雄彝族自治州.武定县.狮山镇 - 其下辖行政村个数为: 28                                  |
| 广东省.佛山市.南海区.狮山镇 - 其下辖行政村个数为: 66                                          |
| 贵州省.黔南布依族苗族自治州.瓮安县.玉山镇 - 其下辖行政村个数为: 8                             |
| 河南省.驻马店市.遂平县.玉山镇 - 其下辖行政村个数为: 15                                        |
| 陕西省.西安市.蓝田县.玉山镇 - 其下辖行政村个数为: 15                                          |
| 江苏省.苏州市.昆山市.玉山镇 - 其下辖行政村个数为: 76                                          |
| 浙江省.金华市.磐安县.玉山镇 - 其下辖行政村个数为: 22                                          |
| 福建省.南平市.建瓯市.玉山镇 - 其下辖行政村个数为: 13                                          |
| 四川省.巴中市.恩阳区.玉山镇 - 其下辖行政村个数为: 40                                          |
| 山东省.临沂市.临沭县.玉山镇 - 其下辖行政村个数为: 42                                          |
| 江苏省.苏州市.常熟市.虞山镇 - 其下辖行政村个数为: 112                                         |
| 上海市.市辖区.浦东新区.金桥镇 - 其下辖行政村个数为: 14                                        |
| 重庆市.市辖区.綦江区.金桥镇 - 其下辖行政村个数为: 7                                           |
| 湖南省.衡阳市.祁东县.金桥镇 - 其下辖行政村个数为: 45                                          |
| 四川省.成都市.双流县.金桥镇 - 其下辖行政村个数为: 13                                          |
| 四川省.遂宁市.蓬溪县.金桥镇 - 其下辖行政村个数为: 7                                           |
+-----------------------------------------------------------------------------------------------+
22 rows in set (0.00 sec)

MariaDB [chinaCode]>

查询village名字中含有皇帝太后的村及其上级行政单位

select a.name as '省份', b.name as '地级市', c.name as '县', d.name as '乡镇', e.name as '村' \
from province a \
left join city b on a.id=b.provinceId \
left join country c on b.id=c.cityId \
left join town d on c.id=d.countryId \
left join village e on d.id=e.townId \
where e.name like '%皇帝%' \
group by e.id \
order by d.name;

操作过程

Fuzzy Search Example1

MariaDB [chinaCode]> select a.name as '省份', b.name as '地级市', c.name as '县', d.name as '乡镇', e.name as '村' \
    -> from province a \
    -> left join city b on a.id=b.provinceId \
    -> left join country c on b.id=c.cityId \
    -> left join town d on c.id=d.countryId \
    -> left join village e on d.id=e.townId \
    -> where e.name like '%皇帝%';
+-----------+-----------+-----------------------+--------------+--------------------------+
| 省份      | 地级市    | 县                    | 乡镇         | 村                       |
+-----------+-----------+-----------------------+--------------+--------------------------+
| 四川省    | 绵阳市    | 北川羌族自治县        | 都坝乡       | 皇帝庙村民委员会         |
| 河南省    | 郑州市    | 新密市                | 白寨镇       | 皇帝岭村委会             |
| 河南省    | 漯河市    | 临颍县                | 皇帝庙乡     | 皇帝庙村委会             |
+-----------+-----------+-----------------------+--------------+--------------------------+
3 rows in set (0.75 sec)

MariaDB [chinaCode]>

Fuzzy Search Example2

MariaDB [chinaCode]> select a.name as '省份', b.name as '地级市', c.name as '县', d.name as '乡镇', e.name as '村' \
    -> from province a \
    -> left join city b on a.id=b.provinceId \
    -> left join country c on b.id=c.cityId \
    -> left join town d on c.id=d.countryId \
    -> left join village e on d.id=e.townId \
    -> where e.name like '%太后%';
+-----------+-----------+-----------+--------------+--------------------+
| 省份      | 地级市    | 县        | 乡镇         | 村                 |
+-----------+-----------+-----------+--------------+--------------------+
| 北京市    | 市辖区    | 平谷区    | 王辛庄镇     | 太后村委会         |
| 山东省    | 烟台市    | 栖霞市    | 观里镇       | 王太后村委会       |
| 河南省    | 郑州市    | 登封市    | 大金店镇     | 太后庙村委会       |
+-----------+-----------+-----------+--------------+--------------------+
3 rows in set (0.77 sec)

MariaDB [chinaCode]>

Other Resource

之前在Viscovery的時候,自己對全家(FamilyMart)全民抓拍雞腿活動進行了簡單的數據分析,鏈接爲 https://github.com/LempStacker/DatabaseRelated/blob/master/MariaDB/Backup/LearningNotesBackup/2015.09.29_%E5%85%A8%E5%AE%B6(FamilyMart)%E2%80%9C%E5%85%A8%E6%B0%91%E6%8A%93%E6%8B%8D%E9%9B%9E%E8%85%BF%E2%80%9D%E6%B4%BB%E5%8B%95%E6%95%B8%E6%93%9A%E5%88%86%E7%B5%84%E5%8C%AF%E7%B8%BD.md


Change Log

  • 2016.06.19 10:48 Sun Asia/Shanghai
    • 初稿完成

  • Note Time: 2016.06.19 10:48 Sun
  • Note Location: Asia/Shanghai
  • Writer: lempstacker