Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

1
vote
1answer
45 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
71 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
24 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
46 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: ...
9
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
45 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
21 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
58 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
218 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
42 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
33 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
53 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
17 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
34 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
561 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
72 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
38 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
79 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
53 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
30 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
115 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
434 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
185 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
250 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
16 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 ...
10
votes
1answer
158 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, ...
2
votes
1answer
374 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
52 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
60 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
45 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 (...
1
vote
2answers
337 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
35 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
811 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
355 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
53 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
61 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 ...
5
votes
2answers
3k 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
205 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
263 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
36 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 ...
0
votes
3answers
235 views

Return 1st or last row from NTILE

Is is possible to return the first (or last) row from a NTILE partition in SQL SERVER? For example, on this fiddle I wish I could have the last member from each group without the comma. SQL Fiddle: ...
1
vote
2answers
75 views

How can I assign group IDs depending on content?

I have the following table with data as shown below; linenrs are monotonically increasing but not necessarily consecutive; when the key field contains an ellipsis ... that indicates an entry that is ...
0
votes
1answer
185 views

OLAP window functions in DB2

I am using DB2 z/OS 11.1.5. According to this reference, OLAP functions such as first_value should be supported at least in DB2 9.7. However, when trying to execute the query declare global ...
0
votes
1answer
21 views

Getting n slip number based on the number of times in a month a slip has been created

Hi Guys so here is my table, I need a slip number for the slipno collom that is based on the number of times the empno has a slipno in a month. To try and better explain here is my table ------------...
2
votes
1answer
3k views

Calculate Running Sum of Each Row From Start, Even When Filtering Records

I have this simplified table entries: ---------------------------- | id | date | amount | ---------------------------- | 1 | 2017-12-01 | 100 | ---------------------------- | 2 | 2017-12-...