Questions tagged [window-functions]

A function that partitions a result set and computes something within that partition without rolling up the data in that partition.

Filter by
Sorted by
Tagged with
59
votes
6answers
54k views

Date range rolling sum using window functions

I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need: SELECT TH.ProductID, ...
30
votes
2answers
35k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
24
votes
4answers
58k views

Limit results to the first 2 ranking rows

In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, ...
18
votes
5answers
50k views

Using DISTINCT in window function with OVER

I'm trying to migrate a query from Oracle to SQL Server 2014. Here is my query which works great in Oracle: select count(distinct A) over (partition by B) / count(*) over() as A_B from MyTable ...
18
votes
6answers
28k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
15
votes
2answers
12k views

Using window function to carry forward first non-null value in a partition

Consider a table that records visits create table visits ( person varchar(10), ts timestamp, somevalue varchar(10) ) Consider this example data (timestamp simplified as counter) ts| person ...
15
votes
1answer
42k views

Running total to the previous row

I need some help with windowing functions. I know you can calculate the sum within a window and the running total within a window. But is it possible to calculate the previous running total i.e. the ...
14
votes
3answers
7k views

How to get the last not-null value in an ordered column of a huge table?

I have the following input: id | value ----+------- 1 | 136 2 | NULL 3 | 650 4 | NULL 5 | NULL 6 | NULL 7 | 954 8 | NULL 9 | 104 10 | NULL I expect the following ...
14
votes
1answer
10k views

Performance comparison between using Join and Window function to get lead and lag values

I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the lead and lag values of a certain time for a ...
13
votes
7answers
2k views

Grouping or Window

I have a situation I think can be solved using window function but I'm not sure. Imagine the following table CREATE TABLE tmp ( date timestamp, id_type integer ) ; INSERT INTO tmp ...
13
votes
3answers
42k views

What is the difference between COUNT(*) and COUNT(*) OVER()

Take the following code example: SELECT MaritalStatus, COUNT(*) AS CountResult COUNT(*) OVER() AS CountOverResult FROM (schema).(table) GROUP BY Marital Status COUNT(*) ...
12
votes
2answers
9k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column. Consider the following table: lap_id (serial), lap_no (int), car_type (...
12
votes
1answer
3k views

Combine two event tables into a single timeline

Given two tables: CREATE TABLE foo (ts timestamp, foo text); CREATE TABLE bar (ts timestamp, bar text); I wish to write a query that returns values for ts, foo, and bar that represents a unified ...
12
votes
1answer
242 views

Why does batch mode window aggregate yield arithmetic overflow?

The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening? SELECT a, p, ...
11
votes
5answers
15k views

SQL counting distinct over partition

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A. MyTable Col_A | Col_B A | 1 A | 1 A | 2 A | 2 A | 2 A | 3 b | ...
11
votes
4answers
5k views

How can I write windowing query which sums a column to create discrete buckets?

I have a table which includes a column of decimal values, such as this: id value size -- ----- ---- 1 100 .02 2 99 .38 3 98 .13 4 97 .35 5 96 .15 6 95 .57 7 94 .25 ...
11
votes
3answers
17k views

How to select the set of last non-NULL values per column over a group?

I am using SQL Server 2016 and the data I am consuming has the following form. CREATE TABLE #tab (cat CHAR(1), t CHAR(2), val1 INT, val2 CHAR(1)); INSERT INTO #tab VALUES ('A','Q1',2,NULL),('A',...
11
votes
3answers
26k views

Calculate rolling sum over 7 consecutive day period on PostgreSQL

I need to get the rolling sum over a period of 7 days for each row (1 row per day). For example: | Date | Count | 7-Day Rolling Sum | ------------------------------------------ | 2016-02-01 | ...
11
votes
1answer
13k views

How do I get the aggregate of a window function in Postgres?

I have a table containing a two columns of permutations/combinations of integer arrays, and a third column containing a value, like so: CREATE TABLE foo ( perm integer[] NOT NULL, combo integer[] ...
10
votes
1answer
571 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
10
votes
2answers
13k views

COUNT(*) OVER ()

I have a COUNT(*) OVER () as part of my query to fetch a large amount of data with about 15 joins to tables (some big, some small). What's the best solution for count here? Separate query to find ...
10
votes
2answers
2k views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
10
votes
2answers
255 views

Windowing offset based on time stamp

I'm writing a query that will be used to page results for a social feed. The concept is the mobile app will request N items, and provide a starting datetime which I have called @CutoffTime below. ...
10
votes
1answer
363 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...
9
votes
5answers
6k views

Optimize Subquery with Windowing Function

As my performance tuning skills never seem to feel sufficient, I always wonder if there is more optimization I can perform against some queries. The situation that this question pertains to is a ...
9
votes
2answers
14k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
9
votes
1answer
18k views

What is the difference between `ORDER BY` and `PARTITION BY` arguments in the `OVER` clause?

These queries below both give me exactly the same results, which I assume is because of my dataset rather than how the arguments work. When using an OVER clause, what is the difference between ORDER ...
9
votes
1answer
8k views

Solving “Gaps and Islands” with row_number() and dense_rank()?

How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it, Let's use something like this ...
8
votes
3answers
11k views

In Microsoft SQL Server 2008, syntax generates the error “The Parallel Data Warehouse (PDW) features are not enabled.”

I have the following virtual column generated from an aggregate over a sorted partition, MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC ) However, when I execute that, I get ...
8
votes
1answer
14k views

Postgres - window function rank and count

I have some tables that track athletes time around a track: competitions, competition_users and competition_user_sessions I am constructing a query that extracts the current rank and total no of ...
8
votes
1answer
3k views

Querying sums of grouped consecutive rows in PostgreSQL 9

I have data about people traveling in different countries like this: country | begintimestamp | distance Germany | 2015-01-01 00:00:00 | 100 Germany | 2015-01-01 01:12:13 | 30 France | ...
8
votes
4answers
763 views

Store millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
7
votes
4answers
6k views

Adding a ROW_NUMBER() with no column to ORDER BY?

So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order. Let's say my source data is: SELECT value FROM STRING_SPLIT('one,two,...
7
votes
2answers
404 views

Average of last 4 runs of a product

I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a ...
7
votes
2answers
2k views

Explain an OVER clause

I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character. If I am reading the ...
7
votes
3answers
5k views

Can I calculate ROW_NUMBER() for only consecutive records?

I need to calculate sequence numbers for consecutive values. That sounds like a job for ROW_NUMBER()! DECLARE @Data TABLE ( Sequence TINYINT NOT NULL PRIMARY KEY, Subset CHAR(1) NOT ...
7
votes
1answer
6k views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ----------------+-----------------------------+-----------------------...
7
votes
1answer
2k views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...
6
votes
1answer
3k views

Row by row subtraction with single input number

I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number. I have table a CREATE TABLE a (id int, code text, qty numeric); And data in it ...
6
votes
3answers
3k views

Calculate 40 day moving average w.r.t to a field

I have a table that stores the information about user calls in a call center. The table has a call_id, date when the call was made, actual date and time of the call, call type and a score associated ...
6
votes
4answers
25k views

Return total number of rows and selected (aggregated) data

I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table. How can I do that or how can I get the same result in ...
6
votes
2answers
7k views

Check if contiguous date interval exists

I have a table in PostgreSQL which describes some events that have a start date and an end date: CREATE TABLE my_table ( event_id serial NOT NULL, start_date timestamp without time zone NOT NULL,...
6
votes
1answer
4k views

What exactly does the last_value() window function do?

While testing my answer to SQL - Getting data from first and last row of each group I noticed something strange. The first_value and last_value window functions appear to behave differently. As ...
6
votes
3answers
515 views

A more intelligent ntile

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value. For example with the following query: select id,...
6
votes
3answers
40k views

Sum of previous n number of columns based on some category

I have following input for which I need to calculate the sum of values for previous x number of weeks for each category. If the x is 3 the output would look like this: Note that the last value is 49 ...
6
votes
1answer
5k views

Window function IGNORE NULLS workaround for PostgreSQL [duplicate]

With the following query I can use the LAG() function to repeat the last non null value of c column: SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000) ...
6
votes
1answer
2k views

Grouping data based on cumulative sum

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using ...
5
votes
4answers
277 views

SQL Challenge - The Sensor Threshold Exception Report

I've added a solution without using window functions and a benchmark with a large data set below Martin's Answer This is a followup thread to GROUP BY using columns not in the SELECT list - when is ...
5
votes
1answer
2k views

Carry over long sequence of missing values with Postgres

I have a table like this: create table foo (foo_label text, foo_price int, foo_date date); insert into foo ( values ('aaa', 100, '2017-01-01'), ('aaa', NULL, '2017-02-...
5
votes
1answer
1k views

Understand why rank() over doesn't fit to don't select duplicates rows

I would like to understand why I have different results I have a table called active_transfert where I log image transfert user_id | image_id | created_at --------|----------|----------- 1 |1 ...

1
2 3 4 5 6