日期维度上不能向上汇总的度量

Qing 20060927

和阿龙探讨一个问题，其实已经不止一次地说起了。只是不知道该如何为他命名，差不多就是跟以前遇到的"通话用户数"问题类似，他这里是订购用户数。这个值，在不同时间粒度上，是需要去重计算的。例如下面一组数，想象这是一个用户订购表，每条记录表示用户发生了订购或退订，date表示日期，usr表示用户，act表示订购（s）/退订（u）。
 
date   usr act
20060901 a s
20060901 b s
20060901 c s
20060902 a u
20060902 b s
20060902 a s
20060902 a u
20060903 c u
20060903 c s
20060903 b u

看，一个用户一天可以多次发生订购、退订行为。

用户a在1号发生了订购，2号退订，然后当天又发生一次订购－退订。如果做一个透视表，选择date维度，度量选择对act计数，肯定会得到这样的结果：从一号到三号里面，订购6次，退订4次。其实这个度量值更加准确的是"订购/退订次数"。但通常需要统计的是"订购/退订用户数"。那么，可以采用去重计数，对usr去重。于是得到：订购用户数3户，退订用户数3户。

也就是说，如果要计算"订购用户数"的时候，必须要去重处理，采用count distinct 方法。显然，这样的计算，需要在日期维度的每个级别上重新运算，而不能简单地从细粒度往粗粒度聚集。比如先计算出每日的订购、退订用户数，如下：

日期  订购用户数 退订用户数

20060901  3   0
20060902  2   1
20060903  1   2
从这个日粒度的数据中，是无法得到下面的结果：
日期跨度 订购用户数 退订用户数
0901-0903 3   3

这种情况，唯一的方法似乎就是对日期维度的每个级别上去重计算，统计这种度量。但痛苦的是，这种去重计算需要最细粒度的原子数据。上面的例子是抽象简化过得，看不出运算量。假设是统计通话用户数，统计日的通话用户数，就得需要count distinct那天所有的话单，统计半年的通话用户数，就得count distinct半年的话单。半年的话单是个什么概念，多少亿条记录啊，算这个数可不容易，况且，还不一定存那么长时间的话单呢。
虽然还有别的替代办法。例如统计半年或一年的通话用户数，就不用在原子数据上count distinct了，太费时间。可以做月度的汇总信息，用户在某月是否通话，通话了就是1，未通话就是0，这可以在一个数量级较小的集合上count distinct，只要带上条件，是否通话＝1。

但这只是解决了些许性能问题。如果要设计一个cube，从日粒度上钻到月粒度，这两个订购用户数并没有累加或者其他什么逻辑运算关系。怎么设计这个cube呢？我以为olap工具应该能够解决这个问题的，但不知道它们分别称呼这种问题为什么。反正，cube一般都是预先将聚集计算好的，因此只要在数据装入cube的时候，允许以什么样的规则聚集。比如求和、计数，或者是期末、期初值等等。没有具体尝试过，因此这种想法也只是猜测。

上面的例子经过整理的，还简单些，往复杂想。拿通话用户数的统计来举例子，一条话单里面，有漫游、长途的标志，那么如果统计某个周期，漫游通话用户数、长途通话用户数呢？是不是复杂些？那样，你得先聚集一个日的汇总表，针对每个用户，有是否通话、是否有长途、是否有漫游等等，这样的"是否"恐怕还会有很多。这些其他维度跟这个度量值之间是否还有什么制约关系呢？

很不清晰的问题，也不知道是否表达恰当，指望能够将它讨论清楚。先在标题中给出"日期维度上不能向上汇总的度量"，比较长，也比较拗口，却也想不出什么好名字。

周勇 20060927

这次qing说的东西到我关注的实际问题了，我也就目前我做的地市级BOSS经营分析项目结合此问题说说我的思路吧。不管好不好大家评价一下。

可能大家开始就对我做的项目有点迷惑，现在目前全省都在上集中的经分系统和数据集市，这些东西在大局上有很多好东西，这个是我们不可质疑的，但是还是缺少一些量身定做的东西，以及及时响应用户的需求。我们为地市做的项目就是走这个空当才有事情可做，有饭可吃。

引用QING:"拿通话用户数的统计来举例子，一条话单里面，有漫游、长途的标志，那么如果统计某个周期，漫游通话用户数、长途通话用户数呢？是不是复杂些？那样，你得先聚集一个日的汇总表，针对每个用户，有是否通话、是否有长途、是否有漫游等等"。

这类问题在数据分析中不胜枚举，我在前面的邮件中对用户细分也是会把人搞得头晕脑涨。

对于QING所说得东东，本人看出两点是需要我们解决的问题：

   1）考虑数据汇总性能问题；
   2）数据抽取的维度指标；

我这边对于这类问题的解决方法是：由用户自定义抽取的KPI指标库(按照大家的叫法应该是维度)、以及各个KPI需要关联的信息指标（也就是需要抽取的字段）。

在很关键上一步指标库确定的基础上，我们抽取原始数据后，在原始数据的基础上，后台是用ORACLE数据库的ocic/c++来实现，根据定义的指标库进行多条路径并发数据的ETL(不知道这个词用在这正不正确)，进行不同粒度和多层次的抽取。

对于上面类似用户"订购－退订"的问题，在处理原始表中首先按照定义的规则进行过滤分拣，a)、上面的表可以由用户定义规则进行分拣成"订购表"和"退订表"，b)、在分拣表的基础上，按照定义的规则做日汇总数据；c)、在日汇总的数据基础上，更新月数据汇总。以上的数据只在原有数据的基础上做新数据的更新。

在BOSS中，对业务办理（很有点雷同"订购－退订"），话单处理，用户细分等可能在处理对性能的要求比较高，这个也是在项目中需要追求的目标。

我这边没有使用专有的ETL工具，全都是结合oci来实现，这样也好，虽然有 很多的不足，但是没有受工具的局限性，实现比较灵活。

以上是我的一点点理解，希望更深的探讨。

Goldenfish 20060927

Cognos提供了一种external的rollup方法，可以索引到一个外部表，去查找rollup后的维度取值，以解决不是常见的sum、average、Max、Min或者这些算符组合的情况。但我觉得，这种问题已经是在OLAP处理的范围边界上了，OLAP最正常应处理的是可加的度量，或以可加的度量为基础，进行算术或sum、average、Max、Min等运算。

阿龙 20060928

看来这个还是应该从指标体系设计上解决了，过去设计指标体系的时候考虑的都是kpi，分析指标，细节数据等。没有考虑总量、相对量等之类的统计学划分，也许从指标体系建设也应该分成业务层，逻辑层，和物理层了。

Qing 20060929

goldenfish给出的方法是我期望的，这能够解决这类度量在cube里面上下钻取的操作。
 
至于jonseluo所言，采用molap实现比较痛苦，而用rolap可以解决。我想如果goldenfish的方法可行的话，两者是一样的。
 
对于molap来说，一个cube即存放了细节数据，也存储了聚集数据。最常见的情况，是细节数据通过常规聚集方法，例如sum、count、avg、max，或者期末、期初等，生成聚集数据。对于rolap来说，一个事实表一般存储特定粒度的数据，也是可以通过聚集函数往上聚集。但对于这里的例子，日粒度的订购用户数根本没有一个聚集函数能够汇总到月的聚集函数。因此，如果是rolap来实现，恐怕也得用"聚集导航"的技术，设计一些聚集表，比如最基础的是日粒度事实表，然后还有周粒度、月粒度的汇总表。当用户发出"月订购用户数"这种查询时，最终的sql将定位到月粒度汇总表上。而想想在molap的方式，如果有了三个粒度的表，分别装入到cube里面去，不也是一样可以实现，没有增加多少复杂度，甚至在性能上更好一些。
 
通过以上朋友们的探讨，我想总结一下，看是不是这样理解的。
 
对于统计那种在日期维度上不可汇总的度量时，用原来的例子举例，假设细节事实表结构为：

(日, 订购用户数 )
 
如果要统计周、月、半年的订购用户数，必须事先准备好另外三张汇总表。它们都得从原子数据计算得到，不能从日粒度表直接汇总，分别是

(周，订购用户数)
(月，订购用户数)
(半年，订购用户数)
 
还有没有其他方法？另外这是最简单的一种形式，假设，还有另外一个维度，订购类型，分成网上订购和手机订购。那么日粒度的事实表结构为：

(日，订购类型，订购用户数)
 
显然，也不能从这个事实表汇总成为（日，订购用户数）。比如1号，通过网上订购的有两个用户，手机订购的有3个，就不能说1号的订购用户数一定为5个。在这种情况下，如果要建聚集表的话，就得将每个维度组合，日+订购类型，周＋订购类型，月+...，如果再有一个维度（如订购时间段）呢？聚集表的数目还会膨胀。
 
这个问题该如何解决呢？
 
或者，根本这不是问题，对于这类度量的统计，只有存在需求，才去统计，不用事先考虑所有可能的维度。
 
我又想到一个例子，自己实际的应用，就是网站的访问量统计。在这个例子里面，访问者（visitor）和访问量（pageviews）几乎总是要放在一起展示的，而对访问者的统计，显然，日、月、时段，甚至你可以任意选择一个时间段，那样访问者这个值都得重新计算的，不知他是怎么设计的。

li-zhengguo 20060929

qing可能长期从事电信方向研究，其实银行里很多指标都不能通过olap工具来实现汇总的。前面有网友说cognos external模式，这正是我们现在所采用的。在我做的项目中，构建了一个数据集市，保存了机构，时间，指标三种维度以及指标值一种度量的汇总数据。其中机构分三层，时间分4种类型（月，季，半年，前几月），指标分成指标大类和指标。数据集市保存三种维度各个层次的各个类型叉乘后的汇总指标值。在生成cube时，采用cognos external的方式，以维表为框架，以事实表为元素，搭建立方体。

为什么要采用这种方式呢？因为银行里很多指标（比如客户数，比率等）是不能通过简单汇总得到的，如果硬要使用olap工具中的一些rollup方式去实现，势必使cube的设计非常复杂。可能有人会问为什么客户数也是不可以简单汇总的，这是因为客户可以在多个机构开设帐户，而某一层结点统计客户数时，要取其子结点的客户作count distinct，这就类似了qing说的这个问题。

微信扫一扫实时了解行业动态

微信扫一扫分享本文给好友