Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
53 views

Getting sum for each day based on most recent timestamp entry for each entity

I have a table that contains an ID, timestamp, and integer. Each row is a "snapshot" of the entity at a given time: TableA ID timestamp num A 2024-02-04 5 B 2024-02-04 3 C 2024-02-07 8 ...
Timothy Fisher's user avatar
0 votes
0 answers
32 views

MySQL 8 window functions optimization with multiple greatest-n-per-group

I'm struggling to make efficient this sort of query: with a as ( select -- the battery cycle cycles_bat_1, -- interested on a single result row per cycle, so using ...
Michele Mariotti's user avatar
3 votes
1 answer
24 views

Optimizing retrieving up to X rows in secondary table per parent row in a primary table

I'm looking to find the best general approach to retrieving up to a given number of records in a secondary table for each record previously retrieved in a primary table. Technical Situation For the ...
Corentin Demachy's user avatar
0 votes
1 answer
41 views

Distinct based on window function (sort curiosity or wrong problem approach)

A lot of values are stored in values. The data is in a hierarchical order, so there could be a probe, on which some measurements have been performed. Many points have been archived with strain and ...
lrahlff's user avatar
1 vote
1 answer
46 views

Identify if the sequence of visits to an area id was interrupted at any point

I have a PostgreSQL table df with the following information: area_id trip_nr timestamp a 3 29/06/2022 17:18:03 a 2 29/06/2022 17:18:02 a 1 29/06/2022 17:18:01 b 1 28/06/2022 19:21:03 c 3 28/06/...
Ruan's user avatar
  • 35
0 votes
0 answers
52 views

Forward fill Nulls with last known value in group and forecast amounts with limits

I'm a bit stuck trying to do forecasting using rolling avg windows functions across different groups. please see the example table below: I've planned to do this across a few steps e.g. Forward fill ...
Jabbasaurus's user avatar
0 votes
0 answers
41 views

SQL Query group by to find EffectiveFrom and EffectiveTo Dates for repeating key value pair

I have a table with a snapshot of repeated data for a key and value pair with record_timestamps. The dataset can also contain late-arriving historical record_timestamps as well. I want to generate an ...
user137279's user avatar
1 vote
1 answer
58 views

Add values and join rows on condition in a Mysql query

I have a table with time-in/time-out data in a structure as shown below. id date timein timeout duration 3 2024-01-02 01:00 02:00 01:00 3 2024-01-02 02:30 07:00 04:30 3 2024-01-02 09:00 09:10 00:...
Shahab Mohd's user avatar
0 votes
1 answer
38 views

Return the longest consecutive sequence of a value in specific column

I am using PostgreSQL 12.0 and trying to get the longest sequence of continuous rows for a specific column and value. The table is called team2 and contains a team's results which looks like: match_id ...
seevans38's user avatar
0 votes
0 answers
30 views

Postgresql update column with window function

Take a look on example: https://www.db-fiddle.com/f/wN8A7pfuGos9KfLokFS3uy/0 It is possible to update payment_line_tab and paid column in efficient way? I would like to set paid column to value: 0 - ...
Robert's user avatar
  • 141
2 votes
2 answers
357 views

Understanding the Impact of ORDER BY in Window Functions on other Partitions in PostgreSQL

I have a simple table odd_nums with columns num and odd: create table odd_nums ( num numeric, odd numeric ); INSERT INTO odd_nums VALUES (0, 0); INSERT INTO odd_nums VALUES (1, 1); INSERT ...
Leonard's user avatar
  • 23
1 vote
2 answers
108 views

Window functions and distinct

I have the following table. Column | Type | Collation | Nullable | Default ----------+-----------------------------+-----------+----------+--------- code | text ...
Zephyrus's user avatar
  • 301
1 vote
0 answers
23 views

Group each set of consecutive seats within a row_id in Snowflake

I want to group row_id and seat_number as follows so I can group by row_id and group_id (desired column) to pick the minimum seat number as start_seat and the maximum seat number as end_seat: row_id ...
kapashh's user avatar
  • 11
1 vote
1 answer
152 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
2k 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
88 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
256 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
29 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
265 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
1k 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
320 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
  • 13.2k
1 vote
2 answers
205 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
2 votes
2 answers
646 views

How to use window function over calculated column?

Table definition: Table "public.transactions" ┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
Jean Louis's user avatar
0 votes
0 answers
62 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
  • 315
1 vote
1 answer
453 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 ...
Eaten by a Grue's user avatar
0 votes
1 answer
48 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
2k 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
611 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
110 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
570 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
236 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
892 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
2 answers
2k 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
829 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
128 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
2k 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
1k 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
928 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
94 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
1k 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
3k 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
6k 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
777 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
884 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
605 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
  • 455
1 vote
2 answers
3k 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
566 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

1
2 3 4 5
7