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.

Filter by
Sorted by
Tagged with
-3
votes
0answers
33 views

Correct SQL query to compute customer support response time [closed]

Consider the following table: TABLE TicketMessage COLUMN TicketId INT COLUMN Sender VARCHAR (either 'customer' or 'staff') COLUMN SentAt DATETIME The goal of the query is to compute the average ...
1
vote
1answer
41 views

Daily Value of Stock Portfolio Postgres

I am working on making a database that queries the daily balance of a stock portfolio. The balance would be calculated as (sharesA x priceA)+(sharesB x priceB), etc. for each day. I have a pricesclose ...
1
vote
2answers
39 views

Group by with a break on date

I am trying to group data on homogenous periods of time (not sure this is clear). Here is a simplified example of my data: CREATE TABLE workhours_over_time ( worker varchar, ...
3
votes
2answers
55 views

Group by time interval and output the source and destination station_id and count

I am stuck with a query CREATE TABLE public.bulk_sample ( serial_number character varying(255), validation_date timestamp without time zone, station_id integer, direction integer ); ...
0
votes
1answer
23 views

last_value ignore null in left join with additional dimension?

This is just a silly example that I started to think about, but can't seem to come up with an elegant solution for. Assuming a Calendar table (I'll use Db2 syntax): create table calendar ( dt date not ...
2
votes
1answer
67 views

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

i wish you all stay healthy and safe in this unprecedented circumstance. i am a newbie and asking my very first question in here. i have following dataset. green: original data. yellow: values that i ...
0
votes
1answer
35 views

Aggregate rows for each group of rows

I am trying to solve a problem which is quite easy to solve in a procedural language, but I fail to solve it in SQL in an efficient manner. Let me first explain the problem. I have a series of events ...
-1
votes
3answers
49 views

How to aggregate in the WHERE clause referencing the subquery from the FROM clause

I got the following query so far: SELECT q1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS ...
0
votes
4answers
56 views

Difference between two rows in MySQL Workbench, but LAG is not authorized

I have a dataset as in this example : id | product_id | date | weight 1 | 454 |2019-06-26 16:08:45| 900 2 | 454 |2019-06-27 13:24:16| 900 3 | 454 |2019-...
9
votes
1answer
367 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
0
votes
1answer
21 views

Adding a running count of distinct values in MySQL 5.7

I have a table that records which hardware was used for a given run (job / batch) on a tool. Vastly simplified, the table looks like: CREATE TABLE hardware ( `run_name` varchar(8) NOT NULL, `...
1
vote
1answer
27 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
1answer
21 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
2
votes
2answers
47 views

Select rows based on values from 1 column being the same?

An example of my database table film id | scene id | value id ----------------------------- 0 | 0 | 7 0 | 1 | 1 0 | 1 | 0 1 | 2 | 8 ...
0
votes
0answers
19 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(...
2
votes
2answers
49 views

Conditional window function with a twist — include at most X preceding rows but exclude some

Asking for a friend who uses SQL Server Parallel Warehouse. They™ has a table of weekly sales amounts, like so (please forgive improper column justification): +-------+------------+ | week | ...
1
vote
1answer
35 views

How do I optimize my transaction level running balances cartesian join?

This is a continuation of an question found here: Generate multiple running totals with GROUP BY day This is a continuation of a previous question, where table definition and sample data can be ...
1
vote
1answer
16 views

Return previous running total when value is null in a time series

This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there). All of this is being done on a PostgreSQL ...
1
vote
1answer
35 views

Generate multiple running totals with GROUP BY day

I have a set of transactions of stock purchases by users and I want to keep track of a running balance of each stock as the year progresses. I am using a windowing function to track the running ...
0
votes
1answer
41 views

Postgresql Recursion Query

I have a table like so: effective_from archived cal 2020-01-19 2020-01-20 15.3 2020-01-13 2020-01-19 42.2 2020-01-17 2020-01-18 13.6 2020-01-16 2020-01-17 11.2 ...
4
votes
1answer
42 views

Generate due dates using function in a select statement

I want to create a semi - monthly amortization schedule by using a function. Here is my function: CASE WHEN day(@dt) < 16 THEN CASE WHEN month(@dt) = 2 -- IF FEBRUARY, THEN GET LAST DAY OF ...
0
votes
2answers
29 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
27 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
18 views

How to get latest record of partition when Field1 & Field2 is not null or get latest record where one is null and 2nd latest where same field isn't?

Hard question to verbalize in the title with character constraint, sorry. I have Table1 with the fields PartitionField, ImportantField1, ImportantField2, DateField. ImportantField1 or ImportantField2 ...
0
votes
0answers
13 views

OLAP Windowing function

I connected a Postgres database to Tableau and now I'm performing OLAP queries on it. I'm currently trying to perform a Windowing query (https://www.postgresql.org/docs/9.1/tutorial-window.html). ...
1
vote
1answer
39 views

How to get next/previous partition count using window function?

I'm trying to get the current_count, next_count, and previous_count for date.month partitions with window functions: WITH tmp1 AS ( SELECT location.city, date.month, date.year , COUNT(*) OVER W ...
0
votes
0answers
28 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 ...
1
vote
1answer
54 views

Cumulative Sum over 12 months by Postgreql 10

I am new to window functioning in postgresql. I have 10 columns in my data set and i want to do the running total based on the MM-YYYY but keep fail to get the correct amount. I tried to manual ...
1
vote
1answer
33 views

Is it expected behavior that MIN() window function in MariaDB/MySQL can return a non-null value from a window with only null values?

A minimal example for MariaDB (reproduced with with 10.2 and latest): DROP TABLE IF EXISTS t; CREATE TABLE t ( id int unsigned not null, value int unsigned null ); INSERT INTO t values (1, ...
1
vote
3answers
79 views

Carry forward non-null value with a windowing function? [duplicate]

In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have: name ...
0
votes
1answer
46 views

Compare running / cumulative total with static target

I have some daily data and monthly data. I want to compare these two datasets, with Hive and perform a payout when the sum of actual for a month passes that respective months target. In the above ...
0
votes
0answers
40 views

How to compress a query to get distinct COUNT and SUM aggregate information on joined rows with sub-queries in Microsoft SQL

I have a query that counts some incidents that happen based on joining some groups of companies and regions. SELECT RC.RegionId, RC.CompanyId, CC.Name, Quantity = SUM(CASE WHEN II....
1
vote
2answers
143 views

DB2 window function - running total of X for preceding N minutes worth of rows

Very new to window functions but think I get the idea. I'm trying to get a rolling sum of one column in 10 minute intervals (determined from a separate timestamp column) A working sample might would ...
0
votes
1answer
21 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 := (...
1
vote
1answer
67 views

Understanding aggregate window functions

Consider the following table: CREATE TABLE T1 ( keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY, col1 VARCHAR(10) NOT NULL ); INSERT INTO T1 VALUES (2, 'A'),(3, 'A'), (5, 'B'),(7, ...
0
votes
2answers
330 views

Postgres Slow ROW_NUMBER() function

I have created the following table in postgres. test=# \d leaderboard_scores; Table "public.leaderboard_scores" Column | Type | ...
1
vote
1answer
29 views

Need to create batches of 2000 records within a subset of records

I have a table with 200,000 records. One of the columns is a location ID. I need to break down the entire table in batches of 2000 but group them by the LocationID column. I have about 200 locations ...
1
vote
1answer
30 views

Get durations from time series table where some condition holds consecutively

Given a table where each row has a timestamp and some measurement, how can I get all of the intervals during which the measurement consecutively satisfies some condition? Here's a SQLfiddle (not sure ...
0
votes
0answers
44 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, ...
1
vote
1answer
27 views

Select rows within group before condition is met

I want to select rows within each group following a condition in time. I have the following table trajectory_data in PostgreSQL 10 with daily points for each group. | traj_id | timestamp | height |...
0
votes
1answer
48 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 ...
3
votes
2answers
37 views

Rolling sum aggregates with self-referencing conditions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
3
votes
3answers
254 views

Reducing table scans using group by and window functions

There is a code that I'm trying to improve that looks like this(simple example): SELECT DISTINCT a.col_a ,COALESCE(b1.col_c, b2.col_c, b3.col_c) FROM tab_a a LEFT JOIN tab_b b1 ...
0
votes
0answers
20 views

Calculating order rank after union

I'm trying to run a query such as this: select id, rank() over(order by last_name, age, id) as position from ( select id, first_name, last_name, age from public.user union select '925c26bf-5971-...
2
votes
2answers
84 views

Count Total time difference between selected row

My data contains an ID, status and time(hh:mm:ss). I need to calculate the total time between every 'Location' and 'Out of tunnel' status Current table ID || time || Status ...
1
vote
1answer
177 views

Count Over returns Row_Number based on Order By

Quick question based on some interesting syntax I've found out. I have a table with these columns: ID INT, DimBuyDetails_Id INT, WeekOfBuy INT, Spots INT, Retired BIT If I run this query I will get ...
1
vote
1answer
34 views

Generate hierarchical path

I have a classstructure table: create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0)); insert into classstructure(classstructureid, ...
0
votes
1answer
69 views

How to use the COUNT function to count up to the current row for each row?

I had trouble phrasing the question, but I think the following example will clear things up. Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in ...
1
vote
1answer
104 views

By using SQL Window Functions, how can I get the lead of the last row in a partition?

Considering the following simple example: select lead(c) over (partition by a order by a) from (values (1,'1','date1'), (1,'2','date1'), (2,null,'date2') ) t(a,b,c) The first two ...
5
votes
3answers
337 views

A more intelligent ntile

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value. For example with the following query: select id,...

1
2 3 4 5 6