CODEWITHSUNDEEP
postgresqlwindow-functions
Gabriel Furstenheim
Gabriel Furstenheim

Reputation: 3432

Refer to current row in window function

Is it possible to refer to the current row in a window partition? I want to do something like the following:

SELECT min(ABS(variable - CURRENT.variable)) over (order by criterion RANGE UNBOUNDED PRECEDING)

That is, i want to find in the given partition the variable which is closest to the current value. Is is possible to do something like that?

As an example, from:

criterion | variable 1 2 2 4 3 2 4 7 5 6

We would obtain:

null 2 0 3 1

Thanks

Upvotes: 5

Views: 1686

Answers (3)

etsa
etsa

Reputation: 5060

Hope this could help you (pay attention for performance problems). I tried this in MSSQL (at bottom you'll find POSTGRESQL version):

CREATE TABLE TX (CRITERION INT, VARIABILE INT);
INSERT INTO TX VALUES (1,2), (2,4),(3,2),(4,7), (5,6);

SELECT CRITERION, MIN_DELTA FROM 
(
SELECT TX.CRITERION 
, MIN(ABS(B.TX2_VAR - TX.VARIABILE)) OVER (PARTITION BY TX.CRITERION) AS MIN_DELTA
, RANK() OVER (PARTITION BY TX.CRITERION ORDER BY ABS(B.TX2_VAR - TX.VARIABILE) ) AS MIN_RANK
FROM TX
CROSS APPLY (SELECT TX2.CRITERION AS TX2_CRIT, TX2.VARIABILE AS TX2_VAR FROM TX TX2 WHERE TX2.CRITERION < TX.CRITERION) B
) C
WHERE MIN_RANK=1
ORDER BY CRITERION 
;

Output:

CRITERION   MIN_DELTA
----------- -----------
2           2
3           0
4           3
5           1

POSTGRESQL Version (tested on Rextester http://rextester.com/VMGJ87600):

CREATE TABLE TX (CRITERION INT, VARIABILE INT);
INSERT INTO TX VALUES (1,2), (2,4),(3,2),(4,7), (5,6);
SELECT * FROM TX;

SELECT CRITERION, MIN_DELTA FROM 
(
SELECT TX.CRITERION 
, MIN(ABS(B.TX2_VAR - TX.VARIABILE)) OVER (PARTITION BY TX.CRITERION) AS MIN_DELTA
, RANK() OVER (PARTITION BY TX.CRITERION ORDER BY ABS(B.TX2_VAR - TX.VARIABILE) ) AS MIN_RANK
FROM TX
LEFT   JOIN LATERAL (SELECT TX2.CRITERION AS TX2_CRIT, TX2.VARIABILE AS TX2_VAR FROM TX TX2 WHERE TX2.CRITERION < TX.CRITERION) B ON TRUE
) C
WHERE MIN_RANK=1
ORDER BY CRITERION 
;

DROP TABLE TX;

Output:

    criterion   variabile
1   1   2
2   2   4
3   3   2
4   4   7
5   5   6

    criterion   min_delta
1   1   NULL
2   2   2
3   3   0
4   4   3
5   5   1

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

If I understand correctly:

with t (v) as (values (-5),(-2),(0),(1),(3),(10))
select v,
    least(
        v - lag(v) over (order by v),
        lead(v) over (order by v) - v
    ) as closest
from t
;
 v  | closest                                                                                                          
----+---------                                                                                                         
 -5 |       3                                                                                                          
 -2 |       2                                                                                                          
  0 |       1
  1 |       1
  3 |       2
 10 |       7

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246493

As far as I know, this cannot be done with window functions.

But it can be done with a self join:

SELECT a.id,
       a.variable,
       min(abs(a.variable - b.variable))
FROM mydata a
   LEFT JOIN mydata b
      ON (b.criterion < a.criterion)
GROUP BY a.id, a.variable
ORDER BY a.id;

Upvotes: 1

Related Questions