数据分析 | 拨开云雾见月明——分组、聚合、行列转换

转载

mob60475703a599 2021-06-03 16:06:52

接上一篇。数据样式如下，总量共计1500万+条：

CARD_NO  RIDING_DATE  RIDING_TIME  TRAFFIC_TYPE  LINE_NO  BUS_NO  TRADE_TYPE  PAID  PREAMT_FLAG
4000000007936933  20170525  81758  1  911  10017  1  1  0
4000000210311917  20170525  140645  1  982  983007  0  1.8  0
4000000005882185  20170525  154507  1  910  91207  0  2.7  0
4000000810038209  20170525  191745  2  103  1034102  0  3.6  0
4000001510052051  20170525  191730  2  113  1134104  0  4.5  0

第一问：包含哪些天的记录？每天分别有多少数据量？

思路：按日期分组，计算记录数，考虑到交通情况与工作日、周末强相关，顺便将日期转为星期格式。 SQL语句：

select RIDING_DATE, 
    datename(weekday,RIDING_DATE) as riding_week, 
    count(*) as records_num
from dbo.traffic_card
group by RIDING_DATE

运行的部分结果：

RIDING_DATE  riding_week  records_num
20160801  星期一  1913208
20160824  星期三  218179
20160825  星期四  257466
……  ……  ……
20170109  星期一  2644115
20170525  星期四  4729824
20170526  星期五  2515023

结论：共计50多天，记录数最少的日期仅有2条，最多的达到470万+条，达到100万条以上的仅有5天，90%的日期数据不完整（包含周末）。

第二问：数据是否完整覆盖了24小时？

思路：筛选记录数最多的三天，按小时分组，统计记录数。 SQL语句：

select RIDING_DATE, 
  datename(weekday,RIDING_DATE) as riding_week, 
  RIDING_TIME / 10000 as riding_hour,
  count(*) as records_num
from dbo.traffic_card
where RIDING_DATE = '20170109' or
  RIDING_DATE = '20170525' or
  RIDING_DATE = '20170526'
group by RIDING_DATE,RIDING_TIME / 10000
order by RIDING_DATE,riding_hour
    运行的部分结果：
RIDING_DATE  riding_week  riding_hour  records_num
20170109  星期一  0  164
……  ……  ……
20170109  星期一  12  119697
……  ……  ……
20170109  星期一  23  2420
20170525  星期四  4  413
……  ……  ……
20170525  星期四  12  187627
……  ……  ……
20170525  星期四  23  8801
20170526  星期五  0  1331
……  ……  ……
20170526  星期五  12  97041
……  ……  ……
20170526  星期五  23  7587

遇到的问题：数据中时间格式为“hhmmss”，通过datename函数转换无法识别，因此直接通过除法转换为小时（SQL Sever默认向下取整）。结论：0~24时完整覆盖的为5月26日的数据，考虑到夜间出行人数较少的话，另外两天的数据也非常完整。

第三问：交通类型字段中的1/2/3哪个是地铁？

思路：对覆盖时段最完整的那天进行分析，按交通类型进行分组统计。 SQL语句：

# 按小时、不同交通类型 group by分组统计记录数
select 
  RIDING_DATE, 
  datename(weekday,RIDING_DATE) as riding_week,
  TRAFFIC_TYPE, 
  RIDING_TIME / 10000 as riding_hour,
  count(*) as records_num
from 
  dbo.traffic_card
where 
  RIDING_DATE = '20170526'
group by 
  RIDING_DATE, TRAFFIC_TYPE, RIDING_TIME / 10000
order by 
  RIDING_DATE, TRAFFIC_TYPE, riding_hour

运行的部分结果：

RIDING_DATE  riding_week  TRAFFIC_TYPE  riding_hour  records_num
20170526  星期五  1  0  1273
……  ……  ……
20170526  星期五  1  23  1803
20170526  星期五  2  0  58
……  ……  ……
20170526  星期五  2  23  5783
20170526  星期五  3  3  1
……  ……  ……
20170526  星期五  3  23  1

遇到的问题：以上这种形式不方便各交通类型之间的对比，因此，需要使用 pivot 聚合函数，将交通类型字段中的1/2/3转为列来展示。 SQL语句：

# 按小时、不同交通类型 pivot聚合 （行转列）
select 
  riding_hour, [1] as type1, [2] as type2, [3] as type2
from(
  select C.TRAFFIC_TYPE, C.RIDING_TIME / 10000 as riding_hour, count(C.CARD_NO) as records_num
  from dbo.traffic_card as C
  where RIDING_DATE = '20170526'
  group by C.TRAFFIC_TYPE, C.RIDING_TIME / 10000
  ) as ST
pivot(
  sum(ST.records_num) for ST.TRAFFIC_TYPE in([1],[2],[3])
  ) as PT
order by riding_hour
    运行的部分结果：
riding_hour  type1  type2  type3
0  1273  58  NULL
1  381  NULL  NULL
2  91  NULL  NULL
3  NULL  NULL  1
4  324  NULL  NULL
5  8267  NULL  NULL
6  101860  10076  NULL
……  ……  ……
12  63781  33260  NULL
……  ……  ……
18  120599  142152  NULL
……  ……  ……
23  1803  5783  1

结论：考虑到公交和地铁的运营时间，可以判断类型1为公交，类型2为地铁，而通过刷卡乘坐出租车的人数较少，也可以确定类型3为出租。