sql - SAS 中的高效滚动总和(窗口聚合)

Question

我有两个表:

tb_payments :contract_id、payment_date、payment_value

tb_reference : contract_id, reference_date

对于 tb_reference 中的每个 (contract_id, reference_date)，我想创建一列 sum_payments 作为 tb_payments 的 90 天滚动总和。我可以用下面的查询来完成这个(非常低效):

%let window=90;
proc sql;
    create index contract_id on tb_payments;
quit;
proc sql;
    create table tb_rolling as
    select a.contract_id,
           a.reference_date,
           (select sum(b.payment_value)
            from tb_payments as b
            where a.contract_id = b.contract_id
                  and a.reference_date - &window. < b.payment_date
                  and b.payment_date <= a.reference_date
           ) as sum_payments
    from tb_reference as a;
quit;

如何使用 proc sql 或 SAS 数据步骤重写它以减少时间复杂度？

编辑更多信息:

我随意选择了90天作为窗口，但是我会针对几个窗口进行计算。可以同时对多个窗口执行计算的解决方案将是理想的

两个表都可以有 10+ 百万行，数据是完全任意的。虽然我的 SAS 服务器非常强大

Contract_ids 可以在两个表中重复

(contract_id, reference_date) 和 (contract_id, payment_date) 对是唯一的

使用示例数据进行编辑:

%let seed=1111;
data tb_reference (drop=i);
    call streaminit(&seed.);
    do i = 1 to 10000;
        contract_id = round(rand('UNIFORM')*1000000,1);
        output;
    end;
run;
proc surveyselect data=tb_reference out=tb_payments n=5000 seed=&seed.; run;
data tb_reference(drop=i);
    format reference_date date9.;
    call streaminit(&seed.);
    set tb_reference;
    do i = 1 to 1+round(rand('UNIFORM')*4,1);
        reference_date = '01jan2016'd + round(rand('UNIFORM')*1000,1);
        output;
    end;
run;
proc sort data=tb_reference nodupkey; by contract_id reference_date; run;
data tb_payments(drop=i);
    format payment_date date9. payment_value comma20.2;
    call streaminit(&seed.);
    set tb_payments;
    do i = 1 to 1+round(rand('UNIFORM')*20,1);
        payment_date = '01jan2015'd + round(rand('UNIFORM')*1365,1);
        payment_value = round(rand('UNIFORM')*3333,0.01);
        output;
    end;
run;
proc sort data=tb_payments nodupkey; by contract_id payment_date; run;

更新:
我将我的天真解决方案与 Quentin 和 Tom 的两个建议进行了比较。

合并方法非常快，并且在 n=10000 时实现了超过 10 倍的加速。它也非常强大，正如汤姆在他的回答中精美地展示的那样。

哈希表非常快，并且实现了超过 500 倍的加速。因为我的数据集很大，这是要走的路，但有一个问题:它们需要适合 RAM。

如果有人需要完整的测试代码，请随时给我发送消息。

Answer 1

如果您获得许可，则可以使用 PROC EXPAND 完成所有这些操作。但是让我们看看如何在没有它的情况下做到这一点。

如果所有日期都出现在 PAYMENTS 表中，那应该不会那么难。只需按 ID 和 DATE 合并两个表。计算运行总和，但也要减去从窗口后面滚出的值。然后只保留引用文件中的日期。

一个问题可能是需要为 CONTRACT_ID 找到所有可能的日期，以便可以使用 LAG() 函数。使用 PROC MEANS 很容易做到这一点。

proc summary data=tb_payments nway ;
  by contract_id ;
  var payment_date;
  output out=tb_id_dates(drop=_:) min=date1 max=date2 ;
run;

还有一个数据步骤。这一步也可以是一个 View 。

data tb_id_dates_all ;
  set tb_id_dates ;
  do date=date1 to date2 ;
    output;
  end;
  format date date9.;
  keep contract_id date ;
run;

现在只需合并三个数据集并计算累积总和。请注意，我包含了一个 do 循环来在一天内累积多次付款(删除示例数据生成代码中的 nodupkey 以对其进行测试)。

如果您想生成多个窗口，那么您将需要多个实际的 LAG() 函数调用。

data want ;
  do until (last.contract_id);
    do until (last.date);
      merge tb_id_dates_all tb_payments(rename=(payment_date=date))
            tb_reference(rename=(reference_date=date) in=in2)
      ;
      by contract_id date ;
      payment=sum(0,payment,payment_value);
    end;
    day_num=sum(day_num,1);

    array lag_days(5) _temporary_ (7 30 60 90 180) ;
    array lag_payment(5) _temporary_ ;
    array cumm(5) cumm_7 cumm_30 cumm_60 cumm_90 cumm_180 ;
    lag_payment(1) = lag7(payment);
    lag_payment(2) = lag30(payment);
    lag_payment(3) = lag60(payment);
    lag_payment(4) = lag90(payment);
    lag_payment(5) = lag180(payment);

    do i=1 to dim(cumm) ;
       cumm(i)=sum(cumm(i),payment);
       if day_num > lag_days(i) then cumm(i)=sum(cumm(i),-lag_payment(i));
       if .z < abs(cumm(i)) < 1e-5 then cumm(i)=0;
    end;
    if in2 then output ;
  end;
  keep contract_id date cumm_: ;
  format cumm_: comma20.2 ;
  rename date=reference_date ;
run;

如果您想让代码灵活适应窗口数量，您需要添加一些代码生成来创建 LAGxx() 函数调用。例如你可以使用这个宏:

%macro lags(windows);
%local i n lag ;
%let n=%sysfunc(countw(&windows));

array lag_days(&n) _temporary_ (&windows) ;
array lag_payment(&n) _temporary_ ;
array cumm(&n)
%do i=1 %to &n ;
  %let lag=%scan(&windows,&i);
 cumm_&lag
%end;
;
%do i=1 %to &n ;
  %let lag=%scan(&windows,&i);
lag_payment(&i) = lag&lag(payment);
%end;
%mend lags;

并用 LAGxx() 函数替换 ARRAY 和赋值语句，调用宏:

%lags(7 30 60 90 180)