A common solution to this type of problem is given by Itzik Ben-Gan in his article The Last non NULL Puzzle:
DROP TABLE IF EXISTS dbo.Example;
CREATE TABLE dbo.Example
id integer PRIMARY KEY,
val integer NULL
I expected t-sql to optimize it out - on a block/record level, the
task to do is very easy and linear, essentially a for loop ( O(n) ).
That's not the query that you wrote. It may not be equivalent to the query that you wrote depending on some otherwise minor detail of the table schema. You're expecting too much from the query optimizer.
With the right ...
This is how I solved a similar problem on Teradata using nested OLAP-functions:
-- find the lowest previous CumSum < 0
-- and adjust the current CumSum to zero
Max(CASE WHEN CumSum < 0 THEN -CumSum ELSE 0 end)
Over (PARTITION BY groupid
ORDER BY pkid
ROWS Unbounded Preceding)
+ CumSum AS ...
You need a "window" aggregate, i.e. an OVER clause in the aggregate. And because the query already has a GROUP BY, the aggregate needed is the SUM() over the COUNT(d.FK_User) you already have:
-- COUNT(d.FK_User) AS UserCount, -- if you need both counts
SUM(COUNT(d.FK_User)) OVER (PARTITION BY u.Username
It seems you need a running total.
If tot_qty is the same in all rows then you can use
tot_qty - SUM(rel_qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS qoh
ORDER BY id;
ROWS UNBOUNDED PRECEDING is the abbreviated version of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
One method, by using OVER() and MAX() and COUNT() based on this source could be:
SELECT ID, MAX(value) OVER (PARTITION BY Value2) as value
SELECT ID, value
,COUNT(value) OVER (ORDER BY ID) AS Value2
ORDER BY ID;
This is usually called a GROUPING AND WINDOWS solution.
Basically you set reset points according some rules, then you set up groups by SUMming reset points and finally use aggregated functions to get the desired value.
;WITH reset AS
/* Next CASE returns 1 when:
a) It is the ...
The second round starting with ID 4 has the time 08:11:55 in your sample data, so if indeed that's the time you're supposed to use as the starting point, the total should amount to 01:31:58. At any rate, here's a solution using the LAG and LEAD window functions. If you want to prevent a sort in the plan, make sure you create the following supporting index:
I've looked at similar problems and have never been able to find a window function solution that does a single pass over the data. I don't think it's possible. Window functions need to be able to be applied to all of the values in a column. That makes reset calculations like this very difficult, because one reset changes the value for all of the following ...
Something along these lines maybe? You can do running totals with the OVER clause for SUM.
CREATE TABLE Expenses (
expense_id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
INSERT INTO Expenses (amount) VALUES (50), (300), (75), (40)
WITH running_total AS (
This is not a trivial problem but it wouldn't be very hard with window functions (and CTEs for readability).
MySQL has not implemented either but lets see how it could be done:
( SELECT po, date, quantity, item,
SUM(quantity) OVER (PARTITION BY item
ORDER BY date, po
Sometimes you can improve query performance just by doing a little bit of tuning instead of changing your entire query. I noticed in your actual query plan that your query spills to tempdb in three places. Here's one example:
Resolving those tempdb spills may improve performance. If Quantity is always non-negative then you can replace UNION with UNION ALL ...
The following script is strictly based on your sample data (and its table structure) and is runnable for SQL Server 2008.
; with c as (
select personid, plancode, deduction
, rownum=ROW_NUMBER() over (partition by PersonID order by personid )
, c2 as (
select personid, plancode, deduction, T.ytd
cross apply (select ytd = sum(...
This is a "running total" type of problem: each row's total is calculated based on that row's value added to, or subtracted from, the previous row's total.
Since you are using SQL Server 2014, Transact-SQL has built-in syntax available to you to help you with getting the results.
Using the simplified model of "One deposit per month, many withdrawals per ...
Like many things, the answer to this question is "it depends". In this case, what it depends on is how many transactions each product will get.
With low to moderate volumes of transactions, it will be very fast to compute the running total on the fly and you don't have to write lots of code to compute, store and maintain the totals.
Index your table!
MySQL doesn't support Window Functions such as SUM(...) OVER(...) but it can be calculated using a variable, a self join or a subquery.
Queries using the question's sample
SELECT created, value
, @growth := @growth + value as growth
, (SELECT @growth := 0 as growth) as v
ORDER BY created
| created | ...
Here's one method of getting running totals after reset. There are probably others out there. Here is your test data:
CREATE TABLE user20907 (
ID INTEGER NULL,
CUSTOMERID INTEGER NULL,
AMOUNT INTEGER NULL,
RESETYN INTEGER NULL
INSERT INTO user20907 VALUES (1,111,5,0);
INSERT INTO user20907 VALUES (2,111,6,0);
INSERT INTO user20907 ...
Using a CURSOR:
ALTER TABLE #reset_runn_total ADD RunningTotal int;
DECLARE @id int, @val int, @reset int, @acm int, @grp int, @last_grp int;
SET @acm = 0;
DECLARE curRes CURSOR FAST_FORWARD FOR
SELECT id, val, reset_val, grp
ORDER BY grp, id;
FETCH NEXT FROM curRes INTO @id, @val, @reset, @grp;
SET @last_grp = @grp;
A combination of OUTER APPLY and ROW_NUMBER seems to do the trick:
WITH CTE AS
Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt)
Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt
FROM CTE A
OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [...
Assuming you use a recent enough version* of MariaDB, you can use window functions instead of variables to get a running total:
-- CREATE VIEW
-- running_total AS
id, credit, debit,
SUM(credit-debit) OVER (ORDER BY id) AS balance
You can create a view with the above query.
*: Window functions are ...
If you combine your price data with your event data in a UNION ALL query then the problem reduces to finding the last non NULL value. Itzik Ben-Gan writes about that problem here:
Given a table T1, with a key column called id and a NULLable value column called col1, return the last non NULL col1 value based on id order.
Going back to your problem, below ...
Quick-and-dirty, not tested on a live instance.
For each row of Event data we need one row from Price - the one which happened most recently, but before the Event's timestamp. TSQL supports the top 1 .. order by notation. By embedding the Price lookup as a sub-query in a SELECT list it will be executed once per row in Event. Predicating Price on Event's ...
Not windowed, but pure SQL version:
WITH x AS (
SELECT TOP 1 id,
val AS running_total,
1 AS level
CASE WHEN x.running_total < x.reset_val THEN x.running_total + r.val ELSE r.val ...
Here's a recursive CTE example I came up with (which seems to work). Is uses Row_Number() OVER to create a sequence number with no gaps. I don't know how well it would perform with your data, but it's something to try.
--Set up demo data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
drop table #temp
create table #temp (PKID int, NumValue int, GroupID ...
You got the syntax slightly wrong. It should be:
SUM([ReturnTaxAmount](x1.TotalAmount,@TaxRate)) -- no AS TaxAmount here
OVER (PARTITION BY x3.InvId
ORDER BY x3.createddate
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING) AS TaxAmount, -- it's ok here
The main thing you need is to generate the required order sequence using row_number().
seq = row_number() over
partition by t.CustID
order by t.InvoiceID,
CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
than use it for calculation of the cumulative balance
Using a Custom Aggregate function
We use CREATE FUNCTION to create a function int_add_pos_or_zero that adds numbers, but if they're less than 0, returns 0.
CREATE FUNCTION int_add_pos_or_zero(int, int)
RETURN greatest($1 + $2, 0);
Now we CREATE AGGREGATE on that so we can run it in a ...
If I get it right you can just add a window function outside of your query like:
SELECT interval_date, campaign_revenue
, SUM(campaign_revenue) OVER (ORDER BY interval_date)
+ (SELECT SUM(revenue)
WHERE s.campaignid = $1
AND s.created < $2
AND s.event_type = 'session_closed') as ...
It might be faster to read all relevant rows from the underlying table in one scan.
And you can run a window function over an aggregate function in the same SELECT.
Test this with EXPLAIN (ANALYZE, TIMING OFF) to see which is faster:
, coalesce(revenue , 0) AS campaign_revenue
, coalesce(total_revenue, 0) AS ...