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.
343
questions
0
votes
1
answer
31
views
PostgreSQL window function: Add value in CASE running sum gets below threshold
For context:
I am playing with a filling level sensor, a water tank and a watering pump and have the following problem:
I have an initial filling value for my tank.
Each time step, I am subtracting ...
0
votes
1
answer
35
views
Finding id of last child in table structure
I have DB structure like this one: structure.
message_id from_user_id to_user_id subject body date_add parent_message_id
1 1 2 message 1 body 1 2024-...
- 141
11
votes
1
answer
840
views
Is "ROW_NUMBER() OVER(ORDER BY xml.node)" well defined?
(It is more a question of documentation rather than behavior. It has been migrated from Stack overflow as was suggested there.)
While researching the answer to another question that required ...
- 221
0
votes
1
answer
32
views
Sort by maximum value per partition [closed]
I have a table with data something like below:
| ID(pk) | name | count |
|--------|------|-------|
| id1 | xyz | 98 |
| id2 | ptr | 5 |
| id3 | xyz | 2 |
| id4 | de | 1 ...
- 103
1
vote
1
answer
78
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
...
- 177
0
votes
0
answers
44
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 ...
- 101
3
votes
1
answer
29
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 ...
0
votes
1
answer
51
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 ...
- 1
1
vote
1
answer
49
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/...
- 35
0
votes
0
answers
169
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 ...
0
votes
0
answers
54
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 ...
1
vote
1
answer
90
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:...
- 113
1
vote
1
answer
54
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 ...
- 13
0
votes
0
answers
41
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 - ...
- 141
2
votes
2
answers
591
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 ...
- 23
1
vote
2
answers
305
views
Window functions and distinct
I have the following table.
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
code | text ...
- 301
1
vote
0
answers
29
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 ...
- 11
1
vote
1
answer
224
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
...
- 13
1
vote
1
answer
4k
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 ...
- 217
0
votes
1
answer
117
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 ...
- 291
0
votes
1
answer
348
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: ...
-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 ...
- 1
0
votes
1
answer
378
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 ...
- 135
0
votes
1
answer
2k
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 | ...
7
votes
0
answers
410
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 ...
- 13.9k
1
vote
2
answers
249
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 ...
- 13
2
votes
2
answers
768
views
How to use window function over calculated column?
Table definition:
Table "public.transactions"
┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
- 155
0
votes
0
answers
66
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 ...
- 317
1
vote
1
answer
468
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(...
- 143
0
votes
0
answers
26
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
...
- 726
0
votes
1
answer
49
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 ...
- 183
3
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
...
0
votes
1
answer
738
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 ...
- 137
0
votes
1
answer
111
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/...
5
votes
3
answers
669
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 ...
- 4,670
2
votes
2
answers
264
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 ...
- 123
2
votes
1
answer
1k
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
...
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 ...
- 43
2
votes
1
answer
945
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
...
- 23
-2
votes
2
answers
140
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 ...
- 103
4
votes
1
answer
3k
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 ...
- 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 ...
- 1
2
votes
1
answer
1k
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 ...
- 123
0
votes
1
answer
95
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 |
---------------+---------------...
- 3
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 ...
- 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, ...
- 111
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 ...
- 11
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 ...
- 207
1
vote
1
answer
854
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 ...
- 21
2
votes
1
answer
966
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 ...
- 123