智慧地铁大屏 · 19 个组件完整操作手册

所以下面每个组件都要新建一个 SQL 数据集，不是一个数据集做所有图。

1. 通用套路（19 个组件都照这个做）

1.1 第 1 步：建 SQL 数据集

公共数据 → 智能地铁数据 → 【新建数据集】 → 选「SQL 数据集」 ↓ 数据连接：zhihuiditie 表 名：按下面每个组件里写的填（比如 ads_in_station_day_top） SQL 语句：把下面框里的整段复制粘贴进去 ↓ 点右上角【预览】 → 确认右边出数据了 → 点【确定】

1.2 第 2 步：新建组件

我的分析 → 智慧地铁可视化大屏 → 点右上角【编辑】 ↓ 左下角 【+ 新建组件】 → 选择刚建好的那个数据集 → 确定

1.3 第 3 步：拖字段出图

① 上方【图表类型】里选我指定的图（条形图/柱状图/环形图/雷达图/指标卡） ② 左侧字段列表，把我写的「纵轴字段」拖到【纵轴】槽位 ③ 再把我写的「横轴字段」拖到【横轴】槽位 ④ 双击标题改成我写的组件名 ⑤ 右上角【保存】

注意：所有 SQL 里都已经写了 WHERE dt = '20180901'，不用你再去筛选器里筛日期。每段 SQL 末尾都有 ORDER BY ... DESC（排行榜必须有），不要删。

2. 组件 1：进站人次排行榜 TOP10

数据集名：ads_in_station_day_top

SELECT in_station AS 进站站点, count(1) AS 进站人次 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_station != '""' GROUP BY in_station ORDER BY 进站人次 DESC LIMIT 10

做完长这样：五和 9,500 → 布吉 8,702 → 丹竹头 7,577 → 民治 7,450 → 龙华 6,040 → 下水径 5,760 → 清湖 5,727 → 大芬 5,031 → 固戍 5,013 → 长龙 4,907。

3. 组件 2：出站人次排行榜 TOP10

数据集名：ads_out_station_day_top

SELECT out_station AS 出站站点, count(1) AS 总人数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND out_station != '""' GROUP BY out_station ORDER BY 总人数 DESC LIMIT 10

做完长这样：深圳北 8,312 → 罗湖站 8,016 → 福田口岸 6,189 → 华强北 5,545 → 老街 5,329 → 会展中心站 5,027 → 南山站 4,894 → 兴东 4,748 → 五和 4,722 → 福田 4,609。

4. 组件 3：进出站人次排行榜

数据集名：ads_in_out_station_day_top

SELECT station AS 站点, sum(in_cnt) AS 进站人次, sum(out_cnt) AS 出站人次, sum(in_cnt) + sum(out_cnt) AS 总人次 FROM ( SELECT in_station AS station, 1 AS in_cnt, 0 AS out_cnt FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_station != '""' UNION ALL SELECT out_station AS station, 0 AS in_cnt, 1 AS out_cnt FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND out_station != '""' ) GROUP BY station ORDER BY 总人次 DESC LIMIT 20

5. 组件 4：线路单日运输乘客总次数排行榜

数据集名：ads_line_send_passengers_day_top

SELECT in_company_name AS 线路, count(1) AS 乘客数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_company_name != '""' GROUP BY in_company_name ORDER BY 乘客数 DESC

对数字：地铁五号线 82,947 → 三号线 68,945 → 一号线 49,995 → 四号线 38,428。

6. 组件 5：乘客通勤时间平均值 / min

数据集名：ads_all_passengers_single_ride_spend_time_average

SELECT round(avg(take_time), 2) AS 平均通勤分钟, count(1) AS 样本数 FROM dws.dws_take_subway_detail WHERE dt = '20180901'

对数字：应该显示 29.86。

7. 组件 6：线路单程直达乘客耗时平均值排行榜

数据集名：ads_line_single_ride_average_time_day_top

SELECT in_company_name AS 线路, round(avg(take_time), 2) AS 平均耗时, count(1) AS 次数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND conn_mark = 1 GROUP BY in_company_name ORDER BY 平均耗时 DESC

对数字：十一号线 24.01 → 三号线 23.72 → 一号线 21.29 → 五号线 20.27。

8. 组件 7：每日运输乘客最多的车站区间排行榜

数据集名：ads_stations_send_passengers_day_top

SELECT if(in_station > out_station, concat(in_station, '-', out_station), concat(out_station, '-', in_station)) AS 乘车区间, count(1) AS 人流量 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_station != out_station AND in_station != '""' AND out_station != '""' GROUP BY 乘车区间 ORDER BY 人流量 DESC LIMIT 20

对数字：赤尾-华强北 1,908 → 福田口岸-福民 1,273 → 深圳北-五和 1,051。

9. 组件 8：站点进站闸机数（雷达图）

数据集名：ads_station_in_equ_num_top

SELECT in_station AS 站点, uniqExact(in_equ_no) AS 闸机数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_station != '""' GROUP BY in_station ORDER BY 闸机数 DESC LIMIT 10

对数字：华强北 24 → 福田 22 → 老街 22 → 市民中心 21 → 少年宫 20。

10. 组件 9：单日从上车到下车间隔时间排行榜

数据集名：ads_passenger_spend_time_day_top

SELECT card_no AS 卡号, in_station AS 进站, out_station AS 出站, round(take_time, 2) AS 通勤分钟 FROM dws.dws_take_subway_detail WHERE dt = '20180901' ORDER BY take_time DESC LIMIT 10

对数字：BEBJGJFFA 下水径→红岭北 285.38 → FFGCDFHGJ 龙华→龙华 280.30。

11. 组件 10：站点出站闸机数（雷达图）

数据集名：ads_station_out_equ_num_top

SELECT out_station AS 站点, uniqExact(out_equ_no) AS 闸机数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND out_station != '""' GROUP BY out_station ORDER BY 闸机数 DESC LIMIT 10

12. 组件 11：线路进站闸机数（饼图）

数据集名：ads_line_in_equ_num_top

SELECT in_company_name AS 线路, uniqExact(in_equ_no) AS 闸机数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_company_name != '""' GROUP BY in_company_name ORDER BY 闸机数 DESC

13. 组件 12：换乘耗时最久的乘客 TOP10

数据集名：ads_conn_spend_time_top

SELECT card_no AS 卡号, in_station AS 进站, out_station AS 出站, round(take_time, 2) AS 通勤分钟 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND conn_mark = 0 ORDER BY take_time DESC LIMIT 10

14. 组件 13：线路出站闸机数（饼图）

数据集名：ads_line_out_equ_num_top

SELECT out_company_name AS 线路, uniqExact(out_equ_no) AS 闸机数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND out_company_name != '""' GROUP BY out_company_name ORDER BY 闸机数 DESC

15. 组件 14：出站交易收入 TOP10

数据集名：ads_station_deal_day_top

SELECT out_station AS 站点, round(sum(deal_money), 2) AS 收入, count(1) AS 笔数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND out_station != '""' GROUP BY out_station ORDER BY 收入 DESC LIMIT 10

对数字：深圳北 3,542,330 → 罗湖站 3,481,475 → 南山站 2,622,940。

16. 组件 15：出站交易所在线路收入 TOP10

数据集名：ads_line_deal_day_top

SELECT out_company_name AS 线路, round(sum(deal_money), 2) AS 收入, count(1) AS 笔数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND out_company_name != '""' GROUP BY out_company_name ORDER BY 收入 DESC

对数字：一号线 27,059,430 → 三号线 26,259,185 → 五号线 26,089,240。

17. 组件 16：每张卡日消费排行

数据集名：ads_card_deal_day_top

SELECT card_no AS 卡号, round(sum(deal_money), 2) AS 消费金额, count(1) AS 次数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' GROUP BY card_no ORDER BY 消费金额 DESC LIMIT 10

对数字：FHIGEIEDB 4,800 → FFGEDJEGD 4,200。

18. 组件 17：每天每线路换乘出站乘客百分比

数据集名：ads_conn_ratio_day_top

SELECT in_company_name AS 线路, sum(if(conn_mark = 0, 1, 0)) AS 换乘人数, count(1) AS 总人数, round(sum(if(conn_mark = 0, 1, 0)) / count(), 4) AS 换乘占比 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND in_company_name != '""' GROUP BY in_company_name ORDER BY 换乘占比 DESC

对数字：七号线 0.693 → 四号线 0.6366 → 九号线 0.6029。

19. 组件 18：每分钟每条线在线人数（环形）

数据集名：ads_on_line_min_top

SELECT in_company_name AS 线路, count() AS 在位数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND concat(take_day, ' ', in_time) <= concat(take_day, ' 08:30') AND concat(take_day, ' ', out_time) > concat(take_day, ' 08:30') GROUP BY in_company_name ORDER BY 在位数 DESC

这里为什么用 concat(...) <= concat(...) 而不是时间函数？ClickHouse 的 toUnixTimestamp 不接受 08:30 这种缺秒的时间串（会直接报错）。2018-09-01 08:30 的字符串字典序就是时间序，所以直接用字符串比较最稳。

对数字（08:30 早高峰）：五号线 16,165 → 三号线 12,281 → 一号线 8,045。

20. 组件 19：每小时每条线在线人数（环形）

数据集名：ads_on_line_hour_top

SELECT in_company_name AS 线路, count() AS 在位数 FROM dws.dws_take_subway_detail WHERE dt = '20180901' AND concat(take_day, ' ', in_time) <= concat(take_day, ' 18:00') AND concat(take_day, ' ', out_time) > concat(take_day, ' 18:00') GROUP BY in_company_name ORDER BY 在位数 DESC

想换成别的小时，只改 SQL 里那两个 18:00 就行（比如 12:00 看午高峰）。

21. 最后一步：把这 19 个组件拼成大屏

1. 我的分析 → 智慧地铁可视化大屏 → 编辑
2. 左下角点「新建仪表板」，起名 智慧地铁可视化大屏
3. 左侧会列出你做的所有组件，一个个拖到画布上，按老师那张图的布局摆
4. 摆好后点「保存」

┌──────────────────────────────────────────────────┐ │ 智慧地铁可视化大屏 [日期筛选] │ ├──────────────────────────────────────────────────┤ │ 进出站人次排行榜（通栏） │ ├───────────────┬──────────────┬───────────────────┤ │ 进站排行榜 │ 通勤平均指标卡 │ 出站排行榜 │ ├───────────────┼──────────────┼───────────────────┤ │ 线路运输乘客数 │ 线路耗时环形 │ 车站区间客流 │ ├───────────────┼──────────────┼───────────────────┤ │ 站点进站闸机雷达│ 通勤最长TOP │ 站点出站闸机雷达 │ ├───────────────┼──────────────┼───────────────────┤ │ 线路进站闸机饼图│ 换乘最久TOP │ 线路出站闸机饼图 │ ├───────────────┼──────────────┼───────────────────┤ │ 车站收入TOP10 │ 线路收入 │ 每卡日消费 │ ├───────────────┼──────────────┼───────────────────┤ │ 换乘出站百分比 │ 每分钟在位数 │ 每小时在位数 │ └───────────────┴──────────────┴───────────────────┘

22. 常见问题