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
1answer
15 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
87 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
40 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. -...
11
votes
5answers
1k 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
91 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
55 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
76 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
29 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
65 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: ...
11
votes
3answers
1k 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
30 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
46 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
25 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
132 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
18 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-...
6
votes
2answers
298 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
44 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
42 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 ...
0
votes
0answers
30 views

Force query plan to filter early when querying view? [duplicate]

I've got a view defined that lists transactions together with a running total, something like CREATE VIEW historyView AS SELECT a.createdDate, a.value, m.memberId, ...
2
votes
2answers
60 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
19 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 | +---------------+-------+--------+----------+...
0
votes
0answers
35 views

Postgresql view that calculate if payment is paid based on previous rows

I have go DB structure like here: db-fiddle I'm looking for some solution that in View for charge_line_tab table will add information if charge line is paid or not, based on previous records in ...
3
votes
1answer
70 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
831 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
90 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
45 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
92 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
76 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
31 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, ...
0
votes
1answer
153 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
730 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
219 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
362 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 ...
1
vote
1answer
19 views

Finding average of multiple delta times

I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example: SELECT *, "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER ...
11
votes
1answer
167 views

Why does batch mode window aggregate yield arithmetic overflow?

The following query performs a windowed SUM over a columnstore table with 1500 total rows, each of which has the value 0 or 1, and it overflows the INT data type. Why is this happening? SELECT a, p, ...
4
votes
1answer
455 views

Select last non-null value for given partition - Postgres 10

I have a table with the following structure: date city cntry free occ 2018-08-13 16:30:00 2 12 5 2018-08-13 16:30:00 2 12 0 2018-08-13 14:30:00 2 12 1 ...
0
votes
1answer
59 views

Add running count of specific state changes

I have a table in PostgreSQL with below fields, where timestamp is simplified as integers: ts status 1 m 2 m 3 i 4 s 5 s 6 i 7 i 8 m 9 s 10 m I want to break ...
1
vote
3answers
64 views

Show rows with non-unique value in one field

I'm familiar with how to aggregate rows as shown in this answer: Show only duplicate values I'm also familiar with how to filter the aggregated results using a HAVING clause. What I can't seem to ...
1
vote
1answer
46 views

Window function with order by returns strange results

Here is example: drop table if exists tst; create table tst ( num integer not null ); insert into tst values (1), (2), (3); -- window functions WITH order by clause select *, max(num) over (...
2
votes
2answers
464 views

Filter on time difference between current and next row

I have a database table with the following structure: id | created -------+--------------- 1 | 2018-07-23 00:01:00 2 | 2018-07-23 00:02:00 ... Now I'd like to return all rows where the ...
2
votes
1answer
37 views

How to count rows based on a column value

I am using Posgresql and I want to write a query to perform the following. Suppose the table is like this: id section views 1 1 10 2 2 8 3 1 20 4 1 21 5 2 ...
4
votes
3answers
1k views

Can I calculate ROW_NUMBER() for only consecutive records?

I need to calculate sequence numbers for consecutive values. That sounds like a job for ROW_NUMBER()! DECLARE @Data TABLE ( Sequence TINYINT NOT NULL PRIMARY KEY, Subset CHAR(1) NOT ...
5
votes
1answer
453 views

Grouping data based on cumulative sum

I have been looking online for an answer but do not really know how to formulate correctly what I would like to achieve and whether it's possible, sorry if the question sounds dumb. I am using ...
1
vote
1answer
60 views

How to force Redshift/Postgres to aggregate nth_value?

I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this: select customer_email, nth_value(created_at, 1) over (partition by ...
2
votes
0answers
63 views

Efficient way to create daily summary table

I'm trying to create a daily summary table that captures characteristics of each client known as of every given date that my company has been in business. The challenge is that there are so many ...
6
votes
2answers
4k views

How to select the set of last non-NULL values per column over a group?

I am using SQL Server 2016 and the data I am consuming has the following form. CREATE TABLE #tab (cat CHAR(1), t CHAR(2), val1 INT, val2 CHAR(1)); INSERT INTO #tab VALUES ('A','Q1',2,NULL),('A',...
5
votes
4answers
211 views

SQL Challenge - The Sensor Threshold Exception Report

I've added a solution without using window functions and a benchmark with a large data set below Martin's Answer This is a followup thread to GROUP BY using columns not in the SELECT list - when is ...
5
votes
1answer
319 views

Calculate drawdown and volatility on 3 years

I have a table with daily prices as below and in this SQLfiddle. Each record is associated to a ShareClassID, a currency, a date, and an amount. In my real scenario I can have at least 10 years of ...
2
votes
1answer
43 views

SELECTing min, max (maybe length) of continuous id ranges in a table

I have a table foo in Postgres (10.3) # select * from foo; id | cust | load_dt ----+-------+------------ 1 | cust1 | 2 | cust1 | 3 | cust1 | 4 | cust2 | 2018-05-15 5 | cust2 | 2018-05-15 ...
4
votes
1answer
60 views

Oracle analytic functions - how to get the highest RANKing row?

Test data: create table test ( grp varchar2(16) , mbr varchar2(16) , reading1 number , reading2 number ); -- group A: 3 members, 1 duplicate set -- group B: 2 members, 1 duplicate, one reading ...