Требуется помощь в написании/улучшении sql-запроса / MySQL

ReSQL.ru

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

Гость

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Требуется помощь в написании/улучшении sql-запроса

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

Требуется помощь в написании/улучшении sql-запроса

#39248816

okuznetsov

Гость

Имеется два запроса:
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:15

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

Требуется помощь в написании/улучшении sql-запроса

#39248819

tanglir

Участник

Сообщения: 30 379

Рейтинг: 0 / 0

okuznetsovпользователей, которые никогда не выполняли бронированиеleft join + is null?

...

Рейтинг:

0 / 0

02.06.2016, 11:19

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

Требуется помощь в написании/улучшении sql-запроса

#39248824

Akina

Участник

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

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

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

02.06.2016, 11:23

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

Требуется помощь в написании/улучшении sql-запроса

#39248886

okuznetsov

Гость

Akina,

Можно и самостоятельный запрос, не принципиально. Просто давно хотелось попробовать (но не предоставлялось случая) поработать с "разностью множеств".

...

Рейтинг:

0 / 0

02.06.2016, 11:55

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

Требуется помощь в написании/улучшении sql-запроса

#39248890

Добрый Э - Эх

Гость

okuznetsov,

not [IN | EXISTS] подзапрос, либо семи-джойн анти (left join + where is null)

...

Рейтинг:

0 / 0

02.06.2016, 11:58

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

Требуется помощь в написании/улучшении sql-запроса

#39248893

Akina

Участник

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

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

Рейтинг: 0 / 0

okuznetsovдавно хотелось попробовать (но не предоставлялось случая) поработать с "разностью множеств".Методика дана:
tanglirleft join + is null
Пробуй.
okuznetsovМожно и самостоятельный запрос, не принципиально.
Ну если производительность по барабану, то да...

...

Рейтинг:

0 / 0

02.06.2016, 11:59

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

Требуется помощь в написании/улучшении sql-запроса

#39248956

MasterZiv

Участник

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

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

Рейтинг: 0 / 0

okuznetsovИмеется два запроса:
1-ый запрос - извлекает из базы список всех когда-либо зарегистрированных пользователей (активных, не заблокированных),
2-ой запрос - извлекает из базы список пользователей (активных, не заблокированных), которые хотя что-то когда-либо бронировали (отель, автомобиль и т.д.)

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

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

Кореллированый подзапрос с NOT EXISTS.
Второй твой запрос нужно сделать подзапросом к первому, введя его через NOT EXISTS, и скоррелировав по идентификаторам пользователя.

...

Рейтинг:

0 / 0

02.06.2016, 12:40

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

Требуется помощь в написании/улучшении sql-запроса

#39249085

okuznetsov

Гость

Вроде бы верно написал, но запрос не правильно работает, получаю одну запись с 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, 13:59

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

Требуется помощь в написании/улучшении sql-запроса

#39249138

Добрый Э - Эх

Гость

okuznetsov,

Ты где такой синтаксис exists выискал?
Посмотри примеры, сравни с тем, что у тебя, почувствуй разницу..

...

Рейтинг:

0 / 0

02.06.2016, 14:31

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

Требуется помощь в написании/улучшении sql-запроса

#39249144

Akina

Участник

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

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

Рейтинг: 0 / 0

okuznetsov

Код: sql

users.id = EXISTS(SELECT DISTINCT

В такой конструкции EXISTS неприменимо, только ALL | ANY | SOME.

...

Рейтинг:

0 / 0

02.06.2016, 14:35

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

Требуется помощь в написании/улучшении sql-запроса

#39249206

okuznetsov

Гость

Я пробовал разные варианты использования 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:18

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

Требуется помощь в написании/улучшении sql-запроса

#39249211

okuznetsov

Гость

отправил сообщение, не дописал.

В общем в результирующем запросе должны быть те записи которые не присутствуют в выдаче см. рис. на вкладке "Result2", т.е это наример - 10, 17,21, 23. Конструкция not exist должна мне позволить это и сделать, я правильно понимаю?

...

Рейтинг:

0 / 0

02.06.2016, 15:23

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

Требуется помощь в написании/улучшении sql-запроса

#39249245

MasterZiv

Участник

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

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

Рейтинг: 0 / 0

да

...

Рейтинг:

0 / 0

02.06.2016, 15:48

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

Требуется помощь в написании/улучшении sql-запроса

#39249264

okuznetsov

Гость

Тогда что-то пошло не так. Возможно чего-то ещё не хватает в запросе.

Попробовал добавить 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

02.06.2016, 15:58

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

Требуется помощь в написании/улучшении sql-запроса

#39251070

okuznetsov

Гость

okuznetsov,

направьте ещё раз в нужную сторону с exist?

...

Рейтинг:

0 / 0

06.06.2016, 10:39

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

Требуется помощь в написании/улучшении sql-запроса

#39251473

okuznetsov

Гость

Пока забросил 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, 18:45

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

Требуется помощь в написании/улучшении sql-запроса

#39251510

MasterZiv

Участник

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

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

Рейтинг: 0 / 0

okuznetsov,

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

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

Удачи!

...

Рейтинг:

0 / 0

06.06.2016, 20:18

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

Требуется помощь в написании/улучшении sql-запроса

#39253112

okuznetsov

Гость

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

08.06.2016, 18:36

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

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

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Требуется помощь в написании/улучшении sql-запроса

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

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

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

start [/forum/topic.php?fid=47&msg=39249085&tid=1831686]:	0ms
get settings:	6ms
get forum list:	10ms
check forum access:	3ms
check topic access:	3ms
track hit:	160ms
get topic data:	9ms
get forum data:	2ms
get page messages:	48ms
get tp. blocked users:	2ms
others:	258ms

total:	501ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы