[SQL][нуб] Упростить запрос MySQL

IG_rok777

Есть таблица (keyvalue)

date

key

value

(date,key) - уникальны в пределах всей таблицы.
Задача - получить значения ключей на определенную дату (GivenDate). Т.е. берем по каждому ключу запись с максимальным date, до GivenDate.
Я делаю это таким запросом:



SELECT 

  keyvalue.key, 

  keyvalue.value 

FROM 

  keyvalue JOIN 

    (SELECT

       keyvalue.key,

       MAX(keyvalue.date) AS maate

     FROM

       keyvalue

     WHERE

       keyvalue.date <= GivenDate

     GROUP BY

       keyvalue.key) AS maates ON (maates.maate = keyvalue.date AND maates.key = keyvalue.key)

Как можно его упростить?

Bibi

надо тестировать, но что-то вроде этого:
select kv1.key, kv1.value
from keyvalue kv1
left join keyvalue kv2 on kv1.key = kv2.key and kv2.date > kv1.date and kv2.date <= GivenDate
where kv2.date is null and kv1.date <= GivenDate

IG_rok777

На тестовой таблице этот запрос показал 0.0189 секунды против 0.0064 изначальных.

Bibi

для этого запроса нужен индекс (key, date)
он есть?

hprt

начальный вариант все равно лучше должен быть

IG_rok777

Нет и это странно

IG_rok777

начальный вариант все равно лучше должен быть

Я просто подумал, что было бы классно, если можно заюзать какую-то специальную агрегатную функцию.
Как-то так MAX_BY_OTHER_FIELD(value, date). Выбрать значение в поле value, где максимальное date. Такое было бы абсудром, если date не уникально в группировке по key, а тут вроде бы "рукой подать" до value, когда делаем MAX(date). Надеюсь вы меня полняли.

hprt

юзать какую-то специальную агрегатную функцию.
Как-то так MAX_BY_OTHER_FIELD(value, date). Выбрать значение в поле value, где максимальное date. Такое было бы абсудром, если date не уникально в группировке по key, а тут вроде бы "рукой подать" до value, когда делаем MAX(date). Надеюсь вы меня полняли.

Я мог бы тебе по MSSQL посоветовать, но MySQL плохо знаю. До появления аналитических функций твой начальный вариант был оптимальным, но в мускуле их вроде нет до сих пор. Может еще что есть - не знаю

IG_rok777

Я мог бы тебе по MSSQL посоветовать, но MySQL плохо знаю. До появления аналитических функций твой начальный вариант был оптимальным, но в мускуле их вроде нет до сих пор. Может еще что есть - не знаю

У нас используется MySQL, к сожалению. А интересно, в Postgre есть такие функции?

hprt

есть

mbolik1

Несколько вопросов:
1. Есть ли индекс на key, date?
2. Сколько строк в таблице? сколько уникальных значений key?
3. Что не устраивает? твой запрос во многих случаях будет оптимальным, в чём потребность переписывать запрос?

IG_rok777

Несколько вопросов:
1. Есть ли индекс на key, date?
2. Сколько строк в таблице? сколько уникальных значений key?
3. Что не устраивает? твой запрос во многих случаях будет оптимальным, в чём потребность переписывать запрос?

1. Уже писал, на тестовой таблице не было. Исправил. На рабочих таблицах есть.
2. По-разному. От сотен до миллионов. Уникальных key явно больше, чем уникальных date в пределах одного key. Примерно, как правило, их количество = количество записей в таблице / 5 - 20.
3. Этот запрос чаще всего используется как вложенный, и практически как таблица среза значений на определенную дату. Я понимаю, когда в соединении таблиц необходимо получить значения на разные даты в разных строках, то там по-другому никак. Сейчас полетят какашки, но я работал с 1Ской и там такие срезы были реализованы виртуальными таблицами, которые просто летали. Но там, опять же, MSSQL. На Postgre все очень жутко тормозило. Т.к. такой запрос используется часто, хотелось бы использовать оптимальное решение.

mbolik1

я работал с 1Ской и там такие срезы были реализованы виртуальными таблицами

Это называется materialized view. В MySQL их нет.

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

Может завести такую таблицу и просто заполнять её перед запросом?

IG_rok777

Может завести такую таблицу и просто заполнять её перед запросом?

Так и делаю, если есть повторы. Но чаще всего их нет. Смысла во временной таблице не вижу.

kill-still

Я мог бы тебе по MSSQL посоветовать, но MySQL плохо знаю. До появления аналитических функций твой начальный вариант был оптимальным, но в мускуле их вроде нет до сих пор. Может еще что есть - не знаю

ня:
вариант 1 читерский:

  SELECT key, value

    FROM   (SELECT key, value

              FROM keyvalue

             WHERE dste < GivenDate

          ORDER BY date DESC) t

GROUP BY key

но так лучше не делать - тут закладываешься на знание того, как оно внутри работает.
вариант 2:

SELECT key, value

  FROM   (SELECT key, value

            FROM keyvalue

           WHERE date < GivenDate

        ORDER BY date DESC) t

 WHERE if (@previous = key, -1, @previous := key) <> -1

мне сегодня отгул дали - проверить не могу, но как-то так должно выглядеть

kill-still

Я просто подумал, что было бы классно, если можно заюзать какую-то специальную агрегатную функцию.
Как-то так MAX_BY_OTHER_FIELD(value, date).

Вообще в мускуле такого нет, но симулировать можно:
Вариант №3, наркоманский:



SELECT key, LEFT(v, LOCATE('^', v value

  FROM   (SELECT key, GROUP_CONCAT(value ORDER BY date DESC SEPARATOR '^') v

            FROM keyvalue

           WHERE date < GivenDate

        GROUP BY key) t

GROUP_CONCAT - оконная функция "для бедных" ^_^
Если value меняется часто, будет работать долго и может упереться в group_concat_max_len.

IG_rok777

Второй вариант самый интересный, спасибо. Потестируем.

Оставить комментарий