Как правильно переписать SQL запрос?

Question

[fman2]

Добрый день, дорогие друзья.

Есть база с пользователями users на 30к пользователей:
user_id
user_name

Есть база с некоторыми числами values, которые добавляются каждый день, но не на каждого пользователя (каждый день из 30к пользователей порядка 25к чисел добавляются)
value_id,
user_id
value
date (ДД.ММ.ГГГГ)

Необходимо получить все значения (value) за сегодняшний день и за прошлый, а также эти значения сравнить (value предыдущего дня > value текущего дня). Но соответственно нужно как-то сделать выборку лишь по тем пользователям, у которых запись в таблице values есть за два дня.
Сейчас мы делаем это вложенным циклом (да простите меня за это) примерно вот так:

for ($i=0; $i<count...; $i++) {
     for ($j=0; $j<count...; $j++) {
          // тут происходит сравнения айди пользователей из данных за вчера и сегодня
          // и если они совпадают - делаем сравнение чисел

Когда пользователей было 200, все работало быстро, но на проверке 30 000 записей из таблицы value скрипт вешается капитально и нужно придумать другой вариант.

Comments

[Arman]

могли показать пример что должны в конце получить?
12|12.12.2014|???
И если у пользователя нет данных за сегодня или вчера, или вообще нет за два дня участвует он поиске?

[fman2]

1. Если у пользователя нет данных за сегодня и вчера, соответственно он в поиске не участвует, но эти данные мы проверяем уже потом. Все что нужно - получить записи за сегодня и за вчера, при этом user_id должен совпадать.

Т.е. должен быть запрос вида:
select * from `values` where date="26.12.2014" or date="25.12.2014"
соответственно у нас на выходе будет таблица:

value_id user_id value date
.......................................................
10123 5982 5 26.12.2014
.......................................................
.......................................................
.......................................................
15123 5982 10 25.12.2014

как видим, user_id (5982) совпадает. Теперь нужно как то эти две записи сравнить по value. И если запись за текущий день, больше чем за предыдущий добавить ее в массив.
Нужно учесть, что такая выборка дает порядка 30к записей и с каждым днем больше, поэтому нужно как то быстро посчитать, максимум 5мин :)

[Arman]

fman2: А если будет меньше нужно добавить значение что было вчера или вообще не добавлять?

[fman2]

Если запись меньше - не добавляем вообще ничего.

Answer 1

[Rsa97]

Для начала рекомендую преобразовать поле `date` в тип DATE, тогда с ним можно будет работать стандартными функциями MySQL и строить по нему индексы.

SELECT `today`.`user_id`, `today`.`value`, `yesterday`.`value`, `users`.`name`
    FROM `values` AS `today`
    INNER JOIN `values` AS `yesterday` ON `yesterday`.`user_id` = `today`.`user_id`
    LEFT JOIN `users` ON `users`.`id` = `values`.`user_id`
    WHERE `today`.`date` = CURDATE()
        AND `yesterday`.`date` = CURDATE()-INTERVAL 1 DAY
        AND `yesterday`.`value` > `today`.`value`

Answer 2

[Arman]

Примерно так должно быть, но не тестировал, нет таблиц живых

SELECT
    a.`user_id`,
    b.`user_name`,
    a.`date`,
    a.`value`
FROM
    `values` AS a
JOIN
    `users` AS b
ON
    b.`user_id` = a.`user_id`
WHERE
    a.`date` = "26.12.2014" 
    AND
    a.`value` >
        (
        SELECT
            c.`value`
        FROM
            `values` AS c
        WHERE
            c.`user_id` = a.`user_id`
            AND
            c.`date` = "25.12.2014"
        LIMIT 1
        )

Comments

[fman2]

Больше спасибо, как будет возможность - обязательно попробую!

[Arman]

fman2: изменил запрос, так как нужно собрать за сегодня и только если значение больше чем было вчера. Если не надо проверять есть такой юзер или нет и брать его user_name, то можно убрать JOIN + "b.`user_name`," - запрос будет шустрее. Если нужно чтоб было, но запрос долго выполняется, то можно убрать JOIN, собрать все user_id и сделать второй запрос в таблицу users по id - так быстрее должно быть, особенно когда данных очень много

[Александр Попов]

А в Вашем запросе что произойдёт, если вложенный запрос не вернёт ни одной строки? Интересно просто. Там подставится NULL и условие сработает как истинное?

[Arman]

Александр Попов: вот сам думаю как правильно нужно. Текущий запрос вернет все записи где значение больше чем было вчера или вчера ничего не было у текущего пользователя. В вопросе автора не понятно нужно такие данные или нет

[Arman]

Александр Попов: Хотя нет. Только что попробовал:
SELECT 10 > NULL // NULL
SELECT IF(10 > NULL, 1, 0) // 0
Поэтому не вернет записи если вчера у юзера не было значений.

[fman2]

Думаю, то что нужно. Сегодня вечером попробую на "боевой" базе, если будут косяки - отпишусь. Парни, еще раз огромное спасибо, вы меня очень очень выручили!

Answer 3

[melnikov_m]

Если числа в таблицу "values" добавляются только они раз в сутки, как вариант оптимизации могу предложить
1) взять запрос выше и запускать его раз в день по крону.
2) Получившиеся значения записывать в "users" (конечно же не актуальные значения за прошлый день нужно удалять).
Практическая выгода: Будите получать value простым селектом из одной таблицы. Не надо будет постоянно обращаться к базе "values", ускорится загрузка страницы, экономится память. Может сейчас не так заметно, но если база будет расширяться, в будущем сильно вам облегчит жизнь.

Comments

[fman2]

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