Блог

Я хочу вернуть самое последнее действие в расписании, которое имело место, сгруппированное по каналам, но упорядоченное по перечислению. Расписание может иметь параметры «ДЕНЬ НЕДЕЛИ» или определенные дни («СР»). Конкретные дни должны иметь приоритет над опцией «ДЕНЬ НЕДЕЛИ».

 CREATE TABLE `heatingtimetable` (
  `id` int(11) NOT NULL,
  `channel` enum('CENTRALHEATING','HOTWATER') NOT NULL,
   `command` enum('ON','OFF') NOT NULL DEFAULT 'OFF',
  `thetime` time NOT NULL,
  `day` enum('SUN','MON','TUE','WED','THU','FRI','SAT','WEEKDAY','WEEKEND','HOLIDAY') NOT NULL
);


INSERT INTO `heatingtimetable` (`id`, `channel`, `command`, `thetime`, `day`) VALUES
(1, 'CENTRALHEATING', 'ON', '08:00:00', 'WEEKDAY'),
(2, 'CENTRALHEATING', 'OFF', '10:00:00', 'WEEKDAY'),
(13, 'CENTRALHEATING', 'ON', '07:00:00', 'WED'),
(14, 'CENTRALHEATING', 'OFF', '9:00:00', 'WED');
 

https://www.db-fiddle.com/#amp;togetherjs=NEuAL2we4r

Я могу вернуть самый последний — но не заказ/перечисление, так как сначала происходит группировка по — всегда появляется самое последнее время. Предполагая, что это 10.15 утра в среду — это возвращает БУДНИЙ ДЕНЬ, но нужно вернуть строку 14 «СР.9 утра».

 SELECT h.channel, command, h.thetime, day FROM
(SELECT channel, MAX(thetime) as thetime
FROM `heatingtimetable` 
where thetime < '10:15'
AND
(DAYOFWEEK(CURDATE()) >= 2 AND DAYOFWEEK(CURDATE()) <=6 AND day = 'WEEKDAY') || (DAYOFWEEK(CURDATE()) = day)
group by channel) as l
INNER JOIN heatingtimetable h on h.channel = l.channel and h.thetime = l.thetime```
 

Переосмыслите структуру, если сможете

Мне удалось найти решение здесь, ниже, но ничего себе, это не очень красиво. Я бы действительно рекомендовал подумать о том, чтобы структурировать это по-другому, чтобы было проще сравнивать текущий день/время с записями. Надеюсь, у вас есть контроль над структурой таблицы и тому подобное.

Сначала подумайте о том, какой запрос вы хотели бы использовать. Затем вы можете работать оттуда, чтобы найти наилучшую структуру таблицы для удовлетворения таких запросов. Сравнение дат было бы намного удобнее, так как вы можете выполнять математику, включающую как дату, так и время. Кроме того, проблематично смешивать различные типы, такие как СР, ПТ, БУДНИ, ВЫХОДНЫЕ, и использовать их в качестве перечислений без естественного способа ранжирования. Вот почему этот запрос ниже является гигантским. Если бы вы могли избавиться от смешивания разных типов, это помогло бы. Если вы не можете этого сделать, вы, возможно, могли бы добавить новую колонку, которая поможет вам оценить СР выше, чем ДЕНЬ НЕДЕЛИ.

Нам нужен способ ранжирования этих

Теперь я понимаю, что важнее СР по сравнению с ДНЯМИ НЕДЕЛИ. В этом случае мы можем сделать это, создав собственное значение рейтинга. Мы хотим ранжировать соответствующий ДЕНЬ НЕДЕЛИ как более высокий, чем соответствующий ДЕНЬ НЕДЕЛИ/ВЫХОДНЫЕ и т. Д. И затем вы также хотите ранжировать по времени с более низким приоритетом, чем у них. Таким образом, мы можем упорядочить эти 3 разные вещи в таком порядке.

CASE WHEN/THEN/ELSE comes in handy for conditionals.

There are MANY situations to consider here

Кстати, ваш запрос не возвращает самое последнее, как вы сказали в некоторых ситуациях. Если сегодня суббота, то DAYOFWEEK(CURDATE()) будет 7, и у вас не будет возвращенных строк. Вероятно, вы хотите, чтобы строка 2 была возвращена, так как она будет самой последней. Ваш запрос также никоим образом не учитывал выходные дни.

Что делать, если текущий день совпадает с днем записи в таблице, но текущее время после времени записи в таблице? Мы не должны считать, что эта запись соответствует дню. Поэтому мы должны включить проверку времени в эти значения рейтинга.

Добавим к этому — Что, если сегодня пятница, и это не совпадает с днем каких-либо записей… но у вас есть одна запись на БУДНИЙ день и одна на ЧТ? Что было бы предпочтительнее в качестве самого последнего? Я предполагаю, что вы хотели бы, чтобы ЧТ был предпочтительнее БУДНЕГО ДНЯ, например, если это один и тот же день. И если бы выбор был между свадьбой и БУДНИМ ДНЕМ, то БУДНИЙ ДЕНЬ был бы предпочтительнее, потому что четверг-более поздний будний день, чем СР.

Это становится довольно сложным.

ВРЕМЯ КУРТИЗА()

Кроме того, я заметил, что вы указали текущий день в SQL с помощью CURDATE (), но для того времени вы просто ввели постоянное значение «10:15». Я не уверен, откуда это взялось, но если вы хотите сделать это и в SQL, это будет просто — CURTIME(). Вы также можете получить дату и время вместе с NOW().

Настройка подсчета очков

Я создал следующие значения рейтинга:

day_score 1 — Это будет 1, если день недели точно совпадает (например, СР/среда), а время ввода таблицы раньше текущего времени, в противном случае 0.

day_score 2 — Это будет 1, если текущий день совпадает с БУДНИМ ДНЕМ/ВЫХОДНЫМИ, а время ввода таблицы раньше текущего времени, в противном случае 0

day_score 3 — Это значение будет использоваться для перехода назад по дням, и чем более поздний день, тем выше оценка. Здесь мы должны использовать субракцию, и мы также должны обернуться. Большая проблема здесь заключается в том, что невозможно получить номер индекса дня недели из ваших значений дня, таких как СР. Функция DAYOFWEEK() примет дату и вернет номер индекса дня недели, но обратного хода нет. Поэтому нам придется проверять каждый возможный день недели. Нам не нужно будет учитывать время в этом, так как мы знаем, что по крайней мере на один день мы возвращаемся назад.

И если мы все еще хотим, чтобы у СВАДЬБЫ был более высокий приоритет, чем в БУДНИЙ день, в этой ситуации, когда мы должны оглядываться назад, должно быть еще больше строк для подсчета очков.

thetime — И, наконец, у меня есть заказ также по убыванию времени, потому что таким образом, если нам придется вернуться к предыдущему дню, и с этим днем будет более одной записи, на первое место будет поставлена последняя.

Я вижу, у вас там тоже КАНИКУЛЫ, но я проигнорировал это. Это уже достаточно сложно, и я не буду кусаться на этом.

Вот моя скрипка DB — https://www.db-fiddle.com/f/wgSzEiWrDgAJuc88de1Fwq/0

СОЗДАЙТЕ ТАБЛИЦУ и вставки ниже-

 CREATE TABLE `heatingtimetable` (
  `id` int(11) NOT NULL,
  `channel` enum('CENTRALHEATING','HOTWATER') NOT NULL,
   `command` enum('ON','OFF') NOT NULL DEFAULT 'OFF',
  `thetime` time NOT NULL,
  `day` enum('SUN','MON','TUE','WED','THU','FRI','SAT','WEEKDAY','WEEKEND','HOLIDAY') NOT NULL
);

INSERT INTO `heatingtimetable` (`id`, `channel`, `command`, `thetime`, `day`) VALUES
(1, 'CENTRALHEATING', 'ON', '08:00:00', 'WEEKDAY'),
(2, 'CENTRALHEATING', 'OFF', '10:00:00', 'WEEKDAY'),
(3, 'CENTRALHEATING', 'OFF', '22:00:00', 'WEEKDAY'),
(4, 'CENTRALHEATING', 'ON', '05:00:00', 'MON'),
(5, 'CENTRALHEATING', 'OFF', '16:00:00', 'TUE'),
(6, 'CENTRALHEATING', 'OFF', '23:00:00', 'THU'),
(7, 'CENTRALHEATING', 'OFF', '6:00:00', 'THU'),
(8, 'CENTRALHEATING', 'ON', '07:00:00', 'FRI'),
(9, 'CENTRALHEATING', 'OFF', '21:00:00', 'FRI'),
(13, 'CENTRALHEATING', 'ON', '07:00:00', 'WED'),
(14, 'CENTRALHEATING', 'OFF', '9:00:00', 'WED');
 

И этот гигантский запрос-

 SELECT 

channel, command, thetime, day, 

CASE WHEN (DAYOFWEEK(CURDATE()) = day AND thetime <= CURTIME()) THEN 1 ELSE 0 END AS day_score1, 
                    
CASE WHEN 
(
    (
    (DAYOFWEEK(CURDATE()) >= 2 AND DAYOFWEEK(CURDATE()) <= 6 AND day = 'WEEKDAY') 
    OR 
    ((DAYOFWEEK(CURDATE()) = 1 OR DAYOFWEEK(CURDATE()) = 7) AND day = 'WEEKEND')
    )
    AND thetime <= CURTIME()
) THEN 1 ELSE 0 END as day_score2, 

CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 6 AND day = 'SAT' THEN 12 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 5 AND day = 'SUN' THEN 12 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 4 AND day = 'MON' THEN 12 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 3 AND day = 'TUE'  THEN 12 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 2 AND day = 'WED' THEN 12 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 1 AND day = 'THU' THEN 12 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 0 AND day = 'FRI' THEN 12 ELSE 0 END 
 
CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 6 AND day = 'FRI' THEN 10 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 5 AND day = 'SAT' THEN 10 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 4 AND day = 'SUN' THEN 10 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 3 AND day = 'MON' THEN 10 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 2 AND day = 'TUE' THEN 10 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 1 AND day = 'WED' THEN 10 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 0 AND day = 'THU' THEN 10 ELSE 0 END  
 
CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 6 AND day = 'THU' THEN 8 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 5 AND day = 'FRI' THEN 8 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 4 AND day = 'SAT' THEN 8 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 3 AND day = 'SUN' THEN 8 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 2 AND day = 'MON' THEN 8 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 1 AND day = 'TUE' THEN 8 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 0 AND day = 'WED' THEN 8 ELSE 0 END  
 
CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 6 AND day = 'WED' THEN 6 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 5 AND day = 'THU' THEN 6 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 4 AND day = 'FRI' THEN 6 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 3 AND day = 'SAT' THEN 6 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 2 AND day = 'SUN' THEN 6 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 1 AND day = 'MON' THEN 6 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 0 AND day = 'TUE' THEN 6 ELSE 0 END  
 
CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 6 AND day = 'TUE' THEN 4 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 5 AND day = 'WED' THEN 4 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 4 AND day = 'THU' THEN 4 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 3 AND day = 'FRI' THEN 4 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 2 AND day = 'SAT' THEN 4 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 1 AND day = 'SUN' THEN 4 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 0 AND day = 'MON' THEN 4 ELSE 0 END  
 
CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 6 AND day = 'MON' THEN 2 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 5 AND day = 'TUE' THEN 2 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 4 AND day = 'WED' THEN 2 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 3 AND day = 'THU' THEN 2 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 2 AND day = 'FRI' THEN 2 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 1 AND day = 'SAT' THEN 2 ELSE 0 END 
  CASE WHEN (7 - DAYOFWEEK(CURDATE())) = 0 AND day = 'SUN' THEN 2 ELSE 0 END  

  
CASE 
    WHEN (7 - DAYOFWEEK(CURDATE())) <= 4 AND day = 'WEEKDAY' THEN 11 
    WHEN (7 - DAYOFWEEK(CURDATE())) >= 5 AND day = 'WEEKEND' THEN 11 
    WHEN (7 - DAYOFWEEK(CURDATE()) >= 6 OR 7 - DAYOFWEEK(CURDATE()) <= 3) AND day = 'WEEKDAY' THEN 9 
    WHEN (7 - DAYOFWEEK(CURDATE()) = 5 OR 7 - DAYOFWEEK(CURDATE()) = 4) AND day = 'WEEKEND' THEN 9 
    WHEN (7 - DAYOFWEEK(CURDATE()) >= 5 OR 7 - DAYOFWEEK(CURDATE()) <= 2) AND day = 'WEEKDAY' THEN 7 
    WHEN (7 - DAYOFWEEK(CURDATE()) = 4 OR 7 - DAYOFWEEK(CURDATE()) = 3) AND day = 'WEEKEND' THEN 7 
    WHEN (7 - DAYOFWEEK(CURDATE()) >= 4 OR 7 - DAYOFWEEK(CURDATE()) <= 1) AND day = 'WEEKDAY' THEN 5 
    WHEN (7 - DAYOFWEEK(CURDATE()) = 3 OR 7 - DAYOFWEEK(CURDATE()) = 2) AND day = 'WEEKEND' THEN 5 
    WHEN (7 - DAYOFWEEK(CURDATE()) >= 3 OR 7 - DAYOFWEEK(CURDATE()) <= 0) AND day = 'WEEKDAY' THEN 3 
    WHEN (7 - DAYOFWEEK(CURDATE()) = 2 OR 7 - DAYOFWEEK(CURDATE()) = 1) AND day = 'WEEKEND' THEN 3 
    WHEN (7 - DAYOFWEEK(CURDATE()) >= 2) AND day = 'WEEKDAY' THEN 1 
    WHEN (7 - DAYOFWEEK(CURDATE()) <= 1) AND day = 'WEEKEND' THEN 1 
    ELSE 0 
END 

AS day_score3

FROM heatingtimetable 

ORDER BY day_score1 DESC, day_score2 DESC, day_score3 DESC, thetime DESC
 

Мой результат прямо сейчас приведен ниже. Сегодня пятница, и текущее время в DB-Fiddle показывает 20:41:40.