区块链技术博客
www.b2bchain.cn

维表设计和非常规需求的碰撞求职学习资料

本文介绍了维表设计和非常规需求的碰撞求职学习资料,有助于帮助完成毕业设计以及求职,是一篇很好的资料。

对技术面试,学习经验等有一些体会,在此分享。

维表设计的感悟
已下是工作中真实遇到的问题,几次做需求的过程中慢慢体会到的一些感受和不成熟总结(毕竟工作经验2年),纯理论知识:参考《阿里大数据之路》

感觉篇幅太长了,把感受写在最前面吧:

1、如果是类似枚举值的这种,直接维护就行了
2、桥表,很有讲究,比如很常见的商品一二三级类目,业务中可能会有很复杂的情况,见例子二。最完美的情况是能确定最小粒度字段,并且在业务中只会扩大不会缩小。特殊情况就找点小技巧小聪明处理下吧

例子一

业务背景:h5活动前端使用的activity_id和后端act_id 配置映射
(第一家公司平台化刚起步,所以下面有些方式比较蠢)
ods_app_activity_config:/data/pythom/h5_activity.py
通过这个爬虫脚本维护在mysql中,每天在凌晨3点爬取一遍数据,重写mysql表并同步到hive中使用。可能出现活动已经上线但是没在文档添加的情况,会导致这张配置表的数据有问题。
问题:
1、完全没有规律,h5就是用文档维护。
2、其中有一个招募活动有各种模板配置,h5后端和服务后端通过另一个id关联活动信息。

算是离职前做的比较大的需求,印象很深,当时对数仓各层的建设都停留在书上的理论知识,其实比较好的做法是要推动这些前端、后端技术人员去统一的。这种人工维护,到后面活动越来越多、上线频繁的情况下是很难受的。能用脚本实现的已经尽量用脚本去做了,比如爬虫爬文档(当时还没有后台!!)
说多了都是泪

例子二

业务背景:推荐前后端场景映射、推荐sce_name和广告scene_id映射
1、中台会根据埋点区分场景根据page_sn、ext参数等规则使用udf在流量表中生成sce_loc_name(推荐业务通用)字段,由于数据报表开发的需要,会将sce_loc_name作为最小场景粒度,映射到一级数据场景sce_name、二级数据场景upper_sce_name,日常报表都是使用数据场景sce_name。
2、业务发展中,要和广告数据对比效果,接入广告数据时中台不愿意映射sce_loc_name做到表中(由于业务原因和权限问题,我自己也没法做),被迫只能使用scene_id
3、第二家公司平台很成熟

下面的简单代码中会涉及到的表:
(1)dwb_flow_impr_i_d
(uid,state_time,sce_name,page_sn,page_el_sn,ext,sce_loc_name)
(2)dim_sce_name ( sce_loc_name,sce_name)
(3)dim_upper_sce_name ( sce_name, upper_sce_name) 一二级场景映射关系,开发dws、rpt最常用
(4)dim_ad_scene_id (scene_id, sce_name) 广告场景映射,出现scene_id对应 一或二级场景的情况,如果对应二级场景维表汇总使用upper_sce_name
(5)dim_scene ( scene, sce_name ) 后端场景和数据场景映射,java枚举类,后端联系数据开发维护

这些表都是跟着业务慢慢出现的
一、dim_upper_sce_name
1、先说说刚进公司的时候是代码是怎么写一二级场景逻辑的:

select sce_name from dwb_flow_impr_i_d union all select 'ABC'  AS sce_name from dwb_flow_impr_i_d where sce_name IN ( a,b,c ) union all  ……

随着将sce_name合并成某个二级场景的需求越来越多代码越来越长,不停增加union all,提议用维表
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |

代码改写:

set hive.auto.convert.join=true;  select t1.sce_name,t2.upper_sce_name from dwb_flow_impr_i_d t1 join dim_upper_sce_name t2 ON t1.sce_name = t2.sce_name  -- 计算曝光次数 select upper_sce_name     ,count(1)  AS impr_cnt_1d from(     select t1.sce_name,t2.upper_sce_name     from dwb_flow_impr_i_d t1     join dim_upper_sce_name t2      ON t1.sce_name = t2.sce_name )t  group by upper_sce_name

2、开始作妖了,场景AB_double口径:sce_name IN (a,b) AND ext[‘type’] = 2
产品内容的单双列展示实验,需要看双列的数据效果

set hive.auto.convert.join=true; select upper_sce_name     ,count(1)  AS impr_cnt_1d from(     select t1.sce_name         ,t2.upper_sce_name     from dwb_flow_impr_i_d t1     join dim_upper_sce_name t2      ON t1.sce_name = t2.sce_name     union all      select sce_name         ,'AB_double'     from dwb_flow_impr_i_d      where sce_name IN(a,b) AND ext['type'] = 2 )t  group by upper_sce_name

很无奈,维表无法支持,只能特殊处理了,维表变成这样:
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |
| AB_double | AB_double|

3、继续,这次要将sce_name拆开,是常见的app上的tab实验,为了分tab看数据效果。而这个sce_name对应的sce_loc_name是这样的(即上文提到的dim_sce_loc_name表的内容):
| sce_loc_name|sce_name|
| ——– | ——– |
| /c_tab | c|

注意下这个内容是不可变的,定下的通用场景,不可以根据某组特殊业务需求变更。我开始写代码了

-- 以下省略2、中的特殊处理 select      (case when sce_name = 'c' AND page_el_sn = '1' THEN 'c_tab1'         when sce_name = 'c' AND page_el_sn = '2' THEN 'c_tab2'          when sce_name = 'c' then 'c_tab_other'      else sce_name end) AS sce_name       ,t2.upper_sce_name from dwb_flow_impr_i_d t1 join dim_upper_sce_name t2 

维表变化:
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |
| AB_double| AB_double|
| c_tab1 | c_tab1|
| c_tab2 | c_tab2|
| c_tab_other | c_tab_other|
| c_tab1 | c|
| c_tab2 | c|
| c_tab_other | c|

这时候可以感受下第三行,在没有做上面sce_name的case when处理的任务中,直接看c场景数据是没问题的,如果去掉了,这个场景就看不了,所以保留。

二、来撸第二张表 dim_ad_scene
需求:报表上展示的是推荐业务侧的场景名(因为我在推荐组)。
这样开始吧,为了简单。
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| d | d |
| e | e |
| d | DE |
| e | DE |

scene_id sce_name
1 a
2 DE
3 b

增加一张广告表dwb_flow_ad_cpc_clk_i_d(uid, ad_id, scene_id, spend, ext)
这里呢是要和推荐场景看齐,希望能单独看d、e场景的数据,然后在广告业务方scene_id=2是最小场景,推荐侧d、e组成的二级场景DE。
开始写代码,自由发挥(看的时候注意下sql里的注释吧)

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select *     from dim_ad_scene     WHERE scene_id!=2 -- 将2拆分成d、e,给一个自定义id     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002' )ad left join dim_upper_sce_name rec  ON ad.sce_name = rec.sce_name ;   -- case when 解释:推荐侧场景可以根据埋点规则来区分别忘了这点 select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d  )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

看起来完美解决问题了。业务开始变了,DE+其它非推荐 = 2,这时候发现报表中场景选DE数据对不上了。我自闭了。

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select *     from dim_ad_scene     WHERE scene_id!=2     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002'     -- 增加其他非推荐     UNION ALL      select 'z999','2999' )ad left join (     select sce_name,upper_sce_name     from dim_upper_sce_name rec     -- 将这3部分放进2_total(新二级场景)     UNION ALL      select 'z999','2_total'     UNION ALL      select 'd','2_total'     UNION ALL      select 'e','2_total' ON ad.sce_name = rec.sce_name ;   select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             when scene_id=2 then 2999             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d  )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

到这里,要增加推荐侧特殊场景AB_double 知道该怎么做了吧

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select sce_name,scene_id     from dim_ad_scene     WHERE scene_id!=2     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002'     UNION ALL      select 'z999','2999'     -- 增加AD_double     union all      select 'AB_double',9999 )ad left join (     select sce_name,upper_sce_name     from dim_upper_sce_name rec     -- 将这3部分放进2_total(新二级场景)     UNION ALL      select 'z999','2_total'     UNION ALL      select 'd','2_total'     UNION ALL      select 'e','2_total' ON ad.sce_name = rec.sce_name ;   select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             when scene_id=2 then 2999             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d      UNION ALL  -- 增加AB_double的自定义scene_id:9999     select         pt         ,uid         ,spend         ,9999 scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d      where scene_id IN(1,3) AND ext['type'] = 2 )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

做这种事情做多了挺烦的。明明用udf映射一下就简单多了

三、dim_scene 后端和数据场景
直接吧后端java 的枚举类拿过来就完事了

维表设计的感悟
已下是工作中真实遇到的问题,几次做需求的过程中慢慢体会到的一些感受和不成熟总结(毕竟工作经验2年),纯理论知识:参考《阿里大数据之路》

感觉篇幅太长了,把感受写在最前面吧:

1、如果是类似枚举值的这种,直接维护就行了
2、桥表,很有讲究,比如很常见的商品一二三级类目,业务中可能会有很复杂的情况,见例子二。最完美的情况是能确定最小粒度字段,并且在业务中只会扩大不会缩小。特殊情况就找点小技巧小聪明处理下吧

例子一

业务背景:h5活动前端使用的activity_id和后端act_id 配置映射
(第一家公司平台化刚起步,所以下面有些方式比较蠢)
ods_app_activity_config:/data/pythom/h5_activity.py
通过这个爬虫脚本维护在mysql中,每天在凌晨3点爬取一遍数据,重写mysql表并同步到hive中使用。可能出现活动已经上线但是没在文档添加的情况,会导致这张配置表的数据有问题。
问题:
1、完全没有规律,h5就是用文档维护。
2、其中有一个招募活动有各种模板配置,h5后端和服务后端通过另一个id关联活动信息。

算是离职前做的比较大的需求,印象很深,当时对数仓各层的建设都停留在书上的理论知识,其实比较好的做法是要推动这些前端、后端技术人员去统一的。这种人工维护,到后面活动越来越多、上线频繁的情况下是很难受的。能用脚本实现的已经尽量用脚本去做了,比如爬虫爬文档(当时还没有后台!!)
说多了都是泪

例子二

业务背景:推荐前后端场景映射、推荐sce_name和广告scene_id映射
1、中台会根据埋点区分场景根据page_sn、ext参数等规则使用udf在流量表中生成sce_loc_name(推荐业务通用)字段,由于数据报表开发的需要,会将sce_loc_name作为最小场景粒度,映射到一级数据场景sce_name、二级数据场景upper_sce_name,日常报表都是使用数据场景sce_name。
2、业务发展中,要和广告数据对比效果,接入广告数据时中台不愿意映射sce_loc_name做到表中(由于业务原因和权限问题,我自己也没法做),被迫只能使用scene_id
3、第二家公司平台很成熟

下面的简单代码中会涉及到的表:
(1)dwb_flow_impr_i_d
(uid,state_time,sce_name,page_sn,page_el_sn,ext,sce_loc_name)
(2)dim_sce_name ( sce_loc_name,sce_name)
(3)dim_upper_sce_name ( sce_name, upper_sce_name) 一二级场景映射关系,开发dws、rpt最常用
(4)dim_ad_scene_id (scene_id, sce_name) 广告场景映射,出现scene_id对应 一或二级场景的情况,如果对应二级场景维表汇总使用upper_sce_name
(5)dim_scene ( scene, sce_name ) 后端场景和数据场景映射,java枚举类,后端联系数据开发维护

这些表都是跟着业务慢慢出现的
一、dim_upper_sce_name
1、先说说刚进公司的时候是代码是怎么写一二级场景逻辑的:

select sce_name from dwb_flow_impr_i_d union all select 'ABC'  AS sce_name from dwb_flow_impr_i_d where sce_name IN ( a,b,c ) union all  ……

随着将sce_name合并成某个二级场景的需求越来越多代码越来越长,不停增加union all,提议用维表
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |

代码改写:

set hive.auto.convert.join=true;  select t1.sce_name,t2.upper_sce_name from dwb_flow_impr_i_d t1 join dim_upper_sce_name t2 ON t1.sce_name = t2.sce_name  -- 计算曝光次数 select upper_sce_name     ,count(1)  AS impr_cnt_1d from(     select t1.sce_name,t2.upper_sce_name     from dwb_flow_impr_i_d t1     join dim_upper_sce_name t2      ON t1.sce_name = t2.sce_name )t  group by upper_sce_name

2、开始作妖了,场景AB_double口径:sce_name IN (a,b) AND ext[‘type’] = 2
产品内容的单双列展示实验,需要看双列的数据效果

set hive.auto.convert.join=true; select upper_sce_name     ,count(1)  AS impr_cnt_1d from(     select t1.sce_name         ,t2.upper_sce_name     from dwb_flow_impr_i_d t1     join dim_upper_sce_name t2      ON t1.sce_name = t2.sce_name     union all      select sce_name         ,'AB_double'     from dwb_flow_impr_i_d      where sce_name IN(a,b) AND ext['type'] = 2 )t  group by upper_sce_name

很无奈,维表无法支持,只能特殊处理了,维表变成这样:
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |
| AB_double | AB_double|

3、继续,这次要将sce_name拆开,是常见的app上的tab实验,为了分tab看数据效果。而这个sce_name对应的sce_loc_name是这样的(即上文提到的dim_sce_loc_name表的内容):
| sce_loc_name|sce_name|
| ——– | ——– |
| /c_tab | c|

注意下这个内容是不可变的,定下的通用场景,不可以根据某组特殊业务需求变更。我开始写代码了

-- 以下省略2、中的特殊处理 select      (case when sce_name = 'c' AND page_el_sn = '1' THEN 'c_tab1'         when sce_name = 'c' AND page_el_sn = '2' THEN 'c_tab2'          when sce_name = 'c' then 'c_tab_other'      else sce_name end) AS sce_name       ,t2.upper_sce_name from dwb_flow_impr_i_d t1 join dim_upper_sce_name t2 

维表变化:
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |
| AB_double| AB_double|
| c_tab1 | c_tab1|
| c_tab2 | c_tab2|
| c_tab_other | c_tab_other|
| c_tab1 | c|
| c_tab2 | c|
| c_tab_other | c|

这时候可以感受下第三行,在没有做上面sce_name的case when处理的任务中,直接看c场景数据是没问题的,如果去掉了,这个场景就看不了,所以保留。

二、来撸第二张表 dim_ad_scene
需求:报表上展示的是推荐业务侧的场景名(因为我在推荐组)。
这样开始吧,为了简单。
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| d | d |
| e | e |
| d | DE |
| e | DE |

scene_id sce_name
1 a
2 DE
3 b

增加一张广告表dwb_flow_ad_cpc_clk_i_d(uid, ad_id, scene_id, spend, ext)
这里呢是要和推荐场景看齐,希望能单独看d、e场景的数据,然后在广告业务方scene_id=2是最小场景,推荐侧d、e组成的二级场景DE。
开始写代码,自由发挥(看的时候注意下sql里的注释吧)

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select *     from dim_ad_scene     WHERE scene_id!=2 -- 将2拆分成d、e,给一个自定义id     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002' )ad left join dim_upper_sce_name rec  ON ad.sce_name = rec.sce_name ;   -- case when 解释:推荐侧场景可以根据埋点规则来区分别忘了这点 select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d  )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

看起来完美解决问题了。业务开始变了,DE+其它非推荐 = 2,这时候发现报表中场景选DE数据对不上了。我自闭了。

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select *     from dim_ad_scene     WHERE scene_id!=2     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002'     -- 增加其他非推荐     UNION ALL      select 'z999','2999' )ad left join (     select sce_name,upper_sce_name     from dim_upper_sce_name rec     -- 将这3部分放进2_total(新二级场景)     UNION ALL      select 'z999','2_total'     UNION ALL      select 'd','2_total'     UNION ALL      select 'e','2_total' ON ad.sce_name = rec.sce_name ;   select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             when scene_id=2 then 2999             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d  )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

到这里,要增加推荐侧特殊场景AB_double 知道该怎么做了吧

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select sce_name,scene_id     from dim_ad_scene     WHERE scene_id!=2     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002'     UNION ALL      select 'z999','2999'     -- 增加AD_double     union all      select 'AB_double',9999 )ad left join (     select sce_name,upper_sce_name     from dim_upper_sce_name rec     -- 将这3部分放进2_total(新二级场景)     UNION ALL      select 'z999','2_total'     UNION ALL      select 'd','2_total'     UNION ALL      select 'e','2_total' ON ad.sce_name = rec.sce_name ;   select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             when scene_id=2 then 2999             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d      UNION ALL  -- 增加AB_double的自定义scene_id:9999     select         pt         ,uid         ,spend         ,9999 scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d      where scene_id IN(1,3) AND ext['type'] = 2 )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

做这种事情做多了挺烦的。明明用udf映射一下就简单多了

三、dim_scene 后端和数据场景
直接吧后端java 的枚举类拿过来就完事了

维表设计的感悟
已下是工作中真实遇到的问题,几次做需求的过程中慢慢体会到的一些感受和不成熟总结(毕竟工作经验2年),纯理论知识:参考《阿里大数据之路》

感觉篇幅太长了,把感受写在最前面吧:

1、如果是类似枚举值的这种,直接维护就行了
2、桥表,很有讲究,比如很常见的商品一二三级类目,业务中可能会有很复杂的情况,见例子二。最完美的情况是能确定最小粒度字段,并且在业务中只会扩大不会缩小。特殊情况就找点小技巧小聪明处理下吧

例子一

业务背景:h5活动前端使用的activity_id和后端act_id 配置映射
(第一家公司平台化刚起步,所以下面有些方式比较蠢)
ods_app_activity_config:/data/pythom/h5_activity.py
通过这个爬虫脚本维护在mysql中,每天在凌晨3点爬取一遍数据,重写mysql表并同步到hive中使用。可能出现活动已经上线但是没在文档添加的情况,会导致这张配置表的数据有问题。
问题:
1、完全没有规律,h5就是用文档维护。
2、其中有一个招募活动有各种模板配置,h5后端和服务后端通过另一个id关联活动信息。

算是离职前做的比较大的需求,印象很深,当时对数仓各层的建设都停留在书上的理论知识,其实比较好的做法是要推动这些前端、后端技术人员去统一的。这种人工维护,到后面活动越来越多、上线频繁的情况下是很难受的。能用脚本实现的已经尽量用脚本去做了,比如爬虫爬文档(当时还没有后台!!)
说多了都是泪

例子二

业务背景:推荐前后端场景映射、推荐sce_name和广告scene_id映射
1、中台会根据埋点区分场景根据page_sn、ext参数等规则使用udf在流量表中生成sce_loc_name(推荐业务通用)字段,由于数据报表开发的需要,会将sce_loc_name作为最小场景粒度,映射到一级数据场景sce_name、二级数据场景upper_sce_name,日常报表都是使用数据场景sce_name。
2、业务发展中,要和广告数据对比效果,接入广告数据时中台不愿意映射sce_loc_name做到表中(由于业务原因和权限问题,我自己也没法做),被迫只能使用scene_id
3、第二家公司平台很成熟

下面的简单代码中会涉及到的表:
(1)dwb_flow_impr_i_d
(uid,state_time,sce_name,page_sn,page_el_sn,ext,sce_loc_name)
(2)dim_sce_name ( sce_loc_name,sce_name)
(3)dim_upper_sce_name ( sce_name, upper_sce_name) 一二级场景映射关系,开发dws、rpt最常用
(4)dim_ad_scene_id (scene_id, sce_name) 广告场景映射,出现scene_id对应 一或二级场景的情况,如果对应二级场景维表汇总使用upper_sce_name
(5)dim_scene ( scene, sce_name ) 后端场景和数据场景映射,java枚举类,后端联系数据开发维护

这些表都是跟着业务慢慢出现的
一、dim_upper_sce_name
1、先说说刚进公司的时候是代码是怎么写一二级场景逻辑的:

select sce_name from dwb_flow_impr_i_d union all select 'ABC'  AS sce_name from dwb_flow_impr_i_d where sce_name IN ( a,b,c ) union all  ……

随着将sce_name合并成某个二级场景的需求越来越多代码越来越长,不停增加union all,提议用维表
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |

代码改写:

set hive.auto.convert.join=true;  select t1.sce_name,t2.upper_sce_name from dwb_flow_impr_i_d t1 join dim_upper_sce_name t2 ON t1.sce_name = t2.sce_name  -- 计算曝光次数 select upper_sce_name     ,count(1)  AS impr_cnt_1d from(     select t1.sce_name,t2.upper_sce_name     from dwb_flow_impr_i_d t1     join dim_upper_sce_name t2      ON t1.sce_name = t2.sce_name )t  group by upper_sce_name

2、开始作妖了,场景AB_double口径:sce_name IN (a,b) AND ext[‘type’] = 2
产品内容的单双列展示实验,需要看双列的数据效果

set hive.auto.convert.join=true; select upper_sce_name     ,count(1)  AS impr_cnt_1d from(     select t1.sce_name         ,t2.upper_sce_name     from dwb_flow_impr_i_d t1     join dim_upper_sce_name t2      ON t1.sce_name = t2.sce_name     union all      select sce_name         ,'AB_double'     from dwb_flow_impr_i_d      where sce_name IN(a,b) AND ext['type'] = 2 )t  group by upper_sce_name

很无奈,维表无法支持,只能特殊处理了,维表变成这样:
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |
| AB_double | AB_double|

3、继续,这次要将sce_name拆开,是常见的app上的tab实验,为了分tab看数据效果。而这个sce_name对应的sce_loc_name是这样的(即上文提到的dim_sce_loc_name表的内容):
| sce_loc_name|sce_name|
| ——– | ——– |
| /c_tab | c|

注意下这个内容是不可变的,定下的通用场景,不可以根据某组特殊业务需求变更。我开始写代码了

-- 以下省略2、中的特殊处理 select      (case when sce_name = 'c' AND page_el_sn = '1' THEN 'c_tab1'         when sce_name = 'c' AND page_el_sn = '2' THEN 'c_tab2'          when sce_name = 'c' then 'c_tab_other'      else sce_name end) AS sce_name       ,t2.upper_sce_name from dwb_flow_impr_i_d t1 join dim_upper_sce_name t2 

维表变化:
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| b | b |
| c | c |
| a | ABC |
| b | ABC |
| c | ABC |
| AB_double| AB_double|
| c_tab1 | c_tab1|
| c_tab2 | c_tab2|
| c_tab_other | c_tab_other|
| c_tab1 | c|
| c_tab2 | c|
| c_tab_other | c|

这时候可以感受下第三行,在没有做上面sce_name的case when处理的任务中,直接看c场景数据是没问题的,如果去掉了,这个场景就看不了,所以保留。

二、来撸第二张表 dim_ad_scene
需求:报表上展示的是推荐业务侧的场景名(因为我在推荐组)。
这样开始吧,为了简单。
| sce_name |upper_sce_name |
| ——– | ——– |
| a | a |
| d | d |
| e | e |
| d | DE |
| e | DE |

scene_id sce_name
1 a
2 DE
3 b

增加一张广告表dwb_flow_ad_cpc_clk_i_d(uid, ad_id, scene_id, spend, ext)
这里呢是要和推荐场景看齐,希望能单独看d、e场景的数据,然后在广告业务方scene_id=2是最小场景,推荐侧d、e组成的二级场景DE。
开始写代码,自由发挥(看的时候注意下sql里的注释吧)

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select *     from dim_ad_scene     WHERE scene_id!=2 -- 将2拆分成d、e,给一个自定义id     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002' )ad left join dim_upper_sce_name rec  ON ad.sce_name = rec.sce_name ;   -- case when 解释:推荐侧场景可以根据埋点规则来区分别忘了这点 select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d  )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

看起来完美解决问题了。业务开始变了,DE+其它非推荐 = 2,这时候发现报表中场景选DE数据对不上了。我自闭了。

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select *     from dim_ad_scene     WHERE scene_id!=2     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002'     -- 增加其他非推荐     UNION ALL      select 'z999','2999' )ad left join (     select sce_name,upper_sce_name     from dim_upper_sce_name rec     -- 将这3部分放进2_total(新二级场景)     UNION ALL      select 'z999','2_total'     UNION ALL      select 'd','2_total'     UNION ALL      select 'e','2_total' ON ad.sce_name = rec.sce_name ;   select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             when scene_id=2 then 2999             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d  )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

到这里,要增加推荐侧特殊场景AB_double 知道该怎么做了吧

-- 报表专用维表 create table tmp_dim_ad_upper_scene AS select ad.scene_id     ,ad.sce_name     ,coalesce(rec.upper_sce_name,ad.sce_name)   AS upper_sce_name FROM(     select sce_name,scene_id     from dim_ad_scene     WHERE scene_id!=2     UNION ALL      select 'd','2001'     UNION ALL      select 'e','2002'     UNION ALL      select 'z999','2999'     -- 增加AD_double     union all      select 'AB_double',9999 )ad left join (     select sce_name,upper_sce_name     from dim_upper_sce_name rec     -- 将这3部分放进2_total(新二级场景)     UNION ALL      select 'z999','2_total'     UNION ALL      select 'd','2_total'     UNION ALL      select 'e','2_total' ON ad.sce_name = rec.sce_name ;   select b.upper_sce_name     ,sum(a.spend) from(     select         pt         ,uid         ,spend         ,(case when scene_id=2 AND page_sn = 1 then 2001 -- d场景             when scene_id=2 AND page_sn = 2 then 2002 -- e场景             when scene_id=2 then 2999             else scene_id          end)                    As scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d      UNION ALL  -- 增加AB_double的自定义scene_id:9999     select         pt         ,uid         ,spend         ,9999 scene_id     from xrec.xrec_dwb_flow_ad_billing_event_cpc_clk_i_d      where scene_id IN(1,3) AND ext['type'] = 2 )a join tmp_dim_ad_upper_scene b  ON a.scene_id = b.scene_id group by upper_sce_name

做这种事情做多了挺烦的。明明用udf映射一下就简单多了

三、dim_scene 后端和数据场景
直接吧后端java 的枚举类拿过来就完事了

部分转自互联网,侵权删除联系

赞(0) 打赏
部分文章转自网络,侵权联系删除b2bchain区块链学习技术社区 » 维表设计和非常规需求的碰撞求职学习资料
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

b2b链

联系我们联系我们