Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

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
0answers
29 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
33 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
13 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
30 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
64 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
60 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
35 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
18 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
60 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
27 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
23 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
46 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
160 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
82 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
48 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
147 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
155 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
36 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 ...
0
votes
0answers
15 views

How to aggregate state log data into time intervals with the same state in PostgreSQL?

I have a log of object states that looks like this timestamp, object_id, state, level 2018-01-01 123 f 100 2018-01-02 123 t 100 2018-01-02 123 f 100 2018-01-...
1
vote
3answers
49 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
41 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
138 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
30 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
298 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
156 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
32 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
56 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 ...
4
votes
2answers
1k 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
192 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
157 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
27 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
168 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
70 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
140 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
25 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
326 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
107 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
65 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 ...
3
votes
1answer
429 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
351 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
48 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
9k 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 ...
0
votes
1answer
35 views

Consolidating multiple related queries, each using different window and/or criteria for counting records

I'm accumulating results for multiple contingency tables, one for each Test. As an example: Contingency Table for TestA (one for each test, and for each word) EventsWithWord ...
0
votes
0answers
119 views

Redshift window function for trend % calculation

I'm trying to figure out how i can calculate the trend on my events table using an window function in redshift, but not sure if this is even possible. Currently we execute 2 queries on our events ...
2
votes
4answers
1k views

Set non-negative floor for rolling sum, in PostgreSQL

This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data, CREATE TABLE foo AS ...