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 |
+------+------------+----------+------+
6
  • 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, 2020 at 5:43
  • Hi, thank you. I have provided a fiddle. Please look at the last line before the tags.
    – spheroid
    Jul 15, 2020 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, 2020 at 6:08
  • Ok thanks! I have replaced it.
    – spheroid
    Jul 15, 2020 at 6:15
  • See my comment to Lennart's answer! p.s. welcome to the forum!
    – Vérace
    Jul 15, 2020 at 19:35

2 Answers 2

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

10
  • 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, 2020 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. Jul 15, 2020 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, 2020 at 11:03
  • @spheroid, I think I understand the origin of the confusion now. I have updated my answer, does that help? Jul 15, 2020 at 12:14
  • This is it, thanks!
    – spheroid
    Jul 15, 2020 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 and acknowledge that you have read and understand our privacy policy and code of conduct.

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