Mysql query multiple select statements output on multiple columns

Question

Table

+------+-------+------------------+
|CLIENT| VALUE |    DATETIME      |
+------+-------+------------------+
|  A   |  1    | 2018-11-10 09:00 |
|  B   |  1    | 2018-11-10 09:00 |
|  C   |  1    | 2018-11-10 09:00 |
|  D   |  2    | 2018-11-10 08:00 |
|  E   |  2    | 2018-11-10 08:00 |
|  F   |  3    | 2018-11-10 08:00 |
|  A   |  1    | 2018-11-10 07:00 |
|  B   |  2    | 2018-11-10 07:00 |
|  C   |  2    | 2018-11-10 07:00 |
|  D   |  3    | 2018-11-10 06:00 |
|  E   |  1    | 2018-11-10 06:00 |
|  F   |  2    | 2018-11-10 06:00 |
|  A   |  1    | 2018-11-08 08:00 |
|  B   |  2    | 2018-11-08 08:00 |
|  C   |  2    | 2018-11-08 08:00 |
|  D   |  1    | 2018-11-08 08:00 |
|  E   |  1    | 2018-11-08 07:00 |
|  F   |  2    | 2018-11-08 07:00 |

I'm newbie to mysql, and I'm in trouble with this query.

I have only one table named "table" with three columns.

This table records many data every day at different time from a specific set of client A,B,C,D,E,F

With one query I need to create new table with one row for each client and with the following 4 columns:

first column should contain the newest value recordered in the table for each client
second column should contain the percentage of time the value is equal to 1 for each client during the last 24 hours
third column should contain the percentage of time the value is equal to 1 for each client during the last 7 days
as previous column but during the last 30 days

I hope someone can help me.

What I would like to receive:

+------+-------------+-----------+--------------+--------------+
|CLIENT| NEWEST VALUE| LAST 24 H | LAST 7 DAYS  | LAST 30 DAYS |
+------+-------------+-----------+--------------+--------------+
|  A   |       1     |    100%   |      100%    |     ...      | 
|  B   |       1     |     50%   |       66%    |     ...      |
|  C   |       1     |     50%   |       33%    |     ...      |
|  D   |       2     |      0%   |       33%    |     ...      |
|  E   |       2     |     50%   |       66%    |     ...      |
|  F   |       3     |      0%   |        0%    |     ...      |

This piece of code works fine to create the "NEWST VALUE" column

SELECT
    client,
    value,
    max(datetime)
FROM
    table
GROUP BY
    client;

and this one create the "LAST 24 H" column

SELECT
    client,
    count(if(value = 1,1, null))/count(value),
FROM
    table
WHERE
    date(datetime) < CURRENT_DATE() - interval 1 day
GROUP BY
    repository_name;

but I'm not able to put all the output together in one new table

user_1144035 · Answer

You can use conditional aggregation. Assuming pre-8.0 MySQL, only the most recent value is really tricky. Here is one approach:

select t.client,
       max(case when t.datetime = c.maxdt then t.value end) as most_recent_value,
       avg(case when t.datetime >= now() - interval 1 day
                then (t.value = 1)
           end) as last_day_percentage,
       avg(case when t.datetime >= now() - interval 7 day
                then (t.value = 1)
           end) as last_7day_percentage,
       avg(case when t.datetime >= now() - interval 30 day
                then (value = 1)
           end) as last_30day_percentage                
from t join
     (select t.client, max(t.datetime) as maxdt
      from t
      group by t.client
     ) c
     on c.client = t.client
group by t.client;

Note that this logic uses a MySQL extension where boolean values are treated as numbers in a numeric context, with 1 for true and 0 for false.

The average produces "0" or "1" for the time period in question, with NULL values for any other record. The avg() function ignores NULL values.

Mysql query multiple select statements output on multiple columns

1 Answers