观远 BI

用视图数据集统计人流量

创建于 2022-11-30 / 最近更新于 2022-11-30 / 1323
字体: [默认] [大] [更大]

需求场景

      数据源为一张拉链表,记录了日维度的用户打卡时间信息,包括两个时间点:进入时间和离开时间。现在需要筛选日期范围,统计这段时间内每分钟的人流量变化。类似场景有:商场、旅游景点、实验室等地方的人流量变化,线上直播间的观看人数监控、收视率统计等。效果图如下。

图片.png

实现难点

1. 看板要求显示到分钟的颗粒度,但是数据集里的两个时间点是一个时间范围。如果用ETL把日期范围扩充为分钟级别,那么数据量会巨幅膨胀,对系统和报表加载都不友好;并且ETL不支持联动全局参数动态计算,所以不建议使用ETL。

2. 在卡片里,作为维度的分钟数依赖于数据集,会受筛选项影响,没有办法一直固定为0~59。要实现维度不受数据集和筛选项影响,这里需要用全局参数和视图数据集来实现。

3. 由于BI功能限制,带时分秒的日期筛选器无法联动全局参数,所以需要把日期和小时分为2个筛选器来做,暂时不兼顾分秒。例如小时选8,即代表筛选早晨8:00~8:59这段时间。

实现方法

一、创建“视图数据集”

     选择要使用的拉链表数据集,参考以下语句写入SQL语句。从中间参数栏为日期筛选找一个日期类型全局参数(例如 [DYNAMIC_PARAMS.查询日期]),为小时数准备一个文本类型多选参数(例如 [DYNAMIC_PARAMS.多选],这样筛选器可以多选;仅单选的话也可使用单选文本参数或者数值参数)。

select h.`小时`,m.`分钟`, count(distinct input1.`用户`) as `用户数` 
from
(select * from 
  (select distinct date(input1.`进入时间`) as `日期` , explode(sequence(0,23)) as `小时`,1 as `常量`
  from input1 )  
where `日期`=date([DYNAMIC_PARAMS.查询日期]) and `小时` in ([DYNAMIC_PARAMS.多选])) h 
left join (select explode(sequence(0,59)) as `分钟`,1 as `常量`) m on h.`常量`=m.`常量` 
left join input1
on (h.`小时`> hour(input1.`进入时间`) and h.`小时`=minute(input1.`进入时间`))  or (h.`小时`= hour(input1.`离开时间`) and m.`分钟`<=minute(input1.`离开时间`)) 
group by h.`小时`,m.`分钟` 
order by h.`小时`,m.`分钟`

图片.png

注释:

Part1: 获取页面筛选的日期和小时数,并实现交叉关联(cross join),得到临时表 h。筛选 2022-11-25 和小时数8、9,实际临时表数据如下。

图片.png

Part2: 创建临时表 m, 从0扩充到59,使用常量字段和临时表 h 关联(此时效果相当于cross join),得到一张包含查询日期、查询小时、完整分钟数的时间维表,共120行。

图片.png

Part3: 用上面得到的时间维表,继续左关联拉链表数据集,对用户ID进行去重计数。要注意关联逻辑:「进入时间」所在的小时,需匹配大于等于「进入时间」的分钟数;「离开时间」所在的小时,需匹配小于等于「离开时间」的分钟数;中间的小时都需要匹配。

二、制作看板

1. 使用创建好的视图数据集新建“折线图”卡片,拖入需要展示的字段到维度栏和数值栏,设置排序和图表的样式,保存回到页面(由于参数默认值设置,可能无法预览,先不用管;也可以从右侧修改默认值来预览效果)。

图片.png

2. 新建日期筛选器,比较操作符选“等于”,设置默认值,然后关联卡片里的日期参数。

注意:不要勾选“提供时分秒选择”,因为加上时分秒后无法联动任何参数;也不要设置“区间”,多日期会严重影响以分钟为维度的折线图视觉效果;必须设置默认值,无筛选项的时候参数本身的默认值生效,可能图表无数据显示。

图片.png

3. 在本地用Excel制作一张包含小时数(0~23)的手工维表,上传到观远BI,用这个数据集创建“选择”类型筛选器,根据需要设置单选或多选,同时设置默认值(必设);然后联动卡片里的文本参数。小时数多选的情况下效果如下图。

图片.png


1 人点赞过