Блог

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

 name: string
value: string
user_id: ID
  

По сути, я пытаюсь найти все совпадающие наборы пользователей, которые используют две или более одинаковых пары ключ-значение.

Например. если бы были эти строки

 name,value,user_id
"test", "value1", "user_1"
"test", "value3", "user_2"
"test2", "value2", "user_1"
"test3", "value3", "user_1"
"test2", "value2", "user_2"
"test3", "value3", "user_2"
  

Я бы ожидал, что результат будет:

 kv_name_1, kv_value_1, kv_name_2, kv_value_2, count
"test2", "value2", "test3", "value3", 2
  

Потому что и user_1, и user_2 имеют две пары ключ-значение, которые идентичны. Если бы был третий или четвертый пользователь с теми же значениями, тогда это было бы количество 4 и т.д.

У меня есть этот запрос, который работает, но, боже, он медленный. Само планирование запроса может занять около 100 мс

 SELECT
    q1.e1Name,
    q1.e1Value,
    q1.e3Name,
    q1.e3Value,
    count(*)
FROM ( SELECT DISTINCT ON (e1.id, e3.id)
        e1.name AS e1Name,
        e1.value AS e1Value,
        e1.id,
        e3.name AS e3Name,
        e3.value AS e3Value,
        e3.id
    FROM
        user_properties e1
        JOIN users eu ON e1.user_id = eu.id
        JOIN user_groups p ON p.group_id = 'x'
            AND p.user_id = eu.id
        JOIN user_properties e2 ON e1.name = e2.name
            AND e1.value = e2.value
            AND e1.id != e2.id
        JOIN users eu2 ON e2.user_id = eu2.id
        JOIN user_groups p2 ON p2.group_id = 'x'
            AND p2.user_id = eu2.id
        JOIN user_properties e3 ON e3.user_id = eu.id
            AND e1.name != e3.name
        JOIN user_properties e4 ON e4.id != e2.id
            AND e4.name = e3.name
            AND e3. "value" = e4.value
            AND e4.user_id = e2.user_id) q1
GROUP BY
    q1.e1Name,
    q1.e1Value,
    q1.e3Name,
    q1.e3Value;
  

Теперь я, вероятно, понимаю, что мне нужно найти способ частично сгруппировать / ограничить количество результатов, возвращаемых каждым подзапросом, но не уверен, где это сделать

Начните с генерации всех пар кандидатов из двух пар ключ / значение. Затем присоединитесь к данным, чтобы определить, сколько раз они появляются:

 with candidates as (
      select kv.key as key1, kv.value as value1, kv2.key as key2, kv.value as value2, kv.user_id
      from keyvalue kv join
           keyvalue kv2
           on kv.user_id = kv2.user_id and
              (kv.key, kv.value) < (kv2.key, kv2.value)
     )
select key1, value1, key2, value2, count(*)
from candidates c1 join
     candidates c2
     using (key1, value1, key2, value2)
where c1.user_id < c2.user_id
group by key1, value1, key2, value2;