Знак зодиака средствами MySQL
Рубрика: Development, MySQL | 13 February 2008, 13:45 | 
Vadim Voituk
Что-то давно я ничего про MySQL не писал – буду исправляться.
Часто на развлекательных сайтах приходится приходится “вычислять” знак зодиака пользователя по его дате рождения. Задача решена уже тысячу раз, и интереса не представляет – проверка попадания даты в интервал релизуется на любом популярном языке программирования в 5-7 строк.
А теперь представьте себе, что нужно написать SQL-запрос в MySQL БД, который выбрает из таблицы всех пользователей с определенным знаком зодиака.
.
С его использованием запрос, который выводит из таблицы users id-пользователя и его знак зодиака выглядит приблизительно так:
[sql]
SELECT id,
stat_birth,
@d:=DAY(FROM_UNIXTIME(stat_birth)) AS stat_birth_day,
@m:=MONTH(FROM_UNIXTIME(stat_birth)) AS stat_birth_month,
CASE
WHEN (@m=3 AND @d>20) OR (@m=4 AND @d<21) THEN 'Oven'
WHEN (@m=4 AND @d>20) OR (@m=5 AND @d<22) THEN 'Taurus'
WHEN (@m=5 AND @d>21) OR (@m=6 AND @d<22) THEN 'Gemini'
WHEN (@m=6 AND @d>21) OR (@m=7 AND @d<23) THEN 'Cancer'
WHEN (@m=7 AND @d>22) OR (@m=8 AND @d<24) THEN 'Leo'
WHEN (@m=8 AND @d>23) OR (@m=9 AND @d<24) THEN 'Virgo'
WHEN (@m=9 AND @d>23) OR (@m=10 AND @d<24) THEN 'Libra'
WHEN (@m=10 AND @d>23) OR (@m=11 AND @d<23) THEN 'Scorpion'
WHEN (@m=11 AND @d>22) OR (@m=12 AND @d<22) THEN 'Sagittarius'
WHEN (@m=12 AND @d>21) OR (@m=1 AND @d<21) THEN 'Capricorn'
WHEN (@m=1 AND @d>20) OR (@m=2 AND @d<19) THEN 'Aquarius'
WHEN (@m=2 AND @d>18) OR (@m=3 AND @d<21) THEN 'Fish'
END AS zodiak
FROM users
[/sql]
По хорошему определение знака зодиака нужно оформить как хранимую процедуру, но в таком случае теряется совместимость с MySQL версий ниже 5-й.
Tweet
Занимательно :)
Человек родился 20 марта. Получается он и Овен, и Рыбы (первый и последний предикаты)?
И имхо, хороший случай, когда лучше ввести дополнительное поле.
Саня, исправлено. Быстро ты баг нашел.
Прикольное решение, но у меня обычно вот так:
mysql> select * from zodiacs;+-------------+-------+-------+
| name | since | till |
+-------------+-------+-------+
| Aries | 3-21 | 4-20 |
| Taurus | 4-21 | 5-22 |
| Gemini | 5-23 | 6-23 |
| Cancer | 6-24 | 7-23 |
| Leo | 7-24 | 8-23 |
| Virgo | 8-24 | 9-22 |
| Libra | 9-23 | 10-23 |
| Scorpio | 10-24 | 11-22 |
| Sagittarius | 11-23 | 12-21 |
| Capricorn | 1-1 | 1-19 |
| Capricorn | 12-22 | 12-31 |
| Aquarius | 1-20 | 2-18 |
| Pisces | 2-19 | 3-20 |
+-------------+-------+-------+
SELECT users.birtday, zodiacs.nameFROM users JOIN zodiacs ON users.birthday BETWEEN DATE(CONCAT(YEAR(users.birthday), '-', zodiacs.since)) AND DATE(CONCAT(YEAR(users.birthday), '-', zodiacs.till));
Работает быстро, хорошо. :)
Действительно такой вариант приятнее глазу. Наверное буду использовать его.
На счет скорости сомневаюсь. Для каждого юзера вызывается 12 х 3 х 2 функций (12 – зодиаки, 3 – DATE+CONCAT+YEAR, 2 – since и till ). Возможно что-то кэшируется, но всё равно это тягомотина – народ от JOIN-ов пытается избавляться, а тут BETWEEN c кучей вычислений.
Табличка радует глаз, да, но это ли критерий хорошего решения?
Рекомендую отдельное поле, записываемое в момент создания записи, ибо читаться оно будет намного чаще, чем меняться дата рождения пользователя ;]
Это какой такой народ от JOIN-ов пытается избавляться?
И зачем?
На больших проектах у нас проводили этап оптимизации, где по возможности упрощали запросы.
Что лучше –
SELECT field FROM table
или JOIN двух таблиц?
А зачем вообще это вычислять на уровне БД? Не проще его вычислять один раз программно и писать рядом с датой рождения и пересчитывать при апдейте? Зачем в базу-то это тянуть?
Внимание, правильный вариант:
CREATE TABLE zodiacs
(
dayofyear number(3,0),
name varchar2(30) NOT NULL,
CONSTRAINT PK_zodiacs PRIMARY KEY (dayofyear)
)
ORGANIZATION INDEX;
INSERT INTO zodiacs(dayofyear, name) VALUES (1, ‘Capricorn’);
INSERT INTO zodiacs(dayofyear, name) VALUES (2, ‘Capricorn’);
INSERT INTO zodiacs(dayofyear, name) VALUES (3, ‘Capricorn’);
…
SELECT u.dayofbirth, z.name
FROM users u, zodiacs z
WHERE z.dayofyear=DAYOFYEAR(u.birthdate)
Хм… Решение не соответствует поставленной задаче
>> SQL-запрос который выбрает из таблицы всех пользователей с определенным знаком зодиака.
Согласен с 8-м комментарием(хоть и есть денормализация) + индекс
Mikhail Sayapin, мерси, оч помогло.