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
0answers
55 views

Performing Rank for a set of data in SQL Server

the Executable create statement and the insert script create table dbo.tab2 ( sessionid varchar(2), paytableid varchar(50), themeid varchar(50) ) INSERT INTO dbo.tab2 ( sessionid, ...
0
votes
0answers
26 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
76 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
75 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
56 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
33 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
213 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
652 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
13 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
23 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
29 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
16 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
35 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
34 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
69 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
29 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
75 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 ...
0
votes
0answers
21 views

Convert multiple rows into different columns - SQL

I am using teradata for some data modification. My table current looks like: |Appl_id| Account_no| |123 |40001| |123 |40002| |125 |40233| |125 |40234| |125 |40235| |126 |40647| ...
1
vote
2answers
42 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
85 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
240 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
23 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
32 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
35 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
167 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
60 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
42 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
30 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
67 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
347 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
105 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
37 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
90 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
82 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
132 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
80 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
46 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
86 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
71 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
239 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
43 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
61 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
174 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-...
10
votes
1answer
567 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
0
votes
1answer
48 views

Adding a running count of distinct values in MySQL 5.7

I have a table that records which hardware was used for a given run (job / batch) on a tool. Vastly simplified, the table looks like: CREATE TABLE hardware ( `run_name` varchar(8) NOT NULL, `...
1
vote
1answer
41 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
1answer
39 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
3
votes
3answers
72 views

Select rows based on values from 1 column being the same?

An example of my database table film id | scene id | value id ----------------------------- 0 | 0 | 7 0 | 1 | 1 0 | 1 | 0 1 | 2 | 8 ...
0
votes
0answers
24 views

Using rank in postgresql

With this query in postgresql WITH ExtractedPosition AS ( SELECT P.rider_id, P.start_date, P.day_index, P.start_hour, P.end_hour, extract(...
2
votes
2answers
68 views

Conditional window function with a twist — include at most X preceding rows but exclude some

Asking for a friend who uses SQL Server Parallel Warehouse. They™ has a table of weekly sales amounts, like so (please forgive improper column justification): +-------+------------+ | week | ...

1
2 3 4 5 6