Search Postgresql Archives

Stumped on windowing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm working with a product that uses effective date based data structures. We then create views using analytic functions that have begin and end dates for when that record was valid. This works fine when there is just one record per item that is valid at any given time (for instance job assignment details).

But now I have a table where multiple rows can be valid for a given date and it is giving me grief. This particular table is about job funding and at any given time a job may be funded out of several accounts. Here is a simplified example of the data:

EMP_ID   POSN      EFF_DATE      FUND      ORG                 PCT              DENSE_RANK
56332    001071    2010-07-01    22086     182030    4.00     1
56332    001071    2010-07-01    24095     184001    10.00    1
56332    001071    2010-07-01    22065     182024    20.00    1
56332    001071    2010-07-01    14001     000121    26.00    1
56332    001071    2010-07-01    22088     182031    40.00    1
56332    001071    2010-12-01    24095     184001    14.00    2
56332    001071    2010-12-01    14001     000121    21.00    2
56332    001071    2010-12-01    22065     182024    25.00    2
56332    001071    2010-12-01    22088     182031    40.00    2
56332    001071    2011-04-01    22086     182030    4.00     3
56332    001071    2011-04-01    24095     184001    10.00    3
56332    001071    2011-04-01    22088     182031    40.00    3
56332    001071    2011-04-01    22065     182024    46.00    3

So my initial attempt was to do something like:

LEAD(eff_date - 1) OVER (PARTITION BY emp_id, posn, fund, org ORDER BY eff_date)

But that wont work in the above example because the fund 22086 drops off completely on 12/01 and returns 4/01. So if I used that approach the result would be wrong between 12/01 and 4/01.  What I really need is the value from the next window but nothing seems to work that way. What I need is something like:

MIN ( CASE WHEN eff_date > CURRENT_ROW.eff_date THEN eff_date END ) OVER (
    PARTITION BY emp_id, posn ORDER BY eff_date) <- a way not to include the current value

MIN(eff_date) OVER (PARTITION BY emp_id, posn ORDER BY eff_date RANGE UNBOUNDED FOLLOWING) <- not include current row

MIN(eff_date) OVER(PARTITION BY emp_id, posn ORDER BY eff_date RANGE BETWEEN CURRENT ROW + 1 AND UNBOUNDED FOLLOWING)

BTW, here is the correctly working dense rank part:
DENSE_RANK() OVER (PARTITION BY emp_id, posn ORDER BY eff_date)

Is there any way to do this with analytic functions or am I going to have to resort to a subquery?

Scott Bailey


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux