Вопрос о самостоятельных соединениях. Наблюдение за взаимодействием двух пользователей
Мне дали задание SQL, которое представляет собой следующую таблицу, имитирующую приложение для знакомств: Имя таблицы = знакомства
user_id | viewing_profile_id | date | liked
1 2 x yes
1 3 x yes
2 1 x yes
2 3 x no
3 1 x no
3 2 x no
по сути, я хочу увидеть процент профилей, которые соответствуют друг другу. совпадение — это когда оба профиля нравятся друг другу.
Кажется, я знаю, что мне делать. Выполните самосоединение с самой таблицей и используйте случай, когда профили нравятся друг другу.
select t.date, sum(t.match) / count(t.match) as p_match
from (
select s1.user_id, s1.viewing_profile_id,
date_trunc('day',s1.date) as date, case when s1.liked = 'yes'
and s2.liked = 'yes' then 1 else 0 end as match
from dating s1
left join dating s2 on s2.ser_id = s1.viewing_profile_id
group by 1,2) t
group by 1
Поскольку это всего лишь составленная таблица, и у меня нет данных для ее выполнения, мне было интересно, смогу ли я получить некоторое представление о том, будет ли это работать.
Я ожидаю, что подзапрос выдаст что-то вроде следующего:
user_id | viewing_profile_id | date | match
1 2 x 1
1 3 x 0
2 1 x 1
2 3 x 0
3 1 x 0
3 2 x 0
@jarlh спасибо за ответ. Я бы предпочел Mysql, но он неограничен. Я также добавил ожидаемые результаты.
Столбцы ожидаемого результата не соответствуют списку выбора запроса.
Ожидаемый запрос был для подзапроса, а не для основного запроса. Моя логика для основного запроса состоит в том, чтобы просто суммировать новый столбец совпадений для общего числа совпадений и подсчитывать общее количество, чтобы получить% совпадающих профилей. Это подзапрос, с которым мне нужна помощь. Конкретно с корпусом и самостоятельным соединением.
Ответы 3
/* for matched users*/
SELECT *
FROM user viewed, user viewing
WHERE viewed.user_id = viewing.viewing_profile_id
and viewed.liked='yes'
and viewing.liked='yes'
/* for Match_Percentage */
SELECT
(SELECT count(*)
FROM user viewed, user viewing
WHERE viewed.user_id = viewing.viewing_profile_id
and viewed.liked='yes'
and viewing.liked='yes')
/
(SELECT count(*) FROM user)
*100 as Match_Percentage
FROM dual;
Если вам просто нужен флаг, указывающий, соответствует ли обратное, то:
select d.*,
(exists (select 1
from dating d2
where d2.user_id = d.viewing_profile_id and
d2.viewing_profile_id = d.user_id and
d2.liked = 'yes'
) and
d.liked = 'yes'
) as match
from dating d;
Вам нужно оставить себя за столом на следующих условиях:
select
d1.*,
case when d2.user_id is null then 0 else 1 end `match`
from dating d1 left join dating d2
on
d1.user_id = d2.viewing_profile_id
and d1.viewing_profile_id = d2.user_id
and d1.liked = 'yes' and d2.liked = 'yes'
order by d1.user_id, d1.viewing_profile_id
Смотрите демо.
Результаты:
| user_id | viewing_profile_id | date | liked | match |
| ------- | ------------------ | ---- | ----- | ----- |
| 1 | 2 | | yes | 1 |
| 1 | 3 | | yes | 0 |
| 2 | 1 | | yes | 1 |
| 2 | 3 | | no | 0 |
| 3 | 1 | | no | 0 |
| 3 | 2 | | no | 0 |
Каков ожидаемый результат с этими примерами данных таблицы?