It's Possible to Create a Rule in Preceding Rows in Sum

It's possible to create a rule in preceding rows in sum?

You may employ the array_agg() trick. It is not as scalable as a native function though.

CREATE TEMP FUNCTION special_sum(x ARRAY<FLOAT64>)
RETURNS FLOAT64
LANGUAGE js
AS """
var sum = 0;
for (const num of x)
{
sum += num;
if (sum < 0)
{
sum = 0;
}
}
return sum;
""";

with stock as (
SELECT -7.34 variation, UNION ALL
SELECT -1.81 UNION ALL
SELECT -0.51 UNION ALL
SELECT 17.19 UNION ALL
SELECT -1.63 UNION ALL
SELECT 2.82 UNION ALL
SELECT -1.00 UNION ALL
SELECT 0.56 UNION ALL
SELECT -17.92 UNION ALL
SELECT 0.34 UNION ALL
SELECT 1.02 UNION ALL
SELECT 0.39 UNION ALL
SELECT 0.25 UNION ALL
SELECT 0.65 UNION ALL
SELECT 0.09 UNION ALL
SELECT -0.32 UNION ALL
SELECT -0.40
)
select variation,
special_sum( array_agg(variation) over (rows unbounded preceding) ) as stock
from stock

The output is pretty close to what you provided:
Sample Image

What is ROWS UNBOUNDED PRECEDING used for in Teradata?

It's the "frame" or "range" clause of window functions, which are part of the SQL standard and implemented in many databases, including Teradata.

A simple example would be to calculate the average amount in a frame of three days. I'm using PostgreSQL syntax for the example, but it will be the same for Teradata:

WITH data (t, a) AS (
VALUES(1, 1),
(2, 5),
(3, 3),
(4, 5),
(5, 4),
(6, 11)
)
SELECT t, a, avg(a) OVER (ORDER BY t ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM data
ORDER BY t

... which yields:

t  a  avg
----------
1 1 3.00
2 5 3.00
3 3 4.33
4 5 4.00
5 4 6.67
6 11 7.50

As you can see, each average is calculated "over" an ordered frame consisting of the range between the previous row (1 preceding) and the subsequent row (1 following).

When you write ROWS UNBOUNDED PRECEDING, then the frame's lower bound is simply infinite. This is useful when calculating sums (i.e. "running totals"), for instance:

WITH data (t, a) AS (
VALUES(1, 1),
(2, 5),
(3, 3),
(4, 5),
(5, 4),
(6, 11)
)
SELECT t, a, sum(a) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM data
ORDER BY t

yielding...

t  a  sum
---------
1 1 1
2 5 6
3 3 9
4 5 14
5 4 18
6 11 29

Here's another very good explanations of SQL window functions.

How to sum the current column value in row with the previous value?

SQL tables represent unordered sets. There is no "previous" row unless columns specify the ordering. In your data, that would suggest xYear and xMonth -- even thought the results do not follow this rule.

Assuming you want a chronological ordering (or ordering based on any columns), then you can use a cumulative sum window function:

select t.*,
sum(xRaseed) over (partition by xAccID order by xYear, xMonth) as running_xRaseed
from t;

Here is a db<>fiddle.

sum() Over(partition by order by a,b)

From 9.22. Window Functions:

When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame. An aggregate used
with ORDER BY and the default window frame definition produces a
“running sum” type of behavior, which may or may not be what's wanted.
To obtain aggregation over the whole partition, omit ORDER BY or use
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other
frame specifications can be used to obtain other effects.

When you ORDER BY fname, all rows within the partition have the "same" position in that order. You also PARTITION BY fname, so ORDER BY fname has no effect. That follows that the window frame is the same for all rows in the partition and therefore the function's results.

Then, when you ORDER BY o_details, it has an effect. The position of the rows in the partition is not the same for all of them anymore. And as the frame is relative to the position of the row in that order, it's different for almost each row and so are the function's results. I wrote almost, because this does not completely apply to the two fnames of 'eva' with the same o_details of 'coat'. They share one position. So for these two rows the function's results are again the same for the a fore mentioned reasons.

SQL, How to correctly sum in while loop and write it to table by weeks number

Consider using a window function to do this:

SELECT testtable.*, 
SUM(WeeklyHours) OVER (PARTITION BY [Order] ORDER BY startday ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WhatTheClientWants
FROM #test as testtable

That's taking each group of records that share the same "partition" (or [Order] column value), sorting them by startday and then summing them all together up to the current row. A cumulative sum inside of a partition.

While it's always tempting to think "WHILE LOOP!" as a solution, in a database that's almost never needed. In over a decade in this field, I've only had to resort to a while loop once for a very procedural solution that had no set based alternative.


Just noticed the nulls for NULL startday in the new column. Clobbering this with a UNION ALL should suffice:

SELECT testtable.*, 
SUM(WeeklyHrs) OVER (PARTITION BY [Order] ORDER BY startday ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WhatTheClientWants
FROM #test as testtable
WHERE StartDay IS NOT NULL
UNION ALL
SELECT testtable.*, NULL
FROM #test as testtable
WHERE StartDay IS NULL

Select all rows where the sum of column X is greather or equal than Y

You need a recursive query like this:

demo:db<>fiddle

WITH RECURSIVE lots_with_rowcount AS (       -- 1
SELECT
*,
row_number() OVER (ORDER BY avail_qty DESC) as rowcnt
FROM mytable
), lots AS ( -- 2
SELECT -- 3
lot_nr,
avail_qty,
rowcnt,
avail_qty as total_qty
FROM lots_with_rowcount
WHERE rowcnt = 1

UNION

SELECT
t.lot_nr,
t.avail_qty,
t.rowcnt,
l.total_qty + t.avail_qty -- 4
FROM lots_with_rowcount t
JOIN lots l ON t.rowcnt = l.rowcnt + 1
AND l.total_qty < --<your demand here>
)
SELECT * FROM lots -- 5
  1. This CTE is only to provide a row count to each record which can be used within the recursion to join the next records.
  2. This is the recursive CTE. A recursive CTE contains two parts: The initial SELECT statement and the recursion.
  3. Initial part: Queries the lot record with the highest avail_qty value. Naturally, you can order them in any order you like. Most qty first yield the smallest output.
  4. After the UNION the recursion part: Here the current row is joined the previous output AND as an additional condition: Join only if the previous output doesn't fit your demand value. In that case, the next total_qty value is calculated using the previous and the current qty value.
  5. Recursion end, when there's no record left which fits the join condition. Then you can SELECT the entire recursion output.

Notice: If your demand was higher than your all your available quantities in total, this would return the entire table because the recursion runs as long as the demanded is not reached or your table ends. You should add a query before, which checks this:

SELECT SUM(avail_qty) > demand FROM mytable


Related Topics



Leave a reply



Submit