Перекрытие в столбце для общего ключа в SQL

Question

Любые советы о том, как преобразовать набор данных, который может иметь несколько значений для каждого пользователя, в набор данных, который показывает, сколько раз два значения перекрываются (находятся для каждого пользователя)?

Исходный гипотетический набор данных:

User_ID Toured_State
A       NY
A       CA
A       FL
B       NY
B       TX
C       NY
C       CA
D       TX

Желаемый набор данных:

State_1     State_2     Count of users that toured both states
NY          CA          2
NY          TX          1
NY          FL          1
NY          NY          0

Это покажет, как часто пользователь, путешествующий по одному состоянию, также путешествует по второму состоянию.

Моей первой мыслью было сделать самосоединение исходного набора данных с идентификатором пользователя, а затем сделать сумму равного количества строк (с учетом обратных дубликатов)? Это самый эффективный подход? Обратите внимание, что пользователи могут совершать поездки по одному или нескольким штатам (не ограничиваясь двумя), включая одно и то же состояние дважды. Я изменил свой пример, поэтому я понимаю, что этот конкретный случай может показаться бесполезным. Спасибо за любые советы заранее.

Answer 1

Я бы сделал это как:

select t1.state as state1, t2.state as state2, count(*)
from t t1 join
     t t2
     on t1.user_id = t2.user_id and t1.state < t2.state
group by t1.state, t2.state
order by count(*) desc;

Если вы предпочитаете считать пользователей, то пользователь count(distinct user_id),

Answer 2

Вы можете попробовать это.

;WITH CTE AS (
    SELECT *, RN= ROW_NUMBER() OVER(PARTITION BY User_ID Order BY User_ID) FROM @T 
)
SELECT 
    T1.Toured_State State_1, 
    T2.Toured_State State_2, 
    COUNT(CASE WHEN T1.Toured_State = T2.Toured_State THEN NULL ELSE 1 END) [Count of users]
FROM CTE T1
    LEFT JOIN CTE T2 ON T1.User_ID = T2.User_ID AND T1.RN <= T2.RN
WHERE T1.RN = 1
GROUP BY T1.Toured_State, T2.Toured_State
ORDER BY [Count of users] DESC