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
1 vote
1 answer
46 views

SELECT / DELETE rows where consecutive null value count is greater than N

I have a table of the following structure, where all columns except the last are non-null: ID receiver send_time recv_time 1 A 00:00:00 00:00:01 2 A 00:00:01 NULL 3 A 00:00:02 NULL 4 A 00:00:03 ...
Edgxxar's user avatar
  • 13
0 votes
1 answer
64 views

Optimal way to get a total count of rows in a paged query in Postgres?

I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
jcollum's user avatar
  • 207
0 votes
1 answer
31 views

Performance of (functional) indices for datetimes and window functions in Postgres

I think this is a fairly standard type of task but I haven't seen anything covering performance in detail, though Index Optimization for Datetime comparing day of week and hour is pretty close. From ...
Charlie Clark's user avatar
0 votes
1 answer
40 views

How do I calculate a streak of data and show the data that corresponds to it on MySQL?

I'm currently working on a project with NBA stats and I want to check a player's longest streak of his highest amount of points on MySQL. I'm open to a 5-10 game streak to simplify it. For example: ...
thisisforfun's user avatar
-1 votes
1 answer
28 views

sql - how do I pick up all services that are open and latest of those ended - complicated

I am having issue capturing all rows that I need. A client can have number of services including same service again if that is an additional help, all services ended will have an end date and all open ...
Annie's user avatar
  • 1
0 votes
1 answer
86 views

ROW_NUMBER() with OUTER APPLY

I have a query that returns the lowest Price of a ProductVariation by using ROW_NUMBER() to enumerate the rows in groupings (partitions) of the ProductId, sorted by the Price ascending (MIN function ...
gregoryp's user avatar
  • 135
0 votes
1 answer
392 views

SQL (Postgres) Filling up missing rows with values from previous row and calculating on result set

I have got this dataset: id | block_number | value | name ----+--------------+-------+------ 2 | 47 | 100 | one 3 | 52 | 200 | one 4 | 58 | 120 | one 5 | ...
Westcoaster's user avatar
7 votes
1 answer
224 views

OUTPUT clause with window functions

Is there an undocumented restriction in the OUTPUT clause, or is this a bug? Given the following table: CREATE TABLE t1 (SomeId int, flag bit, value int); I would like to use a calculated value in an ...
Charlieface's user avatar
  • 11.5k
1 vote
2 answers
110 views

MySQL function returns wrong value on repeat, when it contains window functions

MySQL 8.0.33 I have a stored function to calculate the sum of all time gaps in a series of events on a given day. This function uses a window function LEAD(...) OVER() to determine the gap between the ...
Dmitriy's user avatar
  • 13
0 votes
0 answers
19 views

Generate the next records for a specific condition

I am using postgresql. The table details Create table weather_forecast ( date date, temperature decimal, avg_humidity decimal, avg_dewpoint decimal, avg_barometer decimal, avg_windspeed decimal, ...
Rishabh Mahajan's user avatar
2 votes
2 answers
281 views

How to use window function over calculated column?

Table definition: Table "public.transactions" ┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
Jean Louis's user avatar
0 votes
0 answers
57 views

Create a group number for rolling month

Starting from a table with dates in descending order, starting from the current_date: date ---------- 2023-02-03 2023-02-02 2023-02-01 2023-01-31 ... I want to add a column that contains a group ...
Bart Jonk's user avatar
  • 293
0 votes
0 answers
47 views

Create a column containing group size in postgres

Given the following table: create table t(x integer); insert into t(x) values (1), (1), (2), (2), (2); I'd like to create an additional column which contains the size of the groups, the expected ...
baxx's user avatar
  • 276
1 vote
1 answer
420 views

Why do PostgreSQL WINDOW functions affect the final ordering of all rows in the select?

Let's say we have the following query. select name, pos, rank() over (partition by constructor) r, format('%s / %s', row_number() over (partition by constructor), count(...
Anton Koval''s user avatar
0 votes
0 answers
24 views

Named Window vs Implicit Partition

These three queries seem to produce the same results: SELECT val, ROW_NUMBER() OVER w AS row_num FROM numbers WINDOW w AS (ORDER BY val); SELECT val, ROW_NUMBER() OVER () AS row_num FROM numbers ...
But those new buttons though..'s user avatar
0 votes
1 answer
45 views

Is there a way I can dynamically pivot and assign values?

I have a table candidates id candidate_name --------------------------- 1 john 2 mary a table units name -------- unit1 unit2 unit3 and a table achieved id cand_id ...
Rishav Ghosh's user avatar
2 votes
1 answer
1k views

Concatenate all previous row values from a TEXT column in reverse order

My Postgres version is 11.15. I have a table like: id employee 1 Anna 2 Bill 3 Carol 4 Dave 5 Tom The output I need is id cumulative_employees 1 Anna 2 Bill, Anna 3 Carol, Bill, Anna 4 ...
Ramakrishnan Srinivasan's user avatar
0 votes
1 answer
316 views

Summing/Grouping LAG() results in SQLite query

I have a table with incremental counters in an SQLite database. A stripped-down example would be: PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS "counters"(date TEXT ...
Tobias Leupold's user avatar
0 votes
1 answer
97 views

Optimizing query that finds closest unique records

I've been trying out different queries to get the best performance when querying hierarchical data on Postgres 13.6. Fiddle: https://dbfiddle.uk/060f_h96 Explain: https://explain.dalibo.com/plan/...
mreaglejr's user avatar
5 votes
3 answers
374 views

Can Postgres scan indexes backwards?

We use an Amazon RDS instance with PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit I have a simple classic top-1-per-group query. I need to ...
Vladimir Baranov's user avatar
2 votes
2 answers
176 views

How to order by cumulative value?

In my application widgets move step by step through a workflow. I want to list the widgets ordered by their step due-dates but I can't figure out how to do that. There are many workflows. Each ...
Andy Stewart's user avatar
2 votes
1 answer
627 views

Cumulative running SUM for date ranges with gaps

I am looking for assistance to get a running sum of the difference between two dates that are on separate rows. Table looks like: client_id name autopay_status contract_id start_date end_date 1 ...
michaelosmith's user avatar
4 votes
1 answer
933 views

Aggregate overlapping date intervals

I have a table with some accounts, and their subscription's start and end dates. However, these subscriptions overlap sometimes and I need the start and end dates of every connected subscription ...
9001_db's user avatar
  • 43
2 votes
1 answer
594 views

Cumulative sum in a period of months

I have this table: month_rep fruits harvested 2021-09-01 139 139 2021-10-01 143 11 2021-11-01 152 14 2021-12-01 112 9 2022-01-01 133 10 2022-02-01 145 12 2022-03-01 123 5 2022-04-01 111 4 ...
Jason's user avatar
  • 23
-2 votes
2 answers
118 views

How to give same number if it exist on table or new number if it not exist based on part value?

I work on sql server 2019 i have table #parts have group of numbers on Part Value Field . my issue how to give unique number to every group of numbers on Part Value Field if group numbers on part ...
ahmed barbary's user avatar
4 votes
1 answer
1k views

EXCLUDE CURRENT ROW cripples performance of window functions

I'm working with a customer's database where there are rows representing instances of Users, Activities (table named ActivityRecords), and ActivityUsers in correspondingly named tables, where ...
RVS's user avatar
  • 43
0 votes
1 answer
688 views

Find Consecutive Date in SQL using 2 date columns

I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per ...
Jane 's user avatar
1 vote
1 answer
596 views

Query with the window function FIRST_VALUE produces duplicates

Setup My tables are like this: CREATE TABLE challenges ( id uuid, user_id uuid, fe_challenge_id uuid, status_type varchar(50), created_at timestamp, updated_at timestamp ); This table ...
mrabro's user avatar
  • 113
0 votes
1 answer
90 views

The best approach for optimizing query in large database table

I've PostgreSQL database table named worker_activity following structure Column | Type | Collation | Nullable | ---------------+---------------...
Twissell's user avatar
2 votes
1 answer
952 views

Cumulative sum by day with non-negative floor with PostgreSQL

Using PostgreSQL 12, I need to calculate the cumulative sum by day while filling in missing days. I also need to set a minimum value of 0 if any cumulative sum goes negative. Background There are two ...
brianz's user avatar
  • 121
1 vote
1 answer
2k views

Rolling sum with a floor in Athena/Presto

In Athena, I want to calculate a rolling sum, over a window, that can't go below 0. For example, if summing up a column that has values (1, 2, -1, -2, -1, -2, 1, 2) I should get (1, 3, 2, 0, 0, 0, 1, ...
Max Gibiansky's user avatar
1 vote
1 answer
2k views

(Athena/Presto) Window function (lag) is not reading the previous lag row

I have two tables, credit_memos and credit_memo_items and want to show each event individually and will thus do a insert (doing union in this case which is logically the same). I know the key for both ...
Eugene Choi's user avatar
6 votes
1 answer
3k views

Performance penalty for COUNT(1) OVER (PARTITION BY NULL)

In my application server, I would like to paginate a dataset using LIMIT and OFFSET, and additionally return the total count of the dataset to the user. Instead of making two remote calls to the ...
Matthew Moisen's user avatar
1 vote
1 answer
582 views

Calculate running total of "consecutive days without views" for each group, that can restart multiple times within each group based on conditions

Summary I'm working to create a counter metric that shows, for each client's slug, the number of consecutive days of zero views. The counter should increase for each consecutive day that the client's ...
Matthew's user avatar
  • 21
2 votes
1 answer
633 views

Get rows grouped by a foreign key with count of consecutive values

I have a SQL Server database with a transactions table with client_id, date, and is_cancelled. I'm trying to get the client_ids that have 3 or more transactions in a row marked as is_cancelled, along ...
Healyhatman's user avatar
0 votes
1 answer
475 views

Postgresql: partition by number of rows after the current one

I need to calculate correlation for each say 3 consecutive rows. For example, assume there is a table with the following data. create table a ( asset_id int, time int not null, value ...
yaugenka's user avatar
  • 445
1 vote
2 answers
2k views

MySQL - Update column based on multiple conditions

I have a table which has Rows like these: +----------+--------------+---------------------+------+ | CASE_ID | CAT | BIRTH | TYPE | +----------+--------------+------------------...
Stefanie's user avatar
-3 votes
1 answer
60 views

Display Difference of Date (days) Between Last and Last-1 Issue Of Each KEY1

Table Definition CREATE TABLE TEST ( KEY1 VARCHAR NULL Dt DATE NULL, ISSUE INTEGER NULL, ); Example Data INSERT INTO TEST VALUES (100, '9/01/2010', 5), (100, '14/...
Arunava's user avatar
  • 11
1 vote
2 answers
358 views

Getting rank of row

I am using mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64). I am having a table referral: CREATE TABLE `referrals` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `referred_by` bigint(20) ...
Carol.Kar's user avatar
  • 135
0 votes
0 answers
825 views

Postgres providing default to LAG makes the colum NULL

I've got the query below: WITH CTE AS ( SELECT b.provider_id, b.customer_id customer_id, c.email email, c.created_at created_at, c.deleted_at deleted_at, ...
Burak Kaymakci's user avatar
0 votes
2 answers
27 views

Moving from MySQL 5.x to 8.x - fails due to column called "over"

Interesting and frustrating one, this. We have an old database that is still very actively used via multiple Excel files with VBA in, including archived projects with such files in. There's a bit of ...
Richard Gledhill's user avatar
3 votes
1 answer
135 views

How do I aggregate related rows when there's no group key?

I'm using MariaDB 10.6 and have a table: CREATE TABLE velocities ( `state` int(8) NOT NULL, `timestamp` datetime NOT NULL, `velocity` decimal(5,2) NOT NULL, `name` varchar(15) NOT NULL ) ...
samuel02's user avatar
  • 133
2 votes
2 answers
119 views

Group by x, get other fields too (comparing options and performance)

I'm a novice, trying to find the best way to sum up my data. Oracle 19c. I have a WORKORDER table that has work orders (ISTASK=0) and tasks (ISTASK=1). Tasks are children of work orders. Work orders ...
User1974's user avatar
  • 1,437
0 votes
1 answer
48 views

Can analytic functions add or remove rows from a resultset?

I'm a novice who is starting to learn about Oracle analytic functions (window functions). I know from experience that it's important to fully understand how SQL mechanisms work when using them in ...
User1974's user avatar
  • 1,437
0 votes
1 answer
1k views

Count of null rows preceding each non-null value

I need to answer the following question: What is the average number of email sends it take before a customer opens an email? I know I need to count the number of rows between each Open_Date in order ...
r2k2's user avatar
  • 3
0 votes
1 answer
42 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 -------- ...
jsf80238's user avatar
  • 103
1 vote
1 answer
764 views

Pick the first timestamp before a gap, but the last one of the day if there is no suitable gap

I have a TIMESTAMP column: dates 2021-06-24 05:47:05 2021-06-24 09:47:05 2021-06-24 13:47:05 2021-06-24 17:47:05 I want to pick the first timestamp of a given day that is 3 hours or more before the ...
a_dog_with_no_master's user avatar
3 votes
0 answers
996 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: ...
MattL922's user avatar
  • 131
-1 votes
2 answers
343 views

Meaning of "rows between 15 preceding and 15 following"

WITH trips_by_day AS ( SELECT DATE(trip_start_timestamp) AS trip_date, COUNT(*) as num_trips FROM `bigquery-...
tree em's user avatar
  • 119
2 votes
1 answer
1k views

Getting the row before a row with a certain value in SQL

I have a table like below where user actions are stored with a timestamp. My goal is to identify the action that happened before a specific action (named reference_action) and count the number of ...
Daniel Müller's user avatar

1
2 3 4 5
7