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 ...
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, ...
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), ...
An alternative SQLCLR solution that executes faster and requires less memory:
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:
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)
Also note that on your ...
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 ...
This is how I'd do it:
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 ...
This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:
WITH partitioned AS (
number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
WHERE status = 'FREE'
counted AS (
COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
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-...
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 ...
1. Window functions plus subqueries
Count the steps to form groups, similar to Evan's idea, with modifications and fixes:
, min(date) AS begin
, max(date) AS end
, count(*) AS row_ct -- optional addition
SELECT date, id_type, count(step OR NULL) OVER (ORDER BY date) AS grp
SELECT date, id_type
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
FROM (VALUES (CAST(NULL AS INT)),
(CAST(NULL AS INT))) V(C)
Altering the above query to COUNT(C) would return 0 as when using COUNT with an expression ...
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 ...
select min(country) as country,
min(begintimestamp) as first_begin_ts,
sum(distance) as distance
sum(group_flag) over (order by begintimestamp) as grp
when lag(country) over (order by begintimestamp) = country then null
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 ...
Anyone know what is the problem? Is such as kind of query possible in
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
FROM MyTable M
JOIN (SELECT CAST(COUNT(...
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
The following query achieves the desired result:
select *, first_value(somevalue) over w as carryforward_somevalue
select *, sum(case when somevalue is null then 0 else 1 end) over (partition by person order by id ) as value_partition
) as q
window w as (partition by person, value_partition order by id);
Note the null case statement -...
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 ...
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 ...
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 ...
A simple and fast variant:
SELECT min(number) AS first_number, count(*) AS ct_free
SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp
WHERE status = 'FREE'
GROUP BY grp
HAVING count(*) >= 3 -- minimum length of sequence only goes here
ORDER BY grp
Requires a gapless ...
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 (
-- left ...
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).
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 ...