sql - 使用窗口函数跨分区求和

Question

我有以下问题...

    Time |  A   |  B |  C  --  Sum should be
      1     a1     b1   c1     a1 + b1 + c1
      2     a2     b2   x      a2 + b1 + c1 
      3     a3     x    x      a3 + b2 + c1
      4     x      b3   c2     a3 + b3 + c2

本质上，总和需要跨越最近的值三行中每一行的时间。每个数据列不必须具有当前时间的值。

我已经尝试了几种使用窗口函数的方法，并且没有成功。我写了一个存储过程做我需要的，但它很慢。

CREATE OR REPLACE FUNCTION timeseries.combine_series(id int[], startTime timestamp, endTime timestamp) 
RETURNS setof RECORD AS $$
DECLARE
    retval double precision = 0;
    row_data timeseries.total_active_energy%ROWTYPE;
    maxCount integer = 0;
    sz integer = 0;
lastVal double precision[];
v_rec RECORD;
BEGIN   
    SELECT INTO sz array_length($1,1);

    FOR row_data IN SELECT * FROM timeseries.total_active_energy  WHERE time >= startTime AND time < endTime AND device_id = ANY($1) ORDER BY time
       LOOP
    retval = row_data.active_power;
    for i IN 1..sz LOOP
        IF $1[i]=row_data.device_id THEN
            lastVal[i] = row_data.active_power;
        ELSE
            retval = retVal + COALESCE(lastVal[i],0);
        END IF;
    END LOOP;

    SELECT row_data.time, retval into v_rec;

    return next v_rec;
     END LOOP;

      return ;
  END;
$$ LANGUAGE plpgsql;

调用:

select * from timeseries.combine_series('{552,553,554}'::int[], '2013-05-01'::timestamp, '2013-05-02'::timestamp) 
    AS (t timestamp with time zone, val double precision);

Answer 1

SELECT ts, a, b, c
       , COALESCE(max(a) OVER (PARTITION BY grp_a), 0)
       + COALESCE(max(b) OVER (PARTITION BY grp_b), 0)
       + COALESCE(max(c) OVER (PARTITION BY grp_c), 0) AS special_sum
FROM  (
   SELECT *
         ,count(a) OVER w AS grp_a
         ,count(b) OVER w AS grp_b
         ,count(c) OVER w AS grp_c
   FROM   t
   WINDOW w AS (ORDER BY ts)
   ) sub
ORDER  BY ts;

首先，使用聚合窗口函数 count() 将实际值和后面的 NULL 值放在一个组中:它不会随着 NULL 递增> 值(value)观。

然后从每个组中取max()，得到你要找的东西。此时您也可以使用 min() 或 sum()，因为每组只有一个非空值。

COALESCE() 捕获 NULL 值，如果总的第一时间值是 NULL。

请注意我是如何选择 ts 作为列名的，因为我没有使用像 time 这样的基本类型名称作为标识符。

测试用例

这也是你们首先应该提供示例数据的方式!

CREATE TEMP TABLE t (ts int, a int, b int, c int);

INSERT INTO t VALUES
  (1, 11,   21,   NULL)
 ,(2, 12,   22,   NULL)
 ,(3, 13,   NULL, NULL)
 ,(4, NULL, 23,   32);