概要描述

Oracle 从10g开始支持model高级语句，使用model语句，可以在表上定义一个数据立方体，这个立方体由它的维度和度量定义，并通过规则对每个单元的值进行计算，这相当于是把关系表转换成了多维数组，这个多维不止可以是三维，可以是包含一维、二维的任意维度。这样也就提供了一种在oltp数据库里进行olap的方法。

本文主要介绍在Argodb中如何通过窗口函数临时实现model语法。

详细说明

上面是一个excel表格，记录了每周口罩的进货量、销售量和库存
本周库存 = 本周进货量-本周销售额+上周库存
在excel中，我们可以用上图中的公式，来计算每周的库存

Oracle中，Model子句就是为了解决这类跨行引用

create table sales_fact
(
 prod      varchar2(20),--产品
 year    number,      --年
 week    number,      --月
 sale    number,      --销售额
 receipts       number       --进货量
);

insert into sales_fact values ('口罩',2020,1,100,200);
insert into sales_fact values ('口罩',2020,2,100,200);
insert into sales_fact values ('口罩',2020,3,150,300);
insert into sales_fact values ('口罩',2020,4,1000,5000);
insert into sales_fact values ('口罩',2020,5,2000,10000);
insert into sales_fact values ('口罩',2020,6,3000,0);
insert into sales_fact values ('口罩',2020,7,5000,0);
insert into sales_fact values ('口罩',2020,8,10000,10000);
insert into sales_fact values ('口罩',2020,9,100000,100000);
insert into sales_fact values ('口罩',2020,10,100000,100000);
insert into sales_fact values ('口罩',2020,11,100000,100000);
insert into sales_fact values ('口罩',2020,12,100000,100000);
insert into sales_fact values ('口罩',2020,13,100000,100000);
insert into sales_fact values ('口罩',2020,14,100000,100000);
commit;

select prod ,
       year ,
       week ,
       sale ,    --销售量
       receipts, --进货量
       inventory --库存量
from sales_fact
where 1 = 1
model return updated rows
partition by(prod)
dimension by(year,week)
measures(0 inventory,sale,receipts) rules automatic
order(inventory[year,week] = nvl(inventory[cv(year),cv(week) - 1],0) - sale[cv(year),cv(week)] + receipts[cv(year),cv(week)] )
order by prod,year,week;

prod分区，与Oracle其它分析函数的分区相同，分区列值相同的所有行被认为是在同一个分区中
year、week为维度，维度列唯一辩识每一行，产品为口罩，每年每周只有一行数据
measures(0 inventory, sale, receipts)表示 inventory, sale, receipts三列为计算的列值
计算公式这个，可以类比excel截图中的公式来看， 0 inventory 代表如果找不到上周的库存，默认值为0

Argodb没有直接对应的MODEL子句（截止2025/6/20），但窗口函数（如SUM）可高效实现累积计算。关键思路是：
库存量 = 从第一周到当前周的（-销量 + 进货量）累积和，因为每一周的库存量等于前一周库存量加上（-销量 + 进货量），本质是累积增量。

create table sales_fact
(
prod varchar2(20),
year number,
week number,
sale number,
receipts number
)STORED AS ORC ;

insert into sales_fact SELECT '口罩',2020,1,100,200;
insert into sales_fact SELECT '口罩',2020,2,100,200;
insert into sales_fact SELECT '口罩',2020,3,150,300;
insert into sales_fact SELECT '口罩',2020,4,1000,5000;
insert into sales_fact SELECT '口罩',2020,5,2000,10000;
insert into sales_fact SELECT '口罩',2020,6,3000,0;
insert into sales_fact SELECT '口罩',2020,7,5000,0;
insert into sales_fact SELECT '口罩',2020,8,10000,10000;
insert into sales_fact SELECT '口罩',2020,9,100000,100000;
insert into sales_fact SELECT '口罩',2020,10,100000,100000;
insert into sales_fact SELECT '口罩',2020,11,100000,100000;
insert into sales_fact SELECT '口罩',2020,12,100000,100000;
insert into sales_fact SELECT '口罩',2020,13,100000,100000;
insert into sales_fact SELECT '口罩',2020,14,100000,100000;

WITH base_data AS 
(
    SELECT
    prod,
    year,
    week,
    sale,
    receipts,
    -- 生成连续周号用于累计计算（假设周号从1开始连续）
    ROW_NUMBER() OVER (PARTITION BY prod, year ORDER BY week) as rn
    FROM sales_fact
)
SELECT
    prod,
    year,
    week,
    sale,
    receipts,
    -- 库存计算：累计进货 - 累计销售
    SUM(receipts - sale) OVER (PARTITION BY prod, year ORDER BY rnROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS inventory
FROM base_data
ORDER BY prod, year, week;