Odtnhj

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:

1. first column should contain the newest value recordered in the table for each client


2. second column should contain the percentage of time the value is equal to 1 for each client during the last 24 hours


3. third column should contain the percentage of time the value is equal to 1 for each client during the last 7 days


4. 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

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.