基于SQL的铁路同行人员查询实现_杨雁莹.pdf

2023.4电脑编程技巧与维护特征分类特征描述释义旅客个人信息特征群Name姓名id_type证件类型id_no身份证号1概述共同犯罪是常见的犯罪形式之一，包括一个或几个隐蔽较深的核心成员，他们往往反侦察能力较强，避免多种留下痕迹的事情。但大数据时代，用数据说话已成为人们的共识，将侦查思维应用于数据分析，通过发掘数据之间的关系，由外围人员牵连暴露出隐秘人员，是一个重要的尝试。铁路是客运行业的支柱形式。在铁路公安侦破的刑事案件中，侵财类犯罪（铁路系统自身财产及旅客的随身财务）、危害铁路运输安全类犯罪（非法携带枪支、弹药、管制刀具、危险物品危及公共安全罪、破坏交通设施罪等）、涉毒犯罪这3种类型在铁路违法犯罪案件中占比较高，也是铁路部门日常打击的重点对象1。以上3种都属于共同犯罪高发的犯罪类型，除了在列车上实施犯罪，犯罪分子还经常借助铁路流窜作案，因此，对铁路数据的分析非常必要。2分析基础2.1数据说明常规列车车票的信息中包括姓名、证件类型、身份证号、乘车日期、发车时间、车次号、始发站、终点站、车厢号、座位类型、座位号、票号、票种、售票站点号、取票窗口号、售票时间16个属性字段。为了对字段有更加深层次、更加清晰的了解，可将数据的属性字段分为3种信息特征群，即旅客个人信息特征群、旅客行为信息特征群及铁路信息特征群，这有利于从不同特征群中筛选可用的字段，支持后续的分析。3种信息特征群分别对应的特征描述及释义如表1所示。2.2同行人员的判定规则铁路部门的购票方式主要有两种：互联网线上购票和火车站、代售点购票。经过对互联网线上购票和火车站、代售点购票的实际操作进行分析可以发现，同行人员通过购票软件进行网上购票时，常由一人统一购票；采用线下代售点购票的方式时，常由一人统一购票或选择同一窗口前后排队购买。通过对售票数据中同行人员乘车信息进行深入分析，可以发现以下特征。（1）互联网线上购票同行人购票数据特征。若同行关系人的车票由同一人一次性进行互联网线上购票，则所出车次、发站到站相一致且票号必定相连，此时的票号为7位数字。因为互联网线上购票不考虑出票所需时间，所以购票时间也相同，即为提交订单时间。此时同行人可描述为“车次相同+票号相连时间相同”。基金项目：公安部科技计划项目基于大数据的警务人员精准需求研究（2018LLYJSLGA018）。作者简介：杨雁莹（1973），女，副教授，硕士，研究方向为数据库和数据挖掘；王丹宏（2001），女。基于 SQL 的铁路同行人员查询实现杨雁莹1，王丹宏2（1.南京森林警察学院，南京210023；2.国家税务总局惠州大亚湾经济技术开发区税务局，广东 惠州516081）摘要：基于铁路的票务特征，快速确定铁路同行关系人员对于抓捕犯罪团伙人员、挖掘隐藏核心人物有积极意义。根据现实应用，对已知重点人员的同行人员进行查询确定，对未指定重点人员的大范围同行人员进行筛查，提出了同行人员的判定方法，并利用 SQL 进行查询，有助于打击共同犯罪，提升办案效率。关键词：同行关系人；共同犯罪；SQL 应用表13种信息特征群分别对应的特征描述及释义特征分类特征描述释义旅客行为信息特征群coach_no车厢号seat_no座位号ticket_no票号seat_type_code座位类型ticket_type票类铁路信息特征群train_date乘车日期start_time发车时间board_train_code车次号from_station_name始发站to_station_name终点站office_no售票站点号window_no取票窗口号sale_time售票时间续表87DOI:10.16184/prg.2023.04.0542023.4电脑编程技巧与维护（2）火车站、代售点购票同行人数据特征。若同行关系人的车票为火车站、代售点购票，则票号相连，此时票号为1位英文字母+6位数字，车次一致且售票时间相差短。数据库中显示的售票时间为生成打印车票时间，打印一张车票时间约需5s。根据实际观测和车站数据统计来看，每人次购票所需时间一般在2min内。此时同行人员可描述为“车次相同+票号相连+售票时间相近”。两种购票方式的差异点在于售票时间是相近或相同。为简化实际操作，可将两种思路结合，选择售票时间间隔极短，即将同行人员特征描述为“车次相同+票号相连+售票时间相近”，则可同时满足两种购票方式。在现实应用中，同行人员的判定还需要满足次数的条件，即满足上述同行人特征N次，则可基本确定为同行人。2.3查询优化铁路出行数据是标准的格式化数据，利用SQL进行管理查询准确、高效。目前市场上常见的关系数据库管理系统有Oracle、MySQL、SQL Server等，其核心都是SQL，代码基于SQL Server实现，稍加修改也可适用于其他主流关系数据库管理系统。在实际应用中，铁路旅客乘车数据量十分庞大，为提高查询效率，可利用索引和表分区进行优化。在前面设 定 的 规 则 中，主 要 运 用 的3个 字 段 为 身 份 证 号（id_no）、售票时间（sale_time）和票号（ticket_no），因此基于此3个字段创建3个非聚集索引。同行人查找主要依托售票时间这一字段展开，并且在资料搜集的过程中发现，根据时间进行分区是较为常见且合理的选择。因此，选取售票时间对表进行分区，以月为分区单位，每个分区中存储一个月的数据，这样可有效降低查询耗时。3应用场景及代码实现3.1查询已知人员的同行人在现实应用中，如果已掌握共同犯罪中的某一成员，则可利用该重点人员铁路出行信息，有针对性地查询其同行人员，也可理解为针对选定的车次分析其同行关系人员。此时车次已确定，故不作为变量进行分析，只需设定票号与售票时间的规则即可。通过经验访谈得知，无论是互联网线上购票还是火车站代售点购票，每人每次最多购票5张且票号相连，同时，每人次取票时间控制在2 min内。因此，可以设定规则“票号差值小于5，售票时间差值在2 min内”。选定车次分析重点人员的同行关系人员可以拆解为两个部分，第1个部分将实现手动输入参数时比对同行关系人，以提供自主查询功能；第2个部分则无需手动输入参数，通过重点人员库与乘车数据库进行自动比对，碰撞出重点人员信息后实现对同行关系人员的查找，具体实现途径如下。（1）手动输入参数查询。当输入3个调用参数“乘车日期身份证号车次号”后，可确定唯一的乘车数据。提取对应乘车数据的车次号、售票时间、票号放入变量中作为输入对象。在实际应用中，若购票者通过互联网进行线上购票并在站点进行取票时，会生成两条乘车数据。在这种情况下，参照互联网线上购票情况寻找同行人员是较为准确的。因此，在分析时需要根据售票时间进行倒序排列，提取最靠前的记录。重点代码如下。Select cc=board_train_code,saletime=sale_time,ticketno=ticket_no from data_ticket where id_no=id_no and train_date=dte and board_train_code=traincode order by sale_time desc获取输入信息后进入下一步，通过限制规则查询符合限制条件的人员，输出的结果即为识别的同行人员。限制规则的重点代码如下。select*from data_ticket where train_date=dte andboard_train_code=ccand sale_time between dateadd(mi,-2,saletime)anddateadd(mi,2,saletime)and abs(convert(int,right(tick-et_no,6)-convert(int,right(ticketno,6)1)从大框架上看，第12行属于外层查询；第36行属于内层查询。在外层查询中，第1行限定乘车日期和车次号与输入变量保持一致；第2行使用substring函数，将售票时间前15位及票号前5位组成字符串，整体字符串相同的为一组分组。在内层查询中，第34行选出乘车日期和车次号与输入变量保持一致的所有数据，再次使用substring函数将售票时间前15位及票号前5位组成字符串；第5行对该字符串进行分组；第6行统计该分组在整个表中出现的次数，筛选出现次数大于1次的相关数据，因为次数大于1次，则证明存在同行数据。4分析效果评定4.1准确性购票方式主要分为互联网线上购票和火车站、代售点购票两种。由于互联网购票识别出的群体一定是由同一人统一购票产生的，团体成员间必然存在关系网，识别的精准度接近100%，仅有识别代火车站售点购票时存在误差。但随着互联网特别是手机APP的普及，目前互联网线上购票占比远超火车站代售点购票，进一步保证了分析的准确性。4.2时效性借助某公安处管内售票数据进行同行人员查询的响应时间测试，其数据库表内约有乘车数据8 000万条，查询速度维持在秒级内。由于SQL针对关系数据表查询的高效性及索引、文件分区设置的合理性，表明运用此方法进行同行人员分析具有较高的时效性。5结语相比一般的案件，共同犯罪案件不仅在侦破时难度较大，而且打击时容易存在漏网之鱼，社会危险性很大。依据乘车数据，对同行人员进行分析判断，并借助结构化语言对其方法进行查询实现。将这一方法应用于警务工作中，可有效降低数据筛查时警员人力的消耗、提升办案效率。参考文献1蔡曦蕾.中国铁路犯罪:回顾与反思J.北京社会科学,2018(3):32-47.2张润,方继才.基于SQL游标的数据库应用与探析J.智能计算机与应用,2016,6(6):84-86.89