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

[Linlees]

Запрос на PostgreSQL
Имееются таблицы:
заказы; поля: номер,  работник_оформивший_заказ, дата_размещения, дата_исполнения, номер_товара, кол-во_заказанного_товара. (В одном заказе может быть только один товар)
поставки; поля: номер,  дата_поставки.
накладные; поля: номер_поставки, номер_товара, кол-во_поставленного_товара. (В одной накладной может быть только один товар).
Необходимо вывести:
Для каждого заказа - номер_товара, количество_заказанного_товара, дата_исполнения, количество товара в наличии на дату исполнения. Упорядочить по дате исполнения и наименованию товара.

Заранее спасибо.

[Dimka]

Больше всего меня радует вот этот шаблон:

Требуется помощь
Дано:...
Нужно:...
Заранее спасибо.

Стандартная форма заказа

[Linlees]

Простите, не поняла юмора...
Что значит: форма заказа?
Если вы имеете ввиду, что данная помощь оказывается платно, то я готова заплатить.

[Джон]

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

Поэтому надо просто называть вещи своими именами не стесняясь, Сразу оговорить условия, сроки (это тоже важно) или срочность исполнения уже теперь ЗАКАЗА, а не ПОМОЩИ и тд. При наличии желающих с достаточным количеством свободного времени я думаю решение будет найдено, при полном непротивлении сторон.

[Linlees]

Вот то, что делала я сама:
SELECT O_DATE AS DATA_ZAKAZA, P_NAME AS TOVAR, SUM(ZAKAZ_NUM) AS ZAKAZANO, SUM(OSTATOK) AS V_OSTATKE
FROM (

SELECT q1.ddate AS O_DATE, q1.PROD_NAME AS P_NAME, q1.number AS ZAKAZ_NUM, (q2.number - q1.number) AS OSTATOK
FROM (
   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   FROM
      orders AS o JOIN products AS p ON(o.product_id=p.id)
   ORDER BY o.execution_date) AS q1
   

   JOIN

      (SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
      FROM
         consignment AS c JOIN delivery AS d ON(c.delivery_id=d.id)
      GROUP BY d.date, c.product_id
      ORDER BY d.date,c.product_id) AS q2

   ON(q1.product=q2.product AND q1.ddate > q2.ddate)   
ORDER BY q1.ddate, q1.PROD_NAME

   ) AS Q
GROUP BY Q.O_DATE, Q.P_NAME
ORDER BY Q.O_DATE, Q.P_NAME;

consignment - накладная, order - заказ, delivery - поставка.

Но я уверена, что это не правильно.  Мне кажется, что к данному запросу нужно делать какую то дополнительную процедуру, но т.к. мои знания ограничиваются общим курсом БД, преподаваемом в ВУЗе, я прошу помощи.
Подскажите хотя бы в каком направлении двигаться.

[Dimka]

ЗАКАЗЫ: Номер,  Работник оформивший заказ, Дата размещения, Дата исполнения, Номер товара, Количество заказанного товара.

ПОСТАВКИ: Номер,  Дата поставки.

НАКЛАДНЫЕ: Номер поставки, Номер товара, Количество поставленного товара.

Необходимо вывести:

Для каждого заказа: Номер товара, Количество заказанного товара, Дата исполнения, количество товара в наличии на дату исполнения.

Упорядочить по дате исполнения и наименованию товара.

Вот скажи, как по-твоему, достаточно ли информации указано в "Дано", чтобы выполнить "Необходимо"?

[Sla]

Для каждого заказа - номер_товара, количество_заказанного_товара, дата_исполнения, количество товара в наличии на дату исполнения. Упорядочить по дате исполнения и наименованию товара.

Код:

   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   FROM
      orders AS o JOIN products AS p ON(o.product_id=p.id)
   ORDER BY o.execution_date) 

Выбрали продукт и дату исполнения

Кстати ORDER BY здесь не нужен, потому как у тебя далее будет идти join  и, кроме того, это у тебя подзапрос

Код:

      SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
      FROM
         consignment AS c JOIN delivery AS d ON(c.delivery_id=d.id)
      GROUP BY d.date, c.product_id
      ORDER BY d.date,c.product_id

здесь то же лишний order by

Но принципиальная ошибка, ведь ты используешь объединение запросов
Сравни:
   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
И скажи, почему я обратил на это внимание?

[Linlees]

Вот скажи, как по-твоему, достаточно ли информации указано в "Дано", чтобы выполнить "Необходимо"?

Ивините,  если чего то не хватает, то скажите чего, я дополню. Но я перечислила все поля, которые содержат эти таблицы.

Сравни:
   SELECT p.item AS PROD_NAME, p.id AS product, o.number AS number, o.execution_date AS ddate
   SELECT c.product_id AS product, d.date AS ddate, SUM(c.number) AS number
И скажи, почему я обратил на это внимание?

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

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

[McZim]

Linlees, Что делает SELECT q1.ddate?

[Linlees]

Выводит дату исполнения заказа...

[Sla]

Возможно потому, что эти запросы нельзя объединять?

Почему?

[Linlees]

Почему?

А вот этого я уже не знаю:(

[Sla]

Давай с самого начала (потому как я тут такого ща наговорил, что самому стыдно - union с join перепутал)  разгребать постепенно, а не сразу ваять большой запрос
1. Выбрать
    Заказ, Номер_товара, количество_заказанного_товара, дата_исполнения
2. Выбрать
   номер_товара, количество_поставленного_товара

3. Выбрать
Количество_товара_в наличии

[Linlees]

1. SELECT id, product_id, number, execution_date FROM orders

2. SELECT product_id, number FROM consignment

3. вот тут у меня загвоздка: я не знаю как это можно вычислить.

[Sla]

Linlees, ну а как ты посчитала бы без базы, а на бумажке?

[Linlees]

Нужно проверить на какую дату мы считаем это наличие, потом сосчитать общее количество товари, поставленного на эту дату и вычесть из этого количество товара, заказанного на эту дату.
Если не брать в расчет определенную дату, то запрос на разницу между ВСЕМ поставленным и ВСЕМ заказанным я могу написать

Код:

 SELECT o.product_id, COUNT(c.number), COUNT(o.number) 
FROM orders AS o 
JOIN consignment AS c ON c.product_id=o.product_id)
GROUP BY o.product_id;

   - что-то подобное

а вот как это привязать к определенной дате которая заранее неизвестна ....

[Sla]

а если так?

Код:

SELECT o.product_id, ( COUNT(c.number) - COUNT(o.number) )
FROM orders AS o 
JOIN consignment AS c ON c.product_id=o.product_id)
GROUP BY o.product_id;

[Linlees]

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

[Sla]

А теперь
1. Выбрать
    Заказ, Номер_товара, количество_заказанного_товара,
где дата_исполнения меньше 01/01/2009

2. Выбрать
   номер_товара, количество_поставленного_товара, дата_поставки
где дата_поставки меньше 01/01/2009

[Linlees]

1.SELECT product_id, number FROM orders WHERE (execution_date > 2009-01-01);

2.SELECT c.product_id, c.number, d.date FROM consignment AS c JOIN delivery AS d ON (c.product_id=d.product_id) WHERE (d.date > 2009-01-01); - возможно здесь нужен GROUP BY...

[Sla]

SELECT c.product_id, c.number, d.date
     FROM consignment AS c
      JOIN delivery AS d
on (c.product_id=d.product_id) WHERE (d.date < 2009-01-01)

А зачем group by? У тебя здесь есть агрегатные функции?

МЕНЬШЕ!

[Linlees]

Ой, простите, это моя невнимательность.

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

[Dimka]

Ивините,  если чего то не хватает, то скажите чего, я дополню. Но я перечислила все поля, которые содержат эти таблицы.

Да, но в таблицах нет непосредственно запрашиваемого результата. Дальше Sla уже понял главную проблему:

а как ты посчитала бы без базы, а на бумажке?

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

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

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

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

"Вдруг" не бывает. Это ж не гадание на картах.

Возьми по 5 записей с каждой таблицы (лишь бы они сочетались по датам и связям), и на них подбери, какие нужны операции для получения результата:
1) объединение таблиц (и способ объединения - FROM ... JOIN),
2) фильтрация строк (WHERE),
3) группировка (GROUP BY),
4) фильтрация групп (HAVING),
5) отбор столбцов (SELECT), вычисление значений (в том числе функции COUNT, SUM, AVG и т.д.), ликвидация повторов строк (DISTINCT)
6) сортировка (ORDER BY)
Операции применять в указанном порядке.

[Sla]

dimka, тут есть уже сдвиги

Кажется в голове начинает проясняться, но надо проверить на практике.

[Dimka]

Sla, не думаю.

Начать можно с малого:

сосчитать общее количество товари, поставленного на эту дату

Это элементарно для случая, когда дата задаётся параметром запроса. Но вот случай, когда нужно считать накопительную сумму - это уже на догадливость.

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

Допустим, есть таблица
ГРАФИК ПЛАТЕЖЕЙ: Дата платежа, Сумма платежа.

Например:
01.04.09   20000.00
02.04.09   03742.11
03.04.09   01743.37
04.04.09   00003.37
13.04.09   01129.80
14.04.09   15000.00


Требуется написать запрос, который для каждой даты выдаёт накопительный итог:

01.04.09   20000.00
02.04.09   23742.11
03.04.09   25485.48
04.04.09   25488.85
13.04.09   26618.65
14.04.09   41618.65

[Sla]

select field_data, (select sum(field_sum) from table where field_data<=t.field_data) itog
from table t
order by field_data

Не знаю, не проверял, но должно работать
А вот интересно, поможет ли это в нашем случае?

[Linlees]

Практически не удалось посидеть подумать над запросом, результата пока никакого, последний вариант:

Код:

SELECT ord.product_id, ord.number, ord.execution_date, COUNT(ord.number) AS ostatok 
GROUP BY ord.product_id, ord.number, ord.execution_date
HAVING	COUNT(ord.number)=(
			SELECT (COUNT(c.number) - COUNT(o.number)) 
			FROM orders AS o 
			JOIN consignment AS c ON (c.product_id=o.product_id) 
			JOIN delivery AS d ON (c.delivery_id=d.id)
			GROUP BY o.product_id, d.date, o.execution_date 
			HAVING (d.date<ord.execution_date AND o.execution_date<ord.execution_date)
			)
FROM orders AS ord
ORDER BY ord.product_id;

Но он не работает,  выдает ошибку ERROR:  more than one row returned by a subquery used as an expression.

Завтра буду разбираться, что не так.

[Linlees]

В общем это все что у меня получилось
 

Код:

SELECT (SUM(c.number)-SUM(o.number)) 
			FROM orders AS o 
			JOIN consignment AS c ON (c.product_id=o.product_id) 
			JOIN delivery AS d ON (c.delivery_id=d.id)
			GROUP BY o.product_id, d.date, o.execution_date 
			HAVING (d.date<'2009-04-01' AND o.execution_date<'2009-04-01')
			; 

  -это внутренний  подзапрос, с COUNT он не работает, выдает одни нули. А с SUM работает - проверила по значениям из таблицы. Выдает столбец с разницей поставленного и заказанного на эту дату.
К внешнему запросу где нужно выбрать номер_продукта и дату_исполнения мне его никак не прикрутить.
Все время пишет эту ошибку, что подзапрос выдает более чем один ряд. Где я ошиблась?

[Dimka]

select field_data, (select sum(field_sum) from table where field_data<=t.field_data) itog
from table t
order by field_data

Да, это типовое решение. Я только не знаю, есть ли в PostgreSQL поддержка подзапросов, которые работают в секции SELECT.

А ты напиши решение без таких подзапросов. Чтобы. допустим, в FoxPro 2.6 под DOS работало

[Linlees]

Я только не знаю, есть ли в PostgreSQL поддержка подзапросов, которые работают в секции SELECT.

не поддерживает, сегодня это проверила на собственном опыте.

Код:

SELECT  o.product_id, o.execution_date,  (q2.summa1-q1.summa) AS OSTATOK		
FROM		(SELECT product_id,  SUM(number) AS summa
		FROM orders 
		GROUP BY product_id 
		HAVING MAX(execution_date)<'2009-05-23') AS q1
JOIN
		(SELECT product_id,  SUM(number) AS summa1 
		FROM consignment AS c  
		JOIN delivery AS d ON (c.delivery_id=d.id)
		GROUP BY product_id
		HAVING MAX(d.date)<'2009-05-23') AS q2
ON (q1.product_id=q2.product_id)
JOIN orders AS o
ON (o.product_id=q1.product_id)	
GROUP BY o.execution_date, o.product_id, q2.summa1, q1.summa 
ORDER BY o.execution_date;

Осталась проблема привязки к дате_исполнения...

[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, а как вы установили связи между таблицами, можно сиквел-кодом показать.