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:
- Takes each of the
slugs in thedevicestable - Check the count of records on each
cpu,ramandgpufor each of theslugs - Gets the first and last matching records in these tables (by
time/dateorlog_time, respectively) - Calculate the difference in days between the first record and the last record
- 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
CodePudding user response：
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
;