交通数据分析：BRT日客流分析1

数据结构

BRT作为城市公交的一部分，相比于传统公交，对乘客而言具有很多优点。本文以某市BRT公交数据为例，该BRT公交系统数据结构如下：

CREATE TABLE "BRT"
(    
"JCZBZ" VARCHAR2(1),  --进出站标志
"KHZZZ" VARCHAR2(20), -- 卡号
"JYRQZ" VARCHAR2(8),  -- 交易日期
"JYSJZ" VARCHAR2(6),  -- 交易时间
"JYJEZ" NUMBER(14,0),  -- 交易金额、次数
"JYQYE" NUMBER(14,0),  -- 交易前卡余额、次数
"HCBSZ" VARCHAR2(1),  --换乘标志
"XTSJZ" DATE,  --系统时间
"JZRQZ" VARCHAR2(8),  -- 进站日期
"JZSJZ" VARCHAR2(6),  -- 进站时间
"ZDBHZ" VARCHAR2(4),  -- 站点编号
"XLBHZ" VARCHAR2(4),  -- 线路号
"JZXLZ" VARCHAR2(4), -- 进站线路
"JZBHZ" VARCHAR2(4)  -- 进站站点编号
)

数据汇总处理

对该数据进行简单的汇总处理，可以得到BRT公交系统中累计的入站人数和累计的出站人数：

-- 计算每时每刻BRT上的入站人数
CREATE TABLE BRT_IN AS 
SELECT 
A.线路编号,
A.交易日期, 
A.交易时间,
COUNT(A.交易时间) AS 进站计数 
FROM (
     SELECT 
     B.XLBHZ AS 线路编号, 
     B.JYRQZ AS 交易日期, 
     SUBSTR(B.JYSJZ,0,4) AS 交易时间 
     FROM BRT B 
     WHERE 
     B.JCZBZ='0'
     ) A
GROUP BY 
A.线路编号,
A.交易日期,
A.交易时间

-- 计算BRT上的出站人数,按照分钟进行计数
CREATE TABLE BRT_OUT AS 
SELECT 
A.线路编号,
A.交易日期, 
A.交易时间,
COUNT(A.交易时间) AS 出站计数 
FROM (
	SELECT 
	B.XLBHZ AS 线路编号, 
	B.JYRQZ AS 交易日期, 
	SUBSTR(B.JYSJZ,0,4) AS 交易时间 
	FROM BRT B 
	WHERE 
	B.JCZBZ='1'
	) A
GROUP BY 
A.线路编号,
A.交易日期,
A.交易时间

根据累计入站人数和累计出站人数，可以得到在任一时刻的BRT公交系统中保有的人数：

--计算每时每刻brt上的保留乘客数量(累计求和)
INSERT INTO  BRT_ACCU 
SELECT 
B.线路编号,
B.交易日期,
B.交易时间,
B.进出站计数,
SUM(B.进出站计数) OVER(PARTITION BY B.线路编号,B.交易日期 ORDER BY B.交易时间) AS 当前保留乘客数量
FROM(
	SELECT 
	C.线路编号,
	C.交易日期,
	SUM(C.进出站) AS 进出站计数 
	FROM(
		SELECT 
		A.XLBHZ AS 线路编号,
		A.JYRQZ AS 交易日期,
		SUBSTR(A.JYSJZ,0,4) AS 交易时间,
		CASE WHEN A.JCZBZ='1' THEN -1 ELSE 1 END AS 进出站
		FROM BRT A 
		) C
	GROUP BY 
	C.线路编号,
	C.交易日期,
	C.交易时间 
	) B;

对于BRT公交系统中保有的乘客数量，可以通过下面来得到每天的峰值：

--每天客流峰值出现的位置
SELECT 
线路编号,
交易日期,
交易时间,
当前保留乘客数量
FROM(
	SELECT 
	线路编号,
	交易日期,
	交易时间,
	当前保留乘客数量,
	MAX(当前保留乘客数量) OVER(PARTITION BY 线路编号,交易日期) AS 当前最大值
	FROM (
		SELECT * 
		FROM BRT_ACCU 
		ORDER BY 
		线路编号,
		交易日期,
		交易时间
		)
	) A
WHERE A.当前保留乘客数量 = A.当前最大值