Знак зодиака средствами MySQL

Рубрика: Development, MySQL | 13 February 2008, 13:45 | Vadim Voituk

Что-то давно я ничего про MySQL не писал – буду исправляться.

Часто на развлекательных сайтах приходится приходится “вычислять” знак зодиака пользователя по его дате рождения. Задача решена уже тысячу раз, и интереса не представляет – проверка попадания даты в интервал релизуется на любом популярном языке программирования в 5-7 строк.
А теперь представьте себе, что нужно написать SQL-запрос в MySQL БД, который выбрает из таблицы всех пользователей с определенным знаком зодиака.

Тут на помощь приходит оператор CASE.
С его использованием запрос, который выводит из таблицы 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-й.

Комментариев: 11

11 Responses to “Знак зодиака средствами MySQL”

Комментарии:

  1. Скакунов Александр

    Занимательно :)

    Человек родился 20 марта. Получается он и Овен, и Рыбы (первый и последний предикаты)?

    И имхо, хороший случай, когда лучше ввести дополнительное поле.

  2. Vadim Voituk

    Саня, исправлено. Быстро ты баг нашел.

  3. Mikhail Sayapin

    Прикольное решение, но у меня обычно вот так:

    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.name
    FROM users JOIN zodiacs ON users.birthday BETWEEN DATE(CONCAT(YEAR(users.birthday), '-', zodiacs.since)) AND DATE(CONCAT(YEAR(users.birthday), '-', zodiacs.till));

    Работает быстро, хорошо. :)

  4. Vadim Voituk

    Действительно такой вариант приятнее глазу. Наверное буду использовать его.

  5. Скакунов Александр

    На счет скорости сомневаюсь. Для каждого юзера вызывается 12 х 3 х 2 функций (12 – зодиаки, 3 – DATE+CONCAT+YEAR, 2 – since и till ). Возможно что-то кэшируется, но всё равно это тягомотина – народ от JOIN-ов пытается избавляться, а тут BETWEEN c кучей вычислений.

    Табличка радует глаз, да, но это ли критерий хорошего решения?

    Рекомендую отдельное поле, записываемое в момент создания записи, ибо читаться оно будет намного чаще, чем меняться дата рождения пользователя ;]

  6. Vadim Voituk

    Это какой такой народ от JOIN-ов пытается избавляться?
    И зачем?

  7. Скакунов Александр

    На больших проектах у нас проводили этап оптимизации, где по возможности упрощали запросы.

    Что лучше –

    SELECT field FROM table

    или JOIN двух таблиц?

  8. Sniff

    А зачем вообще это вычислять на уровне БД? Не проще его вычислять один раз программно и писать рядом с датой рождения и пересчитывать при апдейте? Зачем в базу-то это тянуть?

  9. Chabster

    Внимание, правильный вариант:

    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)

  10. Alexey

    Хм… Решение не соответствует поставленной задаче
    >> SQL-запрос который выбрает из таблицы всех пользователей с определенным знаком зодиака.
    Согласен с 8-м комментарием(хоть и есть денормализация) + индекс

  11. Китс

    Mikhail Sayapin, мерси, оч помогло.

Leave a Reply