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
45 views

DB2 window function - running total of X for preceding N minutes worth of rows

Very new to window functions but think I get the idea. I'm trying to get a rolling sum of one column in 10 minute intervals (determined from a separate timestamp column) A working sample might ...
0
votes
1answer
20 views

SELECT statement not displaying data correctly when using variable and reusing it

I have a mysql query which stores the result in variable, but when I use the same variable later, It doesnt return the same value. Am I missing something? SELECT duration, @sum_duration := (...
1
vote
1answer
60 views

Understanding aggregate window functions

Consider the following table: CREATE TABLE T1 ( keycol INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY, col1 VARCHAR(10) NOT NULL ); INSERT INTO T1 VALUES (2, 'A'),(3, 'A'), (5, 'B'),(7, ...
0
votes
2answers
50 views

Postgres Slow ROW_NUMBER() function

I have created the following table in postgres. test=# \d leaderboard_scores; Table "public.leaderboard_scores" Column | Type | ...
1
vote
1answer
28 views

Need to create batches of 2000 records within a subset of records

I have a table with 200,000 records. One of the columns is a location ID. I need to break down the entire table in batches of 2000 but group them by the LocationID column. I have about 200 locations ...
1
vote
1answer
29 views

Get durations from time series table where some condition holds consecutively

Given a table where each row has a timestamp and some measurement, how can I get all of the intervals during which the measurement consecutively satisfies some condition? Here's a SQLfiddle (not sure ...
0
votes
0answers
21 views

What is the equivalent of OVER (PARTITION BY Account ORDER BY TranDate ROWS UNBOUNDED PRECEDING) for Firebird v2.5?

I need an equivalent way to getting OVER (PARTITION BY Account ORDER BY TranDate ROWS UNBOUNDED PRECEDING) for Firebird v2.5 Here is the full code: DECLARE @Test TABLE ( Account INTEGER, ...
1
vote
1answer
22 views

Select rows within group before condition is met

I want to select rows within each group following a condition in time. I have the following table trajectory_data in PostgreSQL 10 with daily points for each group. | traj_id | timestamp | height |...
0
votes
1answer
39 views

Use expression in window function that references columns from the current row

Suppose I have the following query that uses window function: SELECT id , var , num , SUM(var * num) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS calc FROM (VALUES ...
3
votes
2answers
33 views

Rolling sum aggregates with self-referencing conditions

I have what I believe is a use-case for a recursive CTE, but I've yet to figure out how to structure it. The requirement is to create a rolling sum total over an amortization schedule, but payments ...
3
votes
3answers
243 views

Reducing table scans using group by and window functions

There is a code that I'm trying to improve that looks like this(simple example): SELECT DISTINCT a.col_a ,COALESCE(b1.col_c, b2.col_c, b3.col_c) FROM tab_a a LEFT JOIN tab_b b1 ...
0
votes
0answers
20 views

Calculating order rank after union

I'm trying to run a query such as this: select id, rank() over(order by last_name, age, id) as position from ( select id, first_name, last_name, age from public.user union select '925c26bf-5971-...
2
votes
2answers
80 views

Count Total time difference between selected row

My data contains an ID, status and time(hh:mm:ss). I need to calculate the total time between every 'Location' and 'Out of tunnel' status Current table ID || time || Status ...
1
vote
1answer
31 views

Count Over returns Row_Number based on Order By

Quick question based on some interesting syntax I've found out. I have a table with these columns: ID INT, DimBuyDetails_Id INT, WeekOfBuy INT, Spots INT, Retired BIT If I run this query I will get ...
1
vote
1answer
28 views

Generate hierarchical path

I have a classstructure table: create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0)); insert into classstructure(classstructureid, ...
0
votes
1answer
63 views

How to use the COUNT function to count up to the current row for each row?

I had trouble phrasing the question, but I think the following example will clear things up. Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in ...
1
vote
1answer
36 views

By using SQL Window Functions, how can I get the lead of the last row in a partition?

Considering the following simple example: select lead(c) over (partition by a order by a) from (values (1,'1','date1'), (1,'2','date1'), (2,null,'date2') ) t(a,b,c) The first two ...
5
votes
3answers
256 views

A more intelligent ntile

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value. For example with the following query: select id,...
0
votes
0answers
24 views

How to implement row count for REST API?

I’m building a REST API backed by PostgreSQL. I'm encountering something I don’t think is very niche, but I’m having trouble finding any discussion of it. Good REST API practice dictates that if a ...
-1
votes
1answer
21 views

query with LISTAGG() string function does not give the desired output

I'm new to ORACLE and I'm trying to use a string aggregation function in my query to concatenate the value of rows with a comma , this is the structure of my tables: Student(STUDENT_ID,student_name,...
4
votes
1answer
764 views

How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?

It appears I have a case I can't quite wrap my brains around. So coming here in hopes to find pointers to a query that maybe could be helpful to someone else too. In the following, I have a query ...
2
votes
1answer
86 views

Backlog Calculation

I need to create a report that calculates the accumulated ticket backlog, but I can not get the correct result. I already tested several solutions that I found on the internet, but I'm stagnant. -...
10
votes
5answers
3k views

SQL counting distinct over partition

I have a table with two columns, I want to count the distinct values on Col_B over (conditioned by) Col_A. MyTable Col_A | Col_B A | 1 A | 1 A | 2 A | 2 A | 2 A | 3 b | ...
5
votes
1answer
152 views

Window Functions: What is the purpose of Rows Unbounded Preceeding?

What is the purpose of using the Rows Unbounded Preceeding clause in a window function? I think I understand that it's basically saying not to limit how far back to look when rolling up for the ...
1
vote
1answer
73 views

Using Window Function instead of Select sub-query for counting

I have to count the number of Orders a table, per Employee, for two criteria - "Late Orders". "Total Orders" The query includes Orders table and Orders table. They are joined together via the ...
0
votes
3answers
80 views

Window Function to get pairs of records and compute the time difference

I'm working with an event dataset as the one in the example below: Event_Type Event_Timestamp Is_Active A 2010-10-01 00:00:00 1 B 2010-10-01 00:00:01 1 A ...
3
votes
1answer
46 views

Conditional Select from Window Function

I'm using lead() to calculate the difference in values between two records in the same table. This works fine until I need to not report on one of the records based on some other value in the window. ...
1
vote
1answer
469 views

using lag() on multiple columns (gaps and islands issue)

I have a problem I am trying to solve where I need to eliminate duplicate consecutive rows in a table using the first 7 columns as the basis to determine if it is a duplicate. The data looks like: ...
12
votes
3answers
3k views

How to get the last not-null value in an ordered column of a huge table?

I have the following input: id | value ----+------- 1 | 136 2 | NULL 3 | 650 4 | NULL 5 | NULL 6 | NULL 7 | 954 8 | NULL 9 | 104 10 | NULL I expect the following ...
2
votes
2answers
31 views

Calculate change in value window function when the identifier has dropped of the current period

I was wondering how can i got about finding the change in value from one period to another but in my scenario the identifier has dropped off the current period. I'm think this could be done with a ...
0
votes
1answer
50 views

Applying Multiple Window Functions in 1 SELECT using different partition

I was wondering if there is anything wrong with using this method. I have a working demo of what i'm trying to do below In the SELECT statement I have used 2 OVER clauses. Is there any risk that ...
0
votes
0answers
34 views

Dividend reinvestment into shares query

I'm gonna get straight to the problem: Image dividend reinvestment as this scenario: You buy 10 shares of AAPL on 2018-01-01 for $10 -- AAPL pays $1 / per share dividend on 2018-02-01 -> you get $10 ...
0
votes
1answer
416 views

Having issue with window function COUNT counting duplicates

I have a query like so: select distinct(line_items.email) as email, count(orders.order_id) over(partition by orders.order_id) as num_orders from line_items inner join orders on ...
0
votes
1answer
24 views

How can I write a window function using something like row_number to increment only on discrete values?

Say I want to do something like: select my_date, row_number() over(partition by my_date order by my_date asc) from my_table I want the row numbers to come out as: 2018-10-01,1 2018-...
7
votes
2answers
897 views

Explain an OVER clause

I saw a concise TSQL statement that effectively splits a string into its constituent characters, one per line, for the purpose of evaluating the ascii value on each character. If I am reading the ...
0
votes
1answer
48 views

Is there a way to rewrite this MySQL query to use window functions, and not use session variables?

I have the following query drop table if exists tradesAndActions; create table tradesAndActions (transik int, ca_date date, terms float, multiply bit, originalPrice float, originalShares float); ...
0
votes
0answers
80 views

Rank users by different periods and overall

I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the ...
2
votes
2answers
70 views

How to select blocks with sequential data and aggregate the ids

I have the following table: id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int) --------------------------------------------------------------...
0
votes
2answers
83 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
3
votes
1answer
80 views

Reuse previous value in window function

Is it possible to use LAG() to get the previous value of the current column? The computation I need to do is the following: value(n) = (value(n-1) + 1) * column(n) meaning I already have a column, ...
0
votes
1answer
2k views

Window function IGNORE NULLS workaround for PostgreSQL

With the following query I can use the LAG() function to repeat the last non null value of c column: SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000) ...
2
votes
1answer
156 views

SQL Server 2008 Version of rows between… preceding and current row

Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008 As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + ...
1
vote
1answer
141 views

MySQL Computed Column counting other rows with attributes relative to this one

I have a table in a MySQL database that has information about passenger flights. I want to do some analysis of flight delays. I would like to add a calculated attribute to each row that indicates how ...
1
vote
2answers
174 views

Strip longest common suffix and concat in Postgres

Given a table t: id | name ------------ 1 | abcfug 1 | deffug 1 | hijfug 2 | etc How can I do something like: select string_agg(strip_lcs(name), ', ') from t where id = 1 returning: abc, def, ...
1
vote
1answer
326 views

How can I use LEAD LAG-like functions on multiple columns simultaneously

Suppose I have the following table: start_data | end_date ---------------------- t1 | Null Null | t2 t3 | Null Null | t4 t5 | Null The output should be like ...
1
vote
0answers
35 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...
1
vote
1answer
238 views

How to use windowing functions to find gaps in sequences grouped by a column, in PostgreSQL

I have a table structure which has 2 columns: client_id, order_no, and both are integers. I'd like to find gaps in order_no in the data so that, for example, for a table containing these rows: 42, 1 ...
7
votes
4answers
2k views

Adding a ROW_NUMBER() with no column to ORDER BY?

So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order. Let's say my source data is: SELECT value FROM STRING_SPLIT('one,two,...
5
votes
1answer
258 views

If at least a negative number, sum; otherwise just show

I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row. Here's what I have right ...
0
votes
1answer
652 views

Counting using window function with partition over an empty table

If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that? select count(*) over (partition by field_name) from ...