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.

0
votes
1answer
16 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-...
5
votes
2answers
159 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
39 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
23 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
46 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
15 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
32 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
66 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
205 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
49 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
24 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
69 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
32 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
80 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
206 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
125 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
141 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
149 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
268 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
44 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
53 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
42 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
209 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
34 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 ...
3
votes
3answers
471 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
240 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
39 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
59 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
2k 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
199 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
207 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
30 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
57 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
208 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
74 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
162 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
2k 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-...
0
votes
1answer
37 views

Form groups computing max - min depending on number of peers

This is table: CREATE table point ( datetime timestamp, value float) I have data in some range of time 7:00 - 7:15: 0 5 : 5 7:15 - 7:30: - - : null 7:30 - 7:45: 7 - :...
2
votes
3answers
28 views

Summary over sets of points

Using postgresql 10 I have a table gpspoints which among other things contains id dist 1 NULL 2 2.3 3 4.4 4 1.7 5 1.2 6 2.7 and so on Dist is distance in meter ...
4
votes
2answers
375 views

TSQL speedup partition by query

CREATE TABLE entries( uuid BINARY(16) PRIMARY KEY, status VARCHAR(20), client_id VARCHAR(32), creation_date DATETIME, ); status can be NEW, IN_PROGRESS, DONE I ...
2
votes
2answers
108 views

Sum by Implicit Group

I'm sure I'm missing an obvious solution on this, but I'm trying to summarize values for groups that are defined by an explicit group number and an implicit ordering. I'm sure that doesn't make this ...
1
vote
2answers
83 views

SQL Windowing Functions: Are the results ordered?

If I have a window function which uses something like SELECT *, row_number() OVER (ORDER BY something) FROM table should the results be ordered? I am currently testing out my queries in Microsoft ...
4
votes
1answer
507 views

Carry over long sequence of missing values with Postgres

I have a table like this: create table foo (foo_label text, foo_price int, foo_date date); insert into foo ( values ('aaa', 100, '2017-01-01'), ('aaa', NULL, '2017-02-...
3
votes
2answers
389 views

dense_rank max OVER 3 days?

How do I get the MAX (or another aggregate function) for of a column of each row, OVER its 3 days worth of rows? SQL example with expected output and db schema: http://sqlfiddle.com/#!17/24686/3 ...
0
votes
1answer
51 views

How to pivot a table skipping bad value in Postgres

Given a table like: label | x | y | z -------+-------+-------+------ AAA | NULL | 7 | 9 BBB | 0 | 10 | 0 CCC | 12 | NULL | 0 create table coords (label ...
1
vote
2answers
11k views

Sum of previous n number of columns based on some category

I have following input for which I need to calculate the sum of values for previous x number of weeks for each category. If the x is 3 the output would look like this: Note that the last value is 49 ...