Требуется помощь в создании запроса.

[Dimka]

Как я вижу, мой конкретный вопрос и обозначенную мною конкретную проблему с накопительным запросом решить не пытаются, хотя она ключевая для задания, а все усилия прилагают к бессмысленным попыткам слепить запрос, максимально похожий на нужный, вот только дата передаётся параметром. Видимо, рассчитывая, что потом как-нибудь легко это будет переделать.

Linlees, Без накопительного запроса эта задача не решается, поэтому лучше перестать заниматься ерундой и дать ответ на тот вспомогательный вопрос, который я задал в 24-м сообщении темы. За всю тему нигде, ни прямо, ни косвенно ты (даже случайно и ненароком) накопительный запрос ты не написала.

Sla привёл решение, но оно не универсально (с точки зрения СУБД), и не вполне оптимально с точки зрения плана исполнения, хотя и популярно у пользователей, например, Microsoft SQL Server. Однако из него можно извлечь ключевую идею составления накопительных запросов.

[Linlees]

Сейчас же твои попытки больше похожи на попытку получить результат шаманскими гаданиями над кодом, который ты не очень хорошо понимаешь.

Я его и впрямь не очень хорошо понимаю, иначе я бы не обращалась к чьей-то помощи, а справилась бы сама.

Как я вижу, мой конкретный вопрос и обозначенную мною конкретную проблему с накопительным запросом решить не пытаются, хотя она ключевая для задания, а все усилия прилагают к бессмысленным попыткам слепить запрос, максимально похожий на нужный, вот только дата передаётся параметром. Видимо, рассчитывая, что потом как-нибудь легко это будет переделать.

Linlees, Без накопительного запроса эта задача не решается, поэтому лучше перестать заниматься ерундой и дать ответ на тот вспомогательный вопрос, который я задал в 24-м сообщении темы. За всю тему нигде, ни прямо, ни косвенно ты (даже случайно и ненароком) накопительный запрос ты не написала.

Опять таки если бы я могла решить проблему самостоятельно, я бы сюда не обращалась. И я не могу понять почему практически в каждом сообщении вы пытаетесь этим меня унизить.

Спасибо за подсказку, буду думать как решается проблема с накопительным запросом.

[Dimka]

И я не могу понять почему практически в каждом сообщении вы пытаетесь этим меня унизить.

Дать чёткий ответ - это не унижение. А чего-то не знать или не суметь догадаться - это нормально, потому что абсолютно все сперва ничего не знают, а потом узнают. В то же время моя цель - не решить за тебя задачу, а научить решать подобные задачи. В частности, как большую проблему разбить на малые и решать их по очереди, как сконцентрироваться на ключевых вопросах.

Ты не сказала, что:
1) согласна с тем, что проблема именно в накопительном запросе;
2) не знаешь, как составить накопительный запрос;
3) больше не пытаешься это сделать сама, поскольку все твои идеи исчерпаны.

Видимо, прямые ответы по существу для тебя сродни "потере лица", хотя я так не считаю. И потому ты избегаешь конкретики - для тебя это выглядит как попытка "сохранения лица", а для меня - торможение процесса, за счёт отвлечения на разные "церемонии". Если учащийся не любит давать чёткие ответы, то в результате выйдет плохой специалист. Программирование - не гуманитарная сфера, тут частные мнения и точки зрения не играют роли, поскольку всё проверяется на практике.

Будем считать, что твои ответы:
1) да,
2) да,
3) да.

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

Например, если таблица содержит двоичные цифры:

Код:

ДВОИЧНЫЕ_ЦИФРЫ
Цифра
0
1

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

Код:

SELECT
 ДЦ1.Цифра AS Разряд2
 ДЦ2.Цифра AS Разряд1
FROM ДВОИЧНЫЕ_ЦИФРЫ ДЦ1, ДВОИЧНЫЕ_ЦИФРЫ ДЦ2

или для SQL Server

Код:

SELECT
 ДЦ1.Цифра AS Разряд2
 ДЦ2.Цифра AS Разряд1
FROM ДВОИЧНЫЕ_ЦИФРЫ ДЦ1 CROSS JOIN ДВОИЧНЫЕ_ЦИФРЫ ДЦ2

В результате получится таблица:

Код:

Разряд2 Разряд1
0       0
0       1
1       0
1       1

Или к каждой записи первого экземпляра таблицы подставляются все записи второго экземпляра таблицы.

Именно это и нужно для накопительного запроса:
1) к каждой записи первого экземпляра таблицы подставить все записи второго экземпляра таблицы,
2) отфильтровать ненужные строки, в которых записи второго экземпляра таблицы не подходят к записи первого экземпляра таблицы;
3) затем применить группировку по первому экземпляру таблицы, чтобы агретной функцией получить обобщённый результат по записям второго экземпляра таблицы.
(Порядок операций и секции запроса, отвечающие за эти операции, я описывал выше.)

Попробуй составить накопительный запрос по мною приведённой упрощённой таблице. Со своими таблицами пока не связывайся, поскольку там нужны дополнительные группировки по товарам и соединения разных таблиц - это усложняет ответ и затемняет суть дела. Если что-то непонятно - спроси.

[Linlees]

2) отфильтровать ненужные строки, в которых записи второго экземпляра таблицы не подходят к записи первого экземпляра таблицы;

Не  поняла этот момент.

[Dimka]

Linlees, очевидно, что для накопительной суммы операция SUM должна выполняться не по всем записям второй таблицы, а только по тем, дата которых не превышает даты записи из первой таблицы. Это ты уже пыталась делать, но никогда не соединяла таблицу с самой собой.

[Linlees]

Код:

SELECT p1.date, SUM(p2.summa) AS itog
FROM plateg AS p1 CROSS JOIN plateg AS p2
WHERE p2.date<=p1.date
GROUP BY  p1.date
ORDER BY p1.date

[Dimka]

Во, это накопительный запрос. Только лучше бы:

Код:

SELECT
 ГП1.ДатаПлатежа,
 SUM(ГП2.СуммаПлатежа) AS СуммаПлатежейНаДату
FROM
 ГРАФИК_ПЛАТЕЖЕЙ ГП1
  INNER JOIN ГРАФИК_ПЛАТЕЖЕЙ ГП2
   ON ГП1.ДатаПлатежа >= ГП2.ДатаПлатежа
GROUP BY ГП1.ДатаПлатежа

Вообще СУБД обычно сами различают, что фильтровать можно сразу по мере построения CROSS JOIN, но явное задание условия в JOIN гарантирует, что фильтрация будет выполняться именно по мере построения JOIN, так как это условие связывания, а не после. Если же СУБД по какой-то причине решит делать эти операции последовательно, то CROSS JOIN может серьёзно затормозить работу запроса.

Теперь попробуй построить запрос на своей таблице ЗАКАЗЫ, получая в результате:

НомерТовара, ДатаИсполнения, ИтоговоеКоличествоЗаказанногоТовараНаДату

Тут нужно в тот накопительный запрос, который у тебя получился (переделав его под ЗАКАЗЫ), добавить ещё группировку по товарам.

[Linlees]

Код:

SELECT
 o1.execution_date,
 o1.product_id,
 SUM(o2.number) AS itog
FROM
 orders AS o1
  INNER JOIN orders AS o2
   ON o1.execution_date >= o2.execution_date
GROUP BY o1.product_id, o1.execution_date
ORDER BY o1.execution_date

[Dimka]

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

Только не вставляй ORDER BY всегда и везде. Он нужен исключительно для отображения человеку или для экзотических случаев, когда в таблице используются порядковые номера строк.

Дальше полезно применить такой приём:

Тот, что у тебя получилось, считай некой "временной" таблицей. Допустим РАСХОД ТОВАРОВ. Т.е. твой запрос можно считать подзапросом для более общего запроса

Код:

SELECT *
FROM
 (
  SELECT
   З1.НомерТовара,
   З1.ДатаИсполнения,
   SUM(З2.КоличествоЗаказанногоТовара) AS ИтоговоеКоличествоЗаказанногоТовараНаДату
  FROM
   ЗАКАЗЫ З1
    INNER JOIN ЗАКАЗЫ З2
     ON З1.ДатаИсполнения >= З2.ДатаИсполнения
  GROUP BY З1.НомерТовара, З1.ДатаИсполнения
 ) AS РАСХОД_ТОВАРОВ

1) Тебе нужно получить ещё одним подзапросом другую "временную" таблицу по поставкам, пусть она называется ПРИХОД_ТОВАРОВ, в которой бы содержались нужные поля для соединения с РАСХОД_ТОВАРОВ. Однако в ней накопительные суммы не нужны, потому что она не является основой окончательного запроса. Это понятно, почему так?

2) Соединить РАСХОД_ТОВАРОВ и ПРИХОД_ТОВАРОВ аналогично тому, как ты соединяла таблицу ЗАКАЗЫ саму с собой. Ну и остаётся лишь сделать вычитание:

SUM(ПРИХОД_ТОВАРОВ.КоличествоПоставленногоТовара) - ИтоговоеКоличествоЗаказанногоТовараНаДату AS КоличествоТовараВНаличии

[Linlees]

Простите, что забегаю вперед, но у меня возник вопрос - пока мы исходим из того, что товар поставляется один и тот же, в данном случае мы подсчитываем общее кол-во товара, не беря во внимание, что товар может быть разный Как можно подсчитать ков-во заказанного не всего товара в целом, а именно этого товара?

[Sla]

Linlees,
А это что?
НомерТовара

[Linlees]

да

[Dimka]

Простите, что забегаю вперед, но у меня возник вопрос - пока мы исходим из того, что товар поставляется один и тот же, в данном случае мы подсчитываем общее кол-во товара, не беря во внимание, что товар может быть разный Как можно подсчитать ков-во заказанного не всего товара в целом, а именно этого товара?

Так добавь в условие JOIN ... ON равенство по номеру товара. Ты ж ведь что с чем соединяешь? Какие записи должны попасть в группу? Как эти записи получить?

[Linlees]

Код:

SELECT rashod.execution_date, rashod.product_id, (SUM(prihod.number)-rashod.itog) AS nalichie
FROM 	
	(SELECT o1.execution_date,  o1.product_id, SUM(o2.number) AS itog
	FROM orders AS o1
	INNER JOIN orders AS o2
	ON (o1.execution_date >= o2.execution_date AND o1.product_id=o2.product_id)
	GROUP BY o1.product_id, o1.execution_date) AS rashod
	
INNER JOIN

	(SELECT c.product_id, c.number, d.date 
	FROM consignment AS c 
	JOIN delivery AS D 
	ON (d.id=c.delivery_id) ) AS prihod

ON (rashod.execution_date >= prihod.date AND rashod.product_id=prihod.product_id)
GROUP BY rashod.execution_date, rashod.product_id, rashod.itog
ORDER BY rashod.execution_date

[Dimka]

А теперь можно упрощать, убирая подзапросы "временных таблиц", и оптимизировать по производительности.

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

Кстати, для справки: INNER JOIN и просто JOIN - это одно и то же.

И почему  itog включён в группировку? Потому что, если его не включать, не даёт выполнить запрос - говорит, что используется поле, не включённое в группировку?

[Linlees]

А теперь можно упрощать, убирая подзапросы "временных таблиц", и оптимизировать по производительности.

- если можно, покажите пример

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

выдает правильно, я проверила, только вот один нюанс:
например 18 мая было поставлено 16 штук какого-то товара
21 мая исполнили заказ на 4 штуки этого товара.
этот запрос выдаст результат:
"товар"    21-05    4      12
нельзя ли как-то сделать, чтобы он выдавал
"товар"    21-05    4      16, т.е. без учета последнего заказа.
Если убрать знак равенства в условии, то он выдает полную белиберду

Кстати, для справки: INNER JOIN и просто JOIN - это одно и то же.

спасибо, буду знать.

И почему  itog включён в группировку? Потому что, если его не включать, не даёт выполнить запрос - говорит, что используется поле, не включённое в группировку?

да, именно поэтому.

[Dimka]

покажите пример

Покажу.

Если убрать знак равенства в условии, то он выдает полную белиберду

В запросе 2 таких места. В каком убираешь?

[Linlees]

o1.execution_date > o2.execution_date - появляется белиберда
rashod.execution_date > prihod.date - ничего не меняется
если одновременно - то же самое

[Dimka]

По поводу производительности. Основное правило: как можно раньше отсекать лишние записи.

Например, пусть есть таблица T1, в которой 100 записей, и таблицы T2 и T3, в которых по 10000 записей.

Запрос

Код:

SELECT * FROM T1 INNER JOIN T2 ON T1.T2ID = T2.ID INNER JOIN T3 ON T2.T3ID = T3.ID

сначала соединяет 100 и 10000 записей, получая в результате 100 записей, и потом соединяет их с ещё 10000, опять получая 100 записей. Это работает гораздо быстрее, чем запрос

Код:

SELECT * FROM T2 INNER JOIN T3 ON T2.T3ID = T3.ID INNER JOIN T1 ON T2.ID = T1.T2ID

где в начале соединяются 10000 и 10000 записей, что даёт промежуточный результат в 1000000000 записей, который потом соединяется с 100 записями, чтобы получить 100 записей.

Затем. Пусть есть таблица T, в которой 10000 записей, которые образуют 100 групп по полю T.X по 100 записей в каждой. Требуется выбрать 1 группу, в которой T.X = P. Запрос

Код:

SELECT T.X, COUNT(T.Y) AS Y FROM T WHERE T.X = P GROUP BY T.X

должен в начале просмотреть 10000 записей, чтобы отфильтровать 9900 записей и потом оставшиеся 100 записей подвергнуть группировке, собрав их в одну группу и выдав результат в 1 строчку.

Запрос

Код:

SELECT T.X, COUNT(T.Y) AS Y FROM T GROUP BY T.X HAVING T.X = P

или

Код:

SELECT * FROM (SELECT T.X, COUNT(T.Y) AS Y FROM T GROUP BY T.X) T1 WHERE X = P

должен в начале сгруппировать 10000 записей, получив 100 записей с агрегированными результатами, а потом из них отфильтровать 99, оставив 1 запись.

Что тут работает быстрее - вопрос открытый. Операция группировки, как правило, более трудоёмкая, чем фильтр, поэтому выгоднее первый запрос. Однако, если условие фильтрации предполагает сложные вычисления или подзапросы в конструкциях IN или EXISTS, то второй вариант может оказаться предпочтительней.

То же самое касается сочетаний вертикальных и горизонтальных фильтров. Пусть для той же таблицы T надо вывести некий диапазон значений X, которые там есть. Пусть таких значений 5. Запрос

Код:

SELECT DISTINCT T.X FROM T WHERE T.X BETWEEN P AND P1

сначала будет фильтровать 10000 записей, чтобы выбрать 500, из которых потом будет извлечено значение поля X, и лишь затем оператор DISTINCT уберёт повторы, сократив результат до 5-и записей.

А запрос

Код:

SELECT * FROM (SELECT DISTINCT T.X FROM T) T1 WHERE X BETWEEN P AND P1

Вначале выберет поле X, затем уберёт повторные значения, получив 100 строчек, и лишь потом начнёт их фильтрацию, оставив в результате 5.

Опять же, вопрос открытый, что быстрее: DISTINCT или фильтр по 10000 записям - зависит от фильтра.

Запрос вида

Код:

SELECT ID FROM T WHERE X NOT IN (SELECT X FROM U WHERE T.ID = U.TID)

по производительности хуже запроса

Код:

SELECT ID FROM T LEFT OUTER JOIN U ON T.ID = U.TID WHERE U.X IS NULL

при одинаковом результате. Поскольку в первом случае на каждую запись T делается отдельный подзапрос по U, и  таблица U читается столько раз, сколько записей в T. Во втором случае производится левое внешнее соединение, в котором отсутствующие записи U заменяются значениями NULL, после чего именно эти записи отбираются фильтром. Каждая из таблиц читается 1 раз.

И т.д. Главное: точно понимать, в каком порядке СУБД выполняет элементарные операции над таблицами, которые я перечислял. И всегда думать, сколько записей (хотя бы порядок) содержится в тех таблицах, по которым строятся запросы.

[Dimka]

o1.execution_date > o2.execution_date - появляется белиберда

А эта белиберда выглядит как пропадающие записи, неправильные суммы или как NULL?

При строгом неравенстве последняя строчка в группе из 2-го экземпляра таблицы, совпадающая со строчкой из 1-го экземпляра таблицы, в результат, естественно, не попадает. Но эта строчка - единственная для первой группы, поскольку дат, меньших, чем в этой строчке, больше нет. Это приводит к тому, что первая группа целиком выбрасывается из результата.

Тут надо использовать OUTER JOIN (LEFT или RIGHT), но поскольку в результате получится NULL, то SUM(NULL)=NULL, и вся разность тоже будет NULL. Это не то, что нужно. Для борьбы с этим в SQL Server есть функция ISNULL(<проверяемое значение>, <значение по умолчанию, если проверяемое является NULL>), а в MySQL она называется IFNULL. Есть ли она и как называется в PostgreSQL, я не знаю.

[Linlees]

Выглядит как пропадающие записи и неправильные суммы.
OUTER JOIN (LEFT или RIGHT) ничего не дал.

Нашла в инете такую заметку:
"Чтобы соединить с возможными значениями NULL, используйте COALESCE() как здесь:
   SELECT COALESCE(col1, '') || COALESCE(col2, '')
   FROM tab

Чтобы отсортировать данные по значению используйте модификаторы IS NULL и IS NOT NULL в выражении ORDER BY. Когда они будут генерировать значения истина, то при сортировке они будут выше, чем значения ложь, так что записи с NULL будут в отсортированном списке сверху:
   SELECT *
   FROM tab
   ORDER BY (col IS NOT NULL);"

ссылка http://www.postgresql.org/docs/faqs.FAQ_russian.html   вопрос 4.9

Это можно как-то использовать в моем случае, если да - то как?

[Dimka]

OUTER JOIN (LEFT или RIGHT) ничего не дал.

Не верю. Приведи примеры данных из таблиц, на которых проверяешь, и полный запрос с outer join, а также результат запроса.

[Linlees]

Код:

SELECT rashod.execution_date, rashod.product_id, rashod.number, (SUM(prihod.number)-rashod.itog) AS nalichie
FROM 	
	(SELECT o1.execution_date,  o1.product_id, o1.number, SUM(o2.number) AS itog
	FROM orders AS o1
	JOIN orders AS o2
	ON (o1.execution_date >= o2.execution_date AND o1.product_id=o2.product_id)
	GROUP BY o1.product_id, o1.execution_date, o1.number) AS rashod
	
JOIN

	(SELECT c.product_id, c.number, d.date 
	FROM consignment AS c 
	JOIN delivery AS D 
	ON (d.id=c.delivery_id) ) AS prihod

ON (rashod.execution_date >= prihod.date AND rashod.product_id=prihod.product_id)
GROUP BY rashod.execution_date, rashod.product_id, rashod.itog, rashod.number
ORDER BY rashod.execution_date

Результат:
дата                продукт заказано наличие
"2009-02-20"       4              2             8
"2009-03-05"       7              4            16
"2009-03-24"       5              2            26
"2009-03-30"       3              2            13
"2009-04-02"       2              1            22
"2009-04-20"       1              2            21
"2009-04-24"       3              1            22
"2009-04-25"       4              1              7
"2009-05-06"       6              3             17
"2009-05-08"       1              1             20
"2009-05-22"       3              1             21

Код:

SELECT rashod.execution_date, rashod.product_id, rashod.number, (SUM(prihod.number)-rashod.itog) AS nalichie
FROM 	
	(SELECT o1.execution_date,  o1.product_id, o1.number, SUM(o2.number) AS itog
	FROM orders AS o1
	LEFT OUTER JOIN orders AS o2
	ON (o1.execution_date > o2.execution_date AND o1.product_id=o2.product_id)
	GROUP BY o1.product_id, o1.execution_date, o1.number) AS rashod
	
JOIN

	(SELECT c.product_id, c.number, d.date 
	FROM consignment AS c 
	JOIN delivery AS D 
	ON (d.id=c.delivery_id) ) AS prihod

ON (rashod.execution_date >= prihod.date AND rashod.product_id=prihod.product_id)
GROUP BY rashod.execution_date, rashod.product_id, rashod.itog, rashod.number
ORDER BY rashod.execution_date

Результат:
"2009-02-20"    4  2
"2009-03-05"    7  4
"2009-03-24"    5  2
"2009-03-30"    3  2
"2009-04-02"    2  1
"2009-04-20"    1  2
"2009-04-24"    3  1    23
"2009-04-25"    4  1     8
"2009-05-06"    6  3
"2009-05-08"    1  1     21
"2009-05-22"    3  1     22


Если применять во внешнем запросе или RIGHT, результат как в первом запросе.

[Dimka]

А что значат пустые строчки. Ведь NULL-ы же. Первая запись с 3-м товаром - пустая, а все остальные содержат значения. Я же ещё вчера написал:

но поскольку в результате получится NULL, то SUM(NULL)=NULL, и вся разность тоже будет NULL. Это не то, что нужно. Для борьбы с этим в SQL Server есть функция ISNULL(<проверяемое значение>, <значение по умолчанию, если проверяемое является NULL>), а в MySQL она называется IFNULL. Есть ли она и как называется в PostgreSQL, я не знаю.

Нужно SUM оборачивать в функцию, которая заменяет NULL на число (например, 0). Можно и COALESCE.

[Linlees]

Код:

SELECT rashod.execution_date, rashod.product_id, rashod.number, (SUM(prihod.number)-rashod.itog) AS nalichie
FROM 	
	(SELECT o1.execution_date,  o1.product_id, o1.number, (SUM(COALESCE(o2.number,0))) AS itog
	FROM orders AS o1
	LEFT OUTER JOIN orders AS o2
	ON (o1.execution_date > o2.execution_date AND o1.product_id=o2.product_id)
	GROUP BY o1.product_id, o1.execution_date, o1.number) AS rashod
	
JOIN

	(SELECT c.product_id, c.number, d.date 
	FROM consignment AS c 
	JOIN delivery AS D 
	ON (d.id=c.delivery_id) ) AS prihod

ON (rashod.execution_date >= prihod.date AND rashod.product_id=prihod.product_id)
GROUP BY rashod.execution_date, rashod.product_id, rashod.itog, rashod.number
ORDER BY rashod.execution_date 

Все заработало как надо!

dimka, Sla,  спасибо огромное, очень признательна вам за  помощь и терпение.

[voland]

Linlees, а как вы установили связи между таблицами, можно сиквел-кодом показать.