高校一卡通基础信息及常见消费分析数据处理
【中图分类号】G 【文献标识码】B 【文章编号】1008-1216（2016）06C-0082-02
2014年无锡商业职业技术学院（下文涉及流程步骤均以我院为例）在一卡通系统新建过程中，充分考虑了后期与智慧校园对接需求，利用一卡通系统学工号作为校园各信息系统唯一号，通过对校内现行的信息系统按照学工号进行数据清洗，实现了一卡通平台与教务、财务、图书、多媒体、购电、邮件及一些教学平台等系统对接，实现对校园信息化资源的整合与重组，也为后期智慧校园建设夯实了基础。
目前，新生入学报到时即可领取个人校园卡一张，可实现校内进行食堂消费、超市消费、图书借阅、购水购电、宿舍与电子阅览室通道进出等。一卡通数据作为统一数据标准的源头，必须确保准确性与可靠性。此外，由于一卡通基本实现了一卡在手，走遍校园，在做学生成绩及生活习惯分析时，大部分数据可通过一卡通涉及的刷卡数据获取，本文结合近年的工作，对一卡通常见的基础信息导入及刷卡数据的导出做了一些粗浅的归纳统计。
一、 基础人员信息处理
通常，学校招生工作结束后，名单转交给教务处进行分班与编制学号，照片交给教育信息化中心，此时照片的命名可能为身份证号或其他唯一号。教务处完成班级及学号分配工作后，提供给教育信息化中心新生信息表，其中包含学号、身份证号及其他相关信息。同时交给财务处办理校内银行卡及手机号，财务处在添加银行卡号和手机号码后也将此表交给教育信息化中心。
此时，需要根据一卡通系统导入的要求对三张表格进行数据整合与处理。三张表格有可关联的唯一号，即学号和身份证号码。我们可以利用VLOOKUP函数对三张表进行数据处理。VLOOKUP函数是Excel中的一个纵向查找函数，在工作中被广泛运用，VLOOKUP函数是按列查找，最终返回该列所需查询列序对应的值；语法规则如下：
VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）
其中：lookup_value为要查找的值；table_array为要查找的区域；col_index_num为返回数据在查找区域的第几列数；range_lookup为一逻辑值，致命函数VLOOKUP查找时是精确匹配还是模糊匹配，FALSE（0）省略为精确匹配，TRUE（1）为近似匹配。
以下为教务处提供学生信息表查找财务处提供银行卡报表中的银行卡号示例。
公式“=VLOOKUP（A2，银行卡号！$A$2：$C$4228，3，0）”表示在“银行卡号”表中精确查找与“人员信息”表中A2单元格即该学号相同人员的银行卡号。这里“人员信息”表中查找区域需用绝对地址锁定，方便利用Excel填充柄工具向下填充公式，生成所有学生银行卡信息。
二、照片处理
高招系统中导出的照片命名一般为准考证号，而在一卡通系统中导入照片为学号，此时需要对照片批量改名，我们也可以利用Excel中的CONCATENATE函数结合Windows自带的批处理功能完成批量改名。CONCATENATE 函数可将最多 255 个文本字符串合并为一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合。
公式“=CONCATENATE（"rename"，""，C2，".jpg "， ""，B2，".jpg"）”，表示将引号中的字符内容与单元格中的字符内容连接，结果生成了如下文本rename 14320281450254.jpg 14115101.jpg，利用填充柄工具向下填充所有项后，复制到记事本中，并另存为“*.bat”格式文件，将该文件保存到照片文件夹路径，双击执行，很方便地批量完成了照片的重命名工作，提高了工作效率。
三、字段截取
有时候，我们需要取某一列单元格的部分数据，这时候我们可以利用Excel中的LEFT和RIGHT函数，进行截取。比如，在某个系统中需要设置所有人的初始密码为身份证后六位，那么我们可以用公式“=RIGHT（A2，6）”截取A2单元格证件号的后六位数，同样，如果是截取左边6位字符，那么可以利用公式“=LEFT（A2，6）”获取A2单元格证件号的前6位数。
四、中心数据库数据优选
中心数据库中包含了所有与校园卡有关的刷卡信息，如消费、门禁、通道、图书借阅、体育考勤……如果仅仅从一卡通系统的报表功能出发，无法满足我们在做数据分析的时候所需的特定数据。
在做数据分析时，只需要从中心数据库中优选出与需要处理的数据分析相关的数据，如需要统计分析某个专业学生十一月份三餐消费情况，可通过如下SQL命令，直接生成所需的学生消费情况明细，包含学生的学号、姓名、消费类别、消费金额、日期……具体可结合学校一卡通中心数据库结构生成。
select b.outid as 学号，b.name as 姓名，DECODE（a.mealid，1，'早餐'，2，'中餐'，3，'晚餐'，4，'宵夜'） as 餐别，a.opfare as 消费金额，a.opdt as 消费日期，a.acccode 交易科目代码，a.dscrp 交易描述
from rec_main_consume a ，base_customers b
where a.customerid=b.customerid and b.custdept in （'009001004001'，'009002006001'，'009002004'，'009002006002'，'009003004001'，'009003004002'，'009004004001'，'009004004002'）
and a.opdt >= TO_DATE（'2015-11-01'，'yyyy-mm-dd'） and a.opdt < TO_DATE（'2015-12-01'，'yyyy-mm-dd'）and a.acccode in （210，215，214，216，217，221）
五、提取数据统计
将步骤4生成的结果简单处理后，按照餐别导入到数据库中，根据如下命令，可生成每个学生某一时间区间内某个餐别的消费次数及总金额，这样可以方便统计分析每位学生的消费均值。
select studentid，name，mealid，sum（opfare）as spend，count（distinct CONVERT（varchar（100）， opdt， 4） ）as data
from dbo.zhongcan
group by studentid ，name，mealid
通过对海量的一卡通数据进行分析与研究，可从中获取有利于学生管理的数据，合理引导学生消费与生活。学校相关部门也可以通过对学生消费数据进行分析，找到学生消费、生活习惯与学生成绩的关联，积极引导学生学习。
基金项目：无锡商业职业技术学院院级科研课题SYKJ15B13。
