43

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


36

MySQL does not support Window Functions(*). There is what we call "a poor man's window function" in the form of GROUP_CONCAT(). There are plenty of tricks using GROUP_CONCAT to emulate window functions. They are not as pretty (syntactically) and are sometimes too limited. I've written a few. See my blog post complaining about the missing window functions, ...


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


20

Your description results in a table definition like this: CREATE TABLE tbl ( lap_id serial PRIMARY KEY , lap_no int NOT NULL , car_type enum NOT NULL , race_id int NOT NULL -- REFERENCES ... , UNIQUE(race_id, car_type, lap_no) ); General solution for this class of problems To get the longest sequence (1 result, longest of all, arbitrary pick ...


18

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


17

select * from ( SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN FROM Table ) a where a.RN <= 2


17

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set: WITH partitioned AS ( SELECT *, number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp FROM atable WHERE status = 'FREE' ), counted AS ( SELECT *, COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt FROM partitioned ) SELECT id_set, ...


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


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


16

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


15

You could put the original query using rank() into a subquery and wrap it with a query that filters the results.


14

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


14

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


14

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


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


12

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


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

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


11

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


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

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

A simple and fast variant: SELECT min(number) AS first_number, count(*) AS ct_free FROM ( SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp FROM tbl WHERE status = 'FREE' ) x GROUP BY grp HAVING count(*) >= 3 -- minimum length of sequence only goes here ORDER BY grp LIMIT 1; Requires a gapless ...


10

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


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

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


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


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