Fw: 滚动加权指标趋势图生成问题

newkid

8 楼贴出的结果和我测试的结果不符，能否把数据再减少一下，少到可以手工验算的程度？

少佩恩S

newkid 发表于 2021-4-2 05:08
由于 SQL SERVER 没有像ORACLE 那样的 PARTITION OUTER JOIN, 所以要稠密化数据就很麻烦，要么像楼主那样做 ...

经前辈提醒，我发觉我构造的伪数据有问题：主要是数据空洞有问题
举例来说，伪数据中此行为空：

1. select * from [dbo].[PseudoDataTable]
   
2. where SOM = '2016-02-01'
   
3.         and Gender = 'M' and DistChannel = 'agent' and Product = 'Bronze';

复制代码

但是，2016年1月和2016年3月都有客户，这个是不合理的。

1. select * from [dbo].[PseudoDataTable]
   
2. where
   
3.         Gender = 'M' and DistChannel = 'agent' and Product = 'Bronze'
   
4. order by SOM;

复制代码

SOM	Gender	DistChannel	Product	NCustomersSOM	NCustomersEOM	RemainingNCustomerEOM	NewCustomers
1/1/2016	M	agent	Bronze	19064	10669	9661	1008
3/1/2016	M	agent	Bronze	12605	10836	10317	519
4/1/2016	M	agent	Bronze	16783	17712	16053	1659
5/1/2016	M	agent	Bronze	11268	10560	8771	1789
6/1/2016	M	agent	Bronze	11694	13446	11463	1983
7/1/2016	M	agent	Bronze	16941	**06	9299	807
8/1/2016	M	agent	Bronze	16021	17167	13035	4132
9/1/2016	M	agent	Bronze	10342	10759	9555	1204
10/1/2016	M	agent	Bronze	16747	17304	16490	814
11/1/2016	M	agent	Bronze	11593	13501	10468	3033
12/1/2016	M	agent	Bronze	13619	19017	10984	8033
1/1/2017	M	agent	Bronze	10965	13299	10340	2959
2/1/2017	M	agent	Bronze	12292	16181	12131	4050

现实中，NCustomersEOM = 下一个月的NCustomersSOM


现实中，出现空洞的行的情况普遍是某一业务基本未开展。假设agent这个销售渠道不推广Bronze产品，那么就没有这个维度组合的数据。

假设agent在2021年之前都不销售Bronze产品，在2020年开始才进行销售，产生以下数据

1. drop table if exists [dbo].PseudoDataTable;
   
2. create table [dbo].PseudoDataTable (
   
3.     SOM date,
   
4.     Gender char(1),
   
5.     DistChannel varchar(8),
   
6.         Product varchar(8),
   
7.         NCustomersSOM int,
   
8.         NCustomersEOM int,
   
9.         RemainingNCustomerEOM int,
   
10.         NewCustomers int
    
11. );

复制代码

1. insert into [dbo].PseudoDataTable values('2020-12-01', 'M', 'agent', 'Bronze', 0, 0, 0, 0);
   
2. insert into [dbo].PseudoDataTable values('2021-01-01', 'M', 'agent', 'Bronze', 0, 100, 0, 100);
   
3. insert into [dbo].PseudoDataTable values('2021-02-01', 'M', 'agent', 'Bronze', 100, 110, 90, 20);
   
4. insert into [dbo].PseudoDataTable values('2021-03-01', 'M', 'agent', 'Bronze', 110, 140, 100, 40);

复制代码

再将之前的维度表构造代码运行一遍

1. drop table if exists [dbo].[PseudoDataTableUnikDmsn];
   
2. select distinct
   
3.       [Gender]
   
4.       ,[DistChannel]
   
5.       ,[Product]
   
6. into [dbo].[PseudoDataTableUnikDmsn]
   
7. from [dbo].[PseudoDataTable];
   
8. 
   
9. alter table [dbo].[PseudoDataTableUnikDmsn]
   
10. add ID int identity(1,1);
    
11. 
    
12. drop table if exists [dbo].[PseudoDataTablewithDM];
    
13. select
    
14.         B.*,
    
15.         Dm.ID
    
16. into [dbo].[PseudoDataTablewithDM]
    
17. from [dbo].[PseudoDataTable] as B
    
18. inner join [dbo].[PseudoDataTableUnikDmsn] as Dm
    
19. on B.[DistChannel] = Dm.[DistChannel]
    
20. and B.[Gender] = Dm.[Gender]
    
21. and B.[Product] = Dm.[Product];
    
22. 
    
23. 
    
24. drop table if exists #Final;
    
25. select
    
26.         M.*, D.SOM,
    
27.         A1.NCustomersSOM AS NCustomersSOM1,
    
28.         A1.RemainingNCustomerEOM AS RemainingNCustomerEOM1,
    
29.         A2.NCustomersSOM AS NCustomersSOM2,
    
30.         A2.RemainingNCustomerEOM AS RemainingNCustomerEOM2,
    
31.         A3.NCustomersSOM AS NCustomersSOM3,
    
32.         A3.RemainingNCustomerEOM AS RemainingNCustomerEOM3
    
33. 
    
34. INTO #FINAL
    
35. from [dbo].[PseudoDataTableUnikDmsn] as M
    
36. inner join (
    
37.         select
    
38.                 distinct SOM
    
39.         from [dbo].[PseudoDataTablewithDM]
    
40.         ) as D
    
41. on 1 = 1
    
42. left join [dbo].[PseudoDataTablewithDM] as A1
    
43. on M.ID = A1.ID and dateadd(month, 0, D.SOM) = A1.SOM
    
44. left join [dbo].[PseudoDataTablewithDM] as A2
    
45. on M.ID = A2.ID and dateadd(month, -1, D.SOM) = A2.SOM
    
46. left join [dbo].[PseudoDataTablewithDM] as A3
    
47. on M.ID = A3.ID and dateadd(month, -2, D.SOM) = A3.SOM;

复制代码

接下来查询三个维度的积，这样就不需要group by 了

1. select
   
2.         *
   
3. FROM #FINAL
   
4. where
   
5.         Gender = 'M' and DistChannel = 'agent' and Product = 'Bronze'
   
6.         and SOM >= '2020-12-01'

复制代码

Gender	DistChannel	Product	ID	SOM	NCustomersSOM1	RemainingNCustomerEOM1	NCustomersSOM2	RemainingNCustomerEOM2	NCustomersSOM3	RemainingNCustomerEOM3
M	agent	Bronze	16	12/1/2020	0	0	NULL	NULL	NULL	NULL
M	agent	Bronze	16	1/1/2021	0	0	0	0	NULL	NULL
M	agent	Bronze	16	2/1/2021	100	90	0	0	0	0
M	agent	Bronze	16	3/1/2021	110	100	100	90	0	0
M	agent	Bronze	16	4/1/2021	140	105	110	100	100	90

那么，对于购买agent推销的青铜产品的男性顾客数目来说，只有2021年4月才能按照当前方式计算季度滚动留存率

1. SELECT
   
2.         SOM,
   
3.         Gender, DistChannel,Product,
   
4.         SUM(COALESCE(RemainingNCustomerEOM1, 0.0))/SUM(COALESCE(NCustomersSOM1, 0.0)) *
   
5.         SUM(COALESCE(RemainingNCustomerEOM2, 0.0))/SUM(COALESCE(NCustomersSOM2, 0.0)) *
   
6.         SUM(COALESCE(RemainingNCustomerEOM3, 0.0))/SUM(COALESCE(NCustomersSOM3, 0.0)) as retainratio,
   
7.         SUM(COALESCE(RemainingNCustomerEOM1, 0.0)),SUM(COALESCE(NCustomersSOM1, 0.0)),
   
8.         SUM(COALESCE(RemainingNCustomerEOM2, 0.0)),SUM(COALESCE(NCustomersSOM2, 0.0)),
   
9.         SUM(COALESCE(RemainingNCustomerEOM3, 0.0)),SUM(COALESCE(NCustomersSOM3, 0.0)),
   
10.         count(*)
    
11. FROM #FINAL
    
12. where
    
13.         Gender = 'M' and DistChannel = 'agent' and Product = 'Bronze'
    
14.         and SOM = '2021-04-01'
    
15. GROUP BY SOM, Gender, PRODUCT, DistChannel;

复制代码

SOM	Gender	DistChannel	Product	retainratio	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)	(No column name)
4/1/2021	M	agent	Bronze	0.613636	105	140	100	110	90	100	1

其他月份都因为有0作为分母出错。

那么，对于有空洞的情况，比如在2021年3月的季度滚动留存率计算时有一个空洞，对于这种情况，我目前的想处理方式如下
(100/110 * 90/100)^(3/2)

1. select power( (100.0/110.0 * 90.0/100.0), 1.5)

复制代码

约=0.74

对于在2021年1月及之前的季度滚动留存率都应该为空。当然这部分的代码都还没有写。之前没考虑到newkid网友提醒的空洞问题。

另外，现阶段的管理层只需要针对某一维度的sum进行滚动留存率计算，比如所有男性客户的留存率计算。或者所有购买了黄金会员产品的客户留存率计算。那么中间如果有某一细分维度组合的空洞，比如说在线销售(online)的白银产品(silver)在某段时间没有女性顾客购买，产生了几行空洞。由于现阶段管理层未关心到这么细的维度，所以只要某单一维度在所有月度都有数据，直接对此单一维度sum就可以了。业务存续时间不短，因此单一维度的年度滚动留存率计算时暂时未碰到空洞的情况。

针对数个月连乘，我下一步打算把数据log化，这样就可以用window sum的形式来取代相乘，就不用大量左关联了。不过这样形式是否适合Tableau来可视化，还要再进行试验。

少佩恩S

newkid 发表于 2021-4-2 05:32
8 楼贴出的结果和我测试的结果不符，能否把数据再减少一下，少到可以手工验算的程度？

好的，明天到家后用Excel手工造几行数据发上来。

newkid

我想出一种简化的滚动保留率的写法，请测试是否给出所需结果：

select SOM, PRODUCT
      ,exp(sum(ln(r/n))) as ratio
  from (
        select SOM, PRODUCT, offset
              ,sum(RemainingNCustomerEOM) as r
              ,sum(NCustomersSOM) as n
          from (
               select ADD_MONTHS(SOM,offset) as SOM
                     ,offset
                     ,Gender  
                     ,DistChannel  
                     ,Product  
                     ,NCustomersSOM
                     ,NCustomersEOM
                     ,RemainingNCustomerEOM
                     ,NewCustomers
                     ,max(som) over(partition by product) max_som  ---- 如果有一个截止日期作为输入，则不用计算这个最大日期
                from PseudoDataTable t
                     cross join (select level-1 offset from dual connect by level<=3) ----- 需要滚动的月数为三个月             WHERE SOM >= date '2016-11-1'  ---- 起始日期倒推三个月，如果还有一个截止日期作为输入，就直接用在这里
               )
        WHERE SOM >= date '2017-01-01' and som<=max_som  ---- 如果有一个截止日期作为输入，就直接用在这里
        group by SOM, PRODUCT, offset
        )
group by SOM, PRODUCT
order by PRODUCT,SOM;

少佩恩S

newkid 发表于 2021-4-4 04:21
我想出一种简化的滚动保留率的写法，请测试是否给出所需结果：select SOM, PRODUCT      ,exp(sum(ln(r/n)) ...

前辈的这个新写法算出来的结果是一致的，以下是按照newkid前辈的语句翻译的T-SQL的语句（filter上略有改动）

1. select SOM, PRODUCT,
   
2.         exp(sum(log(r/n, exp(1))))  --比率一致
   
3.                 as ratio,
   
4.         sum(r),sum(n)
   
5.   from (
   
6.         select SOM, PRODUCT, offset
   
7.               ,sum(RemainingNCustomerEOM*1.0) as r
   
8.               ,sum(NCustomersSOM*1.0) as n
   
9.           from (
   
10.                select dateadd(month, offset, SOM) as SOM
    
11.                      ,offset
    
12.                      ,Gender  
    
13.                      ,DistChannel  
    
14.                      ,Product  
    
15.                      ,NCustomersSOM
    
16.                      ,NCustomersEOM
    
17.                      ,RemainingNCustomerEOM
    
18.                      ,NewCustomers
    
19.                 from [dbo].[PseudoDataTable] t
    
20.                      inner join (
    
21.                                                 select 0 as offset
    
22.                                                 union select 1 union select 2) as I
    
23.                                         on 1=1
    
24.                                         ----- 需要滚动的月数为三个月
    
25.                                                 WHERE SOM between '2016-09-01' and '2017-01-01'  ---- 起始日期倒推三个月，如果还有一个截止日期作为输入，就直接用在这里
    
26.                ) as T
    
27.         WHERE SOM = '2016-12-01' --and som<=max_som  ---- 如果有一个截止日期作为输入，就直接用在这里
    
28.         group by SOM, PRODUCT, offset
    
29.         ) as T1
    
30. group by SOM, PRODUCT
    
31. order by PRODUCT,SOM;
    

复制代码

在这里我只测试了一个月，不过验证了结果都是一致的。

SOM	PRODUCT	ratio	(No column name)	(No column name)
12/1/2016	Bronze	0.47064808	334172	428639
12/1/2016	Gold	0.467935002	315627	406305
12/1/2016	Silver	0.451906202	301704	393709

这种写法比我之前的写法巧妙多了！下一步我还需要了解一下Tableau看看能否接受这种形式。

newkid

我最后那种写法是把横向的连乘改成了纵向，中间加了笛卡尔积来构造相关的历史数据，这样代码少了，但是中间结果集的数据量增大了。
10楼的写法要把每个月数据变成横向的列，写起来很辛苦，但是不会有很大的中间结果集，可以研究一下SQL SERVER的PIVOT语法是否会有所优化。

newkid

我来解说一下这个改写案例的思路。
原SQL首先把所有月份和所有其他维度做了个笛卡尔积，这是为了稠密化，使得那些维度在每个月份都有一行数据。然后把这个稠密化集合和当月数据，前一月数据和前两月数据分别做三个LEFT JOIN，这是为了把三个月的数据放在同一行做计算。这几个JOIN是很昂贵的，实际应用中需要JOIN的月份数可能还更多。楼主为了提高JOIN效率做了预处理，为每一组维度值取了个ID, 这样只需用一个列作为JOIN KEY。这样把几个月数据关联好之后，在其中两个维度做聚合，然后把聚合结果做横向的连续乘法操作。

我一开始是跟着楼主思路走，既然要稠密化第一反应是用PARTITIONED OUTER JOIN, 这样只需构造一个密集的月份小集合，就可以为每组维度生成所有月份数据。这个神器是ORACLE 10G开始支持的，记得十几年前也是我引荐到这个版块的，后来兔子写了一篇非常棒的技术文章：
http://www.itpub.net/thread-1332059-1-1.html

完成这个稠密化步骤，就可以用我2楼说的方法，把前两个月的数据取到同一行来计算。

但是我在网上搜索之后发现PARTITIONED OUTER JOIN只有ORACLE是支持的，楼主用的SQL SERVER并没有此功能。想来想去我憋了一招，就是先做行转列PIVOT，把所有涉及到的月份数据全部变成横向的列。这样做之后，有些月份可能会没有数据，但是也会有NULL值，所有列都是齐全的。然后再列转行UNPIVOT，所有的列都会转成行，包括那些没数据的，这样实际上就稠密化了。UNPIVOT缺省是会跳过NULL的，ORACLE提供了INCLUDE NULLS选项，但是SQL SERVER并没有，所以不能用。最终我没有用PIVOT/UNPIVOT, 而是用了SUM(CASE)+GROUP BY来实现PIVOT，用CROSS JOIN来实现UNPIVOT。我在UNPIVOT的时候也不是单纯把当月数据转成行，而是把另外两个错开的月份也转在了同一行。这种写法是很啰嗦的，假设楼主要输出的是一年的数据，而年初的数据还要与前一年历史数据做比较，那么PIVOT的时候就相当于要做12+11个月, 而UNPIVOT的时候也有很大的工作量。这种很有规律的大体积代码，很适合写一个小小的代码 生 成 器 来实现，或者用动态SQL。至此稠密化也完成了，前两月数据也放到同一行了，接下来就是做和楼主同样的聚合和连乘操作。
我的第二种思路在写法上要简化很多，用不着把不同数据进行关联到同一行，而是把一行数据复制成三分，其中一份给下个月用，一份给下下个月用。给下个月的OFFSET为1，给下下月的OFFSET为2。复制之后自然就稠密化了。在聚合的时候GROUP BY加上这个OFFSET列，相当于原写法在不同列上的聚合(SUM当月数据，SUM上月数据，SUM上上月数据)，这边只是把聚合结果放在不同的行（OFFSET分别为0,1,2）。然后去掉OFFSET再做一次聚合，这就相当于原来横向的乘法，只是变成了纵向的取对数，求和，再求指数。

newkid

再贴一版用PARTITIONED OUTER JOIN的写法：

with mon as ---- 构造连续的五个月
(select add_months('2016-11-1',level-1) as som from dual connect by level<=5)
,dense_data as (  ----- 分区外连接构造密集数据集
select mon.som
      ,Gender,DistChannel,Product
      ,RemainingNCustomerEOM
      ,NCustomersSOM
  from mon left join PseudoDataTable t PARTITION BY  (Gender,DistChannel,Product) on mon.som=t.som
)
,data as (
select d.*
       ----分析函数取上个月数据
      ,max(RemainingNCustomerEOM) over(partition by Gender,DistChannel,Product order by SOM RANGE BETWEEN interval '1' month PRECEDING AND interval '1' month PRECEDING) as RemainingNCustomerEOM2
      ,max(NCustomersSOM) over(partition by Gender,DistChannel,Product order by SOM RANGE BETWEEN interval '1' month PRECEDING AND interval '1' month PRECEDING) as NCustomersSOM2
       ----分析函数取上上月数据
      ,max(RemainingNCustomerEOM) over(partition by Gender,DistChannel,Product order by SOM RANGE BETWEEN interval '2' month PRECEDING AND interval '2' month PRECEDING) as RemainingNCustomerEOM3
      ,max(NCustomersSOM) over(partition by Gender,DistChannel,Product order by SOM RANGE BETWEEN interval '2' month PRECEDING AND interval '2' month PRECEDING) as NCustomersSOM3
  from dense_data d
)
SELECT  SOM,
        Product,
        SUM(RemainingNCustomerEOM)/SUM(NCustomersSOM) *
        SUM(RemainingNCustomerEOM2)/SUM(NCustomersSOM2) *
        SUM(RemainingNCustomerEOM3)/SUM(NCustomersSOM3) as retainratio
  from data
where som>=date '2017-1-1'
GROUP BY SOM, PRODUCT
order by PRODUCT,SOM
;

newkid

受刘兄启发，用MODEL来实现稠密化以及取前两月数据，写起来也是很方便，只是MODEL效率不高，以前我测试过还不如PL/SQL。

with data as (
SELECT *
FROM PseudoDataTable
where som>=date '2016-11-1'
   MODEL RETURN UPDATED ROWS
     PARTITION BY (Gender,DistChannel,Product)     
     DIMENSION BY (som)         
     MEASURES (RemainingNCustomerEOM, NCustomersSOM, 0 RemainingNCustomerEOM2,0 NCustomersSOM2, 0 RemainingNCustomerEOM3,0 NCustomersSOM3)
     RULES (
       RemainingNCustomerEOM2[FOR som FROM date '2016-11-1' TO date '2017-3-1' INCREMENT interval '1' month] = RemainingNCustomerEOM[add_months(cv(),-1)]
      ,RemainingNCustomerEOM3[FOR som FROM date '2016-11-1' TO date '2017-3-1' INCREMENT interval '1' month] = RemainingNCustomerEOM[add_months(cv(),-2)]
      ,NCustomersSOM2[FOR som FROM date '2016-11-1' TO date '2017-3-1' INCREMENT interval '1' month] = NCustomersSOM[add_months(cv(),-1)]
      ,NCustomersSOM3[FOR som FROM date '2016-11-1' TO date '2017-3-1' INCREMENT interval '1' month] = NCustomersSOM[add_months(cv(),-2)]
       )
)
SELECT  SOM,
        Product,
        SUM(RemainingNCustomerEOM)/SUM(NCustomersSOM) *
        SUM(RemainingNCustomerEOM2)/SUM(NCustomersSOM2) *
        SUM(RemainingNCustomerEOM3)/SUM(NCustomersSOM3) as retainratio
  from data
where som>=date '2017-1-1'
GROUP BY SOM, PRODUCT
order by PRODUCT,SOM
;

这个MODEL是ORACLE独有的，其他数据库没见过。

newkid

写完这个案例，不禁赞叹ORACLE的强大，各种功能吊打其他小弟，杂耍似的花式秀肌肉。