跳到主要内容
版本：5.9.0

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

场景​

基于页面上的筛选器所选值，计算在查询日期所在周的本周销量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名列表」公式：

1collect_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

「参数判断」公式：

123case 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