Loading...
Gesh4o avatar Gesh4o Trainer 305 Точки

DB Basics Exam Preparation 16.02.2017

Здравейте,

По време на подготовката за изпит, проведена на 16.02.2017, с колегите стигнахме до един доста интересен казус, който обещах да разгледам и разясня какво точно се обърка тогава.

Задачата е "12. Left Users" от изпита TheNerdHerd. В нея се изисква да намерим всички съобщения за чат с номер (Id) 17 от тези потребители, които са напуснали чата. Естествения ход на задачата поверява да намерим всички съобщения в този чат, да видим кои от тях са написани от потребители, които са все още активни в него (посредством mapping таблицата UsersChats), и  накрая да ги махнем, оставяйки вече неучастващите в чата потребители и техните съобщения. Заявката може да прилича на това (заедно със сортировката):

SELECT * FROM Messages AS m
INNER JOIN UsersChats AS uc ON uc.ChatId = m.ChatId
WHERE m.ChatId = 17
ORDER BY m.Id DESC

А резултата от заявката (ползвайки примерните данни от Data.sql скрипта):

Output

След проверка можем да остановим, че предпоследната колона съдържа всички потребители, участващи в чата (това е потребител 19), останалите двама (потребител 24 и 25) са напуснали този чат и ние трябва да визуализираме само техните съобщения. Следващите няколко подхода от видеото се оказват неуспешни, нека се спрем на последния такъв:

SELECT m.Id, m.ChatId, m.UserId FROM Messages AS m
LEFT JOIN UsersChats AS uc ON uc.ChatId = m.ChatId
WHERE m.ChatId = 17 AND (m.UserId NOT IN (SELECT UserId FROM UsersChats WHERE ChatId = 17) OR m.UserId IS NULL)
ORDER BY m.Id DESC

В по-горната заявка се разглежда един допълнителен случай: какво става, ако дадения потребител някога е участвал в този чат, а след това неговия профил е изтрит. Отговора е, че неговото съобщение ще се пази, но ще има за стойност NULL на колоната UserId. С подобрениото условие си подсигуряваме, че ще хванем и тези потребители (по-скоро съобщенията, които те са пратили).

Защо обаче горният подход не работи? Оказва се, че условието, по което ни съединяваме двете таблици е непълно. Нека да разгледаме случая, в който имаме и други потребители в този чат (спрямо горния случай потребители 7,8,9 са добавени в него):

Независимо дали тези потребители са пращали съобщения или не, те са активни в този чат. И понеже нашето условие за връзка между двете таблици е нeпълно (join-a ще се случи само по колона  ChatId), ще се случи повторение на данните. Изкараните съобщения са точно тези, от които имаме нужда, но в ненужен брой.

За да решим този проблем трябва да се върнем към нашето join условие и да съединим по още една колона "m.UserId = uc.UserId", което означава следното нещо - потребителя, изпратил съобщението, да е този, който участва в чата:

SELECT * FROM Messages AS m
LEFT JOIN UsersChats AS uc ON uc.ChatId = m.ChatId AND m.UserId = uc.ChatId
WHERE m.ChatId = 17 AND (m.UserId NOT IN (SELECT UserId FROM UsersChats WHERE ChatId = 17) OR m.UserId IS NULL)
ORDER BY m.Id DESC

Така вече нашето решение връща правилните резултати:

Какво забелязваме накрая - предпоследната колона UserId има стойности само NULL. Нормално, няма как да свържем потребител 24 или 25 с чат 17, те са го напуснали и понеже ползваме LEFT JOIN ние можем да изкараме потретибелите, които не са в таблицата с чатовете. Това може да доведе до оптимизация на нашата заявка и тя финално да изглежда ето така:

SELECT * FROM Messages AS m
LEFT JOIN UsersChats AS uc ON uc.ChatId = m.ChatId AND m.UserId = uc.UserId
WHERE m.ChatId = 17 AND uc.UserId IS NULL
ORDER BY m.Id DESC

Искрено се надявам този пост да ви е полезен в решаването на задачата! Ако някои има въпроси по тази задачи, моля да ги зададе тук. :)

Поздрави!

Тагове:
3
Databases Basics 17/02/2017 15:34:21
VasilValchanov avatar VasilValchanov 555 Точки

Заявката работи, но джъдж иска да селектираме само m.Id, m.ChatId, m.UserId иначе доста софт решение на тази задача.

0
Gesh4o avatar Gesh4o Trainer 305 Точки

Нарочно е оставено със "SELECT *" - това какво селектираме в задачата не е толкова важно на фона на самите условия, които трябва да преодолеем. :)

0
VasilValchanov avatar VasilValchanov 555 Точки

Да, при това решение е по видимо какво искаме да направим, а като изваждаме стойности NOT IN използвайки Subquery, реално не можем да ги селектираме.

0
Можем ли да използваме бисквитки?
Ние използваме бисквитки и подобни технологии, за да предоставим нашите услуги. Можете да се съгласите с всички или част от тях.
Назад
Функционални
Използваме бисквитки и подобни технологии, за да предоставим нашите услуги. Използваме „сесийни“ бисквитки, за да Ви идентифицираме временно. Те се пазят само по време на активната употреба на услугите ни. След излизане от приложението, затваряне на браузъра или мобилното устройство, данните се трият. Използваме бисквитки, за да предоставим опцията „Запомни Ме“, която Ви позволява да използвате нашите услуги без да предоставяте потребителско име и парола. Допълнително е възможно да използваме бисквитки за да съхраняваме различни малки настройки, като избор на езика, позиции на менюта и персонализирано съдържание. Използваме бисквитки и за измерване на маркетинговите ни усилия.
Рекламни
Използваме бисквитки, за да измерваме маркетинг ефективността ни, броене на посещения, както и за проследяването дали дадено електронно писмо е било отворено.