Нарастающий итог с группировкой и фильтрацией, без под-селекта и без вендоро-зависимых расширений

Question

Нарастающий итог с группировкой и фильтрацией, без под-селекта и без вендоро-зависимых расширений

Рейтинг: 3Ответов: 1Опубликовано: 19.09.2014

То, чего я добиваюсь: нарастающий итог по quantity и amount с начала дня, за нужную дату, с разбивкой по hour.

Знаю, что в Oracle и MSSQL есть специальные операторы для получения таких данных и что в Postgres есть SELECT OVER PARTITION.
Но сейчас (прототип) используется MySQL (или MariaDB, Percona и т.п.), что будет в продакшне - не знаю. Поэтому нельзя отклоняться в вендоро-зависимые расширения.

Вся таблица:

> SELECT id, dir, product, date, hour, quantity, amount FROM sales order by date, hour;

+------+-----+---------+------------+------+----------+--------+
| id   | dir | product | date       | hour | quantity | amount |
+------+-----+---------+------------+------+----------+--------+
| 2230 |  65 | ABCDEDF | 2014-09-11 |    1 |        1 |     10 |
| 2231 |  64 | ABCDEDF | 2014-09-11 |    3 |        4 |     40 |
| 2232 |  64 | ABCDEDF | 2014-09-11 |    5 |        5 |     50 |
| 2235 |  64 | ZZ      | 2014-09-11 |    7 |        6 |     60 |
| 2233 |  64 | ABCDEDF | 2014-09-11 |    7 |        6 |     60 |
| 2237 |  66 | ABCDEDF | 2014-09-11 |    7 |        6 |     60 |
| 2234 |  64 | ZZ      | 2014-09-18 |    3 |        1 |     11 |
| 2236 |  66 | ABCDEDF | 2014-09-18 |    3 |        1 |    100 |
| 2227 |  64 | ABCDEDF | 2014-09-18 |    3 |        1 |    100 |
| 2228 |  64 | ABCDEDF | 2014-09-18 |    5 |        2 |    200 |
| 2229 |  64 | ABCDEDF | 2014-09-18 |    7 |        3 |    300 |
+------+-----+---------+------------+------+----------+--------+

За нужную дату:

> SELECT id, dir, product, date, hour, quantity, amount FROM sales WHERE date = '2014-09-18';

+------+-----+---------+------------+------+----------+--------+
| id   | dir | product | date       | hour | quantity | amount |
+------+-----+---------+------------+------+----------+--------+
| 2227 |  64 | ABCDEDF | 2014-09-18 |    3 |        1 |    100 |
| 2228 |  64 | ABCDEDF | 2014-09-18 |    5 |        2 |    200 |
| 2229 |  64 | ABCDEDF | 2014-09-18 |    7 |        3 |    300 |
| 2234 |  64 | ZZ      | 2014-09-18 |    3 |        1 |     11 |
| 2236 |  66 | ABCDEDF | 2014-09-18 |    3 |        1 |    100 |
+------+-----+---------+------------+------+----------+--------+

То, чего я добиваюсь, под-селектом. Результат, как надо.

> SELECT date, hour, SUM(quantity),
  ( SELECT SUM(quantity) FROM sales s2
    WHERE s2.hour <= s1.hour AND s2.date = s1.date
  ) AS total
  FROM sales s1
  WHERE s1.date = '2014-09-18'
  GROUP by date, hour;

+------------+------+---------------+-------+
| date       | hour | sum(quantity) | total |
+------------+------+---------------+-------+
| 2014-09-18 |    3 |             3 |     3 |
| 2014-09-18 |    5 |             2 |     5 |
| 2014-09-18 |    7 |             3 |     8 |
+------------+------+---------------+-------+

Смущает:

Если записей в таблице будет много (а их будет около миллиона), то подзапросы будут выполнятся в огромном количестве.
Если мне нужно делать выборку только по какому-то продукту или по дирекции, мне нужно эти условия прописывать и в запрос и в подзапрос (WHERE product = / WHERE dir =).
Считает только одну сумму, а мне нужно две (sum(quantity) и sum(amount)) (ERROR 1241 (21000): Operand should contain 1 column(s)).

Ближайший результат с JOIN'ом:

> SELECT DISTINCT(s1.hour) AS ih, s2.date, s2.hour, s2.quantity, s2.amount, s2.id FROM sales s1 JOIN sales s2 ON s2.date = s1.date AND s2.hour <= s1.hour WHERE s1.date = '2014-09-18' ORDER by ih;

+----+------------+------+----------+--------+------+
| ih | date       | hour | quantity | amount | id   |
+----+------------+------+----------+--------+------+
|  3 | 2014-09-18 |    3 |        1 |    100 | 2236 |
|  3 | 2014-09-18 |    3 |        1 |    100 | 2227 |
|  3 | 2014-09-18 |    3 |        1 |     11 | 2234 |
|  5 | 2014-09-18 |    3 |        1 |    100 | 2236 |
|  5 | 2014-09-18 |    3 |        1 |    100 | 2227 |
|  5 | 2014-09-18 |    5 |        2 |    200 | 2228 |
|  5 | 2014-09-18 |    3 |        1 |     11 | 2234 |
|  7 | 2014-09-18 |    3 |        1 |    100 | 2236 |
|  7 | 2014-09-18 |    3 |        1 |    100 | 2227 |
|  7 | 2014-09-18 |    5 |        2 |    200 | 2228 |
|  7 | 2014-09-18 |    7 |        3 |    300 | 2229 |
|  7 | 2014-09-18 |    3 |        1 |     11 | 2234 |
+----+------------+------+----------+--------+------+

В варианте без DISTINCT строки имеют дубли. Вариации JOIN'ов не помогают.

Как только убираю s2.id из SELECT, получается ерунда, значимые строки просто пропадают:

> SELECT DISTINCT(s1.hour) AS ih, s2.date, s2.hour, s2.quantity, s2.amount FROM sales s1 JOIN sales s2 ON s2.date = s1.date AND s2.hour <= s1.hour WHERE s1.date = '2014-09-18' ORDER by ih;

+----+------------+------+----------+--------+
| ih | date       | hour | quantity | amount |
+----+------------+------+----------+--------+
|  3 | 2014-09-18 |    3 |        1 |    100 |
|  3 | 2014-09-18 |    3 |        1 |     11 |
|  5 | 2014-09-18 |    3 |        1 |    100 |
|  5 | 2014-09-18 |    5 |        2 |    200 |
|  5 | 2014-09-18 |    3 |        1 |     11 |
|  7 | 2014-09-18 |    3 |        1 |    100 |
|  7 | 2014-09-18 |    5 |        2 |    200 |
|  7 | 2014-09-18 |    7 |        3 |    300 |
|  7 | 2014-09-18 |    3 |        1 |     11 |
+----+------------+------+----------+--------+

Суммирование не помогает, наоборот, добавляет ещё проблем. В строках, соответствующих ih = 3 сумма по s2.quantity 9, а должна быть 3. Сумма по s1.quantity вообще непонятно из чего состоит.

> SELECT DISTINCT(s1.hour) AS ih, sum(s1.quantity), s2.date, SUM(s2.quantity) FROM sales s1 JOIN sales s2 ON s2.date = s1.date AND s2.hour <= s1.hour WHERE s1.date = '2014-09-18' GROUP BY ih;

+----+------------------+------------+------------------+
| ih | sum(s1.quantity) | date       | sum(s2.quantity) |
+----+------------------+------------+------------------+
|  3 |                9 | 2014-09-18 |                9 |
|  5 |                8 | 2014-09-18 |                5 |
|  7 |               15 | 2014-09-18 |                8 |
+----+------------------+------------+------------------+

Бонус очки:
Нужна ещё колонка total_reference, в которой будет указан нарастающий итог для того же периода (с начала дня по hour), но для другой даты.

sql

Источник: Stack Overflow на русском

Answer 1

▲ 3Принят

Ну, нужно просто привести набор к один час => одна запись. И для каждой записи из такого набора вычислять нарастающий итог:

SELECT s1.date, s1.hour, s1.quantity, sum(s2.quantity) as total
FROM (select date, hour, sum(quantity) as quantity
      from sales group by date, hour) s1 
  JOIN sales s2 ON 
    s2.date = s1.date AND s2.hour <= s1.hour
WHERE s1.date = '2014-09-18' 
group by s1.date, s1.hour
ORDER by s1.date, s1.hour;

Чтоб получить бонус за другую дату, надо обернуть этот запрос (сделать его вложенным) и добавить агрегацию по записям аналогично:

select s1.date, s1.hour, s1.quantity, s1.total, sum(ifnull(s2.quantity,0)) as total_reference
from (
  SELECT s11.date, s11.hour, s11.quantity, sum(s12.quantity) as total
    FROM (select date, hour, sum(quantity) as quantity
      from sales group by date, hour) s11 
    JOIN sales s12 ON 
      s12.date = s11.date AND s12.hour <= s11.hour
    WHERE s11.date = '2014-09-18' 
    group by s11.date, s11.hour) s1 
  left join sales s2 on 
    s1.date = date_add(s2.date, interval 7 day) and s2.hour <= s1.hour
group by s1.date, s1.hour, s1.quantity, s1.total
ORDER by s1.date, s1.hour;

SQLFiddle

Нарастающий итог с группировкой и фильтрацией, без под-селекта и без вендоро-зависимых расширений

Ответы