Ох... сразу два замечания:
1) никогда не используйте вложенные запросы, ибо, по мере накопления записей в таблицах, время выполнения запроса начнет возрастать до секунд, а то и десятков секунд;
2) при именовании таблиц, полей, да и переменных, при написании кода, не используйте не несущие смысла имена вроде b, idf, cr и т.д. - это делает код трудночитаемым не только для посторонних, но и для написавшего уже через пару недель, а также чревато ошибками.
По проектированию... я пока только бегло посмотрел.
Давайте так: опишите какие данные вам нужно получать, каким образом, что хранить, какие операции проводить и т.д., чтобы я целиком представлял задачу. А уже выборки по отчетности сделаем в конце.
Теперь о задачах:
Как я понял, у мня получилась структура связи таблиц один-ко-многим.
Таблица jurnal — представлен ее сокращенный вариант. На самом деле, там будет еще много полей, «кто, что, когда, зачем и т.п.». Но в учебных целях, мне пока достаточно этих.
В данную таблицу заносятся заявки и их суммы (счет заявки) 'sumdoc' и т.п.
Поле 'id_firm' для связи с таблицей firms.'id'.
Таблица firms содержит наименование компаний по заявкам.
Следовательно, в первом случае, получаем связь один-ко-многим: наименование фирмы (firms.'n_short') → и ее заявки ( COUNT(jurnal.'id'), SUM(jurnal.'sumdoc') )
Таблица credit (возможно, правильней было бы ее назвать orders) содержит суммы поступления по каждой заявке и дату поступления. Т.е., по одной заявке может быть несколько поступлений. Credit, по всей видимости, у меня является связующей таблицей между firms и jurnal. В таблице credit кроме суммы поступления (поле 'postup') и даты поступления (поле 'datapost') имеются поля для связи с таблицами firms.'id' и jurnal.'id' — 'id_jur' и 'id_firm' соответственно.
В идеале, мне нужно получать сводную таблицу по запросам ко всем этим таблицам, с выводом на экран следующих данных:
1. «№ п/п»
2. «Наименование компании» ( поле 'n_short' т. firms )
3. «Количество заявок» ( count(jurnal.'id') )
4. «Сумма заявок» ( sum(jurnal.'sumdoc') )
5. «Количество поступлений» ( count(credit.'id') )
6. «Сумма поступлений» ( sum(credit .'postup') )
7. «Сумма остатка» ('debet': Сумма заявок-Сумма поступлений)
Примерно, то, что на второй картинке результата, приведенной ранее. Только без поля idf (id firms). Ну, а в будущем, делать более подробные выборки по каждой заявке или фирме.
Правда, в свете ваших замечаний, не представляю себе, как не использовать вложенные запросы при обращении к 3-м таблицам, если мне, к примеру, нужно будет получать фирмы, количество заявок и поступлений, суммы по дате поступления сумм (таблица credit 'datapost') и прочие данные по запросу из других таблиц одновременно, а одном запросе. Я впал в ступор:)
А самое главное, как выбрать фирмы, соответствующее количество заявок и их суммы, где сумма заявки = суммам поступлений, ('debet'=0)?
К примеру, для фирмы Южная компания (выделена желтым). Из первого запроса видно, что у этой фирмы 4 заявки, и 4 поступления ('cnt_cr'). Два поступления закрывают первую заявку ('id_jur' = 14) и одно поступление закрывают вторую заявку ('id_jur' = 17). По заявке 'id_jur' = 15 одно поступление и заявка не закрыта, по другому 'id_jur' = 16, поступлений вообще нет.
В итоге запроса для получения «закрытых» заявок должно было получиться:
«Наименование компании» - Южная компания
«Количество заявок» - 2
«Сумма заявок» - 7583,52
«Количество поступлений» - 3
«Сумма поступлений» - 7583.52
«Сумма остатка» - 0
Ох, надеюсь поняли, что я тут изложил. Сумбур, наверное какой-то..
PS: ошибка в данных, перезалил дамп
Например, так: SELECT firms.n_short, SUM( credit.postup ) AS sum_post, SUM( jurnal.sumdoc ) AS sum_zayav, SUM( credit.postup ) - SUM( jurnal.sumdoc ) AS debet, COUNT( DISTINCT credit.id ) AS count_post, COUNT( DISTINCT jurnal.id ) AS count_zayav FROM credit LEFT JOIN firms ON firms.id = credit.id_firm LEFT JOIN jurnal ON jurnal.id_firm = credit.id_firm GROUP BY firms.id
Получаем:
Если нужно выбрать закрытые, добавляем HAVING debet = 0
если наоборот, то HAVING debet != 0
Настоятельно рекомендую почитать про различные типы джойнов, например, здесь.
При этом следует отметить, что UNION JOIN устарел; джойнить можно много таблиц различными способами, в том числе одну и ту же таблицу несколько раз под разными именами (только когда реально необходимо); понимать, что такое DISTINCT - выбор уникальных записей; в подавляющем большинстве случаев объединения работают на порядки быстрее вложенных запросов, однако, в очень редких случаях - наоборот, тут в помощь профайлер (хотя бы в phpMyAdmin и баловство с таблицами на 500к+ записей и полное осознание проделываемого).
Если я что-то упустил - спрашивайте.
В принципе, подход верный, поскольку нужны две независимые группировки.
Первое, что я бы порекомендовал - нагенерить в таблицы, рост которых подразумевается наиболее сильно, по 200-300к записей. Именно случайных из определенных диапазонов, после чего заняться оптимизацией.
В данном месте FROM `jurnal` , `firms`
запятая - то же самое, что INNER JOIN, и, если записать именно в этом виде - будет нагляднее и сразу заметно, что таблицу firms лучше джойнить уже в самом конце к полученному результату, а до этого работать только с айдишниками фирм. В противном случае - лишняя нагрузка по объединению, лишней сортировке и выбрасыванию дубликатов.
Здесь аналогично: FROM `firms` , `credit`
То есть, в итоге, должно получиться три отдельных запроса (два сложных с группировкой и отдельный по firms) и джоины между ними. При этом, в WHERE тоже удастся избавиться от лишней сортировки и удаления дубликатов.
Далее нужно понять, насколько часто будут делаться подобные запросы по отношению к операциям вставки. Если раз в день/неделю/месяц - это одно, если несколько раз в минуту/секунду - нужно делать кэш, где будут храниться уже сгруппированные данные, которые будут пересчитываться по событию добавления записи.
Если фирм вообще будет немного (до 1к) их можно вообще всегда держать в памяти приложения, например, в мапе, тоже с обновлением по событию и "джойнить" уже в приложении.
Don't ask to ask, just ask. В смысле задавайте, будет время - отвечу.
спасибо, сейчас подготовлю вопрос!
В трёх экземплярах? ヅ
Ох... сразу два замечания:
1) никогда не используйте вложенные запросы, ибо, по мере накопления записей в таблицах, время выполнения запроса начнет возрастать до секунд, а то и десятков секунд;
2) при именовании таблиц, полей, да и переменных, при написании кода, не используйте не несущие смысла имена вроде b, idf, cr и т.д. - это делает код трудночитаемым не только для посторонних, но и для написавшего уже через пару недель, а также чревато ошибками.
По проектированию... я пока только бегло посмотрел.
Давайте так: опишите какие данные вам нужно получать, каким образом, что хранить, какие операции проводить и т.д., чтобы я целиком представлял задачу. А уже выборки по отчетности сделаем в конце.
Благодарю за замечания и участие!
Теперь о задачах:
Как я понял, у мня получилась структура связи таблиц один-ко-многим.
Таблица jurnal — представлен ее сокращенный вариант. На самом деле, там будет еще много полей, «кто, что, когда, зачем и т.п.». Но в учебных целях, мне пока достаточно этих.
В данную таблицу заносятся заявки и их суммы (счет заявки) 'sumdoc' и т.п.
Поле 'id_firm' для связи с таблицей firms.'id'.
Таблица firms содержит наименование компаний по заявкам.
Следовательно, в первом случае, получаем связь один-ко-многим: наименование фирмы (firms.'n_short') → и ее заявки ( COUNT(jurnal.'id'), SUM(jurnal.'sumdoc') )
Таблица credit (возможно, правильней было бы ее назвать orders) содержит суммы поступления по каждой заявке и дату поступления. Т.е., по одной заявке может быть несколько поступлений. Credit, по всей видимости, у меня является связующей таблицей между firms и jurnal. В таблице credit кроме суммы поступления (поле 'postup') и даты поступления (поле 'datapost') имеются поля для связи с таблицами firms.'id' и jurnal.'id' — 'id_jur' и 'id_firm' соответственно.
В идеале, мне нужно получать сводную таблицу по запросам ко всем этим таблицам, с выводом на экран следующих данных:
1. «№ п/п»
2. «Наименование компании» ( поле 'n_short' т. firms )
3. «Количество заявок» ( count(jurnal.'id') )
4. «Сумма заявок» ( sum(jurnal.'sumdoc') )
5. «Количество поступлений» ( count(credit.'id') )
6. «Сумма поступлений» ( sum(credit .'postup') )
7. «Сумма остатка» ('debet': Сумма заявок-Сумма поступлений)
Примерно, то, что на второй картинке результата, приведенной ранее. Только без поля idf (id firms). Ну, а в будущем, делать более подробные выборки по каждой заявке или фирме.
Правда, в свете ваших замечаний, не представляю себе, как не использовать вложенные запросы при обращении к 3-м таблицам, если мне, к примеру, нужно будет получать фирмы, количество заявок и поступлений, суммы по дате поступления сумм (таблица credit 'datapost') и прочие данные по запросу из других таблиц одновременно, а одном запросе. Я впал в ступор:)
А самое главное, как выбрать фирмы, соответствующее количество заявок и их суммы, где сумма заявки = суммам поступлений, ('debet'=0)?
К примеру, для фирмы Южная компания (выделена желтым). Из первого запроса видно, что у этой фирмы 4 заявки, и 4 поступления ('cnt_cr'). Два поступления закрывают первую заявку ('id_jur' = 14) и одно поступление закрывают вторую заявку ('id_jur' = 17). По заявке 'id_jur' = 15 одно поступление и заявка не закрыта, по другому 'id_jur' = 16, поступлений вообще нет.
В итоге запроса для получения «закрытых» заявок должно было получиться:
«Наименование компании» - Южная компания
«Количество заявок» - 2
«Сумма заявок» - 7583,52
«Количество поступлений» - 3
«Сумма поступлений» - 7583.52
«Сумма остатка» - 0
Ох, надеюсь поняли, что я тут изложил. Сумбур, наверное какой-то..
PS: ошибка в данных, перезалил дамп
Например, так:
SELECT firms.n_short, SUM( credit.postup ) AS sum_post,SUM( jurnal.sumdoc ) AS sum_zayav,
SUM( credit.postup ) - SUM( jurnal.sumdoc ) AS debet,
COUNT( DISTINCT credit.id ) AS count_post,
COUNT( DISTINCT jurnal.id ) AS count_zayav
FROM credit
LEFT JOIN firms ON firms.id = credit.id_firm
LEFT JOIN jurnal ON jurnal.id_firm = credit.id_firm
GROUP BY firms.id
Получаем:
Если нужно выбрать закрытые, добавляем
HAVING debet = 0если наоборот, то
HAVING debet != 0Настоятельно рекомендую почитать про различные типы джойнов, например, здесь.
При этом следует отметить, что UNION JOIN устарел; джойнить можно много таблиц различными способами, в том числе одну и ту же таблицу несколько раз под разными именами (только когда реально необходимо); понимать, что такое DISTINCT - выбор уникальных записей; в подавляющем большинстве случаев объединения работают на порядки быстрее вложенных запросов, однако, в очень редких случаях - наоборот, тут в помощь профайлер (хотя бы в phpMyAdmin и баловство с таблицами на 500к+ записей и полное осознание проделываемого).
Если я что-то упустил - спрашивайте.
А... да, суммы поленился проверять. Сейчас в запаре, ближе к ночи поковыряюсь и напишу решение.
Дело не в суммах. Выборки не идут в вашем примере.
Вот я и думаю, может с проектированием таблиц что-то не так, раз не возможно обойтись без вложенных запросов.
видимо, на этом всё.
В принципе, подход верный, поскольку нужны две независимые группировки.
Первое, что я бы порекомендовал - нагенерить в таблицы, рост которых подразумевается наиболее сильно, по 200-300к записей. Именно случайных из определенных диапазонов, после чего заняться оптимизацией.
В данном месте
FROM`jurnal` , `firms`
запятая - то же самое, что INNER JOIN, и, если записать именно в этом виде - будет нагляднее и сразу заметно, что таблицу firms лучше джойнить уже в самом конце к полученному результату, а до этого работать только с айдишниками фирм. В противном случае - лишняя нагрузка по объединению, лишней сортировке и выбрасыванию дубликатов.
Здесь аналогично:
FROM `firms` , `credit`То есть, в итоге, должно получиться три отдельных запроса (два сложных с группировкой и отдельный по firms) и джоины между ними. При этом, в WHERE тоже удастся избавиться от лишней сортировки и удаления дубликатов.
Далее нужно понять, насколько часто будут делаться подобные запросы по отношению к операциям вставки. Если раз в день/неделю/месяц - это одно, если несколько раз в минуту/секунду - нужно делать кэш, где будут храниться уже сгруппированные данные, которые будут пересчитываться по событию добавления записи.
Если фирм вообще будет немного (до 1к) их можно вообще всегда держать в памяти приложения, например, в мапе, тоже с обновлением по событию и "джойнить" уже в приложении.
Благодарю за ценные замечания и советы!
Отправить комментарий