让决策更智能
新一代智能数据分析平台

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

观小远发表于:2022年01月13日 19:47:59更新于:2022年04月28日 11:01:48

场景

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

实现要点

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

思路

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

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

实现步骤

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

00161e013c4c8581219b0d898c9024e

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

00161e013d4d705ebaea01193d9d40e

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

00161e015ae9858315b15175e0d1aa4

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.`本周排名`

 00161e015c3779f0bb837076b485777

注意:

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

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

00161e015dc239152a5290c428b8d08

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

00161e01610b02cab25bea9c5339589

00161e01610ddfd388bd0d86bd64d94

「前后5名列表」公式:

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行。可以把该新建字段拖到维度栏验证下结果(保存卡片时记得从维度栏移除)。

00161e0168491a777684c96ef043438

「参数判断」公式:

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名列表」的数组里。

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

数组函数使用请参考 Spark SQL 数组(Array)处理函数及应用

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

00161e016b7a7a5d2f9ba5e1669e496

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

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

00161e016e1856e7cff11d7c7bdf3f8

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

00161e016f2ea063d8a6252e6fa1256

  • 「排名变化」箭头实现方法:把「本周排名」拖入数值栏,设置别名为“排名变化”,点击选择“设置条件格式”,按照下图设置。方法请参考 条件格式比较功能使用说明

00161e01711df3e86c227c1f3d202aa

    您需要登录后才可以回复