Mysql查询在多列上输出的多个select语句

Question

表格

+------+-------+------------------+
|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 |

我是mysql的新手，在这个查询上遇到了麻烦。

我只有一个表，名为" table“，有三列。

这个表每天记录一组特定客户端A，B，C，D，E，F的不同时间的数据。

对于一个查询，我需要为每个客户端创建一个一行的新表，并包含以下4列：

1. 第一列应包含表中记录的每个客户端的最新值。
2. 第二列应包含最后24小时内每个客户端的值等于1的时间百分比。
3. 第三列应包含最后7天内每个客户的值等于1的时间百分比。
4. 如前一栏所示，但在过去30天内

我希望有人能帮我。

我想要的是：

+------+-------------+-----------+--------------+--------------+
|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%    |     ...      |

这段代码可以很好地创建"NEWST VALUE“列。

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

这一列创建了“最后24小时”列

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

但是我不能把所有的输出放在一个新的表中

Answer 1

您可以使用条件聚合。假设是8.0前的MySQL，只有最近的值才是非常棘手的。以下是一种方法：

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;

请注意，此逻辑使用MySQL扩展，其中布尔值在数字上下文中被视为数字，1表示true，0表示false。

平均值为所讨论的时间段生成"0“或"1”，任何其他记录的NULL值。avg()函数忽略NULL值。