Geek Answers Handbook

SQL "First Matching Day"

I have a table with opening_hoursfor restaurants:

SELECT * FROM opening_hours;
+----+---------------+------------+----------+-----+
| id | restaurant_id | start_time | end_time | day |
+----+---------------+------------+----------+-----+
|  1 |             1 | 12:00:00   | 18:00:00 |   1 |
|  2 |             1 | 09:00:00   | 19:00:00 |   4 |
|  3 |             2 | 09:00:00   | 16:00:00 |   4 |
|  4 |             2 | 09:00:00   | 16:00:00 |   5 |
|  5 |             3 | 09:00:00   | 16:00:00 |   4 |
|  6 |             3 | 09:00:00   | 16:00:00 |   5 |
|  7 |             3 | 09:00:00   | 16:00:00 |   1 |
|  8 |             3 | 09:00:00   | 16:00:00 |   6 |
+----+---------------+------------+----------+-----+

http://www.sqlfiddle.com/#!2/eaea09/1

Now I want to get the β€œclosest” the next day or the same day for each restaurant on the current day. For example, if the current day 1, the result would be:

restaurant_id: 1 day: 1
restaurant_id: 2 day: 4
restaurant_id: 3 day: 1

In the case of the day, 1I could do this:

SELECT day FROM opening_hours WHERE day >= 1 GROUP BY restaurant_id LIMIT 1

But if today is 6, it will not work. I will need a query to get the maximum number of days ( 7), and if this could not be found, it should start again with 1. Thus, the result for the day 6will be in this case:

restaurant_id: 1 day: 1
restaurant_id: 2 day: 4
restaurant_id: 3 day: 6

How can I achieve this with a query?

I think it could be something like this in pseudo SQL:

SELECT `day` FROM opening_hours WHERE `day` >= 'today' IF NOT FOUND WHERE `day` >= 1 GROUP BY `restaurant_id` LIMIT 1

edit:

2 , . , . .

+4
4

, :) .

Query:

SELECT * FROM (
    SELECT
    oh.*
    FROM
    opening_hours oh
    ORDER BY restaurant_id, 
    `day` + IF(`day` < $current_day, 7, 0)
) sq
GROUP BY restaurant_id;

:

, . , group by , , , . . MySQL , , ( sql-). , . , order by , MySQL ( ).

:

​​= 1:

root@VM:playground > SELECT * FROM (
    ->     SELECT
    ->     oh.*
    ->     FROM
    ->     opening_hours oh
    ->     ORDER BY restaurant_id,
    ->     `day` + IF(`day` < 1, 7, 0)
    -> ) sq
    -> GROUP BY restaurant_id;
+----+---------------+------------+----------+-----+
| id | restaurant_id | start_time | end_time | day |
+----+---------------+------------+----------+-----+
|  1 |             1 | 12:00:00   | 18:00:00 |   1 |
|  3 |             2 | 09:00:00   | 16:00:00 |   4 |
|  7 |             3 | 09:00:00   | 16:00:00 |   1 |
+----+---------------+------------+----------+-----+
3 rows in set (0.00 sec)

​​= 6:

root@VM:playground > SELECT * FROM (
    ->     SELECT
    ->     oh.*
    ->     FROM
    ->     opening_hours oh
    ->     ORDER BY restaurant_id,
    ->     `day` + IF(`day` < 6, 7, 0)
    -> ) sq
    -> GROUP BY restaurant_id;
+----+---------------+------------+----------+-----+
| id | restaurant_id | start_time | end_time | day |
+----+---------------+------------+----------+-----+
|  1 |             1 | 12:00:00   | 18:00:00 |   1 |
|  3 |             2 | 09:00:00   | 16:00:00 |   4 |
|  8 |             3 | 09:00:00   | 16:00:00 |   6 |
+----+---------------+------------+----------+-----+
3 rows in set (0.00 sec)
+2

- :

SELECT oh1.*
-- set the start day
FROM (SELECT @start := 1) AS start,
-- calculate difference in days
     (SELECT *, (CASE WHEN day-@start >= 0 THEN day-@start ELSE day-@start+7 END) AS diff
      FROM opening_hours) AS oh1
-- find minimum difference
JOIN (SELECT restaurant_id, MIN(CASE WHEN day-@start >= 0 THEN day-@start ELSE day-@start+7 END) AS min_diff
      FROM opening_hours
      GROUP BY restaurant_id) AS oh2
  ON oh1.restaurant_id = oh2.restaurant_id AND
     oh1.diff = oh2.min_diff

@start := 1 DAYOFWEEK(CURDATE()), , .

+1

.

, , - , , , , .

SELECT sub0.restaurant_id, MIN(sub1.day)
FROM
(
    SELECT restaurant_id, MIN( LEAST(ABS(DAYOFWEEK(CURDATE()) - day), ABS(DAYOFWEEK(CURDATE()) - (day + 7)), ABS(DAYOFWEEK(CURDATE()) - (day - 7)))) AS difference
    FROM opening_hours 
    GROUP BY restaurant_id
) sub0
INNER JOIN
(
    SELECT restaurant_id, day,  LEAST(ABS(DAYOFWEEK(CURDATE()) - day), ABS(DAYOFWEEK(CURDATE()) - (day + 7)), ABS(DAYOFWEEK(CURDATE()) - (day - 7))) AS difference
    FROM opening_hours 
) sub1
ON sub0.restaurant_id = sub1.restaurant_id
AND sub0.difference = sub1.difference
GROUP BY sub0.restaurant_id

- ​​ ​​ . , 7 7, , ABS, LEAST, . , 1, 6, 1 + 7 6, 1-7 6 1 6 ( 1 + 7).

/, .

An external query uses MIN only to select one day when 2 is as close as possible.

+1
source

The first step is simply to add 7 to the result of calculating the difference in the day when the day is less than the day you are looking for:

SET @Day = 6;
SELECT  ID, Restaurant_id, Day,
        CASE WHEN Day < @Day THEN 7 ELSE 0 END + Day - @Day AS DaysFromNow
FROM    opening_hours;

This will give:

ID  RESTAURANT_ID   DAY     DAYSFROMNOW
1   1               1       2
2   1               4       5
3   2               4       5
4   2               5       6
5   3               4       5
6   3               5       6
7   3               1       2
8   3               6       0

Then, to get the next holiday, you need to get a minimum DaysFromNowfor each restaurant, and then join the main table:

SET @Day = 6;
SELECT  o.*
FROM    opening_hours AS o
        INNER JOIN
        (   SELECT  Restaurant_id, 
                    MIN(CASE WHEN Day < @Day THEN 7 ELSE 0 END + Day - @Day) AS DaysFromNow
            FROM    opening_hours
            GROUP BY Restaurant_id
        ) AS mo
            ON mo.Restaurant_id = o.Restaurant_id
            AND mo.DaysFromNow = (CASE WHEN Day < @Day THEN 7 ELSE 0 END + Day - @Day);

SQL script example

+1
source

More articles:


All Articles