Ndtyjky

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.