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

14
votes
2answers
16k 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 ...
1
vote
1answer
142 views

How to use an alias name that has RANK() OVER in a WHERE clause

I have a query that has a RANK() OVER function inside it but I would like to use the results displayed on this column in the WHERE clause that follows. How do I write that as all the other questions I ...
0
votes
1answer
205 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
7
votes
2answers
1k 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 ...
4
votes
2answers
62 views

Finding earliest connected value over two columns

I have a, perhaps slightly esoteric, use case. Given a table like the following: id1 | id2 | timestamp -----+-----+--------------------- 3 | 4 | 2016-03-22 09:52:15 1 | 2 | ...
7
votes
2answers
372 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 ...
3
votes
3answers
89 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 | ...
3
votes
1answer
39 views

Window function not working on non selected rows

I'm creating auto switching partitions procedure. I'm creating table that looks like this: CREATE TABLE #MaxPartitionRows (MaxRows BIGINT NOT NULL, PNumber INT NOT NULL, PFName SYSNAME ...
5
votes
1answer
107 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
81 views

Add a total quantity column for groups of rows

I have an Items table and an Inventory table. An Item can belong to multiple Inventory records. I'm trying to return a list of ALL Inventory records but with their quantity in a new column. For ...
1
vote
3answers
117 views

Select all before first break

I have a table with three columns: id, sort, name in PostgreSQL 9.5 database. I need to select all rows starting from sort=1 and before the first break (ordered by sort). For example I have: id | ...
1
vote
0answers
16 views

ROW_NUMBER() window_agg rows estimation

PostgreSQL 9.4 Consider the following pretty simlpe query: EXPLAIN ANALYZE SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY date DESC NULLS LAST) rn ...
2
votes
2answers
163 views

Calculating Running Average using Over clause in SQL server

My table looks like this: CREATE TABLE [dbo].[TimeSeries]( [LOCID] [int] NOT NULL, [Date] DateTime, [YEAR] int, [MONTH] tinyint, [RTT] [int] PRIMARY KEY ( [LOCID] ASC,[Date] ASC) ) It contains 12 ...
2
votes
1answer
60 views

How to get max sequence of rows? [duplicate]

I have a table with statistic of games which has next rows (I only write about rows that has interest in this context): id BIGINT PRIMARY KEY date TIMESTAMP WITH TIME ZONE status ...
-4
votes
1answer
46 views

Why all ranking is 1 for this query [closed]

Declare @ranking_term char set @ranking_term ='Amount' select case (@ranking_term)when'Amount' then ( select Dense_rank() over (order by Amount desc) ) else( select ...
7
votes
1answer
158 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 ...
4
votes
1answer
274 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 ...
1
vote
0answers
43 views

Window function vs group by method: find value for MAX ID

I'm trying to understand the performance issue with Window function when i select value for MAX ID --Old group By method SELECT tj.MainProcedure , tj.StartTime , tj.EndTime , ...
0
votes
1answer
97 views

postgres window functions: using CASE to partition by age group

I am using postgres window functions to get a list of users taking part in a competition and their corresponding ranks based on a number of columns, all good so far....now I need to get rank based on ...
1
vote
1answer
34 views

Sessionizing Web Log Data using MySQL (perhaps by simulating window functions)

I have a large MySQL Table containing about 15 Million rows of web log data. Depending on the IP, useragent and site_id (different sections on the website) I want to create user sessions. The session ...
1
vote
1answer
43 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
2
votes
1answer
54 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, ...
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 | ...
40
votes
5answers
2k 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, ...
0
votes
1answer
129 views

Aggregation by positive and negative values

I have a table called "PROD_COST" with 5 columns: +----+----------+------+-----------+-------------+ | ID | Duration | Cost | COST_NEXT | COST_CHANGE | ...
10
votes
2answers
202 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
262 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 = ...
3
votes
1answer
73 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 ...
2
votes
2answers
227 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
174 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
350 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 | ...
7
votes
4answers
388 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
50 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 ...
3
votes
1answer
63 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
7k 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
165 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
87 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) ...
8
votes
1answer
263 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
156 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
30 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
105 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
57 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
54 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 ...
1
vote
2answers
95 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
581 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
339 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
2answers
875 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 ...
6
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 ...
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
573 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| | ...