数仓建模知识点

维度建模和范式建模

Linux的创始人Torvalds有一段关于“什么才是优秀程序员”的话:“烂程序员关心的是代码,好程序员关心的是数据结构和它们之间的关系”,其阐述了数据模型的重要性。

参考文献:

范式建模

业务交易、服务于业务系统

  • 范式:即关系模式。一张数据表的表结构,符合的设计标准的级别
  • 目的在于降低数据的冗余性和保证数据的一致性。缺点是获取数据时,需要通过Join拼接出最后的数据。
  • 范式建模将事物抽象为 实体(Entity)、属性、关系(Relationship)来表示数据关联和事物描述。

优点:准3NF、节约存储、易扩展、结构清晰、前期投入大,但后期成本低
缺点:周期长,见效慢、构建繁琐、查询复杂、学习成本高、对业务理解和建模能力都要求很高

需要梳理清楚企业各个业务系统的实体,实体间的关系,实体的属性,它的实施周期长,而互联网行业是不断探索,不断迭代的过程,当你还没有梳理清楚的时候,业务就已经发生了改变,甚至当你的数仓还没建好的时候,有可能这个企业已经黄了。所以就有了我接下来讲的维度建模。

三范式

  • 第一范式:保证每列的原子性,比如说地址字段拆分成国家、城市等原子字段
  • 第二范式:保证一张表只描述一件事情
  • 第三范式:保证每列都和主键直接相关,表中的字段和主键直接对应不依靠其他中间字段,说白了就是,决定某字段值的必须是主键。

维度建模

分析、决策、服务于分析系统

维度建模是面向分析场景而生,针对分析场景构建数仓模型。它重点关注快速、灵活的解决分析需求,同时能够提供大规模数据的快速响应性能。不需要完整的梳理企业业务流程和数据,实施周期根据主题边界而定,容易快速实现,这也符合互联网行业的特点。

优点:方便使用、适合进行OLAP操作、建模方法简单、迭代式建设见效快
缺点:反范式、浪费存储、不易扩展、维度补全会造成存储的浪费,维度变化对数据的更新量大、前期成本低,后期会逐渐升高

次周复购率指标如何抽象通用的查询逻辑

分子是当前周的:复购客户数

分母是上周的:合作客户数

指标名 数仓字段 字段含义
次日复购率 rebuy_dt 当天客户跟昨天有交集的复购客户
次周复购率 rebuy_wk 当天客户跟上周一周有交集的复购客户
次月复购率 rebuy_mo 当天客户跟上月一月有交集的复购客户

分子和分母的维度实际上是一样的,但是分母的时间维度的值需要是上个周期的时间;通过sql查询两个时间周期然后join在一起,在最外层进行指标计算,完全可以解决这个指标的计算问题;但是如果通过起源进行指标的计算,如何描述这个指标的计算逻辑?

如何知道某个客户是次周复购的

通过给客户打标的方式,这种方式不可行,原因:

  • 表中存储的数据粒度是到sku的,在sku粒度给客户复购打标签,sku粒度的复购通过这个标签计算是没问题的
  • 但是如果聚合到品类,通过sku的标签网上聚,复购数会减少很多,比如说看三级品类的复购,复购这个三级品类下的某个skuA肯定是复购,但是昨天买了这个三级品类下的skuB,今天买了这个三级品类下的skuA,仍然是这个三级品类的复购
  • 如果按照上面给sku粒度打复购的标签,会导致品类的复购数量减少很多
  • 正常应该先得到今天整个三级品类的购买客户id,再得到昨天整个三级品类的购买客户id,两个id求交集
1
SELECT  bitmap_count(bitmap_and(bitmap_union(customer_cnt),bitmap_union(customer_cnt)))  from app_bi_dashboard_cust_spu_sales_day_withpop  LIMIT 10

实现次周复购的思路:

  1. 查询两次,因为分子和分母的时间维度不同,分子查询本周复购客户数,分母查询上周合作客户数;这种查询方案实现起来还好,但是起源不支持一个指标通过查询两次进行计算。
  2. 冗余一列,叫上周同日合作客户id,因为数仓给的是天粒度的数据,所以每天的数据,在当天的合作客户id(customer_id)的基础上,冗余一列,上周同日的合作客户id(last_wk_customer_id)(如果当前的客户id在上周同日也出现过,那么这个字段就是这个客户id,如果没出现过,这个字段是空),这样计算周的复购的时候,只需要查询一次:bitmap_count(bitmap_and(bitmap_union(customer_id),bitmap_union(last_wk_customer_id)))/bitmap_count(last_wk_customer_id) where wk=?
  3. 将复购指标通过单独的表提供,单独的表提供起来,可以不通过增加列的方式提供,增加一个叫做复购类型的字段,来表示当前购买的客户是哪天的,不同时间周期的指标,公式可以是一个,只要添加不同的type值就行了:``bitmap_count(bitmap_and(bitmap_union(customer_id),bitmap_union(last_customer_id)))/bitmap_count(last_customer_id) where wk=? and type=’last_wk’ `

上述方案2计算次月复购的问题

周每周都是7天,但是月并不是每月都是30天;例如9月份共30天,每天的客户id会跟8月对应的日期比较,记录复购客户id字段;但是8月份有31天,假设9.1有个客户id A,在8.1到8.30都没有购买过,这时候按照上述方案,不是复购的,但是8.31号有这个客户id A,应该算是复购。

所以上述方案的本质问题是,会导致上个周期天数多的情况,复购率会变低。

解决方案:

  • last_mo_customer_id,这个字段,不是记录的上月同天是否复购,而是记录上月周期是否有复购
  • 这里需要注意,上月周期并不一定是完整周期,如果当前周期不是完整周期(例如9.1-9.15),那么计算上月复购客户id的时候,需要对比的是上月同期(8.1-8.15)的客户id,判断是否复购;当然这个需要数仓来进行判断。

还存在的问题:

开始以为新加的last_wk_customer_id是,当天跟上周同天的合作客户的交集,所以说增加一列之后,数据条数是没有变化的,last_wk_customer_id这列要么跟customer_id相同,要么为空。但是这种方式有问题,因为次周复购率的口径是:本周复购客户数,除以上周的合作客户数,但是last_wk_customer_id字段不是上周的全量合作客户数,所以分母不能是这个字段,就没法计算了。

如果想要增加这个字段,last_wk_customer_id应该代表上周全量的客户id,但是这样的话,会导致数据量膨胀,所以不是个很好的方式。

这个问题的解决方案:数仓将上周的数据都放到周一那天下面,月的放在1号下面,不用每天都放上周全量的客户,但是仍然基本上是数据double了。

还有一种解决方式:

  • 新增的字段不是last_week,而是next_week,这样这个字段就可以跟下周一整周的数据做交集,有留存的就记录下来,保证这个字段的值要么是空,要么跟customer_id相同,计算的时候,用这个字段比上customer_id字段即可。
  • 但是需要注意的是,如果前端传的周是2675,那么我们实际查询的时候,需要传2674,或者让数仓给数据的时候,延迟一天

新增sku的不同周期的计算方式

基于数仓提供的是dt粒度的数据的场景

维度不同的数据如何合并到一起

比如说客户覆盖率这种指标,分子是当前所选类目的数据,分母是整个事业部的数据,分子和分母的维度是不同的。

在进行表设计的时候,无法通过union all的方式进行数据提供,因为事业部的数据,对应的类目id是空,union all之后,数据中会多出类目id为空的记录,这个是有问题的。

所以这种方式的查询,只能是通过sql进行join的查询,而不能通过提前进行数据生产的时候给出,比如说冗余一个全事业部客户数的字段,除非数据给的是聚合好的数据,只要是doris实时聚合的数据,就无法通过冗余字段的方式来提供。

具体的查询形式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select
m94460_0.`dt`,
m94460_0.`cat1_id`,
m94460_0.`cust_cnt_954218` / m94460_1.`cust_cnt_954218` `cust_cover_rate`
from
(
SELECT
t65418_0.`dt` `dt`,
t65418_0.`cat1_id` `cat1_id`,
bitmap_union_count(t65418_0.customer_id) `cust_cnt_954218`
FROM
kldp_data_stat.app_invite_business_analysis_integrate_view_p1 t65418_0
GROUP BY
t65418_0.`dt`,
t65418_0.`cat1_id`
) m94460_0
left join (
SELECT
t65418_0.`dt` `dt`,
bitmap_union_count(t65418_0.customer_id) `cust_cnt_954218`
FROM
kldp_data_stat.app_invite_business_analysis_integrate_view_p1 t65418_0
GROUP BY
t65418_0.`dt`
) m94460_1 on m94460_0.`dt` = m94460_1.`dt`

率类指标的日均

为什么率类指标的日均要分子相加除以分母相加,不能多天的值直接相加再除以天数?

例如销售额占比这个指标:

分子 分母 日均结果 整个指标 日均结果
1 1 100%
10000 500000 2%
(10000+1)/(500000+1)=2% (1+0.02)/2=51%

明显是分子分母分别相加之后的结果更合理

参考链接