Да, указанный набор результатов можно вернуть с помощью одного оператора SQL.
К сожалению, MySQL не поддерживает аналитические функции, что делает оператор довольно простым. Несмотря на то, что MySQL не имеет синтаксиса для их поддержки, можно эмулировать некоторые аналитические функции, используя пользовательские переменные MySQL.
Один из способов получить указанный набор результатов (с помощью одного оператора SQL) — использовать операцию JOIN с использованием уникального возрастающего целочисленного значения (rownum, полученного и присвоенного в запросе) для каждой строки.
Например:
SELECT q.rownum AS rownum
, q.date AS latest_date
, q.miles/q.gallons AS latest_mpg
, COUNT(1) AS cnt_rows
, MIN(r.date) AS earliest_date
, SUM(r.miles) AS rtot_miles
, SUM(r.gallons) AS rtot_gallons
, SUM(r.miles)/SUM(r.gallons) AS rtot_mpg
FROM ( SELECT @s_rownum := @s_rownum + 1 AS rownum
, s.date
, s.miles
, s.gallons
FROM mytable s
JOIN (SELECT @s_rownum := 0) c
ORDER BY s.date
) q
JOIN ( SELECT @t_rownum := @t_rownum + 1 AS rownum
, t.date
, t.miles
, t.gallons
FROM mytable t
JOIN (SELECT @t_rownum := 0) d
ORDER BY t.date
) r
ON r.rownum <= q.rownum
AND r.rownum > q.rownum - 2
GROUP BY q.rownum
Желаемое значение «n», указывающее, сколько строк включать в каждую строку свертки, указывается в предикате непосредственно перед предложением GROUP BY. В этом примере до «2» строк в каждой строке промежуточного итога.
Если вы укажете значение 1, вы получите (в основном) исходную возвращенную таблицу.
Чтобы исключить любые «неполные» строки промежуточных сумм (состоящие из менее чем «n» строк), это значение «n» необходимо будет указать снова, добавив:
HAVING COUNT(1) >= 2
Демонстрация sqlfiddle: http://sqlfiddle.com/#!2/52420/2
Следовать за:
Вопрос. Я пытаюсь понять ваше выражение SQL. Выполняет ли ваше решение выбор двадцати строк для каждой строки в базе данных? Другими словами, если у меня есть 1000 строк, будет ли ваш оператор выполнять 20000 выборок? (Я беспокоюсь о производительности)...
A: Вы правы, беспокоясь о производительности.
Чтобы ответить на ваш вопрос, нет, это не выполняет 20 000 выборок для 1000 строк.
Ухудшение производительности происходит из-за двух (по сути идентичных) встроенных представлений (с псевдонимами q и r). Что MySQL делает с ними (в основном), так это создает временные таблицы MyISAM (MySQL называет их «производными таблицами»), которые в основном являются копиями mytable с дополнительным столбцом, каждой строке присваивается уникальное целочисленное значение от 1 до количество строк.
Как только две «производные» таблицы созданы и заполнены, MySQL запускает внешний запрос, используя эти две «производные» таблицы в качестве источника строк. Каждая строка из q сопоставляется с n строками из r для расчета «нарастающей суммы» миль и галлонов.
Для повышения производительности вы можете использовать столбец, уже находящийся в таблице, вместо того, чтобы запрос присваивал уникальные целочисленные значения. Например, если столбец date уникален, вы можете рассчитать «нарастающую сумму» за определенный период дней.
SELECT q.date AS latest_date
, SUM(q.miles)/SUM(q.gallons) AS latest_mpg
, COUNT(1) AS cnt_rows
, MIN(r.date) AS earliest_date
, SUM(r.miles) AS rtot_miles
, SUM(r.gallons) AS rtot_gallons
, SUM(r.miles)/SUM(r.gallons) AS rtot_mpg
FROM mytable q
JOIN mytable r
ON r.date <= q.date
AND r.date > q.date + INTERVAL -30 DAY
GROUP BY q.date
(Для повышения производительности вам потребуется соответствующий индекс, определенный с помощью date в качестве начального столбца в индексе.)
Для первого запроса любые предикаты, включенные (во встроенные запросы определения представления) для уменьшения количества возвращаемых строк (например, возврат только значений даты в прошлом году), уменьшат количество обрабатываемых строк, а также, вероятно, повысить производительность.
Опять же, на ваш вопрос о выполнении 20 000 выборок для 1000 строк... операция вложенных циклов - это еще один способ получить тот же набор результатов. Для большого количества строк это может привести к снижению производительности. (С другой стороны, этот подход может быть достаточно эффективным, когда возвращаются только несколько строк:
SELECT q.date AS latest_date
, q.miles/q.gallons AS latest_mpg
, ( SELECT SUM(r.miles)/SUM(r.gallons)
FROM mytable r
WHERE r.date <= q.date
AND r.date >= q.date + INTERVAL -90 DAY
) AS rtot_mpg
FROM mytable q
ORDER BY q.date
person
spencer7593
schedule
21.02.2013
