Postgresql Select Until Certain Total Amount Is Reached

Postgresql select until certain total amount is reached

select id, 
       date, 
       amount, 
       running_total
from (
    select id,
           date,
           amount,
           sum(amount) over (order by date asc) as running_total
    from transactions
) t
where running_total <= 6

PostgreSQL, select rows until certain amount is reached and exceeded

Just use a cumulative sum:

select t.*
from (select t.*, sum(amount) over (order by date desc) as running_amount
      from t
     ) t
where running_amount - amount < 12
order by date desc;

Postgresql select until certain total amount is reached and lock

I am able to select. . . for update using window functions:

with inparms as (
  select 1 as user_id, 6 as target
), rtotal as (
  select t.id, i.target,
         sum(t.balance) over (partition by t.user_id
                                  order by t.id
                              rows between unbounded preceding
                                       and 1 preceding) as runbalance
    from tb_batch_user t
         join inparms i 
           on i.user_id = t.user_id
)
select t.*
  from rtotal r
       join tb_batch_user t
         on t.id = r.id
 where coalesce(r.runbalance, 0) < r.target
for update of t;

Fiddle here

Select minimal rows until sum of a column greater than a value

You want all the rows until the row where the sum is equal or exceeds 0.5.

This means that if you subtract qty from the sum of the last row that you want returned the result must be less than qty.

Subtract qty from the sum and remove the equal sign from the WHERE clause:

SELECT o.id, o.symbol, o.qty, o.price, o.side, o.status 
FROM (
  SELECT *, SUM(qty) OVER (ORDER BY price ASC) - qty as total_qty 
  FROM orders 
  WHERE symbol = 'BTCUSDT' AND side = 'SELL' AND status = 'NEW'
) AS o 
WHERE o.total_qty < 0.5

See the demo.

Results:

| id  | symbol  | qty  | price | side | status |
| --- | ------- | ---- | ----- | ---- | ------ |
| 1   | BTCUSDT | 0.02 | 6500  | SELL | NEW    |
| 2   | BTCUSDT | 1.00 | 6550  | SELL | NEW    |

PostgreSQL: Select rows until sum-reduction of a single column reaches over a threshold

Add a unique column (primary key) to the ORDER BY clause of the window function, e.g.:

SELECT * FROM (
    SELECT *, SUM(amount) OVER (ORDER BY amount DESC, id) AS running_amount
    FROM public.orders WHERE price = 0.09
) AS t
WHERE t.running_amount <= 15;

In the lack of a unique column you can use the system column ctid.

---

You can use UNION ALL to get the violating row which passes the threshold, e.g.:

WITH cte AS (
    SELECT *, SUM(amount) OVER (ORDER BY amount DESC, id) AS running_amount
    FROM public.orders 
    WHERE price = 0.09
)
SELECT * 
FROM cte
WHERE running_amount <= 15
UNION ALL (
    SELECT * 
    FROM cte
    WHERE running_amount > 15
    LIMIT 1
);

How to select rows until the sum of a column reaches N, where the column is of type TIME

You should really define that column to be an interval. A time column stores a moment in time, e.g. "3 in the afternoon".

However you can cast a single time value to an interval. You also don't need the window function to first calculate the "running total" if you want the total duration per file:

SELECT file_name, sum(audio_duration::interval) as total_duration
FROM data
GROUP BY file_name
HAVING sum(audio_duration::interval) <= interval '10 minute';

---

To permanently change the column type to an interval you can use:

alter table data
  alter duration type interval;

Select rows until running sum reaches specific value

DECLARE @t TABLE (usr VARCHAR(100), dt DATE, amount INT);
INSERT INTO @t VALUES
('a', '2018-01-01', 100), -- 100
('a', '2018-02-01', 100), -- 200
('a', '2018-03-01', 100), -- 300
('a', '2018-04-01', 100), -- 400
('a', '2018-05-01', 100), -- 500
('b', '2018-01-01', 150), -- 150
('b', '2018-02-01', 150), -- 300
('b', '2018-03-01', 150), -- 450
('b', '2018-04-01', 150), -- 600
('b', '2018-05-01', 150); -- 750

DECLARE @Total INT = 301;

WITH cte AS
(
    SELECT *, SUM(amount) OVER (PARTITION BY usr ORDER BY dt) AS RunTotal
    FROM @t
)
SELECT *
FROM   cte
WHERE  cte.RunTotal - cte.amount < @Total -- running total for previous row is less
                                          -- than @Total then include current row

Rolling sum till a certain value is reached, plus calculated duration

A way to solve this efficiently is a procedural solution with two cursors:
One explicit cursor and another implicit cursor of the FOR loop:

CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (dt timestamp
               , val real
               , sum_value real
               , time_at_sum timestamp
               , duration interval) AS
$func$
DECLARE
   _bound real := 1.0;          -- your bound here
   cur CURSOR FOR SELECT * FROM sample s ORDER BY s.dt; -- in chronological order
   s sample;                    -- cursor row 
BEGIN
   OPEN cur;
   FETCH cur INTO time_at_sum, sum_value; -- fetch first row into target

   FOR dt, val IN  -- primary pass over table
      SELECT x.dt, x.value FROM sample x ORDER BY s.dt
   LOOP
      WHILE sum_value <= _bound LOOP
         FETCH cur INTO s;
         IF NOT FOUND THEN  -- end of table
            sum_value := NULL; time_at_sum := NULL;
            EXIT;           -- exits inner loop
         END IF;
         sum_value := sum_value + s.value; 
      END LOOP;
      IF sum_value > _bound THEN  -- to catch end-of-table
         time_at_sum := s.dt;
      END IF;   
      duration := time_at_sum - dt;
      RETURN NEXT;
      sum_value := sum_value - val;  -- subtract previous row before moving on
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM foo();

db<>fiddle here

Should perform nicely since it only needs 2 scans over the table.

Note that I implemented > _bound like your description requires, not >= _bound like your result indicates. Easy to change either way.

Assumes the value column to be NOT NULL.

Related:

• Window Functions or Common Table Expressions: count previous rows within range

Filter a sum of values until a certain threshold is reached

Here LAG() will help to achieve what you want. You can write your query like below:

with cte as (
SELECT
    id, cnt, sec,
    sum(cnt) over (partition by sec order by cnt,id) sum_
FROM
    tbl )
    
    select 
    id, cnt, sum_,
    case
    when sum_<5 or lag(sum_) over (partition by sec order by cnt,id) <5 then 'false'
    else
    'true'
    end as "show"
    from cte

DEMO

---

Related Topics