PostgreSQL – How to Find Matching Pairs Inside Table

postgresql

I have a table where I store logs in Postgres. These logs contain data for cases and the case number. Each row store the case number and more records.

Example:

+-----------------+-----------+---------+------+------+
| case number     | action_id | task_id | user | date |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 23456/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 2         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 14221/2018/FILE | 1         | 6       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 34567/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 6       |      |      |
+-----------------+-----------+---------+------+------+

I'd like to create a query where the result is all the row where task_id = 7, but only contain those where case number has no action_id=2 pair.

Example result:

+-----------------+-----------+---------+------+------+
| case number     | action_id | task_id | user | date |
+-----------------+-----------+---------+------+------+
| 23456/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 24556/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+
| 34567/2018/FILE | 1         | 7       |      |      |
+-----------------+-----------+---------+------+------+

I have to make report from the log but I don't know how to perform this query.

Best Answer

How about this (untested)?

select *
from log_table x
where task_id = 7
and not exists (
  select 1
  from log_table
  where case_number = x.case_number
  and task_id = x.task_id
  and action_id = 2
);

Related Solutions

Postgresql – Optimizing a query on a large table (EXPLAIN ANALYZE result inside)

A few thoughts. Consider range partitioning on "timestamp", it will reduce the amount of work your query have to do. Further optimization might be to calculate and store the agg for "closed" partitions. You will of course have to recalculate this when you modify historical information.

As a bonus it will be much easier to roll-out historical data that is no longer needed.

As mentioned this is just some thoughts and reflections, it may or it may not fit your situation. One pitfall may be the number of different intervals that you use for reporting. If larger intervals is not multiples of the partitioning interval this wont work well.

Edit: How is the plan affected by creating an temp table and an index on "timestamp"?

create index X on market_trades ( timestamp );

create temp table T ( time_range tsrange );

insert into T ( time_range )
WITH vals AS (
    SELECT '2013-08-19 0:00'::timestamp  AS frame_start,
       '2013-08-26 0:00'::timestamp  AS frame_end,
       '17h'::interval               AS interval_length
),   intervals AS (
    SELECT tsrange(start_time,
               lead(start_time, 1, frame_end) OVER (ORDER BY start_time   NULLS FIRST)) AS time_range
    FROM (
        SELECT generate_series(frame_start, frame_end, interval_length) AS  
               start_time, frame_end
        FROM vals
    ) _
    WHERE start_time < frame_end
)
SELECT time_range
FROM intervals i;

create index x on T ( time_range );

analyze t;

SELECT time_range, count(td.id) AS agg
FROM T
LEFT JOIN market_trades td
    ON td.timestamp <@ T.time_range
GROUP  BY T.time_range
ORDER  BY T.time_range;

PostgreSQL – Proper Usage of GROUP BY in INNER JOIN

Query for per agent per day with user name.

Re-frame the Query as per your user table.

    SELECT
        A.agent_id,
        users.NAME,
        A.created_at,
        SUM (TotalLoginHours) AS TotalLoginHours,
        COUNT (A.ID) AS TotalApplication,
        SUM (TotalLoginHours) / COUNT (A.ID) AS ApplicationPerHour,
        (
            SUM (TotalLoginHours) / COUNT (A.ID)
        ) * 1.75 AS RPH
    FROM
        (
            SELECT
                A.agent_id,
                A.ID,
                A.created_at,
                SUM (b.loginhours) AS TotalLoginHours
            FROM
                forms A
            INNER JOIN loginhours b ON A.agent_id = b.user_id
            WHERE
                A.created_at >= '2015-07-10'
            GROUP BY
                A.agent_id,
                A.ID,
                A.created_at
        ) A
    INNER JOIN users ON users.user_id = A.agent_id
    GROUP BY
        A.agent_id,
        users.user_id,
        A.created_at

Related Question