0

I have the following table. I'm using mysql-5.7. Here is the 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                 |
+----+---------------------+---------+------+------+------------+------+-------------------+

I would like to group by date_hour such that after 12:00:00, it counts as the next day. The p_i column should be averaged based on this condition: take average of p_i where validation is 1 and p_i is non-zero. The values should be averaged based on the new version of date_hour. Additionally, another column (nor) is averaged without such a condition.

The output should look like this

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

I tried the following query. I had to disable only_full_group_by to get it to work:

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 |
+------+------------+----------+------+
  • I had to disable only_full_group_by to get it to work: - a query which runs under that condition is a broken query - there's a reason why only_full_group_by is now the default in MySQL! Can you please provide your table structures as DDL and your data as DML - better yet, put them into a fiddle (but be sure to keep them here also!). p.s. welcome to the forum! – Vérace Jul 15 '20 at 5:43
  • Hi, thank you. I have provided a fiddle. Please look at the last line before the tags. – spheroid Jul 15 '20 at 5:55
  • I put your fiddle into dbfiddle.uk - the fiddle of choice here! Perhaps you might like to replace your one with this one? – Vérace Jul 15 '20 at 6:08
  • Ok thanks! I have replaced it. – spheroid Jul 15 '20 at 6:15
  • See my comment to Lennart's answer! p.s. welcome to the forum! – Vérace Jul 15 '20 at 19:35
0

Seems easiest to just add 12 hours to 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));

Fiddle

EDIT: Doing some experiments made me realize what might cause some confusion. Look at your validation column:

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

This means that:

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

Whereas

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

My rephrased query would be:

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));

Beware that avg of a string may give weird results. Finally, ENUM in MySQL is a very weird construction that I would try to avoid. You may be interested in the following observation:

How does MariaDB handle ENUM types used in FOREIGN KEY constraints?

Updated Fiddle

  • I apologize if this is very stupid, but I don't understand case when not (validation = 1 and p_i > 0)? It seems to be the opposite of what I would like to do, but clearly works. @nbk suggested the same thing, so clearly there's something I do not know. – spheroid Jul 15 '20 at 10:39
  • To be honest, when trying to implement your description I got different results than the expected output as stated in your question. @nbk's result matched the expected output, so I copied his expression and rephrased it a bit. – Lennart Jul 15 '20 at 10:43
  • select validation, p_i, case when not (validation = 1 and p_i > 0) then p_i end from ( select 0 as validation, 4 as p_i union all select 0, 2 ) as t; should give 4 and 2 for p_i. Is that what you meant by x? So this expression seems to give the correct result. – spheroid Jul 15 '20 at 11:03
  • @spheroid, I think I understand the origin of the confusion now. I have updated my answer, does that help? – Lennart Jul 15 '20 at 12:14
  • This is it, thanks! – spheroid Jul 15 '20 at 12:19
0

I don't claim to understand what you are doing, but your logic for p_i is wrong. To get your wanted result:

SELECT 
    name, date_hr, AVG(nor), AVG(p_i)
FROM
    (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,
            nor,
            validation,
            CASE
                WHEN validation = 1 AND p_i > 0 THEN NULL
                ELSE p_i
            END AS p_i
    FROM
        report) z1
GROUP BY name , date_hr;
name | date_hr    | AVG(nor) | AVG(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

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.