Блог

У меня есть такая таблица:

 user_id | subscription_id
-------------------------
1       | 1
1       | 2
2       | 3
2       | 4
3       | 1
3       | 2
4       | 3
5       | 3
  

Что я хочу сделать, так это подсчитать, сколько пользователей имеют похожие подписки:

 user_id | same_subscriptions
----------------------------
1       | 1
2       | 0
3       | 1
4       | 1
5       | 1
  

Возможно ли это вообще? Как я могу этого добиться…

Лучшее, что мне удалось сделать, это получить такую таблицу с помощью group_concat:

 user_id | subscriptions
-----------------------
1       | 1,2
2       | 3,4
3       | 1,2
4       | 3
5       | 3
  

Вот как я этого добился:

 SELECT A.user_id, group_concat(B.subscription_id) 
  FROM Subscriptions A LEFT JOIN Subscriptions B ON 
  A.user_id=B.user_id GROUP BY A.user_id;
  

Агрегатная функция GROUP_CONCAT() в этом случае не помогает, потому что в SQLite она не поддерживает ORDER BY предложение, чтобы можно было выполнить безопасное сравнение.
Но вместо этого вы можете использовать GROUP_CONCAT() функцию window:

 SELECT user_id, 
       COUNT(*) OVER (PARTITION BY subs) - 1 same_subscriptions
FROM (
  SELECT user_id, 
    GROUP_CONCAT(subscription_id) OVER (PARTITION BY user_id ORDER BY subscription_id) subs,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY subscription_id DESC) rn
  FROM Subscriptions
)
WHERE rn = 1
ORDER BY user_id
  

Посмотрите демонстрацию.
Результаты:

 > user_id | same_subscriptions
> ------: | -----------------:
>       1 |                  1
>       2 |                  0
>       3 |                  1
>       4 |                  1
>       5 |                  1