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
2answers
70 views

MySQL - Update column based on multiple conditions

I have a table which has Rows like these: +----------+--------------+---------------------+------+ | CASE_ID | CAT | BIRTH | TYPE | +----------+--------------+------------------...
-3
votes
1answer
56 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/...
1
vote
2answers
35 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) ...
0
votes
0answers
33 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, ...
0
votes
2answers
19 views

Moving from MySQL 5.x to 8.x - fails due to column called "over"

Interestined 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
1answer
60 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 ) ...
2
votes
2answers
84 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 ...
0
votes
1answer
28 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 ...
0
votes
1answer
46 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 ...
0
votes
1answer
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 -------- ...
1
vote
1answer
146 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
0answers
242 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: ...
-1
votes
2answers
69 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-...
1
vote
1answer
39 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 ...
0
votes
2answers
1k 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."...
4
votes
4answers
913 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 ...
0
votes
0answers
15 views

SQL window function "select top item partitioned by x,y group by x,y"

I have a database of films. I'm trying to find the top rated film in each genre in each decade. I'm using sqlite3. I can do it with a subquery like so: with subset as ( select genre, decade, max(...
0
votes
1answer
25 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 ...
0
votes
2answers
32 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 ...
0
votes
1answer
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 ...
0
votes
1answer
36 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 |...
1
vote
2answers
73 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 ...
1
vote
2answers
80 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 ...
0
votes
0answers
35 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 ...
3
votes
1answer
250 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 ...
1
vote
2answers
48 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 ...
1
vote
2answers
147 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 ...
0
votes
1answer
590 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: ...
0
votes
1answer
24 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, ...
0
votes
1answer
35 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 ...
0
votes
1answer
41 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 ...
0
votes
1answer
614 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 * ...
1
vote
2answers
63 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, ...
1
vote
1answer
44 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 ...
2
votes
2answers
31 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 ...
0
votes
2answers
71 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 ...
1
vote
2answers
742 views

Postgres lag() function has huge impact on query

As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then ...
0
votes
1answer
261 views

How to select rows with a particular rank per partition?

I want to pick the row with the highest value in a column based on partitioning on another column. The problem is to pick the highest-earning employees from each department, click here for the ...
1
vote
0answers
44 views

Processing data in chunks with window function and where clause

I have a timeseries table where I need to process data in partitions of hours and processing of each individual row depends on the previous row. Please consider this simple table. CREATE TABLE IF NOT ...
-1
votes
1answer
154 views

How to get previous date record of the most recent date record in PSQL

Table: Contract_Price Columns: Customer, Part, Location, Currency, Price, Effective_date Case: Need to get the most recent Price of Part# by the effective date. (Which I am able to get it) but also ...
-1
votes
2answers
313 views

Query based on sum of previous n rows with condition

I have a table of football games with columns: id,match_date,team1_id,team2_id,score_team1,score_team2 I need a result showing: id match_date team1_id team2_id points for team1 (3 - for the win, 1 - ...
2
votes
2answers
323 views

Assign number incrementally, reset when value changes

declare @t table (scanCode varchar(6), dates datetime, flag varchar(1)) insert @t ( scanCode, dates, flag) select '182086','01 Jul 2020','P' union all select'182086','02 Jul 2020','P' union all ...
2
votes
1answer
110 views

Daily Value of Stock Portfolio Postgres

I am working on making a database that queries the daily balance of a stock portfolio. The balance would be calculated as (sharesA x priceA)+(sharesB x priceB), etc. for each day. I have a pricesclose ...
1
vote
2answers
49 views

Group by with a break on date

I am trying to group data on homogenous periods of time (not sure this is clear). Here is a simplified example of my data: CREATE TABLE workhours_over_time ( worker varchar, ...
2
votes
2answers
112 views

Group by time interval and output the source and destination station_id and count

I am stuck with a query: CREATE TABLE public.bulk_sample ( serial_number character varying(255), validation_date timestamp, -- timestamp of entry and exit station_id integer, ...
0
votes
1answer
106 views

last_value ignore null in left join with additional dimension?

This is just a silly example that I started to think about, but can't seem to come up with an elegant solution for. Assuming a Calendar table (I'll use Db2 syntax): create table calendar ( dt date not ...
3
votes
1answer
382 views

Window Function - SUM() OVER (PARTITION BY ... ORDER BY ...)

I have following dataset. green: original data. yellow: values that I want to calculate. red: dummy row that is manually added to set up starting point? PureAsk = MAX(Ask - PreviousBalance, 0) ...
0
votes
1answer
48 views

Aggregate rows for each group of rows

I am trying to solve a problem which is quite easy to solve in a procedural language, but I fail to solve it in SQL in an efficient manner. Let me first explain the problem. I have a series of events ...
-1
votes
3answers
78 views

How to aggregate in the WHERE clause referencing the subquery from the FROM clause

I got the following query so far: SELECT q1.ID, Content, Volume, CustomerID, runtot FROM ( SELECT ID, Content, Volume, CustomerID, SUM(Volume) OVER (PARTITION BY CustomerID ORDER BY ID) AS ...
0
votes
4answers
467 views

Difference between two rows in MySQL Workbench, but LAG is not authorized

I have a dataset as in this example : id | product_id | date | weight 1 | 454 |2019-06-26 16:08:45| 900 2 | 454 |2019-06-27 13:24:16| 900 3 | 454 |2019-...

1
2 3 4 5 6