用BI展现不同时间段内个股和大盘的走势

背景需求

需要展示上市公司个股的股价和对应上市的大盘走势，为了直观对比，要求：

• 比较时间段为最近5日、最近15日、最近30日、最近60日、最近90日、最近180日、最近1年和最近3年；

• 展示个股收盘价、个股和大盘的累计涨幅（不是昨日涨幅）3个指标；

• 上述时间段内比较时，个股和大盘的起点一致（即多线条从同一起点出发）。

最终效果

实现方式

思路：首先在ETL里计算出各个时间段内的个股累计涨幅和大盘累计涨幅；然后，在页面，通过全局参数传参，展现对应字段的数据。

1. 数据准备

• 数据集按照主键（机构编码、机构中文简称（和机构编码一一对应）、Date ）聚合好

• 新建文本类型全局参数

2. 用ETL计算各个时间段内的股价累计涨幅

2.1. 方案一

以计算近一年个股和大盘累计涨幅为例：

1) 新建计算字段，获取数据集最大日期；

当前数据集最大日期：MAX([Date]) OVER (PARTITION BY NULL)

2) SQL节点，筛选近一年的数据；也可以用筛选节点实现；

SELECT * from input1 
where input1.`Date`>= DATE_SUB(input1.`当前数据集最大日期`,364)

3) 按照机构编码分组，对日期排序；

排名：row_number() over (partition by [机构编码] order by [Date])

4) 获取个股和大盘的基点数据；

个股基点：IF([排名]=1,[个股收盘价],null)
大盘基点：IF([排名]=1,[大盘收盘价],null)

5) 获取个股和大盘的基点数据最终值；

个股基点最终值：max([个股基点])over(partition by [机构编码])
大盘基点最终值：max([大盘基点])over(partition by [机构编码])

解释：用max开窗函数替换掉 [个股基点]、[大盘基点]字段中的null值，数值大小不发生变化。

6) 计算累计涨幅；

个股股价累计涨幅(一年)：[个股收盘价]/[个股基点最终值]-1
大盘累计涨幅(一年)：   [大盘收盘价]/[大盘基点最终值]-1

7) 使用多个分支分别计算出各个时间段的累计涨幅，以时间区间最长的“近3年”数据为主表，把各个时间段的数据关联起来输出。

ETL模型参考下图：

2.2. 方案二

1) 新建计算字段，获取数据集最大日期;

当前数据集最大日期: MAX([Date]) OVER (PARTITION BY NULL)

2) 对不同时间段进行标记, 用于后续日期分组和筛选数据；

3年前初始日期：[当前数据集最大日期] - interval 3 years
最近1年： case when [Date]>[当前数据集最大日期]- interval 1 year then '最近1年' end
最近半年：case when [Date]>=[当前数据集最大日期]-interval 179 days then '最近半年' end

3) 筛选最长的时间段--近3年数据：[Date] > [3年前初始日期]

4) 计算每个时间段个股和大盘的基点数据；

个股基点_最近1年：nvl2([最近1年],first([个股收盘价])over(partition by [机构编码],[最近1年] order by [Date]),null)
个股基点_最近3年：first([个股收盘价])over(partition by [机构编码] order by [Date])

解释：按照日期 [Date] 排序，用 first 开窗函数获取时间段内最早日期的 [个股收盘价]；nvl2函数用来判断处理 [最近1年] 里的非null值，实现只计算 [最近1年] 这个时间段里的数据。

5) 计算累计涨幅；选择列后输出。

个股股价累计涨幅(一年)：[个股收盘价]/[个股基点_最近1年]-1
大盘累计涨幅(一年)：   [大盘收盘价]/[大盘基点_最近1年]-1

ETL模型可参考下图：

方案对比如下，选择适合的方案即可：

方案一：逻辑简单清晰，入手友好；ETL支线多，结构稍复杂。

方案二：需要熟练掌握窗口函数、逻辑稍复杂；ETL单线，结构简单。

3. 仪表板卡片制作

3.1. 使用全局参数展示动态指标

新建2个数值类型字段，放入数值栏，实现动态指标；1个布尔类型，放入筛选栏，实现动态日期切换。

个股累计涨幅：
case [DYNAMIC_PARAMS.市值管理股价快捷日期筛选]  
when '最近5日走势' then [个股股价累计涨幅(5天)]  
when '最近15日走势' then [个股股价累计涨幅(15天)]  
when '最近30日走势' then [个股股价累计涨幅(30天)]  
when '最近60日走势' then [个股股价累计涨幅(60天)]  
when '最近90日走势' then [个股股价累计涨幅(90天)]  
when '最近180日走势' then [个股股价累计涨幅(180天)]  
when '最近1年走势' then [个股股价累计涨幅(一年)]  
when '最近3年走势' then [个股股价累计涨幅(三年)]  
else null  end

大盘累计涨幅：
case [DYNAMIC_PARAMS.市值管理股价快捷日期筛选]  
when '最近5日走势' then [大盘累计涨幅(5天)]  
when '最近15日走势' then [大盘累计涨幅(15天)] 
when '最近30日走势' then [大盘累计涨幅(30天)]  
when '最近60日走势' then [大盘累计涨幅(60天)]  
when '最近90日走势' then [大盘累计涨幅(90天)]  
when '最近180日走势' then [大盘累计涨幅(半年)]  
when '最近1年走势' then [大盘累计涨幅(一年)]  
when '最近3年走势' then [大盘累计涨幅(三年)]  
else null  end

日期筛选布尔值：
写法1：
case [DYNAMIC_PARAMS.市值管理股价快捷日期筛选]
when '最近5日走势'  then  datediff([当前数据集最大日期],[Date])<=4
when '最近15日走势' then  datediff([当前数据集最大日期],[Date])<=14
when '最近30日走势' then  datediff([当前数据集最大日期],[Date])<=29
when '最近60日走势' then  datediff([当前数据集最大日期],[Date])<=59
when '最近90日走势' then  datediff([当前数据集最大日期],[Date])<=89
when '最近180日走势' then  datediff([当前数据集最大日期],[Date])<=179
when '最近1年走势' then  datediff([当前数据集最大日期],[Date])<=364
when '最近3年走势' then  datediff([当前数据集最大日期],[Date])<=1094
end
写法2：[最近5日]等字段可替换为计算好的累计涨幅字段
case [DYNAMIC_PARAMS.市值管理股价快捷日期筛选]
when '最近5日走势'  then  [最近5日] is not null
when '最近15日走势' then  [最近15日] is not null
when '最近30日走势' then  [最近30日] is not null
when '最近60日走势' then  [最近60日] is not null
when '最近90日走势' then  [最近90日] is not null
when '最近180日走势' then [最近半年] is not null
when '最近1年走势'  then  [最近1年] is not null
when '最近3年走势'  then  [最近3年] is not null
end

3.2. 设置多线图和页面联动

3.2.1. 卡片小技巧    

     为了保证3个数值指标起点一致，并且个股收盘价优先展示，制作多线图时，以下几点需要特别注意：

1) 由于个股价格和累计涨幅的数值范围相差较大，如果把3个字段都放入数值栏（包括叠加图形数值栏），可视化效果较差；同时，个股累计涨幅就是根据个股收盘价计算的，这两个指标的起伏走势实际是一致的，只显示 [个股累计涨幅] 就可以代表个股收盘价的走势。

解决方法：把2个累计涨幅放入数值栏，把3个字段依次放入「更多工具提示」；勾选「是否只显示工具提示」。

2）按照上述方法设置后，光标放上去，会发现浮窗里图例颜色和线条的颜色不一致。这是因为数值栏和工具提示栏字段数量不一致，两个地方分别按照主题颜色依次生效，互相独立，所以需要保证数值栏和工具提示栏字段数量一致。

解决方法：新建数值类型常量字段，内容建议为null或0，当做第一个指标放入数值栏。这个字段不会影响图表展示，也可以设置别名为空格来降低存在感。

3）其他可视化优化：

• 主题色第一个设置为白色，以此让收盘价前面的图例小点为白色，和其他指标区分开；

• 坐标轴、数据标签、图例都设置不显示。

• 

3.2.2. 页面小技巧

• 新建参数筛选器，选择卡片里使用的参数，筛选器会自动联动当前页面使用该参数的卡片；可根据需求把参数筛选器设置为“按钮栏”样式。

• 由于多线图隐藏了图例， 可以根据需求做一个自定义图例。如果要显示固定内容，可以在多线图下面放一个图片；如果要联动筛选器，例如个股显示筛选的「机构中文简称」，大盘显示对应的市场，可以通过“轮播列表”卡片来实现。实现方法请参考类似案例 轮播列表使用小妙招。

OK，大功告成！