观远 BI

用视图数据集实现动态显示前后N个排名

创建于 2022-11-01 / 最近更新于 2022-11-08 / 2079
字体: [默认] [大] [更大]

场景

基于页面上的筛选器所选值,计算在查询日期所在周的本周销量Top10的商品在上周的排名,以及排名的变化情况。当从筛选器筛选商品ID时,图表变为显示该商品本周排名的前后N个排名的商品信息。效果如下图。 

image.png

实现要点

视图数据集、全局参数、窗口函数。

思路

该场景分为两部分。第一部分:基于页面上的筛选器所选值,计算在查询日期所在周的本周销量Top10的商品在上周的排名,以及排名的变化情况。如果只实现这个需求,直接在卡片里使用窗口函数新建字段也可以实现,也可以使用视图数据集。

难度主要在第二部分:当从筛选器筛选商品ID时,图表变为显示该商品本周排名的前后N个排名的商品信息。页面筛选器联动卡片时,会对数据集数据进行筛选。筛选商品ID后,会先从数据集筛选出该商品ID的数据,然后再进行排序计算,这样得到的排名就是错误的,且数据集里的行数和卡片里聚合后的行数不一致,也没办法用窗口函数控制前后行数显示。即所有排名信息受前3个筛选器「查询日期」、「事业部」和「门店名称」控制,但是不受「商品ID」控制;「商品ID」主要用来显示选中的商品的排名,和定位前N名和后N名的商品。该场景需要使用视图数据集来实现。

实现步骤

1.  提前在ETL里把数据聚合到需要的颗粒度。当前案例里,用ETL聚合得到了每个事业部-每个门店-每个商品-每周和上一周的销售额、销量。因为页面筛选器需要筛选日期,所以ETL里输出了每周一的日期用来识别每周。数据结构如下图。

image.png

2.   在页面上使用该数据集字段创建选择筛选器「事业部」、「门店名称」和「商品ID」,另外新建日期筛选器。先不用设置联动。

image.png

3.   为上一步筛选器字段分别准备1个类型相同的全局参数(新建或使用已有参数都行)。文本字段「事业部」、「门店名称」使用文本参数 [DYNAMIC_PARAMS.BU/Region]和[DYNAMIC_PARAMS.门店ID](默认值建议设为“All”),「商品ID」使用数值参数 [DYNAMIC_PARAMS.参数1](默认值建议设为0),查询日期使用日期参数[DYNAMIC_PARAMS.查询日期]。参数介绍请参考 全局参数

image.png

4.   使用第一步的ETL数据集创建视图数据集,在SQL语句里引用全局参数(商品ID参数除外),对数据进一步进行聚合,直接计算出每个商品ID的本周销量、上周销量、本周排名和上周排名,仅保留必须在图表里展示的字段。因为「查询日期」、「事业部」和「门店名称」不需要显示在图表卡片里,且直接来源于ETL数据集,所以视图数据集里不需要保留这三个字段。SQL语句如下。

SELECT t.* from
(SELECT a.*,
 dense_rank() over (order by a.`本周销量` desc) as `本周排名`,
 dense_rank() over (order by a.`上周销量` desc) as `上周排名`
 from
 (SELECT input1.`商品ID`,sum(input1.`上周销量`) as `上周销量`,sum(input1.`销量`) as `本周销量`
  FROM input1
  WHERE
  input1.`周` = DATE_TRUNC('week',[DYNAMIC_PARAMS.查询日期])
  and
  (input1.`事业部` in ([DYNAMIC_PARAMS.BU/Region]) or 'All' in ([DYNAMIC_PARAMS.BU/Region]))
  and
  (input1.`门店名称` in ([DYNAMIC_PARAMS.门店ID])  or 'All' in ([DYNAMIC_PARAMS.门店ID]))
  group by input1.`商品ID`) AS a ) t 
order by t.`本周排名`

image.png

注意:

1)     'All' in ([DYNAMIC_PARAMS.BU/Region])/ 'All' in ([DYNAMIC_PARAMS.门店ID])  'All' 可替换为该参数的默认值。添加该条件判断参数默认值,是为了实现筛选器为空时该字段不参与计算。否则,即使筛选器为空,参数默认值也会参与计算,但是一般字段里不会有“All”这个选项,就会造成筛选器为空时,卡片不能计算出任何数据。

2)    因为参数默认值基本都不存在于数据集字段值范围,视图数据集预览无数据是正常现象。

image.png

5.   回到仪表板,制作卡片。每个商品ID已经在视图数据集里聚合到只有一行数据,所以数值字段拖到数值栏后可以不用选择或修改聚合方式。新建计算字段1「前后5名列表」(文本类型)和字段2「参数判断」(布尔型)。把「参数判断」拖入筛选栏,保持默认选项True不变。如果表格不显示数据,在右侧「参数默认值」里修改「查询日期」直到有数据显示,然后检查计算结果是否正确

image.png

「前后5名列表」公式:

1

collect_list([商品ID])over(partition by 1 order by [本周排名] rows between 5 preceding and 5 following)

解释:collect_list 用来把同一列的多个商品ID合并成一个数组;窗口函数里 partition by 1 order by [本周排名] 代表商品ID不分组按照排名升序排列;rows between 5 preceding and 5 following 代表取数范围为从当前行往前5行到往后5行。可以把该新建字段拖到维度栏验证下结果(保存卡片时记得从维度栏移除)。

image.png

「参数判断」公式:

1

2

3

case when [DYNAMIC_PARAMS.参数1]=0 then [本周排名]<=10

else array_contains([前后5名列表],[DYNAMIC_PARAMS.参数1])

end

解释:case when [DYNAMIC_PARAMS.参数1]=0 then [本周排名]<=10  参数为默认值时,设置显示前10名。可直接在卡片右侧修改参数默认值,修改后的新默认值仅对当前卡片生效,0需要和右侧参数的默认值保持一致。array_contains([前后5名列表],[DYNAMIC_PARAMS.参数1]) 可以判断参数值是否存在于新建字段「前后5名列表」的数组里。

窗口函数使用请参考窗口函数使用介绍

6.   保存卡片后回到页面。分别给4个筛选器设置联动,选择对应参数保存即可。

image.png

  • 筛选的商品ID所在行高亮实现方法:

A.    新建字段「是否参数商品」,引用商品ID对应的参数 [DYNAMIC_PARAMS.参数1]来判断哪个商品ID是筛选中的值。

image.png

B.    任意选择一个维度栏或者数值栏的字段,设置条件格式,新建行规则。由于列表中只有图表显示出来的字段,点击右下角进入“更多”,选择「是否参数商品」,值范围等于1,然后设置突出显示的格式保存即可。

image.png

  • 「排名变化」箭头实现方法:把「本周排名」拖入数值栏,设置别名为“排名变化”,点击选择“设置条件格式”,按照下图设置。

image.png


6 人点赞过