Difference of two rows in one SELECT SQL statement

Question

I have a database table that has a structure similar to the one shown below:

CREATE TABLE dated_records ( recdate DATE NOT NULL col1 DOUBLE NOT NULL, col2 DOUBLE NOT NULL, col3 DOUBLE NOT NULL, col4 DOUBLE NOT NULL, col5 DOUBLE NOT NULL, col6 DOUBLE NOT NULL, col7 DOUBLE NOT NULL, col8 DOUBLE NOT NULL ); 

I want to write an SQL statement that will allow me to return a record containing the changes between two set dates for the specified columns - for example. col1, col2 and col3

for example, if I wanted to see how much the value in col1, col2 and col3 changed during the interval between two dates. The dumb way to do this is to select the rows (separately) for each date, and then split the fields outside of the db server -

 SQL1 = "SELECT col1, col2 col3 FROM dated_records WHERE recdate='2001-01-01'"; SQL1 = "SELECT col1, col2 col3 FROM dated_records WHERE recdate='2001-02-01'"; 

however, I am sure there is a way to a more reasonable way to make the difference using pure SQL. I guess this will be due to the use of self-join (and possibly a nested subquery), but I can complicate the situation - I decided it was better to ask SQL experts here to find out how they would solve this problem in the most efficient way.

Ideally, SQL should be DB agnostic, but if it should be bound to a specific db, then it should be PostgreSQL.

Answer 1

Just select two lines, combine them into one and subtract the values:

 select d1.recdate, d2.recdate, (d2.col1 - d1.col1) as delta_col1, (d2.col2 - d1.col2) as delta_col2, ... from (select * from dated_records where recdate = <date1> ) d1 cross join (select * from dated_records where recdate = <date2> ) d2 

Answer 2

I think that if you want to do this, you will get rows of results that do not intersect with two select queries, you can use the EXCEPT operator:

The EXCEPT statement returns rows that are in the first result set but not in the second.

So, your two requests will become one single request with the addition of the except operator:

 SELECT col1, col2 col3 FROM dated_records WHERE recdate='2001-01-01' EXCEPT SELECT col1, col2 col3 FROM dated_records WHERE recdate='2001-02-01' 

Answer 3

 SELECT COALESCE (a.col1 - ( SELECT b.col1 FROM dated_records b WHERE b.id = a.id + 1 ), a.col1) FROM dated_records a WHERE recdate='2001-01-01'; 

Answer 4

You can use window functions plus DISTINCT :

 SELECT DISTINCT first_value(recdate) OVER () AS date1 ,last_value(recdate) OVER () AS date2 ,last_value(col1) OVER () - first_value(col1) OVER () AS delta1 ,last_value(col2) OVER () - first_value(col2) OVER () AS delta2 ... FROM dated_records WHERE recdate IN ('2001-01-01', '2001-01-03') 

For any two days. Uses a single index or table scan, so it should be fast.

I did not order a window, but all calculations use the same window, so the values ​​are consistent.

This solution can be easily generalized for calculations between n lines. In this case, you can use nth_value() from the Postgres arsenal of window functions .

Answer 5

It would seem like a faster way to write this if you are looking for a simple delta.

 SELECT first(col1) - last(col1) AS delta_col1 , first(col2) - last(col2) AS delta_col2 FROM dated_records WHERE recdate IN ('2001-02-01', '2001-01-01') 

You may not know if the first line is the first or the second line, but you can always wrap the answer in abs(first(col1)-last(col1))