postgresql SKU活跃度分析

所选时间范围内，同一个sku有多种可能，不连续，连续2天，连续3天，连续4天等可能出现，原则上只保留最大的连续天数，作为该sku的活跃程度

--自定义pg函数，输入某个sku所有出库日期，输出最大连续天数
CREATE OR REPLACE FUNCTION "public_schema"."max_day_diff"(date[])
  RETURNS "pg_catalog"."numeric" AS $BODY$         
DECLARE
   tmp int := 1 ; 
   res int := 0 ; 
         last_outbound_time date ;
   outbound_time date ;         
BEGIN
 for outbound_time in (
 select 
 unnest($1)
 order by unnest($1)
 )
 LOOP 
 if extract(day from outbound_time::timestamp - COALESCE(last_outbound_time,outbound_time)::timestamp) = 1 then
         tmp = tmp + 1 ;
 else 
   if tmp > res then
    res = tmp ;
                end if ; 
        tmp = 1 ;
 end if;
  last_outbound_time = outbound_time ;
 END LOOP ; 

if tmp > res then
    res = tmp ;
end if ;

RETURN res ;  
END ; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100 ;
        
        
with t1 as 
(select 
distinct 
order_sku_id as sku,
date(order_create_time) as outbound_time
from main_table 
) 

select
sku,
max_day_diff(array_agg(outbound_time)) 最大连续天数
from t1
group by sku 
order by sku;