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.
328
questions
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
...
- 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 ...
- 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 ...
- 241
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: ...
-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 ...
- 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 ...
- 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 | ...
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 ...
- 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 ...
- 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,
...
2
votes
2
answers
281
views
How to use window function over calculated column?
Table definition:
Table "public.transactions"
┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
- 155
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 ...
- 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 ...
- 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(...
- 143
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
...
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 ...
- 171
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
...
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 ...
- 127
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/...
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 ...
- 4,577
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 ...
- 123
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
...
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 ...
- 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
...
- 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 ...
- 103
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 ...
- 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 ...
- 1
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 ...
- 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 |
---------------+---------------...
- 3
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 ...
- 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
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 ...
- 11
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 ...
- 187
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 ...
- 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 ...
- 123
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 ...
- 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 |
+----------+--------------+------------------...
- 15
-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/...
- 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) ...
- 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,
...
- 101
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 ...
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
) ...
- 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 ...
- 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 ...
- 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 ...
- 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
-------- ...
- 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 ...
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:
...
- 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-...
- 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 ...
- 123