0

I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where I'm at.

UPDATE Opphistory t
SET    MaxStageSortOrder = sub.max_snapshotdate
FROM  (
   SELECT opportunityid, max(snapshotdate) AS max_snapshotdate
   FROM   Opphistory
   WHERE forecastcategory <> 'Omitted' and snapshotdate <= t.snapshotdate
   GROUP  BY 1
   ) sub
WHERE t.opportunityid = sub.opportunityid

It is the snapshotdate <= t.snapshotdate that appears to fail.

1
  • get the MAX value from the latest record BEFORE the date of the current record. Either you want the value form a certain row, or you want the MAX value from a set. You can't have both at the same time. Table definition and Postgres version are always useful to add. Oct 24, 2021 at 21:27

2 Answers 2

2

Seems like you were aiming for a correlated subquery:

UPDATE opphistory t
SET    MaxStageSortOrder = (
   SELECT max(snapshotdate)
   FROM   Opphistory t1
   WHERE  t1.opportunityid = t.opportunityid
   AND    t1.snapshotdate < t.snapshotdate  -- "before", so not <=
   AND    t1.forecastcategory <> 'Omitted'
   );

A derived table in the FROM clause of an UPDATE cannot reference columns of the main table. That's possible in a correlated subquery or a LATERAL subquery. But, unfortunately, table expressions in the FROM clause of an UPDATE are (at least up to pg 14) always joined with a CROSS JOIN (effectively) to the main table. You can work around this limitation by repeating the main table in the FROM clause, binding that one-to-one to the main table, and then joining to the "proxy" with any join type. In the case at hand, we don't even need that:

UPDATE Opphistory t
SET    MaxStageSortOrder = sub.max_snapshotdate
FROM  (
   SELECT opphistory_id
        , max(snapshotdate) OVER (PARTITION BY opportunityid ORDER BY snapshotdate 
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS max_snapshotdate
   FROM   Opphistory
   WHERE  forecastcategory <> 'Omitted'
   ) sub
WHERE  t.opphistory_id = sub.opphistory_id
AND    t.MaxStageSortOrder IS DISTINCT FROM sub.max_snapshotdate;

opphistory_id is supposed to be the PRIMARY KEY of the table.

I expect the second query to be substantially faster for big tables, as it (probably) only needs a single sequential scan and a sort to compute the running max for all rows. Running a correlated subqery for every row (like in the first query) has its price.

About ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING:

Note subtle differences:

  • The first query update every row, no matter what.
  • The second query omits ...
    • ... rows with forecastcategory = 'Omitted' and rows with forecastcategory IS NULL
      (The first query only excludes those from the max computation.)
    • ... rows with opportunityid IS NULL
    • ... rows that would not change - due to the added last line.

If (opportunityid, snapshotdate) is not defined UNIQUE NOT NULL, we may have to do more, starting with a definition how to deal with duplicates and NULL values.

It's not clear from your question what you want exactly.

If you really just want the snapshotdate from the "previous" row, consider the simpler window function lag():

UPDATE Opphistory t
SET    MaxStageSortOrder = sub.max_snapshotdate
FROM  (
   SELECT opphistory_id
        , lag(snapshotdate) OVER (PARTITION BY opportunityid ORDER BY snapshotdate) AS max_snapshotdate
   FROM   Opphistory
   WHERE  forecastcategory <> 'Omitted'
   ) sub
WHERE  t.opphistory_id = sub.opphistory_id
AND    t.MaxStageSortOrder IS DISTINCT FROM sub.max_snapshotdate;
0
1

The subquery has to be correlated or uncorrelated, it can't be half way between. In this case, it seems like it needs to be correlated.

UPDATE Opphistory t
SET    MaxStageSortOrder = (
   SELECT max(snapshotdate)                    
   FROM   Opphistory sub
   WHERE forecastcategory <> 'Omitted' and 
         snapshotdate <= t.snapshotdate and 
         t.opportunityid = sub.opportunityid
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.