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

learn more… | top users | synonyms

40
votes
5answers
1k 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, ...
14
votes
2answers
15k 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 ...
10
votes
4answers
2k 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 ...
10
votes
2answers
2k 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 specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type ...
10
votes
2answers
200 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
244 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
6answers
8k 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 ...
8
votes
1answer
204 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 ...
7
votes
4answers
369 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
1answer
5k 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 ...
7
votes
1answer
4k 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[] ...
7
votes
1answer
143 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
2answers
955 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 ...
5
votes
3answers
4k 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 ...
5
votes
2answers
5k 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 ...
5
votes
1answer
128 views

“Ranking” values in an ORDER BY clause?

I have the following table: Table "public.employee_employee" Column | Type | Modifiers ...
5
votes
2answers
68 views

Combine two event tables for multiple timelines into a single result set

This question is an extension to a question I've previously asked that was overly simplified. The more accurate example is demonstrated in this SQLFiddle, where I demonstrate a working (but slow) ...
5
votes
1answer
97 views

Sort by number of related rows in referencing table

Let there be two tables: Users id [pk] | name --------+--------- 1 | Alice 2 | Bob 3 | Charlie 4 | Dan Emails id | user_id | email ----+---------+------- 1 | 1 ...
4
votes
3answers
255 views

Scalable query for running counts of events within x previous days

I already posted this question on stackoverflow but I thought that I might get a better answer here. I have a table storing millions of events occurring to users: ...
4
votes
1answer
312 views

How to get a row_number to have the behavior of dense_rank

I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
4
votes
1answer
1k 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 ...
4
votes
1answer
446 views

RANGE clause error within window function

I'm trying to use postgres' RANGE clause within a window function to dynamically set the window to the past 4 weeks not including the current date. Here is my attempt at it: SELECT ...
4
votes
1answer
101 views

Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg: Time person Result 08:12:36 ...
3
votes
2answers
2k views

Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this SELECT * FROM ( SELECT ...
3
votes
1answer
6k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
1answer
1k views

Select row at the nth percentile

I have two tables, table1 and table2. Let the two tables contain date, id and latency column. I have a simple query that performs a join on the two tables and returns a set of rows: Select ...
3
votes
1answer
70 views

GROUP BY uninterrupted sequence of logs for same location

I'm having a hard time describing my problem or finding it via Google. I want to be able to group rows based on location and employee, but the group should be split if a row with the same location ...
3
votes
1answer
218 views

Grouping() equivalent in PostgreSQL?

I have a SQL query that runs in MS SQL Server and it has grouping(), rank() and rollup() functions / keywords in it. The query is given below. How can this be translated to PostgreSQL? select top ...
3
votes
1answer
998 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
3
votes
1answer
257 views

Query with dynamic ranges in PostgreSQL based on status

We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
3
votes
1answer
57 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
2
votes
4answers
471 views

Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'

I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below: declare @t table ( id int ,code char(2) ...
2
votes
3answers
2k 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 ...
2
votes
3answers
1k views

What is the simplest way of excluding the current row from the result of an analytic?

I want to do something like the following: with w as ( select level as foo, decode(level,8,1,mod(level,4))*100 as bar from dual connect by level<9 ) select foo, ...
2
votes
2answers
117 views

Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses

The error in the title is produced when add the second condition in my where clause. I understand the error, have tried using subqueries as seen in other posts to fix this but no luck. Does anyone ...
2
votes
2answers
81 views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
2
votes
2answers
669 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 ...
2
votes
1answer
34 views

How to group list of start times by a category?

I have a list people and when they started occupied various rooms in the building. Like so: IF OBJECT_ID('tempdb..#RoomAssignments') IS NOT NULL DROP TABLE #RoomAssignments; CREATE TABLE ...
2
votes
1answer
488 views

Maximum sum() of overlapping ranges

Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
2
votes
2answers
85 views

Nearest value for each foreign key

I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list. Unfortunately, the gap from the given ...
2
votes
1answer
51 views

Measure time duration between events, partitioned by hours

I have the following table in PostgreSQL 9.4, which persists users log on/off events (log on is event_type 1, log off is event_type 0). CREATE TABLE user_online_offline_events ( id serial, ...
2
votes
1answer
756 views

Using PL/R for clustering geometries

I have a Postgres/PostGIS database of geography points which I'm trying to cluster; meaning I want to group and count them by proximity. I have had first success with the kmeans Postgresql extension, ...
2
votes
1answer
659 views

Filter on a window function without writing an outer SELECT statement

Since window functions cannot be included in the WHERE clause of the inner SELECT, is there another method that could be used to write this query without the outer SELECT statement? I'm using Oracle. ...
2
votes
1answer
368 views

sorting groups of related rows by average values while keeping the groups together

I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible. Consider the following table. corrmodel=# SELECT * from data limit 1; id | datasubgroup_id | datafile_id | ...
2
votes
2answers
124 views

Calculating the percentage change from the previous row

I just entered the world of window functions. I have a similar problem to solve as in this question. I have this table: The last column (%Grown) is given by: -(1 - price of row 2 / price of row ...
1
vote
1answer
221 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to ...
1
vote
2answers
31 views

Selecting Values That Differ From Section Average

I'm attempting to select data from a table that's already been made containing records of file size as taken every twelve hours. The table looks something like this: surrogate_id | partition_id | ...
1
vote
1answer
110 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 ...
1
vote
1answer
261 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 | ...
1
vote
2answers
485 views

Count events on “user based” sliding window

So, if I wanted to know how many events a certain user bought in certain timeframe windows. I will use 7 for the example, but the query should accept this number of days as a parameter, which will be ...