Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Требуется помощь в написании/улучшении sql-запроса / 18 сообщений из 18, страница 1 из 1
02.06.2016, 11:15
    #39248816
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Имеется два запроса:
1-ый запрос - извлекает из базы список всех когда-либо зарегистрированных пользователей (активных, не заблокированных),
2-ой запрос - извлекает из базы список пользователей (активных, не заблокированных), которые хотя что-то когда-либо бронировали (отель, автомобиль и т.д.)

Задача: нужно написать третий запрос (или как-то доработать существующие), который бы позволил извлечь из первых двух запросов пользователей, которые никогда не выполняли бронирование.

Подскажите, как можно реализовать?

1-ый запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT DISTINCT
users.id,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
users
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
-- INNER JOIN bookingsHistory ON (users.id = bookingsHistory.userId)
WHERE
users.activated = 1 AND
users.enabled = 1
ORDER BY
users.id ASC;




2-ой запрос:

Код: 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 DISTINCT
bookingsHistory.userId,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
bookingsHistory
INNER JOIN users ON (bookingsHistory.userId = users.id)
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
WHERE
users.activated = 1 AND
users.enabled = 1 AND
bookingsHistory.userId<>0 AND
bookingsHistory.action='booking'
ORDER BY
bookingsHistory.userId ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 11:19
    #39248819
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovпользователей, которые никогда не выполняли бронированиеleft join + is null?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 11:23
    #39248824
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
А почему нужно писать третий запрос обязательно на основе первых двух, а не самостоятельный запорс?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 11:55
    #39248886
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Akina,

Можно и самостоятельный запрос, не принципиально. Просто давно хотелось попробовать (но не предоставлялось случая) поработать с "разностью множеств".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 11:58
    #39248890
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsov,

not [IN | EXISTS] подзапрос, либо семи-джойн анти (left join + where is null)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 11:59
    #39248893
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovдавно хотелось попробовать (но не предоставлялось случая) поработать с "разностью множеств".Методика дана:
tanglirleft join + is null
Пробуй.
okuznetsovМожно и самостоятельный запрос, не принципиально.
Ну если производительность по барабану, то да...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 12:40
    #39248956
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsovИмеется два запроса:
1-ый запрос - извлекает из базы список всех когда-либо зарегистрированных пользователей (активных, не заблокированных),
2-ой запрос - извлекает из базы список пользователей (активных, не заблокированных), которые хотя что-то когда-либо бронировали (отель, автомобиль и т.д.)

Задача: нужно написать третий запрос (или как-то доработать существующие), который бы позволил извлечь из первых двух запросов пользователей, которые никогда не выполняли бронирование.

Подскажите, как можно реализовать?

Кореллированый подзапрос с NOT EXISTS.
Второй твой запрос нужно сделать подзапросом к первому, введя его через NOT EXISTS, и скоррелировав по идентификаторам пользователя.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 13:59
    #39249085
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Вроде бы верно написал, но запрос не правильно работает, получаю одну запись с id=1 (соответственно так не должно быть, записей в базе в достаточном кол-ве). Соответственно где-то я накосячил


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT DISTINCT
users.id,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
users
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
-- INNER JOIN bookingsHistory ON (users.id = bookingsHistory.userId)
WHERE
users.activated = 1 AND
users.enabled = 1 AND
users.id = EXISTS(SELECT DISTINCT bookingsHistory.userId FROM bookingsHistory INNER JOIN users ON bookingsHistory.userId = users.id WHERE users.activated = 1 AND users.enabled = 1 AND bookingsHistory.userId<>0 AND bookingsHistory.action='booking' ORDER BY bookingsHistory.userId ASC)
ORDER BY
users.id ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 14:31
    #39249138
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsov,

Ты где такой синтаксис exists выискал?
Посмотри примеры, сравни с тем, что у тебя, почувствуй разницу..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 14:35
    #39249144
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsov
Код: sql
1.
users.id = EXISTS(SELECT DISTINCT

В такой конструкции EXISTS неприменимо, только ALL | ANY | SOME.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 15:18
    #39249206
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Я пробовал разные варианты использования Exist, вот последняя. Если использовать not exist, то селект выдаёт пустоту. А в данном контексте, селект выдаёт такую же выдачу, что если бы я не закомментировал exist и не использовал, в общем не отрабатывает ехist. На рисунке во вкладке "Result1" - это то что выдаёт данный селект, и эта выдача полностью повторяет выдачу без использования конструкции exist. Во вкладке "Result2" - это то что выдаётся в конструкции exist. В общем итоговой выдаче согласно


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT DISTINCT
users.id,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
users
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
-- INNER JOIN bookingsHistory ON (users.id = bookingsHistory.userId)
WHERE 
EXISTS(SELECT DISTINCT bookingsHistory.userId FROM bookingsHistory INNER JOIN users ON bookingsHistory.userId = users.id WHERE users.activated = 1 AND users.enabled = 1 AND bookingsHistory.userId<>0 AND bookingsHistory.action='booking') AND 
users.activated = 1 AND
users.enabled = 1
ORDER BY
users.id ASC
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 15:23
    #39249211
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
отправил сообщение, не дописал.

В общем в результирующем запросе должны быть те записи которые не присутствуют в выдаче см. рис. на вкладке "Result2", т.е это наример - 10, 17,21, 23. Конструкция not exist должна мне позволить это и сделать, я правильно понимаю?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 15:48
    #39249245
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
да
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.06.2016, 15:58
    #39249264
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Тогда что-то пошло не так. Возможно чего-то ещё не хватает в запросе.

Попробовал добавить join (отметил желтым). Теперь выдача дублирует выдачу см. рис. на вкладке "Result2", но с более расширенными данными соответственно. Вроде бы всё нормально, так и должно бы быть, добавляю к exist - NOT, в результате опять выдача пустая, а не должна была быть пустой. Что-то не отрабатывает, не могу понят что именно

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT DISTINCT
users.id,
users.login,
users.roleId,
roles.name as rolesName,
users.fullname,
users.activated,
users.enabled as usersEnabled,
users.agencyId,
agencies.name as agenciesName,
agencies.enabled as agenciesEnabled
FROM
users
INNER JOIN agencies ON (users.agencyId = agencies.id)
INNER JOIN roles ON (users.roleId = roles.id)
[color=yellow]INNER JOIN bookingsHistory ON (users.id = bookingsHistory.userId)[/color]
WHERE 
EXISTS(SELECT DISTINCT bookingsHistory.userId FROM bookingsHistory INNER JOIN users ON bookingsHistory.userId = users.id WHERE users.activated = 1 AND users.enabled = 1 AND bookingsHistory.userId<>0 AND bookingsHistory.action='booking') AND 
users.activated = 1 AND
users.enabled = 1
ORDER BY
users.id ASC;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2016, 10:39
    #39251070
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsov,

направьте ещё раз в нужную сторону с exist?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2016, 18:45
    #39251473
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
Пока забросил exist. Решил сделать как многие советовали через LEFT JOIN и IS NULL


Код: sql
1.
2.
3.
4.
5.
SELECT *
FROM users A
LEFT JOIN bookingsHistory B
ON A.id = B.userId
WHERE B.userId IS NULL





не уверен, что сделал правильно, т.к. запрос долго выполняется (ни разу не дождался до конца). в bookingsHistory около 600 000 строк.

Код: sql
1.
2.
3.
4.
5.
EXPLAIN SELECT *
FROM users A
LEFT JOIN bookingsHistory B
ON A.id = B.userId
WHERE B.userId IS NULL




id Select_type table type rows Extra
1 SIMPLE A ALL 5146
1 SIMPLE B ALL 550486 Using where; Not exists
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2016, 20:18
    #39251510
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
okuznetsov,


тихо сам с собою?

тебе все сказано уже, только SQL осталось выучить, изобрести машину времени, вернуться назад к моменту получения тобой последних ЦУ в этом топике и написать запрос за 5 минут...

Удачи!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2016, 18:36
    #39253112
okuznetsov
okuznetsov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Требуется помощь в написании/улучшении sql-запроса
MasterZiv,

Последовал вашим советам, кроме изобретения машины времени и возвращению назад к моменту получения мною последних ЦУ. Решил продолжить со своих ЦУ (где тихо сам с собою общался:-)) - http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1216601&msg=19264524

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

SELECT
a.id as agenciesId,
a.name as agenciesName,
CONCAT('http://online-express.ru/office/agencies/',a.id) AS urlAgency,
count(u.id) as agenciesNumberLoginov,
FROM_UNIXTIME(a.createTime) as agenciesCreateTime,
FROM_UNIXTIME(a.updateTime) as agenciesUpdateTime,
a.enabled as agenciesEnabled,
u.activated userActivated,
u.enabled as usersEnabled
FROM users u
LEFT JOIN bookingsHistory b ON u.id = b.userId
INNER JOIN agencies a ON (u.agencyId = a.id)
WHERE
b.userId IS NULL AND
u.activated = 1 AND
u.enabled = 1 AND
a.enabled = 1
GROUP BY
agenciesId, agenciesName
ORDER BY agenciesCreateTime DESC


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

alter table bookingsHistory add index(userId);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Требуется помощь в написании/улучшении sql-запроса / 18 сообщений из 18, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 462ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1831686]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 156ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 32ms
get tp. blocked users:
 1ms
others: 239ms
total:  462ms
x
x
Закрыть


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