观远 BI

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

创建于 2023-07-12 / 最近更新于 2023-07-12 / 1782
字体: [默认] [大] [更大]

背景需求

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

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

  • 展示个股收盘价、个股和大盘的累计涨幅(不是昨日涨幅)3个指标;

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

最终效果

图片.png

实现方式

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

1. 数据准备

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

  • 新建文本类型全局参数

图片.png

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模型参考下图:

图片.png

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年前初始日期]

图片.png

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模型可参考下图:

图片.png

方案对比如下,选择适合的方案即可:

方案一:逻辑简单清晰,入手友好;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)其他可视化优化:

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

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

  • 图片.png

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

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

图片.png

OK,大功告成!


118 人点赞过