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
0 votes
0 answers
5 views

EXCLUDE CURRENT ROW causes exponential increases in query time (Postgres)

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 ...
user avatar
  • 1
0 votes
1 answer
26 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 ...
user avatar
-1 votes
0 answers
14 views

Selecting rows based on the value of the previous or next row

Say I have this table in SQLite: +----+---------+ | id | status | +====+=========+ | 1 | FAILURE | +----+---------+ | 2 | FAILURE | +----+---------+ | 3 | SUCCESS | +----+---------+ | 4 | FAILURE ...
user avatar
  • 109
1 vote
1 answer
63 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 ...
user avatar
  • 111
0 votes
0 answers
20 views

ML feature table for transactions in last 7 days

I have a database with the following schema : CREATE TABLE transactions ( transaction_id UUID, date DATE, user_id UUID, is_blocked BOOL, transaction_amount INTEGER, transaction_category_id INTEGER ); ...
user avatar
  • 1
0 votes
0 answers
30 views

Group datetimes by "session" logic (gap between events) [duplicate]

There are a couple similar questions out there -- this is NOT about grouping things by 30 minute intervals or similar. This question is similar to Google Analytics logic. A "session" is ...
user avatar
  • 1,493
0 votes
1 answer
76 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 | ---------------+---------------...
user avatar
0 votes
1 answer
19 views

Calculate difference based on timestamp

in my PostgreSQL DB I have got table like that: calendar_tab calendar_id - int user_id - int type - varchar(16) start_date - timestamp end_date - timestamp Users add different events to this table. ...
user avatar
  • 101
1 vote
1 answer
95 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 ...
user avatar
  • 111
0 votes
1 answer
94 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, ...
user avatar
1 vote
1 answer
178 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 ...
user avatar
3 votes
1 answer
173 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 ...
user avatar
1 vote
1 answer
39 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 ...
user avatar
  • 21
2 votes
1 answer
77 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 ...
user avatar
0 votes
1 answer
46 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 ...
user avatar
  • 395
1 vote
2 answers
367 views

MySQL - Update column based on multiple conditions

I have a table which has Rows like these: +----------+--------------+---------------------+------+ | CASE_ID | CAT | BIRTH | TYPE | +----------+--------------+------------------...
user avatar
-3 votes
1 answer
58 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/...
user avatar
  • 11
1 vote
2 answers
59 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) ...
user avatar
  • 135
0 votes
0 answers
137 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, ...
user avatar
0 votes
2 answers
26 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 ...
user avatar
3 votes
1 answer
78 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 ) ...
user avatar
  • 133
2 votes
2 answers
96 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 ...
user avatar
  • 1,387
0 votes
1 answer
33 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 ...
user avatar
  • 1,387
0 votes
1 answer
102 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 ...
user avatar
  • 3
0 votes
1 answer
32 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 -------- ...
user avatar
  • 103
1 vote
1 answer
265 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 ...
user avatar
3 votes
0 answers
413 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: ...
user avatar
  • 131
-1 votes
2 answers
93 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-...
user avatar
  • 119
1 vote
1 answer
45 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 ...
user avatar
0 votes
2 answers
2k views

Postgres SUM over PARTITION BY "tolerate" null values

I have a view which calculates rolling sum taking in value form previous row based on PARTITION BY clause, and I need to indicate one field in PARTITION BY clause can have nulls in them. sum(s."...
user avatar
  • 103
4 votes
4 answers
1k views

Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
user avatar
  • 1,302
0 votes
1 answer
34 views

Order by starting from "the middle" in a windowing function

What I have is a table like this, where I need to do a running total of the values column for the upcoming months. The issue is that I would like to start from month 4 (shown in column 3) and go all ...
user avatar
  • 249
0 votes
2 answers
52 views

Concatenate string based on a condition

I want to assign a value to a new column based on a condition for other rows associated with a user_id. A user can see the same step with different devices at different times. However, only the first ...
user avatar
  • 101
0 votes
1 answer
18 views

Select N preceding rows when column match is true

I am attempting to select 10 rows preceding any row where the keyPerformanceIndicator column is TRUE (order by descending date/time). I suspect this is somehow achieved through a window function in ...
user avatar
  • 13
0 votes
1 answer
40 views

Aggregating rows from a condition in a linking table in SQL

I am having trouble wrapping my head around a query that gives me these results I have a table that gives me Devices uses per day. dbo.DailyUses Uses | Date | Device 3 | 2020-3-1 | A2 5 |...
user avatar
1 vote
2 answers
144 views

Calculating a cumulative sum with an alias in postgresql

I have two tables - user credit and payments. credit is just a total of the amount of credit a user has at the present. Payments is a list of payments by date that a user needs to make. I need to ...
user avatar
1 vote
2 answers
89 views

Time based sampling

Is there any way to do sampling on time series data in PostgreSQL (v11) based on a fixed time interval? For example, we are storing 1 record per second for 24 hours in a given table. This means ...
user avatar
0 votes
0 answers
36 views

How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor

Please refer to the following schema at SQL Fiddle when reading the use case below. In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
user avatar
  • 1,913
3 votes
1 answer
378 views

Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3. CREATE TABLE test ( `id` bigint NOT NULL AUTO_INCREMENT, created_date ...
user avatar
  • 239
1 vote
2 answers
109 views

Time-series window query for calculating rolling average across group by subquery is inaccurate due to missing rows

I'm trying to generate a rolling average based on a generated time series joined with a subquery on each day of the time series. However, the subquery occasionally does not have a result for a given ...
user avatar
  • 113
1 vote
2 answers
216 views

"SELECT LAG(id) FROM cb_cat WHERE id=450";

I want to return the previous row based on the current id number. All id numbers are UNIQUE, but are NOT (necessarily) SEQUENTIAL. SELECT LAG(id) FROM cb_cat WHERE id=450; I thought this query should ...
user avatar
  • 113
0 votes
1 answer
972 views

How to use FIRST_VALUE window function in MySQL/MariaDB to get one row per group?

I'm pleased to see window functions landed in MariaDB 10.2. I thought they would be good for first-of-group problems, but I'm struggling to see how they're efficient. I have something like this: ...
user avatar
0 votes
1 answer
29 views

Using activity over last few days to label user as "active"

Here's an example dataset. with activity_cte (day, user_id, act1, act2) as ( values ('2020-01-01'::date, 1, 0, 1), ('2020-01-01'::date, 3, 1, 0), ('2020-01-02'::date, 1, 3, ...
user avatar
  • 115
0 votes
1 answer
37 views

PL-SQL "LAST_VALUE" function does not give the desired result

I have a table with below structure: create table DOC_AMOUNT_DETAIL ( col_id NUMBER, amount NUMBER, operation_date DATE ) and some example data for this table is : col_id ...
user avatar
  • 1,302
0 votes
1 answer
44 views

Result set with multiple rows based on max date and not NULL

Staff changed their profiles as shown in this table: The result of latest information should be like this: I applied the technique from this question for embedded Firebird of LibreOffice Base, the ...
user avatar
  • 5
0 votes
1 answer
1k views

How to get the last non-null value that came before the current row

Say I have the following table: create table tab (ts integer, a integer); insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3); When i query the data there may be some nulls: select * ...
user avatar
  • 125
1 vote
2 answers
69 views

Find the greatest difference between each unique record with different timestamps

I have a PostgreSQL table with the following schema and data: CREATE TABLE IF NOT EXISTS T( id uuid PRIMARY KEY, username varchar(15), person varchar(10), tweets int, ...
user avatar
1 vote
1 answer
52 views

Is the order of records in a VALUES list well-defined (postgres)?

Postgres 9.4 introduced the WITH ORDINALITY clause which adds an ordinality column to a table-like expression, somewhat similar to ROW_NUMBER() OVER (). Apparently, this is used to provide ranks for ...
user avatar
2 votes
2 answers
34 views

How to filters data with two windows conditions?

I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I ...
user avatar
  • 567
0 votes
2 answers
81 views

How can I improve a slow comparison query that have over partition and group by

I have a query (see below) that is working just fine, when there aren't that many rows, around 5k. However, if I run the same query on a table with around 80k rows the query takes between 30 and 60 ...
user avatar
  • 199

1
2 3 4 5
7