欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle分析函数model使用时需要注意的几个地方(一)

程序员文章站 2022-05-05 09:59:55
...

分析函数中的model函数功能强大,对于做数据分析的人来说绝对是一个利器。
我曾经用model函数改写了数十个用存储过程实现的报表取数,一个SQL语句就完全替代了一个上百行代码的存储过程,而且报表口径也完成了统一,这一点在我另外一篇文章中做了详细说明:
基于model函数建设口径统一的统计指标库

虽然model函数很强大,但刚接触的时候还是不免有很多需要留意的地方,一不小心就会发现统计结果与预想的不一样。这里罗列几个方面并给出解决方案:

0.样例数据

--基础数据表
desc T_SALEAMOUNT
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
YEARMON  DATE         Y               年月                    
PRODTYPE VARCHAR2(20) Y               产品类型       
QUANTITY NUMBER(10)   Y               数量   
AMOUNT   NUMBER(12,2) Y               金额       

--样例数据中有3个月的数据
select distinct yearmon from T_SALEAMOUNT order by 1;
YEARMON
-----------
2018/1/1
2018/2/1
2018/3/1

--6种产品类型
select distinct PRODTYPE from T_SALEAMOUNT;
PRODTYPE
--------------------
阳春面
啤酒
蛋炒饭
王老吉
泡饭
饮料

1.RETUNR UPDATED ROWS语句的作用

model后加了这个子句后,会过滤掉原来的所有记录,只留下model组合的数据,比如不加return updated rows的结果如下:

SQL> select * from T_SALEAMOUNT
  2  model
  3  partition by (yearmon)
  4  dimension by (prodtype)
  5  measures (amount)
  6  (
  7  amount['主食']=sum(amount)[prodtype in('阳春面','蛋炒饭','泡饭')]
  8  )
  9  /
YEARMON     PRODTYPE                 AMOUNT
----------- -------------------- ----------
2018/3/1    饮料                      43348
2018/3/1    蛋炒饭                     6366
2018/3/1    王老吉                    20524
2018/3/1    主食                      19088
2018/2/1    饮料                      15923
2018/1/1    饮料                      19506
2018/2/1    阳春面                    26640
.......(省略)

加了return updated rows后,只剩下【主食】一种:

SQL> select * from T_SALEAMOUNT
  2  model return updated rows
  3  partition by (yearmon)
  4  dimension by (prodtype)
  5  measures (amount)
  6  (
  7  amount['主食']=sum(amount)[prodtype in('阳春面','蛋炒饭','泡饭')]
  8  )
  9  /
YEARMON     PRODTYPE                 AMOUNT
----------- -------------------- ----------
2018/3/1    主食                      19088
2018/1/1    主食                      87670
2018/2/1    主食                      33610

2.节点数据的处理

假设prodtype是有上下级关系的,其中“饮料”包括“啤酒”和“王老吉”,那么统计一下数量:

SQL> select * from t_saleamount where yearmon=date'2018-03-01'
  2  model
  3  partition by(yearmon)
  4  dimension by (prodtype)
  5  measures(amount,quantity)
  6  (quantity['饮料']=sum(quantity)[prodtype in('啤酒','王老吉')]
  7  ,amount['饮料']=sum(amount)[prodtype in('啤酒','王老吉')]);
YEARMON     PRODTYPE                 AMOUNT   QUANTITY
----------- -------------------- ---------- ----------
2018/3/1    饮料                      31988       9655
2018/3/1    阳春面                     7787       7296
2018/3/1    啤酒                      11464       3785
2018/3/1    泡饭                       4935       5738
2018/3/1    蛋炒饭                     6366       9671
2018/3/1    王老吉                    20524       5870

而实际数据中是有人添加了一些prodtype=’饮料’的节点数据,“饮料”类的总数应该是9687。
Oracle分析函数model使用时需要注意的几个地方(一)

因此,在写model的时候,需要把根节点也统计进去,这种场景主要是出现在自动生成的model语句时,in子句里面的内容往往是通过一个树形字典表connect by 取出来组装的,此时需要记住把根节点放进去。
当然,放进去以后虽然总数是对了,但是又引发了一个新的问题:
总数和子类的合计值不匹配:
Oracle分析函数model使用时需要注意的几个地方(一)

两者兼顾的方法只能是把合计值单列为另外一个名称,比如:

SQL> select * from t_saleamount where yearmon=date'2018-03-01' and prodtype in('啤酒','王老吉','饮料')
  2  model
  3  partition by(yearmon)
  4  dimension by (prodtype)
  5  measures(amount,quantity)
  6  (quantity['饮料(小计)']=sum(quantity)[prodtype in('啤酒','王老吉','饮料')]
  7  ,amount['饮料(小计)']=sum(amount)[prodtype in('啤酒','王老吉','饮料')]);
YEARMON     PRODTYPE                 AMOUNT   QUANTITY
----------- -------------------- ---------- ----------
2018/3/1    饮料                      43348         32
2018/3/1    啤酒                      11464       3785
2018/3/1    王老吉                    20524       5870
2018/3/1    饮料(小计)                75336       9687