過濾和聚合不在 group by 子句 MySQL 中的列

我有下表。我正在使用 mysql-5.7。這是dbfiddle

select * from report;
+----+---------------------+---------+------+------+------------+------+-------------------+
| id | date_hour           | speed   | name | nor  | validation | p_i  | speed_performance |
+----+---------------------+---------+------+------+------------+------+-------------------+
|  1 | 2018-02-03 13:23:00 |   -12.3 | SYN  |   10 | 1          | 3    | 4                 |
|  2 | 2018-02-03 11:23:00 |   -6.36 | SYN  |   13 | 0          | 4    | 5                 |
|  3 | 2018-02-03 01:23:00 |  -26.36 | SYN  |   24 | 0          | 2    | 4                 |
|  4 | 2020-04-06 21:23:00 | -156.36 | SYN  |   16 | 1          | 3    | 6                 |
|  5 | 2020-04-06 03:23:00 |  -36.36 | YRT  |  136 | 0          | 2    | 5                 |
|  6 | 2020-04-06 12:23:00 |  -16.36 | SYN  |   13 | 1          | 4    | 4                 |
+----+---------------------+---------+------+------+------------+------+-------------------+

我想group by date_hour這樣，之後12:00:00，它算作第二天。該p_i列應基於此條件進行平均：取其中average1且非零。這些值應根據新版本的. 此外，另一列 ( ) 在沒有這種條件的情況下進行平均。p_i``validation``p_i``date_hour``nor

輸出應如下所示

+------+------------+----------+------+
| name | date_hr    | avg(nor) | p_i  |
+------+------------+----------+------+
| SYN  | 2018-02-03 |     18.5 | NULL |
| SYN  | 2018-02-04 |       10 |    3 |
| SYN  | 2020-04-07 |     14.5 |  3.5 |
| YRT  | 2020-04-06 |      136 | NULL |
+------+------------+----------+------+

我嘗試了以下查詢。我必須禁用only_full_group_by它才能工作：

select name, 
case when time(date_hour) > '12:00:00' 
then date(date_hour) + interval 1 day 
else date(date_hour) end as date_hr, 
avg(nor), 
case when validation = 1 and p_i > 0 then avg(p_i) else null end as p_i 
from report group by name, date_hr;

+------+------------+----------+------+
| name | date_hr    | avg(nor) | p_i  |
+------+------------+----------+------+
| SYN  | 2018-02-03 |     18.5 |    3 |
| SYN  | 2018-02-04 |       10 | NULL |
| SYN  | 2020-04-07 |     14.5 | NULL |
| YRT  | 2020-04-06 |      136 |    2 |
+------+------------+----------+------+

似乎最容易將 12 小時添加到 date_hour：

select name, DATE(DATE_ADD(date_hour,INTERVAL 12 hour)) as date_hr
    , avg(nor)
    , avg( case when not (validation = 1 and p_i > 0) then p_i end) as p_i
from report
group by name, DATE(DATE_ADD(date_hour,INTERVAL 12 hour))
order by name, DATE(DATE_ADD(date_hour,INTERVAL 12 hour));

小提琴

編輯：做一些實驗讓我意識到什麼可能會導致一些混亂。查看您的驗證列：

`validation` enum('0','1') DEFAULT NULL,

這意味著：

COUNT(1) WHERE validation = 0  => 0
COUNT(1) WHERE validation = 1  => 3
COUNT(1) WHERE validation = 2  => 3

然而

COUNT(1) WHERE validation = '0'  => 3
COUNT(1) WHERE validation = '1'  => 3
COUNT(1) WHERE validation = '2'  => 0

我改寫的查詢是：

select name, DATE(DATE_ADD(date_hour,INTERVAL 12 hour)) as date_hr
    , avg(nor)
    , avg( case when (validation = '1' and p_i <> '0') then p_i end) as p_i
from report
group by name, DATE(DATE_ADD(date_hour,INTERVAL 12 hour))
order by name, DATE(DATE_ADD(date_hour,INTERVAL 12 hour));

請注意，字元串的 avg 可能會產生奇怪的結果。最後，MySQL 中的 ENUM 是一種非常奇怪的結構，我會盡量避免。您可能對以下觀察感興趣：

MariaDB 如何處理 FOREIGN KEY 約束中使用的 ENUM 類型？

更新小提琴

引用自：https://dba.stackexchange.com/questions/270960