Как правильно изменить запрос? / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как правильно изменить запрос?

22 сообщений из 22, страница 1 из 1

Как правильно изменить запрос?

#39902441

LiYing

Гость

Обнаружилось, что следующий запрос (минимальная часть из более комплексного, на котором воспроизводится ошибка):

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

SELECT	CONCAT(name,", ",addr_building,IF(addr_letter is null, "", CONCAT("/",addr_letter))) adr,
	SUM(ds.h_sum_debt+ds.hw_sum_debt) dolg,
	COUNT(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
	COUNT(o.id_contragent) ord_in_mkd,SUM(o.debt_sum) ord_sum
FROM data_sgrc ds 
INNER JOIN contragents c ON ds.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
INNER JOIN streets s ON m.id_street=s.id
		
LEFT JOIN orders o ON ds.id_contragent=o.id_contragent
		AND o.date_from >= ds.period AND o.date_from < ds.period + INTERVAL 1 MONTH
		
WHERE ds.period='2019-11-01' AND m.id=47
GROUP BY m.id

правильно подсчитывает dolg, dolg_count, ord_in_mkd, ord_sum только в том случае , когда на одного id_contragent приходится одна запись за период выборки в таблице orders o . Если же записей несколько, то dolg, dolg_count увеличивается на их количество, что неправильно (с моей точки зрения, но не SQL :)).
Если убрать агрегатные функции и группировку из запроса, добавив id_contragent для наглядности:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

SELECT	CONCAT(name,", ",addr_building,IF(addr_letter is null, "", CONCAT("/",addr_letter))) adr,ds.id_contragent,
	(ds.h_sum_debt+ds.hw_sum_debt) dolg,
	(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
	(o.id_contragent) ord_in_mkd,(o.debt_sum) ord_sum
FROM data_sgrc ds 
INNER JOIN contragents c ON ds.id_contragent=c.id 
INNER JOIN mkd m ON c.id_mkd=m.id 
INNER JOIN streets s ON m.id_street=s.id
		
LEFT JOIN orders o ON ds.id_contragent=o.id_contragent
		AND o.date_from >= ds.period AND o.date_from < ds.period + INTERVAL 1 MONTH
		
WHERE ds.period='2019-11-01' AND m.id=47

то в результате запроса это дублирование видно (см. фрагмент скриншота ниже).
Собственно вопрос: как правильно подсчитать ord_in_mkd, ord_sum (словами: кол-во приказов, выданных на должника за период на общую сумму), чтобы не изменить dolg, dolg_count (словами: общий долг и кол-во должников за период)?

...

Рейтинг:

0 / 0

13.12.2019, 12:07

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39902481

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Навскидку так:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

SELECT sq.adr,
       sq.dolg,
       dolg.count,
       COUNT(o.id_contragent) ord_in_mkd,
       SUM(o.debt_sum) ord_sum
FROM ( SELECT CONCAT(name,", ",addr_building,IF(addr_letter is null, "", CONCAT("/",addr_letter))) adr,
              SUM(ds.h_sum_debt+ds.hw_sum_debt) dolg,
              COUNT(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
	      ds.id_contragent,
	      ds.period,
              m.id
       FROM data_sgrc ds 
       INNER JOIN contragents c ON ds.id_contragent=c.id 
       INNER JOIN mkd m ON c.id_mkd=m.id 
       INNER JOIN streets s ON m.id_street=s.id
       WHERE ds.period='2019-11-01' 
         AND m.id=47
       GROUP BY m.id
) as sq
LEFT JOIN orders o ON sq.id_contragent=o.id_contragent
                  AND o.date_from >= sq.period 
                  AND o.date_from < sq.period + INTERVAL 1 MONTH
GROUP BY sq.id

...

Рейтинг:

0 / 0

13.12.2019, 13:10

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39902500

LiYing

Гость

Akina,

спасибо, должно сработать! Попробую уже в понедельник, т.к. убежал с работы, отпишусь...

...

Рейтинг:

0 / 0

13.12.2019, 13:53

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39902595

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

На самом деле меня как-то смущает вот эта последовательность:

Код: sql

1.
2.

       WHERE ... m.id=47
       GROUP BY m.id

И закрадывается подозрение, что GROUP BY тут - что в запросе, что в подзапросе, - нафиг не нужны от слова "совсем".

...

Рейтинг:

0 / 0

13.12.2019, 15:25

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39902997

LiYing

Гость

Akina,

В реальном запросе группировка по МКД нужна, AND m.id=47 вставил в WHERE только для теста (отбор по одному МКД, искал причину увеличения dolg, dolg_count).

...

Рейтинг:

0 / 0

14.12.2019, 11:11

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903448

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

LiYing,

У тебя m.id задаётся в запросе как параметр и тут же по нему идёт группировка .
Какой в этом смысл?

...

Рейтинг:

0 / 0

16.12.2019, 14:02

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903450

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

LiYing,

в первом запросе GROUP BY неверный, в выражениии

CONCAT(name,", ",addr_building,IF(addr_letter is null, "", CONCAT("/",addr_letter))) adr,

полно полей, не входящих в GROUP BY.
Они все должны быть там.

...

Рейтинг:

0 / 0

16.12.2019, 14:03

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903452

MasterZiv

Участник

Откуда: Питер

Сообщения: 32 427

Рейтинг: 0 / 0

LiYing

Так-то всё на уровне "рыба не смогла..."

...

Рейтинг:

0 / 0

16.12.2019, 14:04

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903469

LiYing

Гость

MasterZiv

LiYing,

У тебя m.id задаётся в запросе как параметр и тут же по нему идёт группировка .
Какой в этом смысл?

Ответ был дан в предыдущем от цитируемого посте.

...

Рейтинг:

0 / 0

16.12.2019, 14:18

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903474

LiYing

Гость

Akina

Навскидку так:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

SELECT sq.adr,
       sq.dolg,
       dolg.count,
       COUNT(o.id_contragent) ord_in_mkd,
       SUM(o.debt_sum) ord_sum
FROM ( SELECT CONCAT(name,", ",addr_building,IF(addr_letter is null, "", CONCAT("/",addr_letter))) adr,
              SUM(ds.h_sum_debt+ds.hw_sum_debt) dolg,
              COUNT(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
	      ds.id_contragent,
	      ds.period,
              m.id
       FROM data_sgrc ds 
       INNER JOIN contragents c ON ds.id_contragent=c.id 
       INNER JOIN mkd m ON c.id_mkd=m.id 
       INNER JOIN streets s ON m.id_street=s.id
       WHERE ds.period='2019-11-01' 
       GROUP BY m.id
) as sq
LEFT JOIN orders o ON sq.id_contragent=o.id_contragent
                  AND o.date_from >= sq.period 
                  AND o.date_from < sq.period + INTERVAL 1 MONTH
GROUP BY sq.id

Убрал опечатку в 3-й строке:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

SELECT sq.adr,
       sq.dolg,
       sq.dolg_count,
       COUNT(o.id_contragent) ord_in_mkd,
       SUM(o.debt_sum) ord_sum
FROM ( SELECT CONCAT(name,", ",addr_building,IF(addr_letter is null, "", CONCAT("/",addr_letter))) adr,
              SUM(ds.h_sum_debt+ds.hw_sum_debt) dolg,
              COUNT(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
	      ds.id_contragent,
	      ds.period,
              m.id
       FROM data_sgrc ds 
       INNER JOIN contragents c ON ds.id_contragent=c.id 
       INNER JOIN mkd m ON c.id_mkd=m.id 
       INNER JOIN streets s ON m.id_street=s.id
       WHERE ds.period='2019-11-01' 
       GROUP BY m.id
) as sq
LEFT JOIN orders o ON sq.id_contragent=o.id_contragent
                  AND o.date_from >= sq.period 
                  AND o.date_from < sq.period + INTERVAL 1 MONTH
GROUP BY sq.id

Но не подсчитываются данные в строках:

Код: sql

1.
2.

       COUNT(o.id_contragent) ord_in_mkd,
       SUM(o.debt_sum) ord_sum

всегда 0 и null.

...

Рейтинг:

0 / 0

16.12.2019, 14:24

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903505

LiYing

Гость

LiYing

всегда 0 и null.

Хотя, если убрать агрегатные функции и группировку из запроса, видно, что данные из orders присоединяются. Почему ж тогда не суммируются?

...

Рейтинг:

0 / 0

16.12.2019, 14:57

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903521

Alex_Ustinov

Участник

Откуда: Nickel

Сообщения: 3 965

Рейтинг: 0 / 0

ну добавь эти поля o.id_contragent и o.debt_sum в запрос без группировки и посмотри что там у тебя

...

Рейтинг:

0 / 0

16.12.2019, 15:06

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903538

LiYing

Гость

Alex_Ustinov

ну добавь эти поля o.id_contragent и o.debt_sum в запрос без группировки и посмотри что там у тебя

Все null.
Но, кажется, я понял почему так. В подзапросе Акины данные сгруппированы по id_mkd, а внешняя группировка идет по id_contragent данные джойнятся по id_contragent (которые уже свернуты в группировке) и они никак не стыкуются. Осталось понять, как это побороть :)

...

Рейтинг:

0 / 0

16.12.2019, 15:27

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903567

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Ну тогда остаётся одно. Делаешь подзапрос с текстом исходного запроса. Делаешь второй подзапрос, в котором нет таблицы orders. И джойнишь их, беря ord_sum из первого подзапроса, а всё остальное из второго.

...

Рейтинг:

0 / 0

16.12.2019, 15:58

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903579

LiYing

Гость

Akina,

не поможете это сделать, как сие записать? Ум за разум уже заходит от попытки представить такую конструкцию, query конструктор постоянно ругается %)

...

Рейтинг:

0 / 0

16.12.2019, 16:25

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903852

LiYing

Гость

Akina,

С утра нашло озарение Составил таки рабочий запрос:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.

SELECT q1.adr,q1.dolg,q1.dolg_count,q2.ord_in_mkd,q2.ord_sum
FROM 
-- суммируем долги:
	(
		SELECT CONCAT(name,", ",addr_building,IF(addr_letter IS NULL, "", CONCAT("/",addr_letter))) adr, 
			SUM(ds.h_sum_debt+ds.hw_sum_debt) dolg, 
			COUNT(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
			m.id
		FROM data_sgrc ds
		INNER JOIN contragents c ON ds.id_contragent=c.id
		INNER JOIN mkd m ON c.id_mkd=m.id
		INNER JOIN streets s ON m.id_street=s.id
		WHERE ds.period='2019-11-01'
		GROUP BY m.id 
	) q1
-- суммируем приказы:
LEFT JOIN
	(
		SELECT COUNT(o.id_contragent) ord_in_mkd, 
			SUM(o.debt_sum) ord_sum, 
			m.id
		FROM orders o
		INNER JOIN contragents c ON o.id_contragent=c.id
		INNER JOIN mkd m ON c.id_mkd=m.id
		WHERE o.date_from >= '2019-11-01' AND o.date_from < '2019-11-01' + INTERVAL 1 MONTH
		GROUP BY m.id
	) q2 ON q1.id=q2.id

Правильно я понял Вашу мысль?

...

Рейтинг:

0 / 0

17.12.2019, 09:19

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903866

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

LiYing

Правильно я понял Вашу мысль?

Вероятно, да.
В тексте запроса не все поля имеют алиасы таблиц, но, судя по именам полей типа addr_?????, они из таблицы streets, так что лишних таблиц нет, всё ровно.

...

Рейтинг:

0 / 0

17.12.2019, 10:05

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903872

LiYing

Гость

Akina

В тексте запроса не все поля имеют алиасы таблиц, но, судя по именам полей типа addr_?????, они из таблицы streets, так что лишних таблиц нет, всё ровно.

Не совсем так, но, думаю, это не важно. С алиасами так:

Код: sql

CONCAT(s.name,", ",m.addr_building,IF(m.addr_letter is null, "", CONCAT("/",m.addr_letter))) adr

...

Рейтинг:

0 / 0

17.12.2019, 10:19

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903897

LiYing

Гость

Akina,
А если к q1 надо еще пару выборок из других таблиц приджойнить, то делаем аналогично?

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.

SELECT q1.adr,q1.dolg,q1.dolg_count,q2.ord_in_mkd,q2.ord_sum,q3.claims_in_mkd,q3.claims_sum,q4.agr_in_mkd,q4.agr_sum
FROM 
-- суммируем долги:
	(
		SELECT 	CONCAT(s.name,", ",m.addr_building,IF(m.addr_letter IS NULL, "", CONCAT("/",m.addr_letter))) adr, 
					SUM(ds.h_sum_debt+ds.hw_sum_debt) dolg, 
					COUNT(IF(ds.h_sum_debt+ds.hw_sum_debt>0.01, 1, NULL)) dolg_count,
					m.id
		FROM data_sgrc ds
		INNER JOIN contragents c ON ds.id_contragent=c.id
		INNER JOIN mkd m ON c.id_mkd=m.id
		INNER JOIN streets s ON m.id_street=s.id
		WHERE ds.period='2019-11-01'
		GROUP BY m.id 
	) q1
-- суммируем приказы:
LEFT JOIN
	(
		SELECT 	COUNT(o.id_contragent) ord_in_mkd, 
					SUM(o.debt_sum) ord_sum, 
					m.id
		FROM orders o
		INNER JOIN contragents c ON o.id_contragent=c.id
		INNER JOIN mkd m ON c.id_mkd=m.id
		WHERE o.date_from >= '2019-11-01' AND o.date_from < '2019-11-01' + INTERVAL 1 MONTH
		GROUP BY m.id
	) q2 ON q1.id=q2.id
-- суммируем претензии:
LEFT JOIN
	(
		SELECT 	COUNT(cl.id_contragent) claims_in_mkd, 
					SUM(cl.debt_sum_h+cl.debt_sum_hw) claims_sum, 
					m.id
		FROM claims cl
		INNER JOIN contragents c ON cl.id_contragent=c.id
		INNER JOIN mkd m ON c.id_mkd=m.id
		WHERE cl.date_from >= '2019-11-01' AND cl.date_from < '2019-11-01' + INTERVAL 1 MONTH
		GROUP BY m.id
	) q3 ON q1.id=q3.id
-- суммируем соглашения:
LEFT JOIN
	(
		SELECT 	COUNT(a.id_contragent) agr_in_mkd, 
					SUM(a.debt_sum_h+a.debt_sum_hw) agr_sum, 
					m.id
		FROM agreements a
		INNER JOIN contragents c ON a.id_contragent=c.id
		INNER JOIN mkd m ON c.id_mkd=m.id
		WHERE a.date_from >= '2019-11-01' AND a.date_from < '2019-11-01' + INTERVAL 1 MONTH
		GROUP BY m.id
	) q4 ON q1.id=q4.id

Ничего нельзя упростить?
Такой чудовищный explain выходит:

...

Рейтинг:

0 / 0

17.12.2019, 10:48

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903903

LiYing

Гость

...

Рейтинг:

0 / 0

17.12.2019, 10:52

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903920

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

LiYing

А если к q1 надо еще пару выборок из других таблиц приджойнить, то делаем аналогично?

Угу.

LiYing

Ничего нельзя упростить?

Ну только каждый отдельный подзапрос разве.

LiYing

Такой чудовищный explain выходит:

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

Код: sql

1.
2.
3.

INNER JOIN contragents c ON a.id_contragent=c.id
INNER JOIN mkd m ON c.id_mkd=m.id
WHERE a.date_from >= '2019-11-01' AND a.date_from < '2019-11-01' + INTERVAL 1 MONTH

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

...

Рейтинг:

0 / 0

17.12.2019, 11:03

| Ответить | Цитировать | Написать

Как правильно изменить запрос?

#39903929

LiYing

Гость

Akina,

Спасибо, попробую заюзать и CTE.

...

Рейтинг:

0 / 0

17.12.2019, 11:11

| Ответить | Цитировать | Написать

22 сообщений из 22, страница 1 из 1

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как правильно изменить запрос?

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

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

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

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

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&msg=39902441&tid=1828813]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	160ms
get topic data:	10ms
get forum data:	3ms
get page messages:	54ms
get tp. blocked users:	1ms
others:	309ms

total:	569ms