# Tag Info

12

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 ); INSERT dbo.Example (id, val) VALUES (1, 136), (2, NULL), (3, 650), (4, NULL), (5, NULL), (6, NULL), (7, ...

11

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 ...

10

This is how I solved a similar problem on Teradata using nested OLAP-functions: SELECT dt.*, -- 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 ...

9

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: SELECT u.Username, -- COUNT(d.FK_User) AS UserCount, -- if you need both counts SUM(COUNT(d.FK_User)) OVER (PARTITION BY u.Username ...

9

It seems you need a running total. If tot_qty is the same in all rows then you can use SELECT id, tot_qty, rel_qty, tot_qty - SUM(rel_qty) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS qoh FROM dbo.foo ORDER BY id; ROWS UNBOUNDED PRECEDING is the abbreviated version of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This ...

8

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 FROM ( SELECT ID, value ,COUNT(value) OVER (ORDER BY ID) AS Value2 FROM dbo.HugeTable ) a ORDER BY ID; Result Id UpdatedValue 1 136 2 136 3 650 4 650 5 650 6 650 7 954 8 954 9 104 10 ...

7

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 ( SELECT AuditID, CreditID, TS, Val, /* Next CASE returns 1 when: a) It is the ...

7

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: ...

6

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 ...

6

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, amount decimal(19,5) ) INSERT INTO Expenses (amount) VALUES (50), (300), (75), (40) GO WITH running_total AS ( SELECT expense_id, amount, SUM(...

6

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: WITH running_purchase AS ( SELECT po, date, quantity, item, SUM(quantity) OVER (PARTITION BY item ORDER BY date, po ...

6

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 ...

5

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 ) from dbo.Payroll ) , c2 as ( select personid, plancode, deduction, T.ytd from c cross apply (select ytd = sum(...

5

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 ...

4

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! Make ...

4

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 Variable SELECT created, value , @growth := @growth + value as growth FROM data , (SELECT @growth := 0 as growth) as v ORDER BY created Output | created | ...

4

Please check if the performance is sufficient: SELECT empid ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW()) FROM emplog b WHERE b.empid = a.empid AND b.time > ...

4

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 ); BEGIN TRANSACTION; INSERT INTO user20907 VALUES (1,111,5,0); INSERT INTO user20907 VALUES (2,111,6,0); INSERT INTO user20907 ...

4

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 FROM #reset_runn_total ORDER BY grp, id; OPEN curRes; FETCH NEXT FROM curRes INTO @id, @val, @reset, @grp; SET @last_grp = @grp; ...

4

A combination of OUTER APPLY and ROW_NUMBER seems to do the trick: WITH CTE AS ( SELECT *, Tot_Cnt = ROW_NUMBER() OVER(ORDER BY dt) FROM #x ) SELECT A.dt, A.[Name], B.Unq_Cnt, A.Tot_Cnt, Ratio_of_Cnts = CONVERT(NUMERIC(10,4),B.Unq_Cnt)/A.Tot_Cnt FROM CTE A OUTER APPLY (SELECT Unq_Cnt = COUNT(DISTINCT [...

4

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 SELECT id, credit, debit, SUM(credit-debit) OVER (ORDER BY id) AS balance FROM tbl_total ORDER BY id ; You can create a view with the above query. *: Window functions are ...

3

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 ...

3

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 ...

3

Not windowed, but pure SQL version: WITH x AS ( SELECT TOP 1 id, val, reset_val, val AS running_total, 1 AS level FROM reset_runn_total UNION ALL SELECT r.id, r.val, r.reset_val, CASE WHEN x.running_total < x.reset_val THEN x.running_total + r.val ELSE r.val ...

3

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 go create table #temp (PKID int, NumValue int, GroupID ...

3

You got the syntax slightly wrong. It should be: SELECT x1.TotalAmount, 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 x2....

3

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, t.Date, CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END ) than use it for calculation of the cumulative balance ; WITH cte AS ( SELECT CustID, [...

3

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) RETURNS int AS \$\$ BEGIN RETURN greatest(\$1 + \$2, 0); END; \$\$ LANGUAGE plpgsql IMMUTABLE; Now we CREATE AGGREGATE on that so we can run it in a ...

3

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) FROM sale_event WHERE s.campaignid = \$1 AND s.created < \$2 AND s.event_type = 'session_closed') as ...

3

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: SELECT interval_ts , coalesce(revenue , 0) AS campaign_revenue , coalesce(total_revenue, 0) AS ...

Only top voted, non community-wiki answers of a minimum length are eligible