4

I have a query which produces data similar to the following:

a_key     | a_timestamp | a_value
---------------------------------
345223452 | 2016-03-07  | 1
029370982 | 2017-01-12  | 1
984656834 | 2017-02-03  | 1
928459238 | 2014-08-06  | 2
298338793 | 2015-07-10  | 2
203897932 | 2015-09-12  | 3
123477399 | 2016-05-03  | 3

I need to eliminate the most recent timestamp for each given value.

Keys are all unique. Values in this query are all non-unique (that is, there will be no value that is not contained in at least two rows) due to filters already applied.

It seems there should be some easy way to do this with an aggregate function or a "group by" clause, but I can't seem to work it out.

How can I filter this result to exclude the most recently seen key for each value?

(If it helps, I only really need the key from the results, not all three columns.)

I am using Postgres 9.3.11.

3

My first thought would be to use a window function like ROW_NUMBER(), almost identical to your solution.

Here are a few more ways to write this query:

WITH mytable AS
  ( --- the query --- )
SELECT a_key
FROM mytable AS t
WHERE EXISTS 
      ( SELECT *
        FROM mytable AS n
        WHERE t.a_value = n.a_value
          AND t.a_timestamp < n.a_timestamp
      ) ;

Using another window function, LEAD():

SELECT a_key
FROM
  ( SELECT a_key,
           (LEAD(a_timestamp) OVER (PARTITION BY a_value
                                    ORDER BY a_timestamp)
            IS NOT NULL) AS ok
    FROM mytable
  ) AS pointless
WHERE ok ; 

A variation on the above, using a different condition to check which rows to keep:

           (LEAD(a_value) OVER (ORDER BY a_value, a_timestamp)
            = a_value) AS ok 

And a rather weird solution:

SELECT a_key
FROM mytable

EXCEPT

( SELECT DISTINCT ON (a_value) a_key
  FROM mytable
  ORDER BY a_value, a_timestamp DESC
) ;

Modified NOT IN to NOT EXISTS:

SELECT a_key
FROM mytable AS t
WHERE NOT EXISTS
      ( SELECT 1 
        FROM mytable AS m 
        WHERE m.a_value = t.a_value 
        HAVING MAX(m.a_timestamp) = t.a_timestamp
      ) ;

And modified again to a JOIN:

SELECT t.a_key
FROM mytable AS t 
  JOIN
    ( SELECT a_value, max(a_timestamp) AS a_timestamp 
      FROM mytable                   
      GROUP BY a_value 
    ) AS m 
  ON  t.a_value = m.a_value 
  AND t.a_timestamp < m.a_timestamp ;

Regarding performance, I did a test with a small 200K rows table (not a subquery), with and without indexes, of the various methods.

Since the query needs to return a large majority of rows (more than 50% and could be close to 100% depending on the distribution), I wouldn't expect indexes to be particularly helpful.

The window function solutions (ROW_NUMBER(), RANK(), LEAD()) performed quite well and similarly to each other (less than 2 sec).

The EXISTS method came a bit slower and last was the DISTINCT ON method (around 3 sec).

The NOT IN method by @joanolo shows a materialized subplan and was really slow (but it may be more efficient if the mytable subquery returns fewer rows). Modified to a similar NOT EXISTS lowered the response time to about 3 seconds. The JOIN modification was somewhat better, around 2 - 2.5 sec.

The plans showed sequential scans of course and most improved with indexes, doing index scans instead (and lowering response time to about 1-1.5 sec for the window functions and the join methods).

(I used (a_value, a_timestamp) and (a_value, a_timestamp, a_key) indexes and variations changing to timestamp DESC but the actual indexes are more or less irrelevant to the specific example, since we have no idea how complex the sybquery is.)

7
  • I like your weird solution. :) For the others, I'll have to read up on the functions you're using. Thanks! – Wildcard Feb 25 '17 at 2:52
  • The weird would probably be the less efficient! – ypercubeᵀᴹ Feb 25 '17 at 3:14
  • In my small test, the window function solution (rank, row_number, lead) performed better. I tested with a base table of course, with only 200k rows. Depending on what the subquery is, it might be able to use some indexes. – ypercubeᵀᴹ Feb 25 '17 at 3:20
  • The weird solution isn't weird at all. That's what I would have suggested, too. If performance needs to be optimized, the OP would have to provide essential information about the setup. Table definitions, query, cardinalities, resources. There may be faster ways. – Erwin Brandstetter Feb 25 '17 at 14:53
  • @ErwinBrandstetter yeah, it seems interesting. I labeled weird because it wouldn't be my first choice. EXCEPT + DISTINCT ON seems like a complex way when we want to return almost all the table (or at least half of it). See my edit with some (limited) test. The window functions performed a bit better but of course that says almost nothing for the actual case which might be a join over 20 tables for all we know. – ypercubeᵀᴹ Feb 25 '17 at 16:00
2

There may be an easier way, but here is what I worked out:

SELECT
  a_key
FROM (
  SELECT
    a_key,
    a_timestamp,
    a_value,
    rank() OVER (
      PARTITION BY
        a_value
      ORDER BY
        a_timestamp DESC
    )
  FROM
    mytable
) AS pointless
WHERE
  rank != 1
;

It's worth noting that this can NOT be done with "group by" and aggregate functions, because aggregate functions combine rows. Window functions, by contrast, do not. I hadn't dealt with window functions before.

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

2
  • 1
    Yes, that's a good way to solve this. You could also use ROW_NUMBER() instead of RANK(). The result would be the same, since you have no rows with duplicate a_value and a_timestamp values. But it might be more efficient. – ypercubeᵀᴹ Feb 25 '17 at 0:43
  • 1
    And I'd probably use WHERE rank > 1 (you have rank <> 1 but the rank can never be 0 or negative.) No idea if that would make any difference but it wouldn't hurt. – ypercubeᵀᴹ Feb 25 '17 at 0:52
2

You can also get the "most recent timestamps" by means of a GROUP BY. To get the most recent timestamp, you just use max(a_timestamp).

This SELECT will do the job of finding the most recent timestamps:

SELECT
    a_value, max(a_timestamp) AS most_recent_timestamp
FROM
    t
GROUP BY
    a_value ;

If you want to retrieve "everything except the rows having these value/timestamp pairs", you can do it using a NOT IN, this way:

SELECT
    a_key, a_timestamp, a_value
FROM 
    t
WHERE
   (a_value, a_timestamp) NOT IN
       (SELECT
           a_value, max(a_timestamp) AS most_recent_timestamp
       FROM
           t
       GROUP BY
           a_value
       ) ;

This is the result you'll get:

|     a_key |                 a_timestamp | a_value |
|-----------|-----------------------------|---------|
| 345223452 |     March, 07 2016 00:00:00 |       1 |
| 029370982 |   January, 12 2017 00:00:00 |       1 |
| 928459238 |    August, 06 2014 00:00:00 |       2 |
| 203897932 | September, 12 2015 00:00:00 |       3 |

If, for some groupings, the maximum timestamp is shared by various rows, they're all taken out of the result.

You can check it at SQLFiddle

3
  • The real column names are different, actually. I simplified for illustration. – Wildcard Feb 24 '17 at 7:59
  • (suggestion: use a_key, a_value, a_timestamp; or something similar) – joanolo Feb 24 '17 at 8:07
  • Good idea; done. – Wildcard Feb 24 '17 at 20:45

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.