Сохранение и восстановление базы данных MySQL →

MySQL. Ближайшие дни рождения.

/ Просмотров: 254
MySQL. Ближайшие дни рождения.

Есть таблица дней рождения. Необходимо сделать запрос ближайших именинников. В инете так и не нашел ничего красивого, вот что получилось у меня.

SET @nd := 12;
SET @sd := NOW();
Select * 
    from `bday_test` 
    where 
        (DAYOFYEAR(birthday) - DAYOFYEAR(@sd) > 1 AND DAYOFYEAR(birthday) - DAYOFYEAR(@sd) <= @nd)
        OR DAYOFYEAR(birthday)+364 - DAYOFYEAR(@sd) < @nd 
    order by 
        MONTH(birthday) < MONTH(@sd), 
        MONTH(birthday), 
        DAYOFMONTH(birthday);

В MySQL есть такая функция DAYOFYEAR которая возвращает порядковый номер дня года для аргумента date в диапазоне от 1 до 366.

В нашем запросе мы делаем следующее:

1. проверяем, что DAYOFYEAR дня рождения больше DAYOFYEAR текущей даты.

2. проверяем, что DAYOFYEAR дня рождения больше DAYOFYEAR текущей даты не более чем на 10 дней.

3. На этом бы и все, но нужно отработать ситуацию, когда текущая дата приходится на конец года, а ближайшие дни рождения уже в следующем году. Для этого, прибавляем к DAYOFYEAR дня рождения 364. Таким образом, 4 января будет иметь порядковый номер дня года ~368. Как и во втором пункте, проверяем, что полученное число больше DAYOFYEAR текущей даты не более чем на 10 дней.

Теперь протестируем.

Создадим таблицу дней рождения:

CREATE TABLE `bday_test` (
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `birthday` date NOT NULL DEFAULT '0000-00-00',
 `full_name` varchar(128) NOT NULL DEFAULT '''',
 PRIMARY KEY (`id`));

Для того, чтобы заполнить таблицу данными создадим процедуру:

DELIMITER $$
DROP PROCEDURE IF EXISTS `xbetomskru_ru`.`ins_bds`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ins_bds`()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE d DATE;
  DECLARE yr INT;
  WHILE i < 367 DO
    SET yr = FLOOR(RAND()*(100)+1920);
    SET d = MAKEDATE(yr, i);
    INSERT INTO `bday_test` (`birthday`, `full_name`) VALUES (d, DATE_FORMAT(d, '%d %M %Y'));
    SET d = MAKEDATE(1976, i);
    INSERT INTO `bday_test` (`birthday`, `full_name`) VALUES (d, DATE_FORMAT(d, '%d %M %Y'));
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

Эта процедура в цикле числа от 1 до 366 и создает дату из дня года и случайного года (1920 - 2020). Из-за 29 февраля этот алгоритм пропускает некоторые даты. Для того, чтобы эти пробелы исключить, попутно создаем даты только для 1976 года.

Выполняем процедуру, заполняем таблицу:

call ins_bds();

На всякий случай добавляем несколько дней рождения 29 февраля:

INSERT INTO `bday_test` (`birthday`, `full_name`) VALUES ('2020-02-29', CONCAT('LEAP ', DATE_FORMAT('2020-02-29', '%d %M %Y')));
INSERT INTO `bday_test` (`birthday`, `full_name`) VALUES ('2016-02-29', CONCAT('LEAP ', DATE_FORMAT('2016-02-29', '%d %M %Y')));
INSERT INTO `bday_test` (`birthday`, `full_name`) VALUES ('2012-02-29', CONCAT('LEAP ', DATE_FORMAT('2012-02-29', '%d %M %Y')));
INSERT INTO `bday_test` (`birthday`, `full_name`) VALUES ('2008-02-29', CONCAT('LEAP ', DATE_FORMAT('2008-02-29', '%d %M %Y')));

Для тестирования будем использовать параметрический запрос:

SET @nd := 12;
SET @sd := NOW();
Select * from `bday_test` where (DAYOFYEAR(birthday) - DAYOFYEAR(@sd) > 1 AND DAYOFYEAR(birthday) - DAYOFYEAR(@sd) <= @nd) OR DAYOFYEAR(birthday)+364 - DAYOFYEAR(@sd) < @nd order by MONTH(birthday), DAYOFMONTH(birthday);
+------+------------+---------------+
| id   | birthday   | full_name     |
+------+------------+---------------+
| 1716 | 1976-03-31 | 31 March 1976 |
| 1715 | 2007-04-01 | 01 April 2007 |
| 1718 | 1976-04-01 | 01 April 1976 |
| 1720 | 1976-04-02 | 02 April 1976 |
| 1719 | 1928-04-02 | 02 April 1928 |
| 1717 | 1993-04-02 | 02 April 1993 |
| 1721 | 1940-04-03 | 03 April 1940 |
| 1722 | 1976-04-03 | 03 April 1976 |
| 1724 | 1976-04-04 | 04 April 1976 |
| 1726 | 1976-04-05 | 05 April 1976 |
| 1723 | 1998-04-05 | 05 April 1998 |
| 1725 | 1951-04-06 | 06 April 1951 |
| 1728 | 1976-04-06 | 06 April 1976 |
| 1727 | 1942-04-07 | 07 April 1942 |
| 1730 | 1976-04-07 | 07 April 1976 |
| 1729 | 1938-04-08 | 08 April 1938 |
| 1732 | 1976-04-08 | 08 April 1976 |
| 1734 | 1976-04-09 | 09 April 1976 |
| 1731 | 1941-04-09 | 09 April 1941 |
| 1733 | 1975-04-10 | 10 April 1975 |
| 1736 | 1976-04-10 | 10 April 1976 |
| 1735 | 1931-04-11 | 11 April 1931 |
+------+------------+---------------+
22 rows in set (0.00 sec)

Теперь сделаем запрос, как будто текущая дата ближе к 29 февраля:

SET @nd := 12;
SET @sd := '2020-02-25';
Select * from `bday_test` where (DAYOFYEAR(birthday) - DAYOFYEAR(@sd) > 1 AND DAYOFYEAR(birthday) - DAYOFYEAR(@sd) <= @nd) OR DAYOFYEAR(birthday)+364 - DAYOFYEAR(@sd) < @nd order by MONTH(birthday), DAYOFMONTH(birthday);
+------+------------+-----------------------+
| id   | birthday   | full_name             |
+------+------------+-----------------------+
| 1649 | 1948-02-27 | 27 February 1948      |
| 1650 | 1976-02-27 | 27 February 1976      |
| 1651 | 2009-02-28 | 28 February 2009      |
| 1652 | 1976-02-28 | 28 February 1976      |
| 2269 | 2012-02-29 | LEAP 29 February 2012 |
| 2268 | 2016-02-29 | LEAP 29 February 2016 |
| 2267 | 2020-02-29 | LEAP 29 February 2020 |
| 2270 | 2008-02-29 | LEAP 29 February 2008 |
| 1654 | 1976-02-29 | 29 February 1976      |
| 1653 | 1982-03-01 | 01 March 1982         |
| 1656 | 1976-03-01 | 01 March 1976         |
| 1658 | 1976-03-02 | 02 March 1976         |
| 1655 | 1965-03-02 | 02 March 1965         |
| 1657 | 1957-03-03 | 03 March 1957         |
| 1660 | 1976-03-03 | 03 March 1976         |
| 1662 | 1976-03-04 | 04 March 1976         |
| 1659 | 1970-03-04 | 04 March 1970         |
| 1664 | 1976-03-05 | 05 March 1976         |
| 1661 | 1961-03-05 | 05 March 1961         |
| 1666 | 1976-03-06 | 06 March 1976         |
| 1663 | 1973-03-06 | 06 March 1973         |
| 1665 | 1966-03-07 | 07 March 1966         |
| 1668 | 1976-03-07 | 07 March 1976         |
| 1667 | 1990-03-08 | 08 March 1990         |
| 1670 | 1976-03-08 | 08 March 1976         |
| 1669 | 1931-03-09 | 09 March 1931         |
+------+------------+-----------------------+
26 rows in set (0.00 sec)

Теперь сделаем запрос, как будто текущая дата ближе к концу года:

SET @nd := 12;
SET @sd := '2020-12-25';
Select * from `bday_test` where (DAYOFYEAR(birthday) - DAYOFYEAR(@sd) > 1 AND DAYOFYEAR(birthday) - DAYOFYEAR(@sd) <= @nd) OR DAYOFYEAR(birthday)+364 - DAYOFYEAR(@sd) < @nd order by MONTH(birthday), DAYOFMONTH(birthday);
+------+------------+------------------+
| id   | birthday   | full_name        |
+------+------------+------------------+
| 1535 | 1933-01-01 | 01 January 1933  |
| 1536 | 1976-01-01 | 01 January 1976  |
| 1537 | 1954-01-02 | 02 January 1954  |
| 1538 | 1976-01-02 | 02 January 1976  |
| 1539 | 1950-01-03 | 03 January 1950  |
| 1540 | 1976-01-03 | 03 January 1976  |
| 1542 | 1976-01-04 | 04 January 1976  |
| 1541 | 1971-01-04 | 04 January 1971  |
| 1543 | 1984-01-05 | 05 January 1984  |
| 1544 | 1976-01-05 | 05 January 1976  |
| 1545 | 1989-01-06 | 06 January 1989  |
| 1546 | 1976-01-06 | 06 January 1976  |
| 1548 | 1976-01-07 | 07 January 1976  |
| 1547 | 1972-01-07 | 07 January 1972  |
| 2258 | 1976-12-27 | 27 December 1976 |
| 2257 | 1982-12-28 | 28 December 1982 |
| 2260 | 1976-12-28 | 28 December 1976 |
| 2262 | 1976-12-29 | 29 December 1976 |
| 2261 | 1992-12-29 | 29 December 1992 |
| 2259 | 2011-12-29 | 29 December 2011 |
| 2264 | 1976-12-30 | 30 December 1976 |
| 2263 | 2007-12-31 | 31 December 2007 |
| 2265 | 1940-12-31 | 31 December 1940 |
| 2266 | 1976-12-31 | 31 December 1976 |
+------+------------+------------------+
24 rows in set (0.00 sec)

Косяк, сначала идет январь, а потом декабрь. Нужно наоборот. Добавляем в сортировку "MONTH(birthday) < MONTH(@sd)":

SET @nd := 12;
SET @sd := '2020-12-25';
Select *, MONTH(birthday)<MONTH(@sd) as ffff from `bday_test` where (DAYOFYEAR(birthday) - DAYOFYEAR(@sd) > 1 AND DAYOFYEAR(birthday) - DAYOFYEAR(@sd) <= @nd) OR DAYOFYEAR(birthday)+364 - DAYOFYEAR(@sd) < @nd order by MONTH(birthday)<MONTH(@sd), MONTH(birthday), DAYOFMONTH(birthday);
+------+------------+------------------+------+
| id   | birthday   | full_name        | ffff |
+------+------------+------------------+------+
| 2258 | 1976-12-27 | 27 December 1976 |    0 |
| 2257 | 1982-12-28 | 28 December 1982 |    0 |
| 2260 | 1976-12-28 | 28 December 1976 |    0 |
| 2261 | 1992-12-29 | 29 December 1992 |    0 |
| 2262 | 1976-12-29 | 29 December 1976 |    0 |
| 2259 | 2011-12-29 | 29 December 2011 |    0 |
| 2264 | 1976-12-30 | 30 December 1976 |    0 |
| 2266 | 1976-12-31 | 31 December 1976 |    0 |
| 2263 | 2007-12-31 | 31 December 2007 |    0 |
| 2265 | 1940-12-31 | 31 December 1940 |    0 |
| 1535 | 1933-01-01 | 01 January 1933  |    1 |
| 1536 | 1976-01-01 | 01 January 1976  |    1 |
| 1537 | 1954-01-02 | 02 January 1954  |    1 |
| 1538 | 1976-01-02 | 02 January 1976  |    1 |
| 1540 | 1976-01-03 | 03 January 1976  |    1 |
| 1539 | 1950-01-03 | 03 January 1950  |    1 |
| 1541 | 1971-01-04 | 04 January 1971  |    1 |
| 1542 | 1976-01-04 | 04 January 1976  |    1 |
| 1544 | 1976-01-05 | 05 January 1976  |    1 |
| 1543 | 1984-01-05 | 05 January 1984  |    1 |
| 1546 | 1976-01-06 | 06 January 1976  |    1 |
| 1545 | 1989-01-06 | 06 January 1989  |    1 |
| 1548 | 1976-01-07 | 07 January 1976  |    1 |
| 1547 | 1972-01-07 | 07 January 1972  |    1 |
+------+------------+------------------+------+
24 rows in set (0.01 sec)

© Ewgenik, 2023
Работает на MaxSite CMS | Время: 0.6361 | SQL: 21 | Память: 4.22MB | Вход