用视图数据集统计人流量

需求场景

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

实现难点

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.`分钟`

注释：

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

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

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

二、制作看板

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

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

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

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