44

Great question, Paul! I used a couple different approaches, one in T-SQL and one in CLR. T-SQL quick summary The T-SQL approach can be summarized as the following steps: Take the cross-product of products/dates Merge in the observed sales data Aggregate that data to the product/date level Compute rolling sums over the past 45 days based on this aggregate ...


25

This is a long answer, so I decided to add a summary here. At first I present a solution that produces exactly the same result in the same order as in the question. It scans the main table 3 times: to get a list of ProductIDs with the range of dates for each Product, to sum up costs for each day (because there are several transactions with the same dates), ...


24

An alternative SQLCLR solution that executes faster and requires less memory: Deployment Script That requires the EXTERNAL_ACCESS permission set because it uses a loopback connection to the target server and database instead of the (slow) context connection. This is how to call the function: SELECT RS.ProductID, RS.TransactionDate, RS....


23

The answer is to use 1 PRECEDING, not CURRENT ROW -1. So, in your query, use: , SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PreviousRunningTotal Also note that on your ...


19

This is how I'd do it: SELECT * FROM #MyTable AS mt CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc FROM #MyTable AS mt2 WHERE mt2.Col_A = mt.Col_A -- GROUP BY mt2.Col_A ) AS ca; The GROUP BY clause is redundant given the data provided in the question, but may give you a ...


18

COUNT(*) Returns all rows ignoring nulls right? I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULLs SELECT COUNT(*) FROM (VALUES (CAST(NULL AS INT)), (CAST(NULL AS INT))) V(C) Returns 2. Altering the above query to COUNT(C) would return 0 as when using COUNT with an expression ...


17

This gives the distinct count(*) for A partitioned by B: dense_rank() over (partition by B order by A) + dense_rank() over (partition by B order by A desc) - 1


17

If you are on 64-bit Enterprise, Developer, or Evaluation edition of SQL Server 2014 you can use In-Memory OLTP. The solution will not be a single scan and and will hardly use any window functions at all but it might add some value to this question and the algorithm used could possibly be used as inspiration to other solutions. First you need to enable In-...


17

The following query achieves the desired result: select *, first_value(somevalue) over w as carryforward_somevalue from ( select *, sum(case when somevalue is null then 0 else 1 end) over (partition by person order by id ) as value_partition from test1 ) as q window w as (partition by person, value_partition order by id); Note the null case statement -...


17

1. Window functions plus subqueries Count the steps to form groups, similar to Evan's idea, with modifications and fixes: SELECT id_type , min(date) AS begin , max(date) AS end , count(*) AS row_ct -- optional addition FROM ( SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp FROM ( SELECT date, id_type ...


16

The Parallel Data Warehouse (PDW) features are not enabled. This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MIN: Windowing function support was considerably extended in 2012, compared with the basic implementation available starting with ...


15

Your issue appears to be that you are applying the same WINDOW (named w) for both your COUNT(*) and your rank(). When you use a WINDOW which contains an ORDER BY clause, and you then apply certain aggregations such as SUM or COUNT, it applies the aggregation continuously across the ordering, which is why your COUNT and rank() are identical. If you modify ...


14

By far the cleanest solution is to use window function sum with rows between: with days as ( SELECT date_trunc('day', d)::date as day FROM generate_series(CURRENT_DATE-31, CURRENT_DATE-1, '1 day'::interval) d ), counts as ( select days.day, sum((random()*5)::integer) num FROM days -- left ...


14

The PARTITION BY works as a "windowed group" and the ORDER BY does the ordering within the group. However, because you're using GROUP BY CP.iYear, you're effectively reducing your window to just a single row (GROUP BY is performed before the windowed function). The average of a single row will be the value of that row, in your case AVG(CP.mUpgradeCost). As ...


13

Anyone know what is the problem? Is such as kind of query possible in SQL Server? No it isn't currently implemented. See the following connect item request. OVER clause enhancement request - DISTINCT clause for aggregate functions Another possible variant would be SELECT M.A, M.B, T.A_B FROM MyTable M JOIN (SELECT CAST(COUNT(...


13

select min(country) as country, min(begintimestamp) as first_begin_ts, sum(distance) as distance from ( select t1.*, sum(group_flag) over (order by begintimestamp) as grp from ( select *, case when lag(country) over (order by begintimestamp) = country then null else 1 ...


13

Well that was fun :) My solution is a bit slower than @GeoffPatterson's but part of that is the fact that I'm tying back to the original table in order to eliminate one of Geoff's assumptions (i.e. one row per product/date pair). I went with the assumption this was a simplified version of a final query and may require additional information out of the ...


13

The relatively low row-mode performance of LEAD and LAG window functions compared with self joins is nothing new. For example, Michael Zilberstein wrote about it on SQLblog.com back in 2012. There is quite a bit of overhead in the (repeated) Segment, Sequence Project, Window Spool, and Stream Aggregate plan operators: In SQL Server 2016, you have a new ...


13

You need an extra column to carry along the running total (fiddle). In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is your SUM(E1, E2, ... En-1). WITH R AS (SELECT N, B, E = B, RunningTotalE = B FROM A WHERE ...


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

It really should be noted that MariaDB 10.2 (released in 2017 May) has window functions. That's certainly one avenue to pursue if you need MySQL and window functions.


11

So the trick here is a property of two equally incrementing series which produce a difference that can be used to identify islands {11,12,13} - {1,2,3} = {10,10,10}. This property isn't enough to identify islands in and of itself, but it's a crucial step that we can exploit to do so. Background Stepping aside from the problem. Let's check this out.. Here ...


11

When and if you are able to upgrade from SQL Server 2012 to SQL Server 2016, you may be able to take advantage of the much improved performance (especially for frameless window aggregates) provided by the new batch mode Window Aggregate operator. Almost all large data processing scenarios work better with columnstore storage than rowstore. Even without ...


11

Using the concatenation technique from The Last non NULL Puzzle by Itzik Ben Gan would look like this with your sample table and column data types. select T.cat, cast(substring( max(cast(T.t as binary(2)) + cast(T.val1 as binary(4))), 3, 4 ) as int), cast(...


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

Sample data: CREATE TABLE dbo.Thing ( Product integer NOT NULL, TheDate date NOT NULL, TheWeight decimal(5, 1) NOT NULL ); INSERT dbo.Thing (Product, TheDate, TheWeight) VALUES (900000, CONVERT(date, '20160101', 112), 20.0), (900000, '20160303', 12.2), (900000, '20160706', 15.0), (900000, '20160707', 14.0), (900000, '...


10

COUNT(*) OVER () is one of those operations that sounds like it ought to be cheap for the query optimizer. After all, SQL Server already knows how many rows are returned by the query. You're just asking it to project that value into the result set of the query. Unfortunately, COUNT(*) OVER () can be an expensive operation depending on the query that you're ...


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


10

I would form groups with the window function count() and then take the first value for each group: SELECT foo_label , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price , foo_date FROM ( SELECT foo_label , count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp , ...


10

The canonical way to do this is the following: ROW_NUMBER() OVER(ORDER BY (SELECT NULL)). If you're golfing, you might try something like this: SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM STRING_SPLIT('one,two,three,four,five',',') It works for the simple case you posted in the question: I should say that there is not a documented ...


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