Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Требуется помощь в написании/улучшении sql-запроса / 17 сообщений из 17, страница 1 из 1
31.05.2016, 12:48
    #39247057
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Задача: нужно написать запрос, позволяющий выявить туристические агентства "Использующие систему для бронирования гостиниц под визы."


Выдачу в запросе нужно оформить таким образом:

1) Название турагентства
2) ссылка на агентство - http://online-express.ru/office/agencies/2
3) Количество заказов всего
4) Количество заказов аннулировано
5) Процент аннуляции = (Количество заказов аннулировано)/(Количество заказов всего)*100


Критерии который должны быть в запросе:

- Период времени "с" - "по" (дата создания заказа)
- Не учитывать агентства с менее чем "заказов всего" - числовое значение. Чтобы отсечь агентства , сделавшие 0-1-2-3.... заказа за этот период, они могли действительно аннулироваться.
- Не учитывать заблокированные агентства. Чтобы можно было блокировать хулиганов и они не мешались в выборке.



Что получилось и не получилось у меня реализовать:

- получилось достать из базы необходимые данные, но все эти данные в одной большой "куче". Не могу сообразить как улучшить запрос, чтобы сгруппировать, подсчитать и оформить выдачу в запросе как описано выше.

Код: 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.
SELECT DISTINCT
agencies.id,
-- count(bookings.id) as 'Кол-во заказов всего',
agencies.name,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS agenciesUrl,
agencies.enabled,
suppliersVisaSupport.support,
bookings.orderId,
bookings.userId,
bookings.status,
FROM_UNIXTIME(bookings.createTime) AS Created
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
WHERE
agencies.enabled = 1 AND
suppliersVisaSupport.support = 2 AND --
-- bookings.status = 4 AND
-- agencies.id = 3979 AND
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31'
-- GROUP BY
-- agencies.`name` 
ORDER BY
agencies.`name` ASC




Как выглядит выдача запроса - приведена на рисунке:
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.05.2016, 12:52
    #39247068
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
где

agencies.enabled = 1 -- Не учитываются заблокированные агентства

suppliersVisaSupport.support = 2 -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)

bookings.status = 4 -- аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ)

FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.05.2016, 15:39
    #39247305
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Появились сомнения, что возможно задуманное нельзя реализовать одним запросом.

Очень нужна помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.05.2016, 16:31
    #39247379
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsov1) Название турагентства
2) ссылка на агентство - http://online-express.ru/office/agencies/2
3) Количество заказов всего
4) Количество заказов аннулировано
5) Процент аннуляции = (Количество заказов аннулировано)/(Количество заказов всего)*100


Критерии который должны быть в запросе:

а) Период времени "с" - "по" (дата создания заказа)
б) Не учитывать агентства с менее чем "заказов всего" - числовое значение. Чтобы отсечь агентства , сделавшие 0-1-2-3.... заказа за этот период, они могли действительно аннулироваться.
в) Не учитывать заблокированные агентства. Чтобы можно было блокировать хулиганов и они не мешались в выборке.

1,2 - указать в GROUP BY
3,4,5 - COUNT
а,в - WHERE
б - HAVING
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 08:12
    #39247721
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Cygapb-007,

Не понимаю как вы предлагаете count-ами в одном запросе одновременно подсчитать и "Количество заказов всего" и "Количество заказов аннулировано", т.к. bookings.status = 4 - это аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ). Из этого следует, что общее кол-во заказов (и аннулиров., подтвержд. и отмененных) можно подсчитать: добавив как вы написали в селект count(agencies.`id`) as 'Кол-во заказов всего' и GROUP BY agencies.`name`, agenciesUrl. А вот что делать с "Количество заказов аннулировано" не понял?


Код: 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.
SELECT DISTINCT
agencies.id,
count(agencies.`id`) as 'Кол-во заказов всего',
agencies.name,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS agenciesUrl,
agencies.enabled,
suppliersVisaSupport.support,
bookings.orderId,
bookings.userId,
bookings.status,
FROM_UNIXTIME(bookings.createTime) AS Created
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)
-- bookings.status = 4 AND -- аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ)
-- agencies.id = 3979 AND
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
agencies.`name`, agenciesUrl
ORDER BY
agencies.`name` ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 08:25
    #39247728
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovCygapb-007,

Не понимаю как вы предлагаете count-ами в одном запросе одновременно подсчитать и "Количество заказов всего" и "Количество заказов аннулировано", т.к. bookings.status = 4 - это аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ). Если значения разных статусов нужно раскидать по разным колонкам, то это легко делается через CASE | IF.
Как вариант реализации "кросс-таба"/pivot-a
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 11:33
    #39247923
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovbookings.status = 4 - это аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ). что делать с "Количество заказов аннулировано" не понял?
COUNT(*) все равно, что SUM(1)
для MySQL можно написать еще проще: `Количество заказов аннулировано`=SUM(bookings.status = 4)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 13:22
    #39248067
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Вот что получилось:
- удалось вывести в отдельном столбце не только подсчитанное кол-во аннулированных заказов, но также и "отказанных", "подтвержденных" и "ожидающих подтверждения".

Что требуется улучшить:
- удалось подсчитать "кол-во заказов всего" для каждого турагентства, но как видно из прикрепленного рисунка (см. столбец "Кол-во заказов всего") - не совсем тот подсчёт который требуется и хотелось бы увидеть. Не знаю возможно ли в данном случае, но требуется подсчитать так: на примере турагенства "Витал Форс", т.е. сейчас в табл. три записи этого агентства, в колонке "Кол-во заказов всего" подсчитано: 1, 2 и 3 (числа правильно подсчитаны), нужно сделать чтобы была колонка или строка в которой будет вычисляться 1+2+3 = 6.

В общем нужно суммировать записи в колонке "Кол-во заказов всего" для одних и техже турагентств.



Код: 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 DISTINCT
agencies.id,
count(DISTINCT bookings.orderId) as 'Кол-во заказов всего',
agencies.name,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS agenciesUrl,
agencies.enabled,
suppliersVisaSupport.support,
bookings.status,
dicStatuses.nameRu as statusNameRu,
IF( bookings.status=4,count(DISTINCT bookings.orderId), 0 ) as statusCancelled,
IF( bookings.status=3,count(DISTINCT bookings.orderId), 0 ) as statusRejected,
IF( bookings.status=8,count(DISTINCT bookings.orderId), 0 ) as statusConfirmed,
IF( bookings.status=6,count(DISTINCT bookings.orderId), 0 ) as statusAwaitingResponse
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
INNER JOIN dicStatuses ON bookings.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)
-- bookings.status = 4 AND -- аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ)
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
agencies.`name`, bookings.status
ORDER BY
agencies.`name` ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 13:24
    #39248075
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 13:38
    #39248087
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovЧто требуется улучшить:
- удалось подсчитать "кол-во заказов всего" для каждого турагентства, но как видно из прикрепленного рисунка (см. столбец "Кол-во заказов всего") - не совсем тот подсчёт который требуется и хотелось бы увидеть. Не знаю возможно ли в данном случае, но требуется подсчитать так: на примере турагенства "Витал Форс", т.е. сейчас в табл. три записи этого агентства, в колонке "Кол-во заказов всего" подсчитано: 1, 2 и 3 (числа правильно подсчитаны), нужно сделать чтобы была колонка или строка в которой будет вычисляться 1+2+3 = 6.

В общем нужно суммировать записи в колонке "Кол-во заказов всего" для одних и техже турагентств.



Код: 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 DISTINCT
agencies.id,
count(DISTINCT bookings.orderId) as 'Кол-во заказов всего',
agencies.name,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS agenciesUrl,
agencies.enabled,
suppliersVisaSupport.support,
bookings.status,
dicStatuses.nameRu as statusNameRu,
IF( bookings.status=4,count(DISTINCT bookings.orderId), 0 ) as statusCancelled,
IF( bookings.status=3,count(DISTINCT bookings.orderId), 0 ) as statusRejected,
IF( bookings.status=8,count(DISTINCT bookings.orderId), 0 ) as statusConfirmed,
IF( bookings.status=6,count(DISTINCT bookings.orderId), 0 ) as statusAwaitingResponse
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
INNER JOIN dicStatuses ON bookings.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)
-- bookings.status = 4 AND -- аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ)
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
agencies.`name`, bookings.status
ORDER BY
agencies.`name` ASC

убери из секции GROUP BY поле bookings.status, в Select-листе добавь count(DISTINCT bookings.orderId) as Total.
Наслаждайся полученным результатом
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 13:39
    #39248089
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Добрый Э - ЭхokuznetsovЧто требуется улучшить:
- удалось подсчитать "кол-во заказов всего" для каждого турагентства, но как видно из прикрепленного рисунка (см. столбец "Кол-во заказов всего") - не совсем тот подсчёт который требуется и хотелось бы увидеть. Не знаю возможно ли в данном случае, но требуется подсчитать так: на примере турагенства "Витал Форс", т.е. сейчас в табл. три записи этого агентства, в колонке "Кол-во заказов всего" подсчитано: 1, 2 и 3 (числа правильно подсчитаны), нужно сделать чтобы была колонка или строка в которой будет вычисляться 1+2+3 = 6.

В общем нужно суммировать записи в колонке "Кол-во заказов всего" для одних и техже турагентств.



Код: 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.
SELECT DISTINCT
agencies.id,
count(DISTINCT bookings.orderId) as 'Кол-во заказов всего',
agencies.name,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS agenciesUrl,
agencies.enabled,
suppliersVisaSupport.support,
bookings.status,
dicStatuses.nameRu as statusNameRu,
IF( bookings.status=4,count(DISTINCT bookings.orderId), 0 ) as statusCancelled,
IF( bookings.status=3,count(DISTINCT bookings.orderId), 0 ) as statusRejected,
IF( bookings.status=8,count(DISTINCT bookings.orderId), 0 ) as statusConfirmed,
IF( bookings.status=6,count(DISTINCT bookings.orderId), 0 ) as statusAwaitingResponse,
 count(DISTINCT bookings.orderId) as Total
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
INNER JOIN dicStatuses ON bookings.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)
-- bookings.status = 4 AND -- аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ)
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
 agencies.`name`
ORDER BY
agencies.`name` ASC

убери из секции GROUP BY поле bookings.status, в Select-листе добавь count(DISTINCT bookings.orderId) as Total.
Наслаждайся полученным результатом
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 15:04
    #39248186
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Разумеется пробовал так делать перед тем как спрашивать здесь.

Убрать из секции GROUP BY поле bookings.status - нельзя, т.к. перестает вычисляться правильно колонка "кол-во аннулированных заказов" (statusCancelled), но также перестают вычисляться и другие поля: "отказанные" (statusRejected), "подтвержденные" (statusConfirmed) и "ожидающие подтверждения" (statusAwaitingResponse).
Предполагаю, что скорее всего нужно как-то по другому подсчитывать сумму аннулированных заказов по каждому турагентству. подскажите куда копать?


По поводу: Select-листе добавь count(DISTINCT bookings.orderId) as Total: см, эта конструкция уже добавлена ранее)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 15:37
    #39248230
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovВот что получилось:

Код: 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 DISTINCT
agencies.id,
count(DISTINCT bookings.orderId) as 'Кол-во заказов всего',
agencies.name,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS agenciesUrl,
agencies.enabled,
suppliersVisaSupport.support,
bookings.status,
dicStatuses.nameRu as statusNameRu,
IF( bookings.status=4,count(DISTINCT bookings.orderId), 0 ) as statusCancelled,
IF( bookings.status=3,count(DISTINCT bookings.orderId), 0 ) as statusRejected,
IF( bookings.status=8,count(DISTINCT bookings.orderId), 0 ) as statusConfirmed,
IF( bookings.status=6,count(DISTINCT bookings.orderId), 0 ) as statusAwaitingResponse
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
INNER JOIN dicStatuses ON bookings.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)
-- bookings.status = 4 AND -- аннулированный заказ (8 - заказ поддтверждён, 3 - отменённый заказ)
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
agencies.`name`, bookings.status
ORDER BY
agencies.`name` ASC


Выделенное неверно в принципе, потому что условие применяется ПОСЛЕ группировки.
должно быть
Код: sql
1.
2.
count(DISTINCT IF( bookings.status=4,bookings.orderId,null)) as statusCancelled,
-- и т.д.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 15:42
    #39248238
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
опс...
Не увидел группировку по bookings.status...
Сорь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.06.2016, 15:43
    #39248244
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Хотя так и не понял, зачем ее включили в группировку...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 07:32
    #39248598
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Спасибо, работает как нужно.

Получившийся запрос:


Код: 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 DISTINCT
agencies.id,
agencies.name as nameAgency,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS urlAgency,
IF( agencies.enabled=1,"Нет",null) as 'blockedAgency',
IF( suppliersVisaSupport.support=2,"Да",null) as 'supportVisa',
count(DISTINCT IF( bookings.status=4,bookings.orderId,null)) as 'numberOrdersCancelled',
count(DISTINCT IF( bookings.status=3,bookings.orderId,null)) as 'numberOrdersRejected',
count(DISTINCT IF( bookings.status=8,bookings.orderId,null)) as 'numberOrdersConfirmed',
count(DISTINCT IF( bookings.status=6,bookings.orderId,null)) as 'numberOrdersResponse',
count(DISTINCT bookings.orderId) as 'numberOrdersTotal',
count(DISTINCT IF( bookings.status=4,bookings.orderId,null)) / count(DISTINCT bookings.orderId) * 100 as 'procentCancelled'
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
INNER JOIN dicStatuses ON bookings.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND -- агентства использующие систему для бронирования гостиниц под визы (0 - не использует, 3 - виз. поддержка не нужна)
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
agencies.`name`
HAVING
numberOrdersTotal > 3
ORDER BY
procentCancelled DESC, agencies.`name` ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 14:02
    #39249096
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovСпасибо, работает как нужно.

Получившийся запрос:


Код: 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.
SELECT DISTINCT
agencies.id,
agencies.name as nameAgency,
CONCAT('http://site.ru/office/agencies/',agencies.id) AS urlAgency,
IF( agencies.enabled=1,"Нет",null) as 'blockedAgency',
IF( suppliersVisaSupport.support=2,"Да",null) as 'supportVisa',
count(DISTINCT IF( bookings.status=4,bookings.orderId,null)) as 'numberOrdersCancelled',
count(DISTINCT IF( bookings.status=3,bookings.orderId,null)) as 'numberOrdersRejected',
count(DISTINCT IF( bookings.status=8,bookings.orderId,null)) as 'numberOrdersConfirmed',
count(DISTINCT IF( bookings.status=6,bookings.orderId,null)) as 'numberOrdersResponse',
count(DISTINCT bookings.orderId) as 'numberOrdersTotal',
count(DISTINCT IF( bookings.status=4,bookings.orderId,null)) / count(DISTINCT bookings.orderId) * 100 as 'procentCancelled'
FROM
bookings
INNER JOIN agencies ON bookings.agencyId = agencies.id
INNER JOIN suppliersVisaSupport ON suppliersVisaSupport.supplierId = bookings.supplierId
INNER JOIN dicStatuses ON bookings.status = dicStatuses.id
WHERE
agencies.enabled = 1 AND -- Не учитываются заблокированные агентства
suppliersVisaSupport.support = 2 AND 
-- агентства использующие систему для бронирования гостиниц под визы 
-- (0 - не использует, 3 - виз. поддержка не нужна)
FROM_UNIXTIME(bookings.createTime) BETWEEN '2016-01-01' and '2016-05-31' -- Период времени "с" - "по" (дата создания заказа)
GROUP BY
agencies.`name`
HAVING
numberOrdersTotal > 3
ORDER BY
procentCancelled DESC, agencies.`name` ASC

выделенное красным противоречит группировке по AGENCIES (т.к. suppliersVisaSupport зависит от сворачиваемых при группировке bookings, то есть тоже будет свернут. Какое при этом будет взято одиночное значение - предсказать невозможно)

Наверное, лучше написать MAX(IF( suppliersVisaSupport.support=2,"Да",null))

А выделенное желтым заблокирует индекс по bookings.createTime (если он есть)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Требуется помощь в написании/улучшении sql-запроса / 17 сообщений из 17, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 510ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1831712]:
 0ms
get settings:
 8ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 183ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 41ms
get tp. blocked users:
 1ms
others: 242ms
total:  510ms
x
x
Закрыть


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