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

7
votes
2answers
142 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. ...
2
votes
2answers
35 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 ...
2
votes
2answers
25 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 ...
1
vote
1answer
93 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 | ...
6
votes
4answers
317 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 ...
1
vote
1answer
32 views

Calculate column running total

Given the following schema and dummy data: CREATE TABLE transactions ( id SERIAL PRIMARY KEY, memo VARCHAR(255) NOT NULL, amount INT NOT NULL, tx_date DATE NOT NULL ); INSERT INTO ...
2
votes
1answer
32 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 ...
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 ...
0
votes
1answer
45 views

PostgreSQL window functions very slow on big table

I have a PostgreSQL table constructed as device_id | point | dt_edit ----------+-----------+---------- 100 | geometry | timestamp 101 | geometry | timestamp 100 | geometry | ...
5
votes
2answers
32 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) ...
7
votes
1answer
88 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 ...
1
vote
1answer
35 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
0answers
26 views

Does SQL Server 2012 Standard Edition have an option for select a window over time range?

As an example, in StackOverflow they answer the question Finding a count of rows in an arbitrary date range using Oracle. One of the queries is very similar to what I need, but in reverse, I need ...
2
votes
2answers
59 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, ...
1
vote
1answer
31 views

SUM function fails when tie exist

I want to do ABC analysis on this. As you see, each model has severval partID, each partID has a number of counts. As you see, the Cum_Counts is wrong when 'ties' exists. I want to calcute ...
0
votes
1answer
51 views

Filtering SQL Search Query

How can I filter results in a SQL query? Let’s say I have Products A-Z, and each product has a monthly observation for X Months, but for most products the value in the observation stays the same for ...
0
votes
2answers
65 views

Speed up SELECT with WINDOW, (PARTITION … ORDER) with compound index

I have the following table: CREATE TABLE ranking_points ( id serial NOT NULL, store_id integer, book_id integer, list_name character varying(255), rank integer, created_at timestamp ...
2
votes
1answer
289 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 ...
4
votes
3answers
139 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: ...
2
votes
2answers
275 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 ...
5
votes
2answers
4k 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 ...
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 ...
0
votes
1answer
130 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
1
vote
1answer
103 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 ...
9
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 ...
0
votes
1answer
9 views

extract state changes from snapshot-based archive table

I have a table snapshot, storing snapshots (of the new state) every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to ...
0
votes
1answer
47 views

Rank function creating duplicate partitions? (two rank 1's for the same key)

The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back. WITH rank_cte AS ...
1
vote
1answer
59 views

Count of 3 consecutive unsynced dates

I have a table set out like so: user_id the_date minutes_asleep ---------------------------- 1 2015-01-01 480 1 2015-01-02 0 1 2015-01-03 0 1 2015-01-04 0 ...
0
votes
0answers
41 views

How to determine is a value exists in a column within a partition

I have a set of data where I need to look at partitioned data and determine if a value exists within that partition. Example data: AccountNumber Code Status Credited ...
5
votes
1answer
91 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 ...
3
votes
1answer
85 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 ...
2
votes
3answers
900 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 ...
1
vote
1answer
94 views

SQL Server Query performance with window function

We write a query that include unpivot, partition by and order by.Query is: SELECT PersonId ,SalaryDate ,ID ,Type ,SalaryValue ,ROW_NUMBER() OVER ( PARTITION BY PersonId ...
0
votes
2answers
236 views

Is it possible to speed up this PARTITION BY query?

I've got a timeserie table. Each datapoint has also a statut field which indicates its providing source. I'd like to request last available datapoint as of date T but there could be several results, ...
2
votes
4answers
383 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) ...
0
votes
0answers
120 views

Window function sorting performance

For background, I am on an instance of SQL Server 2012 Enterprise Edition. I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in ...
0
votes
0answers
52 views

Postgres - Combine similar queries with extra where clause

I am trying to combine a number of fairly time consuming queries into one (if possible) The database contains race-track data that records the times for laps completed by drivers (or users here), the ...
3
votes
1answer
850 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 ...
2
votes
1answer
551 views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ...
1
vote
1answer
103 views

Calculate value differences of rows before and after bounded by multiple referenced rows

Supposed I have a user travelling table like this: id | start_time | end_time | location | ----+--------------+------------+------------+ 1 | 1 | 2 | Loc_A | 1 | ...
3
votes
2answers
1k 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
190 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
309 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 ...
2
votes
1answer
465 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. ...
0
votes
1answer
109 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
0
votes
1answer
726 views

row_number over partition at Version < Firebird 3

I´m currently at the end. Given facts: ID ID2 FROM TO COSTS 4985 501671 2013-07-25 00:00:00 2013-12-30 00:00:00 17.8 4985 501671 2013-09-05 00:00:00 ...
1
vote
1answer
153 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
4
votes
1answer
121 views

“Ranking” values in an ORDER BY clause?

I have the following table: Table "public.employee_employee" Column | Type | Modifiers ...
0
votes
1answer
125 views

How can I get the latest five scores per user per subject?

There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this: ...
6
votes
1answer
3k 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 ...