如何根據動態標準計算運行最小值？

我有下表。

         time        | pid  | qty | event 
--------------------- ------ ----- -------
 2021-11-27 16:15:35 | 2207 |  1  | start  
 2021-11-27 16:15:12 | 2206 |  1  | stop   
 2021-11-27 16:00:11 | 2207 |  2  | stop   
 2021-11-27 15:51:43 | 2206 |  1  | start  
 2021-11-27 15:46:49 | 2206 |  4  | stop   
 2021-11-27 15:42:47 | 2206 |  4  | start  
 2021-11-27 15:41:36 | 2206 |  1  | stop   
 2021-11-27 15:41:29 | 2208 |  3  | start  
 2021-11-27 15:41:15 | 2207 |  2  | start  
 2021-11-27 15:39:58 | 2206 |  1  | start 

可以通過以下方式創建：

CREATE TABLE simple (
    time  TIMESTAMPTZ UNIQUE NOT NULL,
    pid   BIGINT,
    qty   BIGINT,
    event TEXT
);
INSERT INTO simple VALUES
    ('2021-11-27 16:15:35' , 2207 ,  1  , 'start'),
    ('2021-11-27 16:15:12' , 2207 ,  1  , 'stop '),  
    ('2021-11-27 16:00:11' , 2207 ,  2  , 'stop '),  
    ('2021-11-27 15:51:43' , 2206 ,  1  , 'start'),  
    ('2021-11-27 15:46:49' , 2206 ,  4  , 'stop '),  
    ('2021-11-27 15:42:47' , 2206 ,  4  , 'start'),  
    ('2021-11-27 15:41:36' , 2206 ,  1  , 'stop' ), 
    ('2021-11-27 15:41:29' , 2208 ,  3  , 'start'),  
    ('2021-11-27 15:41:15' , 2207 ,  2  , 'start'),  
    ('2021-11-27 15:39:58' , 2206 ,  1  , 'start');  

在每一行的時間戳，我想計算直到該行的qty所有實時（非停止）start事件的最小值，如下所示：

         time        | pid  | qty | event | min 
--------------------- ------ ----- ------- -----
 2021-11-27 16:15:35 | 2207 |  1  | start | 1 -- 2207 min pid again
 2021-11-27 16:15:12 | 2206 |  1  | stop  | 3 -- 2208 min pid, only one not stopped
 2021-11-27 16:00:11 | 2207 |  2  | stop  | 1
 2021-11-27 15:51:43 | 2206 |  1  | start | 1 -- 2206 min pid again
 2021-11-27 15:46:49 | 2206 |  4  | stop  | 2
 2021-11-27 15:42:47 | 2206 |  4  | start | 2 
 2021-11-27 15:41:36 | 2206 |  1  | stop  | 2  -- 2206 stopped, now 2207 is min pid
 2021-11-27 15:41:29 | 2208 |  3  | start | 1  
 2021-11-27 15:41:15 | 2207 |  2  | start | 1  -- min pid is still 2206
 2021-11-27 15:39:58 | 2206 |  1  | start | 1  -- first

我嘗試了多種方法，似乎我最好的選擇是定義自定義聚合函式，盡管我缺乏這樣做的技術專長。歡迎任何有用的指標！

uj5u.com熱心網友回復：

您可以使用視窗函式。

在 cte 中list，我們首先計算：

• 表中每一行的 pid 結果數量simple為total
• 此結果數量有效且不變的時間范圍為 time_interval

然后，我們加入了熱膨脹系數list與表simple，同時保持在該行time_interval中list包含time的simple，和我們計算的最小值total由分組time中的simple表：

WITH list AS
(
SELECT sum(CASE 
             WHEN event = 'start'
             THEN qty
             ELSE -qty
           END
          ) 
           OVER (PARTITION BY pid 
                 ORDER BY time ASC 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS total
     , CASE
         WHEN event = 'start'
         THEN tstzrange( time
                       , first_value(time) 
                             OVER (PARTITION BY pid
                                   ORDER BY time ASC
                                   ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
                                  )
                       )
         ELSE null :: tstzrange
       END  AS time_interval
  FROM simple
)
SELECT time, min(total)
  FROM simple AS s
  LEFT JOIN list AS l
    ON l.time_interval @> s.time
 GROUP BY time
 ORDER BY time DESC 

您可以在此處查看結果。

uj5u.com熱心網友回復：

您可以使用子查詢：

select e.*, (select min(e1.qty) from events e1 where e1.time <= e.time and not exists 
    (select 1 from events e2 where e2.time <= e.time and e2.time >= e1.time and e2.pid = e1.pid and e2.event='stop')) 
from events e

uj5u.com熱心網友回復：

這是另一種方式：

select * from simple s1
cross join lateral ( 
   select min(sumqty) minqty from (
      select sum(case when event='start' then 1 else -1 end * qty) as sumqty
      from simple s2
      where s2.time <= s1.time
      group by pid
      ) t where sumqty > 0
) t order by time desc

db<>在這里擺弄

標籤：sql PostgreSQL的 时间序列

上一篇：TSQL-從記錄集動態生成查詢

下一篇：如何在另一個查詢中使用CTE查詢的結果