badera badera - 1 year ago 44
Java Question

Select active state of state history table in a given period

I have a simple table containing a history of states of a 'reader' object. The state can be ACTIVE or INACTIVE. Now I would like to have a query, which picks all readers, which where ACTIVE in a given period. The following image shows what I mean: The 'readers' with the green 'lifeline' where ACTIVE in the displayed period; so they shall be returned in the query.

enter image description here

The tabel

looks like this (example only, not matching image above):

reader_id | state | timestamp
1 | ACTIVE | 1467331201089
2 | ACTIVE | 1467332454545
1 | INACTIVE | 1467348875254
3 | ACTIVE | 1467350416546
1 | ACTIVE | 1467351871123
2 | INACTIVE | 1467352111545

Of course, there is a
Entity to which this
is mapped to.

I need to have a JPA query for this, but I have problems including the readers into the result, which have no 'rising edge' (state change from INACTIVE to ACTIVE) within the period (which were already active before the period we look into).

I see basically too parts of the query:

  • First, select all having a rising edge. This is simple:
    SELECT DISTINCT a.reader FROM ReaderActivity a WHERE a.timestamp >= :startTimestamp AND a.timestamp < :endTimestamp AND a.state = 'ACTIVE'

  • Secondly, it should append to the list above all readers, which has as last state ACTIVE before start of period. This is what I do not get correctly: I tried (combined into the fist query):
    SELECT DISTINCT a.reader FROM ReaderActivity a WHERE a.timestamp >= :startTimestamp AND a.timestamp < :endTimestamp AND a.state = 'ACTIVE' or (SELECT aa.reader FROM ReaderActivity aa where aa.reader = a.reader AND aa.timestamp < :startTimestamp AND aa.state = 'ACTIVE' GROUP BY aa.reader ORDER BY max(aa.timestamp)) != null

This returns also the red marked reader in the image above because with the
AND aa.state = 'ACTIVE'
in the sub query, we already filter out all ACTIVE state so the last one is of course ACTIVE. I should select the last state before start period and afterwards check, if it is ACTIVE or not. But how?

Can anybody bring me to the right way?
Thanks in advance!

Answer Source

The first part of your query will bring back records whose state changed to active during the time period as you wanted. The second part of the query needs some refining.

To do what you are asking, you are going to have to bring back the most recent state changes for every reader_id up until the moment of "startTimeStamp". From these, you will then simply select the ones that were listed as "ACTIVE" at that time.

Something like..

Select a.reader_id 
From ReaderActivity a 
Where a.timeStamp in
  Select max(b.timeStamp) 
  From ReaderActivity b 
  Where b.timestamp < :startTimeStamp 
  And b.reader_id = a.reader_id 
  Group By b.reader_id
and a.state = "ACTIVE"

This query will return all [reader_id]'s that were active at the start of the specified time period. You can run this inside your original query's where clause or you can just append it to the end of your query with a UNION and run a Select Distinct(reader_id) on that whole mess.

Selecting from the max value of a group in a table is always kind of quirky to me. But I hope this query helps!