mysqlsqldatabasemultiple-tables

Querying multiple MySQL tables based on distinct values, and return the matches count and time difference between first and last record

Question Summary

I need to select all distinct values of column slug in a table, then go through other multiple tables, and for each count the records where slug appears, as well as find the difference in days between the first and last appearance.

Example Data

Assume a table that contains references to items, and additional tables that contains timed records for each of these items. Let's use the example of devices coupled with records for CPU, RAM & GPU usage metrics at different times, based on each device.

+---------------------------+-----------------------------+-----------------------------+---------------------------------+
| Table `devices`           | Table `cpu`                 | Table `ram`                 | Table `gpu`                     |
+---------------------------+-----------------------------+-----------------------------+---------------------------------+
| slug (varchar, prim. key) | slug (varchar, prim. key)   | slug (varchar, prim. key)   | slug (varchar, prim. key)       |
+---------------------------+-----------------------------+-----------------------------+---------------------------------+
| created (timestamp)       | time (timestamp, prim. key) | date (timestamp, prim. key) | log_time (timestamp, prim. key) |
+---------------------------+-----------------------------+-----------------------------+---------------------------------+
| 30d_users (int)           | cpu_use (float)             | ram_use (float)             | gpu_use (float)                 |
+---------------------------+-----------------------------+-----------------------------+---------------------------------+
| 7d_users                  |                             |                             |                                 |
+---------------------------+-----------------------------+-----------------------------+---------------------------------+

For the sake of the example, let's populate some values onto these:

+---------+---------------------+-----------+----------+
| slug    | created             | 30d_users | 7d_users |
+---------+---------------------+-----------+----------+
| desktop | 2021-02-18 05:10:04 | 1982      | 713      |
+---------+---------------------+-----------+----------+
| laptop  | 2021-02-16 05:10:04 | 1783      | 449      |
+---------+---------------------+-----------+----------+
| tablet  | 2021-02-19 05:10:04 | 119       | 8        |
+---------+---------------------+-----------+----------+
| phone   | 2021-02-27 05:10:04 | 2263      | 1567     |
+---------+---------------------+-----------+----------+

+-----------------------------------------+---+-----------------------------------------+---+-----------------------------------------+
|                CPU Table                | • |                RAM Table                | • |                GPU Table                |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| slug    | time                | cpu_use | • | slug    | date                | ram_use | • | slug    | log_time            | gpu_use |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| tablet  | 2021-03-14 05:10:06 | 72      | • | desktop | 2021-03-14 05:10:06 | 57      | • | phone   | 2021-03-14 05:10:06 | 64      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| phone   | 2021-03-14 05:10:07 | 33      | • | laptop  | 2021-03-14 05:10:07 | 84      | • | desktop | 2021-03-14 05:10:07 | 48      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| laptop  | 2021-03-15 05:10:04 | 93      | • | tablet  | 2021-03-14 05:10:04 | 31      | • | laptop  | 2021-03-15 05:10:04 | 51      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-16 05:10:05 | 31      | • | phone   | 2021-03-14 05:10:05 | 64      | • | desktop | 2021-03-15 05:10:05 | 29      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| tablet  | 2021-03-16 05:10:05 | 47      | • | desktop | 2021-03-16 05:10:05 | 90      | • | phone   | 2021-03-15 05:10:05 | 82      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| phone   | 2021-03-16 05:10:06 | 37      | • | tablet  | 2021-03-16 05:10:06 | 84      | • | phone   | 2021-03-16 05:10:06 | 71      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| laptop  | 2021-03-16 05:10:07 | 28      | • | laptop  | 2021-03-16 05:10:07 | 98      | • | laptop  | 2021-03-16 05:10:07 | 76      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| phone   | 2021-03-17 05:10:06 | 94      | • | desktop | 2021-03-17 05:10:06 | 28      | • | phone   | 2021-03-17 05:10:06 | 79      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-17 05:10:07 | 87      | • | phone   | 2021-03-17 05:10:07 | 17      | • | desktop | 2021-03-17 05:10:07 | 34      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| tablet  | 2021-03-17 05:10:08 | 93      | • | tablet  | 2021-03-17 05:10:08 | 67      | • | tablet  | 2021-03-17 05:10:08 | 38      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| laptop  | 2021-03-17 05:10:09 | 54      | • | laptop  | 2021-03-17 05:10:09 | 96      | • | laptop  | 2021-03-17 05:10:09 | 95      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-18 05:10:07 | 21      | • | tablet  | 2021-03-18 05:10:07 | 50      | • | tablet  | 2021-03-18 05:10:07 | 32      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| laptop  | 2021-03-18 05:10:08 | 17      | • | laptop  | 2021-03-18 05:10:08 | 30      | • | laptop  | 2021-03-18 05:10:08 | 27      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| tablet  | 2021-03-19 05:10:08 | 42      | • | tablet  | 2021-03-19 05:10:08 | 79      | • | tablet  | 2021-03-19 05:10:08 | 26      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| phone   | 2021-03-19 05:10:09 | 30      | • | phone   | 2021-03-19 05:10:09 | 80      | • | tablet  | 2021-03-19 05:10:09 | 64      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-19 05:10:10 | 81      | • | desktop | 2021-03-19 05:10:10 | 60      | • | desktop | 2021-03-19 05:10:10 | 91      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| laptop  | 2021-03-19 05:10:11 | 63      | • | laptop  | 2021-03-19 05:10:11 | 71      | • | laptop  | 2021-03-19 05:10:11 | 67      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| laptop  | 2021-03-20 05:10:09 | 93      | • | laptop  | 2021-03-20 05:10:09 | 95      | • | laptop  | 2021-03-20 05:10:09 | 95      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-20 05:10:10 | 76      | • | phone   | 2021-03-20 05:10:10 | 40      | • | phone   | 2021-03-20 05:10:10 | 37      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| tablet  | 2021-03-20 05:10:11 | 87      | • | tablet  | 2021-03-20 05:10:11 | 61      | • | tablet  | 2021-03-20 05:10:11 | 69      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-21 05:10:10 | 92      | • | desktop | 2021-03-21 05:10:10 | 45      | • | desktop | 2021-03-21 05:10:10 | 80      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| phone   | 2021-03-22 05:10:11 | 67      | • | phone   | 2021-03-22 05:10:11 | 54      | • | phone   | 2021-03-24 05:10:11 | 48      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+
| desktop | 2021-03-22 05:10:12 | 47      | • | laptop  | 2021-03-26 05:10:12 | 90      | • | tablet  | 2021-03-29 05:10:12 | 22      |
+---------+---------------------+---------+---+---------+---------------------+---------+---+---------+---------------------+---------+

The Desired Outcome

Now, say I want to run a query that gets a summary by:

  1. Takes each of the slugs in the devices table
  2. Check the count of records on each cpu, ram and gpu for each of the slugs
  3. Gets the first and last matching records in these tables (by time / date or log_time, respectively)
  4. Calculate the difference in days between the first record and the last record
  5. Returns the results with a structure of slug - table_name_diff - table_name_count (times the number of queried tables, 3 in the case of the example above)

For instance, taking the example data above, the result would be:

+---------+-----------+----------+-----------+----------+-----------+----------+
| slug    | cpu_count | cpu_diff | ram_count | ram_diff | gpu_count | gpu_diff |
+---------+-----------+----------+-----------+----------+-----------+----------+
| desktop | 7         | 6        | 5         | 7        | 5         | 7        |
+---------+-----------+----------+-----------+----------+-----------+----------+
| laptop  | 6         | 5        | 7         | 10       | 6         | 5        |
+---------+-----------+----------+-----------+----------+-----------+----------+
| tablet  | 5         | 6        | 6         | 6        | 6         | 12       |
+---------+-----------+----------+-----------+----------+-----------+----------+
| phone   | 5         | 8        | 5         | 8        | 6         | 10       |
+---------+-----------+----------+-----------+----------+-----------+----------+

I managed to achieve this for a singular table only, (but not for multiple tables, and without taking the slug values from the devices table), by querying:

SELECT DISTINCT slug, DATEDIFF(MAX(time), MIN(time)) as cpu_diff, COUNT(*) 
FROM cpu 
GROUP BY slug 
ORDER BY `cpu_diff` DESC
Solution

  • You're on the right track!

    Consider building from your existing approach:

    WITH cpu_summary AS (
  SELECT slug
       , DateDiff(dd, Max(time), Min(time) AS cpu_diff
       , Count(*) AS cpu_count
  FROM   cpu
  GROUP
      BY slug
)
, ram_summary AS (
  <a very similar looking query to the above one, but on the ram table>
)
, gpu_summary AS (
  <take a guess ;-)>
)
SELECT devices.slug
     , cpu_summary.cpu_diff
     , cpu_summary.cpu_count
     , ram_summary.ram_diff
     , ram_summary.ram_count
     , gpu_summary.gpu_diff
     , gpu_summary.gpu_count
FROM   devices
 LEFT
  JOIN cpu_summary
    ON cpu_summary.slug = devices.slug
 LEFT
  JOIN ram_summary
    ON ram_summary.slug = devices.slug
 LEFT
  JOIN gpu_summary
    ON gpu_summary.slug = devices.slug
;