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

0
votes
0answers
21 views

Simplify Month Over Month % Change, 4 Months Moving Average %

invoice created_at | amount | status | ------------+--------+--------- 2016-06-01 | 50 | 2 | 2016-05-30 | 150 | 2 | 2016-04-29 | 32 | 2 | 2016-03-28 | ...
3
votes
2answers
37 views

Count rows with max per group and additional condition

I have the following table: horse_main_id | race_id | horse_name | rating100 | race_result ---------------+---------+-------------------+-----------+------------- 23 | 159 | ...
1
vote
1answer
33 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
2
votes
1answer
11 views

In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row?

I have a table with the following schema: CREATE TABLE foo ( data TEXT NOT NULL, count BIGINT NOT NULL DEFAULT 1, PRIMARY KEY (data) ); I'd like to get the relative rank of a single entry, ...
1
vote
0answers
23 views

optimize a window aggregate function

First, the query SELECT tc.numreg, com.numdep, tc.numcom, tc.numiris, sum(tc.conso_climat_reel)/sum(sum(tc.conso_climat_reel)) OVER (PARTITION BY tc.numreg) AS ratio, ...
1
vote
1answer
20 views

Postgresql: compare record against sibling, but limit search only to active records

I'm trying to find a way to compare records against it's sibling record while limiting the comparison only to the most recent entry for each set. I know it does not make to much sense so I'll go ...
1
vote
1answer
17 views

PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is. now i want to calculate the percentile ntile(100) ...
3
votes
1answer
16 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 ...
0
votes
3answers
45 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
0
votes
1answer
212 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 ...
15
votes
2answers
17k 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
153 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 ...
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
66 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
377 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
131 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
40 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
109 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

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
118 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
179 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
62 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
164 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
298 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
45 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
122 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
36 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
55 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 | ...
41
votes
5answers
3k 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
152 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
266 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
74 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
279 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
199 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
386 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
395 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
53 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
66 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
183 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
100 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
285 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
185 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
123 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, ...