Гость
Войти | Профиль | Очистить
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как правильно изменить запрос? / 22 сообщений из 22, страница 1 из 1
13.12.2019, 12:07
    #39902441
LiYing
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, 13:10
    #39902481
Akina
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.
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:53
    #39902500
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Akina,

спасибо, должно сработать! Попробую уже в понедельник, т.к. убежал с работы, отпишусь...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.12.2019, 15:25
    #39902595
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
На самом деле меня как-то смущает вот эта последовательность:
Код: sql
1.
2.
       WHERE ... m.id=47
       GROUP BY m.id


И закрадывается подозрение, что GROUP BY тут - что в запросе, что в подзапросе, - нафиг не нужны от слова "совсем".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2019, 11:11
    #39902997
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Akina,

В реальном запросе группировка по МКД нужна, AND m.id=47 вставил в WHERE только для теста (отбор по одному МКД, искал причину увеличения dolg, dolg_count).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 14:02
    #39903448
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
LiYing,

У тебя m.id задаётся в запросе как параметр и тут же по нему идёт группировка .
Какой в этом смысл?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 14:03
    #39903450
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
LiYing,

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

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

полно полей, не входящих в GROUP BY.
Они все должны быть там.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 14:04
    #39903452
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
LiYing
Akina,

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


Так-то всё на уровне "рыба не смогла..."
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 14:18
    #39903469
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
MasterZiv
LiYing,

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

Ответ был дан в предыдущем от цитируемого посте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 14:24
    #39903474
LiYing
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:57
    #39903505
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
LiYing
всегда 0 и null.

Хотя, если убрать агрегатные функции и группировку из запроса, видно, что данные из orders присоединяются. Почему ж тогда не суммируются?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 15:06
    #39903521
Alex_Ustinov
Alex_Ustinov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
ну добавь эти поля o.id_contragent и o.debt_sum в запрос без группировки и посмотри что там у тебя
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 15:27
    #39903538
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Alex_Ustinov
ну добавь эти поля o.id_contragent и o.debt_sum в запрос без группировки и посмотри что там у тебя

Все null.
Но, кажется, я понял почему так. В подзапросе Акины данные сгруппированы по id_mkd, а внешняя группировка идет по id_contragent данные джойнятся по id_contragent (которые уже свернуты в группировке) и они никак не стыкуются. Осталось понять, как это побороть :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 15:58
    #39903567
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Ну тогда остаётся одно. Делаешь подзапрос с текстом исходного запроса. Делаешь второй подзапрос, в котором нет таблицы orders. И джойнишь их, беря ord_sum из первого подзапроса, а всё остальное из второго.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.12.2019, 16:25
    #39903579
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Akina,

не поможете это сделать, как сие записать? Ум за разум уже заходит от попытки представить такую конструкцию, query конструктор постоянно ругается %)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 09:19
    #39903852
LiYing
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, 10:05
    #39903866
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
LiYing
Правильно я понял Вашу мысль?
Вероятно, да.
В тексте запроса не все поля имеют алиасы таблиц, но, судя по именам полей типа addr_?????, они из таблицы streets, так что лишних таблиц нет, всё ровно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 10:19
    #39903872
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Akina
В тексте запроса не все поля имеют алиасы таблиц, но, судя по именам полей типа addr_?????, они из таблицы streets, так что лишних таблиц нет, всё ровно.

Не совсем так, но, думаю, это не важно. С алиасами так:
Код: sql
1.
CONCAT(s.name,", ",m.addr_building,IF(m.addr_letter is null, "", CONCAT("/",m.addr_letter))) adr
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 10:48
    #39903897
LiYing
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:52
    #39903903
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.12.2019, 11:03
    #39903920
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
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:11
    #39903929
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно изменить запрос?
Akina,

Спасибо, попробую заюзать и CTE.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как правильно изменить запрос? / 22 сообщений из 22, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 173ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1828813]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 73ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 1ms
others: 11ms
total:  173ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]