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

sql_tigerliu

newkid 发表于 2021-4-7 07:44
受刘兄启发，用MODEL来实现稠密化以及取前两月数据，写起来也是很方便，只是MODEL效率不高，以前我测试过还 ...

苏大师邀我也参与一下, 我就大致写了一下, 请大家帮忙看看是否合适, 这个可以直接拿来执行:
with tmp(product,som,RemainingNCustomerEOM,NCustomersSOM) --simulate original after group by
as
(
select 'Bronze',date'2016-01-01',100, 200   from dual union all
select 'Bronze',date'2016-02-01' ,80, 100   from dual union all
select 'Bronze',date'2016-03-01' ,50, 200   from dual union all
select 'Bronze',date'2016-04-01' ,40, 200   from dual union all
select 'Bronze',date'2016-05-01' ,80, 100   from dual union all
select 'Bronze',date'2016-06-01' ,20, 50    from dual union all
select 'Bronze',date'2016-07-01' ,80, 200   from dual union all
select 'Bronze',date'2016-08-01' ,80, 200   from dual union all
select 'Bronze',date'2016-09-01' ,80, 200   from dual union all
select 'Bronze',date'2016-10-01' ,80, 200   from dual union all
select 'Bronze',date'2016-11-01' ,80, 200   from dual union all
select 'Bronze',date'2016-12-01' ,80, 200   from dual union all
select 'Bronze',date'2017-01-01' ,80, 200   from dual union all
select 'Bronze',date'2017-02-01' ,80, 200   from dual union all
select 'Bronze',date'2017-03-01' ,80, 200   from dual union all
select 'Gold',  date'2016-01-01' ,80, 200   from dual union all
select 'Gold',  date'2016-02-01' ,80, 200   from dual union all
select 'Gold',  date'2016-03-01' ,80, 200   from dual union all
select 'Gold',  date'2016-04-01' ,80, 200   from dual union all
select 'Gold',  date'2016-05-01' ,80, 200   from dual union all
select 'Gold',  date'2016-06-01' ,80, 200   from dual union all
select 'Gold',  date'2016-07-01' ,80, 200   from dual union all
select 'Gold',  date'2016-08-01' ,80, 200   from dual union all
select 'Gold',  date'2016-09-01' ,80, 200   from dual union all
select 'Gold',  date'2016-10-01' ,80, 200   from dual union all
select 'Gold',  date'2016-11-01' ,80, 200   from dual union all
select 'Gold',  date'2016-12-01' ,80, 200   from dual union all
select 'Gold',  date'2017-01-01' ,80, 200   from dual union all
select 'Gold',  date'2017-02-01' ,80, 200   from dual union all
select 'Gold',  date'2017-03-01' ,80, 200   from dual union all
select 'Silver',date'2016-01-01' ,80, 200   from dual union all
select 'Silver',date'2016-02-01' ,80, 200   from dual union all
select 'Silver',date'2016-03-01' ,80, 200   from dual union all
select 'Silver',date'2016-04-01' ,80, 200   from dual union all
select 'Silver',date'2016-05-01' ,80, 200   from dual union all
select 'Silver',date'2016-06-01' ,80, 200   from dual union all
select 'Silver',date'2016-07-01' ,80, 200   from dual union all
select 'Silver',date'2016-08-01' ,80, 200   from dual union all
select 'Silver',date'2016-09-01' ,80, 200   from dual union all
select 'Silver',date'2016-10-01' ,80, 200   from dual union all
select 'Silver',date'2016-11-01' ,80, 200   from dual union all
select 'Silver',date'2016-12-01' ,80, 200   from dual union all
select 'Silver',date'2017-01-01' ,80, 200   from dual union all
select 'Silver',date'2017-02-01' ,80, 200   from dual union all
select 'Silver',date'2017-03-01' ,80, 200   from dual
)
,tmp1 as
(
select  product,som,row_number() over (partition by product order by som) as rn,
    RemainingNCustomerEOM,NCustomersSOM
from tmp
)
select P,D,r,n,ratio,som,r/n
from tmp1
model return updated rows
dimension by (product as P,rn as D)
measures (1 as ratio, RemainingNCustomerEOM as R,NCustomersSOM as N,som)
rules iterate (3)
(
ratio [P,D] order by  P,D =nvl(R[CV(P),CV(D) - ITERATION_NUMBER ],1) / nvl(N[CV(P),CV(D)- ITERATION_NUMBER ],1) * ratio [cv(P),cv(D)]
) order by P,D
;

newkid

sql_tigerliu 发表于 2021-4-7 08:45
苏大师邀我也参与一下, 我就大致写了一下, 请大家帮忙看看是否合适, 这个可以直接拿来执行:with tmp(produc ...

刘兄这个写法和原来的需求不等价。原写法有个痛点，就是在找月份之间的关系的时候，必须其他的三个维度全部都匹配。这些月份匹配做完之后，再聚合到product上面。而你这个写法是在聚合到 product 之后才找匹配的，只是寻找一个维度，即相同 product 的上月，上上月数据来做连乘，这样结果是和原来不同的。而且你是假定同一个PRODUCT的月份不会出现空洞，所以才可用ROW_NUMBER()做MODEL的索引。不过楼主也说了，确实最后聚合就不会出现空洞，而在分散到多个维度的细粒度数据上是有可能出现空洞的。

sql_tigerliu

newkid 发表于 2021-4-7 09:39
刘兄这个写法和原来的需求不等价。原写法有个痛点，就是在找月份之间的关系的时候，必须其他的三个维度全部 ...

我的写法是一个思路,主要是用model解决连续相乘的问题, 至于各种维度如何group by, 我这里没有过多关注.  model我也很少研究, 用的比较少. partitioned outer join的写法倒是第一次见, 厉害!

newkid

如果能保证每组维度中间不出现空洞，那么找月份匹配的操作就可以延迟到最终的聚合之后才来做，那么整个事情就简单得多了。
9楼的数据是有空洞的，我写了这段代码来增加一些模拟数据，补齐空洞：

insert into PseudoDataTable (som,gender,distchannel,product,NCustomersSOM,NCustomersEOM,RemainingNCustomerEOM,NewCustomers)
select add_months(last_som,n) as som,gender,distchannel,product,NCustomersSOM,NCustomersEOM,RemainingNCustomerEOM,NewCustomers
  from (
      select t.*,months_between(som,last_som) m
         from (select t.*,lag(som) over(partition by gender,distchannel,product order by som) last_som
                 from PseudoDataTable t
               ) t
        where months_between(som,last_som)<>1
  ) t
,lateral(select level n from dual connect by level<t.m);


最终查询就非常简单，不需要用MODEL来做乘法，因为分析函数SUM可以变成对数反对数来求乘积：
with d as
(select som,Product
      ,sum(RemainingNCustomerEOM) RemainingNCustomerEOM
      ,sum(NCustomersSOM) NCustomersSOM
  from PseudoDataTable t
where som>=date '2016-11-1'
  group by som,Product
)
select * from (
select d.som,d.product
      ,exp(sum(ln(RemainingNCustomerEOM/NCustomersSOM))
                 over(partition by product order by som RANGE BETWEEN interval '2' month PRECEDING AND current row) ---- 如果需求不止追溯两个月，只需稍微修改一下这里
           ) ratio
from d
)
where som>=date '2017-1-1'
order by product,som;

少佩恩S

前辈们又讨论了许多，小弟真是不胜感激。月初加班搞得没什么个人时间，躺下后想到一种T-SQL的写法，简单试了下，好像可以，先发上来参与讨论。大家的后续讨论周末再细细看下。

1. 
   
2. select SOM
   
3.         ,Gender  
   
4.         ,DistChannel  
   
5.         ,Product  
   
6.                 , RemainingNCustomerEOM*1.0/NCustomersSOM*1.0 * iif(offset1 >= -2, RemainingNCustomersEOM_lag1*1.0/NCustomersSOM_lag1 *1.0, 1)
   
7.                 * iif(offset2 >= -2, RemainingNCustomersEOM_lag2*1.0/NCustomersSOM_lag2 *1.0, 1)
   
8.         --- 下一步是这里要加判断到底几个月可以用，然后吧上面的数值 to the power of 3/n
   
9. from
   
10. (select SOM
    
11.         ,Gender  
    
12.         ,DistChannel  
    
13.         ,Product  
    
14.         ,NCustomersSOM
    
15.         ,NCustomersEOM
    
16.         ,RemainingNCustomerEOM
    
17.         ,NewCustomers,
    
18.                 lag(RemainingNCustomerEOM, 1) over (partition by Gender  
    
19.         ,DistChannel,Product  order by SOM) as RemainingNCustomersEOM_lag1,
    
20.                 lag(NCustomersSOM, 1) over (partition by Gender  
    
21.         ,DistChannel  ,Product  order by SOM) NCustomersSOM_lag1,
    
22.                 datediff(month, SOM,
    
23.                 lag(SOM, 1) over (partition by Gender  
    
24.         ,DistChannel  ,Product  order by SOM)) offset1,
    
25.                 lag(RemainingNCustomerEOM, 2) over (partition by Gender  
    
26.         ,DistChannel,Product  order by SOM) RemainingNCustomersEOM_lag2,
    
27.                 lag(NCustomersSOM, 2) over (partition by Gender  
    
28.         ,DistChannel  ,Product  order by SOM) NCustomersSOM_lag2,
    
29.                 datediff(month, SOM,
    
30.                 lag(SOM, 2) over (partition by Gender  
    
31.         ,DistChannel  ,Product  order by SOM)) as offset2
    
32. from [dbo].[PseudoDataTable]) T
    
33. where som = '2016-12-01'
    
34. order by Gender  
    
35.         ,DistChannel  
    
36.         ,Product  

复制代码

newkid

少佩恩S 发表于 2021-4-8 21:47
前辈们又讨论了许多，小弟真是不胜感激。月初加班搞得没什么个人时间，躺下后想到一种T-SQL的写法，简单试 ...

你这个写法还是把不同月份取到同一行来做计算，用的是LAG函数，如果保证数据连续，LAG和我用RANGE WINDOW得到的结果是相同的，写起来比较简便。

少佩恩S

sql_tigerliu 发表于 2021-4-7 08:45
苏大师邀我也参与一下, 我就大致写了一下, 请大家帮忙看看是否合适, 这个可以直接拿来执行:with tmp(produc ...

谢谢老虎前辈给的思路，您这里用到的model clause, ITERATION_NUMBER, CV 函数等都是第一次见，我得先去学习下甲骨文文档。。。惭愧

少佩恩S

newkid 发表于 2021-4-8 00:27
如果能保证每组维度中间不出现空洞，那么找月份匹配的操作就可以延迟到最终的聚合之后才来做，那么整个事情 ...

前辈这个思路我现在明白了，真巧妙，特别是range between 跟 interval months，一下子把滚动平均和空洞月份都处理掉了。

少佩恩S

newkid 发表于 2021-4-8 00:27
如果能保证每组维度中间不出现空洞，那么找月份匹配的操作就可以延迟到最终的聚合之后才来做，那么整个事情 ...

刚刚用数据对前辈的方法进行了验证，计算结果是正确的

1. select * from
   
2. (
   
3. select d.som,d.product
   
4.       ,exp(
   
5.           sum(
   
6.           log(RemainingNCustomerEOM/NCustomersSOM, exp(1))) over(partition by product order by som rows BETWEEN 2 PRECEDING and current row)
   
7.                                  )---- 如果需求不止追溯两个月，只需稍微修改一下这里
   
8.             ratio
   
9. from
   
10. (select som,Product
    
11.       ,sum(RemainingNCustomerEOM*1.0000) RemainingNCustomerEOM
    
12.       ,sum(NCustomersSOM*1.0000) NCustomersSOM
    
13.   from PseudoDataTable t
    
14. where som>= '2016-10-1' and som < '2018-01-01'
    
15.   group by som,Product
    
16. ) d
    
17. ) T
    
18. where som ='2016-12-1';

复制代码

som        product        ratio
2016-12-01        Bronze        0.470648080391569
2016-12-01        Gold        0.467935002022686
2016-12-01        Silver        0.45190620164939


另外写的时候发现SQL Server好像还不支持range between写法，临时简单改写为rows between

newkid

SQL server 支持 range:  https://www.red-gate.com/simple-talk/sql/learn-sql-server/window-functions-in-sql-server-part-2-the-frame/     当然，如果月份连续，用 rows 也一样。