0

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 at 16:46
0

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'.

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.