Restructure create and delete events on rows into single table

Question

Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log.

I have table that logs 'create' and 'delete' events for a resource that corresponds to an object.

Every event:

Is it's own row
Has a unique ID
Has a timestamp of when it occurred.

-------------------------------------
| id   | type   |object| timestamp |
-------------------------------------
| 1    | create | A    | 2019-1-1  |
| 2    | create | A    | 2019-1-2  |
| 3    | delete | A    | 2019-1-2  |
| 4    | create | B    | 2019-1-1  |
| 5    | delete | B    | 2019-1-2  |
| 6    | create | A    | 2019-1-3  |
| 7    | create | B    | 2019-1-4  |
| 8    | create | C    | 2019-1-5  |
-------------------------------------

The question I want to answer is how many resources still exist for a given object.

Example:

According to the data in the sample table, Object 'A' still has 2 resources that exist (three where created, one was deleted.). Object B and C each have one remaining resource.

Is it possible (as in the linked problem) to restructure this in a view where I have create and delete columns with timestamps?

You seem to have two different questions. "how many resources still exist for a given object" vs. "restructure this in a view". Which is it? What would the view look like given your sample data? Which A is being deleted in the event with id=3? — mustaccio, Jun 19 '19 at 16:46

score 0 · Answer 1 · answered Jun 19 '19 at 18:53

No restructure is needed. (Or I don't understand why there might need to be a restructure.)

SELECT  object,
        SUM(type = 'create') - SUM(type = 'delete') AS remaining_ct
    FROM tbl
    GROUP BY object;

This assumes that 'creates' occur before 'deletes'.

Restructure create and delete events on rows into single table

1 Answers1