1

I need some help with a complex query. Table def:

                                        Table "public.agent_log"
     Column      |           Type           |                          Modifiers                         
-----------------+--------------------------+-------------------------------------------------------------
 class           | text                     |
 hostname        | text                     |
 ip_address      | text                     |
 promise_handle  | text                     |
 promiser        | text                     |
 promisee        | text                     |
 policy_server   | text                     |
 rowId           | integer                  | not null default nextval('"agent_log_rowId_seq"'::regclass)
 timestamp       | timestamp with time zone |
 promise_outcome | text                     |
Indexes:
    "primary_key" PRIMARY KEY, btree ("rowId")
    "client_by_timestamp" btree ("timestamp", class)

Each line in the table is a log. There will be many rows that are the same except for the timestamp. As the data ages, I want to cull the rows. I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group. Example:

2014-01-22T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 any 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 any 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 any 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 any 192.168.0.2 mars.example.com ...
2014-01-22T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-22T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-22T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-22T13:44:00 cpu_1 192.168.0.2 mars.example.com ...
2014-01-23T13:44:00 cpu_1 192.168.0.1 moon.example.com ...
2014-01-23T14:44:00 cpu_1 192.168.0.1 moon.example.com ... KEEP
2014-01-23T14:44:00 cpu_1 192.168.0.2 mars.example.com ... KEEP
2014-01-23T13:44:00 cpu_1 192.168.0.2 mars.example.com ...

I'm testing with this, note that I'm using select instead of delete for this test. I can't get the where correct and the few things I've tried resulted in a very long query. Any suggestions for improvement or alternate approaches welcome.

explain SELECT * FROM agent_log 
WHERE 
NOT IN (
   SELECT
          class,
          max(timestamp) as timestamp,
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
      FROM agent_log
      WHERE timestamp < now() - interval '7 days' 
      GROUP BY
          class,
          DATE_TRUNC( 'day', timestamp),
          hostname,
          ip_address,
          promise_handle,
          promiser,
          promisee,
          policy_server,
          promise_outcome
)
AND timestamp < now() - interval '7 days' 
2

1 Answer 1

1

I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group.

Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead.

The query is surprisingly simple with DISTINCT ON:

SELECT DISTINCT ON (class, ip_address, hostname, ts::date) *
FROM   agent_log
WHERE  ts < now() - interval '7 days'
ORDER  BY class, ip_address, hostname, ts::date, ts DESC;

Detailed explanation:
Select first row in each GROUP BY group?

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.