I'm not exactly sure how to phrase the title. I have a query that I cannot figure out:

I have a table 'values' with timestamps (1970 epoch decimal) and a blob for each row. I have a second table called 'keys' that contains a timestamps and keys to decrypt each of the blobs in the first table 'values'. The key changes periodically at random intervals and each time the key changes, a new set of keys are written to the 'keys' table. There are multiple keys and when a new key set is written to the 'keys' table, each key has a separate entry with the same timestamp.

if I do something like this:

select distinct timestamp from keys;

I get a set returned for every time the keys rotated and I wrote a new keyset into the database.

What I would like is a sql statement in mysql that returns timestamps for each keyset and the total number of records in the 'values' table between each of those key timestamps.

For instance:

etc...

The last row needs special consideration since its the "current" set doesn't have another entry in the keytable (yet..)

SQL Fiddle with Sample Data: SQL FIDDLE WITH SAMPLE DATA

For the sample data above, the results should be: | Timestamp | Count | | --------- | ----- | | 1635962134 | 14| | 1636043734 | 28| | 1636119328 | 11|

CodePudding user response：

You are a little limited by the mySQL version, but you can use a variable to help create the row set. You could do it with joins, too, but it would be a little more complicated.

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=b5d587b30f1a758ce31e3fa4745f26d0

SELECT k.key1, k.key2, count(*) as vol
FROM my_values v
JOIN (
  SELECT key_ts as key1, @curr-1 as key2, @curr:= key_ts
  FROM (
    SELECT DISTINCT key_ts FROM my_keys 
    JOIN (SELECT @curr:=9999999999) var
    ORDER BY key_ts DESC
  ) z
) k ON (v.val_ts BETWEEN k.key1 and k.key2)
GROUP BY key1, key2

First (the innermost subquery) select the distinct timestamps from my_keys and order them. I use that join just to set the variable. You could use a SET statement in a separate query also. I set the variable to an arbitrarily high timestamp, so that the last timestamp in the series will always have a partner.

Select from that the key and the variable value minus 1 (to prevent overlap), and then after that set the variable to the current key. That has to be done after everything else in the select query. That will generate two paired timestamps representing a time range.

Then just join my_values to those keys, and use BETWEEN as the join condition.

Let me know if that works for you.