likes
comments
collection
share

我也来爬一爬12306 - Day6 查询和分析今日我们基于已经爬取的信息，对数据库中的信息进行了基本的分析和研究，从而

作者站长头像
站长
· 阅读数 28

本文是《我也来爬一爬12306》系列文件中，第二天的内容，本系列文章的起始文章是：

概述

今天的任务比较简单和轻松愉快，算是一个成果检查。我们已经有了中国铁路系统很多关于车站、车次、列车、时刻方面的数据，就可以在其中开展一些有趣的查询和分析了。这些问题，应该也是经典的关于数据库应用的问题，特别值得初学数据库的读者了解和掌握。

在前面的操作和程序中，我们已经将相关的信息，从12306网站和接口中获取后，放在了一个SQLite3结果数据库文件当中。本章节的所有操作，都基于这个数据库和其中的数据进行展开。

前面我们已经提到，可以直接从官方上，下载sqlite3程序，并连接相关的数据库文件，就可以展开数据的操作和分析。这个连接正常建立后，就进入了一个交互式的命令行界面(以sqlite作为提示符)：

sqlite3 .\tldb.sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite>

sqlite> .tables
tl_regions   tl_schdule   tl_stations  tl_strains   tl_trainums

sqlite环境，有一套自己的操作指令，可以使用.help进行查询；也可以直接输入SQL语句，进行数据方面的操作。本文后续的SQL指令，都是以这种方式执行的。

数据查询和分析

笔者想到一些相关主题，可以在数据库中进行查询和分析。需要注意，下面的示例只是作为笔者现有爬取的数据，可能并不是最完整和权威的，但相关的分析查询操作方式本身，作为技术探讨的依据应该是没有太大问题的。

如果读者有其他的想法，可以在实现程序和数据库之后，自己构思并实践。

查询特定车次的时刻表

这是本项目的一个主要设计目标。因为在进行爬取操作之后，相关的数据应该都已经被录入数据库当中。现在，如果需要基于车次，对时刻表进行查询，就可以使用标准的SQL查询语句展开查询，相关参考代码和结果如下：


sqlite> select H.iorder,H.scode,S.name,H.atime from tl_schdule H
   ...> join tl_trainums T on T.trainum = H.trainum and T.tcode = 'G336'
   ...> join tl_stations S on S.scode = H.scode
   ...> order by iorder ;
iorder|scode|name|atime
1|NZQ|福田|400
2|IZQ|广州南|38
3|IIQ|英德西|78
4|SNQ|韶关|100
5|ICQ|郴州西|133
6|ZAQ|株洲西|191
7|CWQ|长沙南|210
8|MQQ|汨罗东|242
9|YIQ|岳阳东|267
10|WHN|武汉|320
11|OYN|信阳东|368
12|MDN|明港东|387
13|ZLN|驻马店西|406
14|LBN|漯河西|430
15|ZAF|郑州东|472
16|EGF|新乡东|498
17|HPP|邯郸东|538
18|EDP|邢台东|556
19|SJP|石家庄|601
99|BXP|北京西|678
Run Time: real 0.005 user 0.000000 sys 0.000000

当然，逻辑上而言，时刻表是和日期紧密结合的，每天都有一套时刻表，但我们简化了这个问题，只维持最新的时刻表。

中国铁路有多少办理客运业务的车站

sqlite> select count(1) scount from tl_stations
   ...> union all
   ...> select count(distinct(scode)) from tl_schdule;
scount
3340
2250

基于当前的数据，中国铁路有3340个车站(有电报码)，但只有2250在办理客运业务(出现在时刻表中)。

中国铁路有多少趟客运列车

sqlite>  select count(distinct(tcode)) tcount from tl_strains;

tcount
15740

基于当前的数据，中国铁路有15740趟客运列车(有有效车次)。但注意一趟列车来回，一般有不同的车次号。

中国铁路客运，有哪些类型的列车，分别有多少

sqlite> select substr(tcode,1,1) tcode, count(1) ccount from tl_trainums group by 1 order by 1;

tcode|ccount
1|4
2|4
4|69
5|26
6|68
7|54
8|32
C|2459
D|2554
G|4241
K|1214
S|747
T|146
Y|40
Z|214

按照中国客运列车的编码规则，可以进行相关的操作和分析。但也会发现，这个编码规则并不是特别严格，比如还有很多全数字的车次编码。然后，我们也可以来熟悉一下这个规则：

  • C 城际列车
  • D 动车
  • G 高铁
  • K 快车
  • S 市郊列车
  • T 特快
  • Y 旅游列车
  • Z 直达列车

哪些车站作为始发站、终点站和过路车站的车次最多

// 过路站(含起始)
sqlite>  with C as (select scode, count(1) tcount from tl_strains group by 1 order by 2 desc limit 10) select C.scode,S.name, C.tcount from C join tl_stations S on S.scode = C.scode ;
scode|name|tcount
BOP|北京东|1676
VBP|昌平北|1675
QIP|清河|1635
VAP|北京北|1620
FTP|北京丰台|1604
IFP|北京朝阳|1591
BJP|北京|1588
GBQ|广州北|1514
GXQ|广州西|1514
SPQ|石牌|1514

// 始发站 终点站
sqlite> with
   ...> C1 as ( select scode, count(1) filter(where iorder=1) scount from tl_schdule group by 1 order by 2 desc limit 5),
   ...> C2 as ( select scode, count(1) filter(where iorder=99) scount from tl_schdule group by 1 order by 2 desc limit 5),
   ...> C as (select '起' stype,* from C1 union all select '终', * from C2)
   ...> select C.stype, C.scode, S.name, C.scount from C join tl_stations S on S.scode = C.scode ;
stype|scode|name|scount
起|IZQ|广州南|318
起|AOH|上海虹桥|307
起|ICW|成都东|264
起|VNP|北京南|260
起|IOQ|深圳北|241
终|IZQ|广州南|318
终|AOH|上海虹桥|318
终|ICW|成都东|262
终|VNP|北京南|250
终|IOQ|深圳北|241

哪几趟车的停靠站最多，运营时间最长

sqlite> with C as (select trainum, count(1) scount from tl_schdule group by 1 order by 2 desc limit 5)
   ...> select S.tcode,S1.name startname, S2.name endname, C.scount from C
   ...> join tl_trainums S on S.trainum = C.trainum
   ...> join tl_stations S1 on S1.scode = S.startcode
   ...> join tl_stations S2 on S2.scode = S.endcode;
tcode|startname|endname|scount
K551|佳木斯|温州|49
K545|佳木斯|成都西|46
K997|海拉尔|成都西|45
K552|温州|佳木斯|45
K995|成都西|海拉尔|45

sqlite> with C as (select trainum, atime from tl_schdule where iorder = 99 order by 2 desc limit 5)
   ...> select S.tcode,S1.name startname, S2.name endname, C.atime from C
   ...> join tl_trainums S on S.trainum = C.trainum
   ...> join tl_stations S1 on S1.scode = S.startcode
   ...> join tl_stations S2 on S2.scode = S.endcode;
tcode|startname|endname|atime
K4915|乌鲁木齐|上海|3524
K4918|乌鲁木齐|上海|3524
K545|佳木斯|成都西|3523
K995|成都西|海拉尔|3382
Z263|拉萨|广州|3331

基于当前的数据，中国铁路客运，停靠车站数最多的是K551(佳木斯-温州)，共停靠49个站。运营时间最长的是K4915(乌鲁木齐-上海)，共运行3424分钟。遗憾的是，这个基础信息中，没有运营里程和运营线路的数据。不然的话，我们可以增加很多有趣的统计分析项目，例如运行速度、停车站距、线路密度等等。

除直达之外，哪趟列车的哪两个站点间的运行时间最长？

with
   ...> S as (select trainum tn from (select trainum, count(1) scount from tl_schdule group by trainum) where scount > 2),
   ...> A as (select trainum, scode, iorder, case iorder when 1 then 0 else atime end atime from tl_schdule S1 join S on tn = trainum),
   ...> D as (select trainum, scode, lag(scode) over (partition by trainum order by iorder) pscode , atime - lag(atime) over (partition by trainum order by iorder)  dtime from A )
   ...> select T.tcode, S1.name pname, S2.name, dtime, D.trainum from D
   ...> join tl_trainums T on T.trainum  = D.trainum
   ...> join tl_stations S1 on S1.scode  = D.pscode
   ...> join tl_stations S2 on S2.scode  = D.scode
   ...> where pscode is not null order by dtime desc limit 1;

Y117|银川|贵阳|1365|870000Y79300

这个查询问题的解决，可以看到sqlite虽然只是一个轻量级的文件数据库系统，也可以提供相当程度的数据分析功能，包括窗口函数。

最后说明一下，笔者在本文成文的时候，由于程序的问题，并没有完整的获取所有预想的数据，只是一部分数据，但这些数据不影响数据查询和分析的方式，所以笔者也将其列举在此。但需要理解这些信息并不完全正确和完整，毕竟我们并没有直接从12306的数据库中进行查询。

小结

今日我们基于已经爬取的信息，对数据库中的信息进行了基本的分析和研究，从而在更多的层面上，了解了中国铁路，和12306信息系统相关的业务和数据。

在下一日，我们将会对整个实践的过程进行总结和扩展思考。

转载自:https://juejin.cn/post/7402800227810689060
评论
请登录