0

I have a table that has a, b, and timestamp fields.

Table is getting very long, and I would like to remove records older than a year, with some exceptions - I need to keep:

  • at least 4 latest records for each value of a, regardless how old
  • at least 4 latest records for each value of b, regardless how old

For example, in the following case:

=> select a, count(*) from Data group by a;
 a  | count
----+-------
 A  |     4
 B  |    12
 C  |    17

=> select a, count(*) from Data where timestamp > now() - INTERVAL '1 year' group by a;
 a  | count
----+-------
 A  |     0
 B  |     8
 C  |    11

=> select b, count(*) from Data group by b;
 b  | count
----+-------
 1  |     5
 2  |    13
 3  |    15

=> select b, count(*) from Data where timestamp > now() - INTERVAL '1 year' group by b;
 b  | count
----+-------
 1  |     3
 2  |     8
 3  |    10

I want to end up with something like this:

=> select a, count(*) from Data group by a;
 a  | count
----+-------
 A  |     4
 B  |     8   (possibly more for b=1 records)
 C  |    11   (possibly more for b=1 records)
    
=> select b, count(*) from Data group by b;
 b  | count
----+-------
 1  |     4
 2  |     8   (possibly more for a='A' records)
 3  |    10   (possibly more for a='A' records)

In this case:

  • I want to keep all 4 records with a='A', even though they are all older than a year.
  • For records with b=1 I want to keep the latest 4 and remove the oldest one.
  • For records with a='B' or a='C' or b=2 or b=3, I don't need to worry about records older than a year.

Of course, my actual data has 1000 different values for the two columns.

2 Answers 2

0

Use the ROW_NUMBER() function (manual) - all of the code below is available on the fiddle here.

WItH cte AS
(  
  SELECT 
    a, b, ts, 
    ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY ts DESC) AS rn
  FROM data
)
SELECT 
  a, b, ts
FROM
  cte
WHERE RN <= 4;

Window functions are very powerful and well worth investing time in learning - you will be repaid many times over!

I put some of my own sample data in the fiddle, but you should be able to follow the logic. Note the sorting by DESC.

A couple of points

  • the sample data in the fiddle is not exactly yours, you might think about providing one yourself in future with your questions,

  • it's not a good idea to use SQL keywords as variable names - it's error prone, makes reading your SQL difficult to read and also, it'll be non-portable. Some systems require double-quotes (") for such variables, others the back-tick (`) and some, like PostgreSQL don't require either.

    This is too much of a mess - just don't use keywords and use snake_case (lower case with extra words concatenated using an underscore (_). Here is an excellent SQL style guide - the important thing is to pick a style and** stick to it!

0

Building upon the code by Vérace...
I think the solution should utilize two individual ROW_NUMBER() columns for a and b respectively. If you use just one column for a, b combined, you will keep at least four entries for any possible combination of a and b.

The result would look something like this:

WITH cte AS
(  
  SELECT 
    a, b, ts, 
    ROW_NUMBER() OVER (PARTITION BY a ORDER BY ts DESC) AS rn1,
    ROW_NUMBER() OVER (PARTITION BY b ORDER BY ts DESC) AS rn2
  FROM data
)
SELECT 
  a, b, ts
FROM
  cte
WHERE (rn1 <= 4)
  OR (rn2 <= 4)
  OR ts > NOW() - INTERVAL '1 year';

Here is the corresponding dbfiddle.

BTW: I think the question is a bit misleading. At first you mention that for every value of a and for every value of b the latest four entries have to be kept regardless of their timestamp. Then you go on and say that

For records with a='B' or a='C' or b=2 or b=3, I don't need to worry about records older than a year.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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