智慧地铁大屏 · 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_averageSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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_topSELECT 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 个组件拼成大屏我的分析 → 智慧地铁可视化大屏 → 编辑左下角点「新建仪表板」起名 智慧地铁可视化大屏左侧会列出你做的所有组件一个个拖到画布上按老师那张图的布局摆摆好后点「保存」┌──────────────────────────────────────────────────┐ │ 智慧地铁可视化大屏 [日期筛选] │ ├──────────────────────────────────────────────────┤ │ 进出站人次排行榜通栏 │ ├───────────────┬──────────────┬───────────────────┤ │ 进站排行榜 │ 通勤平均指标卡 │ 出站排行榜 │ ├───────────────┼──────────────┼───────────────────┤ │ 线路运输乘客数 │ 线路耗时环形 │ 车站区间客流 │ ├───────────────┼──────────────┼───────────────────┤ │ 站点进站闸机雷达│ 通勤最长TOP │ 站点出站闸机雷达 │ ├───────────────┼──────────────┼───────────────────┤ │ 线路进站闸机饼图│ 换乘最久TOP │ 线路出站闸机饼图 │ ├───────────────┼──────────────┼───────────────────┤ │ 车站收入TOP10 │ 线路收入 │ 每卡日消费 │ ├───────────────┼──────────────┼───────────────────┤ │ 换乘出站百分比 │ 每分钟在位数 │ 每小时在位数 │ └───────────────┴──────────────┴───────────────────┘22. 常见问题现象原因解决预览 SQL 报错复制时漏了字符重新整段复制注意别漏结尾的 LIMIT 10图表是空的字段拖错位置值字段要拖到「横轴/指标」文字字段拖到「纵轴/维度」数字对不上数据集还是旧的直连表确认新建的是 SQL 数据集不是数据库表图表类型选不到雷达图数据维度不够雷达图需要 1 个维度 1 个指标检查是否都拖了想改日期换 dt把 SQL 里的 20180901 改成 20180831但那天只有 170 行数据