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.

35 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
75 views

Postgres - limit window function evaluation to once per partition

I have a table of daily stock quotes with ~40M rows and I'm trying to run a query that spits out the most recent date and the number of stocks that made a new one year high on that date. For example: ...
3
votes
1answer
239 views

Window Function - SUM() OVER (PARTITION BY … ORDER BY …)

I have following dataset. green: original data. yellow: values that I want to calculate. red: dummy row that is manually added to set up starting point? PureAsk = MAX(Ask - PreviousBalance, 0) ...
2
votes
0answers
76 views

Efficient way to create daily summary table

I'm trying to create a daily summary table that captures characteristics of each client known as of every given date that my company has been in business. The challenge is that there are so many ...
2
votes
1answer
467 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
0answers
384 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 ...
1
vote
0answers
53 views

Performing Rank for a set of data in SQL Server

the Executable create statement and the insert script create table dbo.tab2 ( sessionid varchar(2), paytableid varchar(50), themeid varchar(50) ) INSERT INTO dbo.tab2 ( sessionid, ...
1
vote
0answers
37 views

Processing data in chunks with window function and where clause

I have a timeseries table where I need to process data in partitions of hours and processing of each individual row depends on the previous row. Please consider this simple table. CREATE TABLE IF NOT ...
1
vote
1answer
103 views

Use expression in window function that references columns from the current row

Suppose I have the following query that uses window function: SELECT id , var , num , SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc FROM (VALUES ...
1
vote
0answers
38 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...
1
vote
1answer
571 views

How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows: 42, 1 ...
1
vote
1answer
189 views

How to force Redshift/Postgres to aggregate nth_value?

I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this: select customer_email, nth_value(created_at, 1) over (partition by ...
1
vote
0answers
243 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 | ...
1
vote
0answers
77 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, tc....
1
vote
0answers
33 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 ...
1
vote
0answers
857 views

How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: Consecutive count for last 5 months: Last 3 month Count Last 6 month Count I have to find Consecutive count for last 5 months for a ...
0
votes
0answers
26 views

Calculate a cumulative amount with a given time

I want to identify customers who have spent more than D dollars over a period of P hours. Suppose D=$10 and P=48. Then in the transaction data below: CUSTOMER STAMP AMOUNT -------- ...
0
votes
0answers
13 views

SQL window function “select top item partitioned by x,y group by x,y”

I have a database of films. I'm trying to find the top rated film in each genre in each decade. I'm using sqlite3. I can do it with a subquery like so: with subset as ( select genre, decade, max(...
0
votes
2answers
29 views

Concatenate string based on a condition

I want to assign a value to a new column based on a condition for other rows associated with a user_id. A user can see the same step with different devices at different times. However, only the first ...
0
votes
1answer
16 views

Select N preceding rows when column match is true

I am attempting to select 10 rows preceding any row where the keyPerformanceIndicator column is TRUE (order by descending date/time). I suspect this is somehow achieved through a window function in ...
0
votes
0answers
29 views

How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor

Please refer to the following schema at SQL Fiddle when reading the use case below. In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
0
votes
0answers
21 views

Convert multiple rows into different columns - SQL

I am using teradata for some data modification. My table current looks like: |Appl_id| Account_no| |123 |40001| |123 |40002| |125 |40233| |125 |40234| |125 |40235| |126 |40647| ...
0
votes
1answer
105 views

How to select rows with a particular rank per partition?

I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the ...
0
votes
0answers
24 views

Using rank in postgresql

With this query in postgresql WITH ExtractedPosition AS ( SELECT P.rider_id, P.start_date, P.day_index, P.start_hour, P.end_hour, extract(...
0
votes
2answers
30 views

Update value in same view using CASE expression

I am creating a view where i have daily data of different items and each item has a state. I created a column that shows what the column was yesterday using LAG(state) OVER(PARTITION BY item_id ORDER ...
0
votes
1answer
49 views

postgresql cumulative counts in date range

I'm trying to get the cumulative count of rows (by group_id) between two dates that represent a time period where the row was active. I have a table like this: group_id | id | type | start_date ...
0
votes
0answers
64 views

Postgres Notify via trigger function that SELECTs the result of window function using new row?

I have a query that runs a select that operates on a range of rows to find and return the max, min, etc., e.g.: SELECT time, device_id, MAX(high) OVER(rows between 4 preceding and ...
0
votes
1answer
22 views

SELECT statement not displaying data correctly when using variable and reusing it

I have a mysql query which stores the result in variable, but when I use the same variable later, It doesnt return the same value. Am I missing something? SELECT duration, @sum_duration := (...
0
votes
0answers
148 views

What is the equivalent of OVER (PARTITION BY Account ORDER BY TranDate ROWS UNBOUNDED PRECEDING) for Firebird v2.5?

I need an equivalent way to getting OVER (PARTITION BY Account ORDER BY TranDate ROWS UNBOUNDED PRECEDING) for Firebird v2.5 Here is the full code: DECLARE @Test TABLE ( Account INTEGER, ...
0
votes
0answers
47 views

Dividend reinvestment into shares query

I'm gonna get straight to the problem: Image dividend reinvestment as this scenario: You buy 10 shares of AAPL on 2018-01-01 for $10 -- AAPL pays $1 / per share dividend on 2018-02-01 -> you get $10 ...
0
votes
1answer
1k views

Having issue with window function COUNT counting duplicates

I have a query like so: select distinct(line_items.email) as email, count(orders.order_id) over(partition by orders.order_id) as num_orders from line_items inner join orders on ...
0
votes
0answers
102 views

Rank users by different periods and overall

I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the ...
0
votes
2answers
308 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
0
votes
1answer
26 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
0answers
83 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 ...
-1
votes
1answer
90 views

How to get previous date record of the most recent date record in PSQL

Table: Contract_Price Columns: Customer, Part, Location, Currency, Price, Effective_date Case: Need to get the most recent Price of Part# by the effective date. (Which I am able to get it) but also ...