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.
332
questions
0
votes
0
answers
21
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
145
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
42
views
Window functions and distinct
I have the following table.
Column | Type | Collation | Nullable | Default
----------+-----------------------------+-----------+----------+---------
code | text ...
- 293
1
vote
0
answers
14
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
71
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
311
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
47
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 ...
- 251
0
votes
1
answer
105
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
118
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
722
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
249
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.7k
1
vote
2
answers
135
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
402
views
How to use window function over calculated column?
Table definition:
Table "public.transactions"
┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
- 155
0
votes
0
answers
59
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
49
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 ...
- 296
1
vote
1
answer
435
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
46
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
409
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
105
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
444
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
190
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
699
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
1k
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
661
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
124
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
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 ...
- 43
0
votes
1
answer
809
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
685
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
92
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
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
4k
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 ...
- 197
1
vote
1
answer
637
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
716
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
527
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
3k
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
410
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
877
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
139
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,447
0
votes
1
answer
50
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,447
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