Перекрытие столбца на основе другого столбца

У меня есть простая таблица в Postgres, содержащая дни недели и людей.

INSERT INTO mytable (day, person)
values
('Monday', 'A'),
('Monday', 'A'),
('Monday', 'B'),
('Tuesday', 'A'),
('Wednesday', 'A'),
('Wednesday', 'B'),
('Thursday', 'B'),
('Thursday', 'B');

Я хотел бы найти количество пересекающихся людей для каждой пары дней. Так, например, в понедельник есть человек A и B. Во вторник есть только человек B, поэтому в результате получится следующая строка:

('Monday', 'Tuesday', 1)

Окончательный результат должен выглядеть так:

('Monday', 'Monday', 2),
('Monday', 'Tuesday', 1),
('Monday', 'Wednesday', 2),
('Monday', 'Thursday', 1),
('Tuesday', 'Tuesday', 1),
('Tuesday', 'Monday', 1),
('Tuesday', 'Wednesday', 1),
('Tuesday', 'Thursday', 0),
('Wednesday', 'Wednesday', 2),
('Wednesday', 'Monday', 2),
('Wednesday', 'Tuesday', 1),
('Wednesday', 'Thursday', 1),
('Thursday', 'Thursday', 1),
('Thursday', 'Monday', 1),
('Thursday', 'Tuesday', 0),
('Thursday', 'Wednesday', 1)

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


postgresql sql postgresql-9.5
person user2242044    schedule 20.03.2019    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Используйте самостоятельное соединение:

select t1.day, t2.day, count(distinct t2.person) as num_overlaps
from mytable t1 join
     mytable t2
     on t1.person = t2.person
group by t1.day, t2.day
order by t1.day, t2.day;

РЕДАКТИРОВАТЬ:

Я вижу, вы также хотите включить нулевое перекрытие. Это немного сложнее. Для этого вам нужно сгенерировать все комбинации дней, а затем присоединить данные:

select d1.day, d2.day, count(distinct t2.person)
from (select distinct day from mytable) d1 cross join
     (select distinct day from mytable) d2 left join
     mytable t1
     on t1.day = d1.day left join
     mytable t2
     on t2.day = d2.day and t2.person = t1.person
group by d1.day, d2.day
order by d1.day, d2.day;

Вот скрипка db ‹>.

person Gordon Linoff    schedule 20.03.2019
comment
Спасибо, я просматриваю запрос, и он не дает правильных результатов. Может ли это быть из-за людей в один день, когда вы присоединяетесь лично? - person user2242044; 20.03.2019
comment
@ user2242044. . . Во втором запросе = для лиц должно быть <>. - person Gordon Linoff; 20.03.2019
comment
Спасибо. Я думаю, вам просто нужно, чтобы ваши левые соединения были (select distinct * from mytable), чтобы мы не учитывали дважды, если человек появляется дважды в один и тот же день. Или что-то подобное. Все еще кажется не совсем правильным. - person user2242044; 20.03.2019
comment
Основываясь на вашем комментарии, t2 присоединяется на основе разных людей, но он не проверяет, совпадают ли дни с похожими. Не уверен, что я хорошо это объяснил, но посмотрите вторник, четверг. Запрос возвращает 1, но результаты должны давать 0. - person user2242044; 20.03.2019
comment
@ user2242044. . . Я добавил скрипку db ‹›. Я думаю, это работает. - person Gordon Linoff; 20.03.2019