Блог

Главная - Вопросы по программированию - PostgreSQL: отображение перекрестных ссылок внутри таблицы удобным для пользователя способом

PostgreSQL: отображение перекрестных ссылок внутри таблицы удобным для пользователя способом

#postgresql #cross-reference

#postgresql #перекрестные ссылки

Вопрос:

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

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

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

Вот пример (упорядоченный по времени создания desc):

 | verification_id | account_id   | reference |
|-----------------|--------------|-----------|
|       4a        |      4       |     4a    |
|       3a        |      3       |    1a,3a  |
|       2b        |      2       |    2a,2b  |
|       2a        |      2       |     2a    |
|       1a        |      1       |     1a    |

Меня не интересует тот факт, что ссылка указывает на то, что уникальный параметр появляется как в проверках 2a, так и в 2b, потому что они принадлежат одной учетной записи.

Меня интересует тот факт, что уникальный параметр отображается как в account_id 1, так и в 3, как указано в ссылочной ячейке (т. Е. 1a, 3a).

Это результат, который я ищу:

 | account_id  | connected to |
|-------------|--------------|
|      1      |      3       |
|      3      |      1       |

Или даже просто первой строки было бы достаточно.

Надеюсь, я хорошо объяснил проблему и результат.

Комментарии:

1. Вы не должны хранить значения, разделенные запятыми, подобным образом. Есть ли у вас шанс исправить эту неисправную модель данных?

2. Каков тип данных reference столбца?

3. Что, если references содержит 10 разных значений? Какой именно результат вы хотите тогда?

4. > Каков тип данных ссылочного столбца? Массив JSON

5. > Что, если ссылки содержат 10 разных значений? Какой именно результат вы хотите тогда? Хороший вопрос. Я бы хотел, чтобы они были централизованы вокруг одного основного account_id. Должен сказать, вы меня немного озадачили этим вопросом.

Ответ №1:

Объедините по неравенству для account_id и verification_id , а затем проверьте перекрытие массива с amp;amp; :

 select a.account_id, b.account_id as connected_to
  from verification a
       join verification b
         on a.account_id != b.account_id
        and a.verification_id != b.verification_id
        and string_to_array(a.reference, ',') amp;amp; 
              string_to_array(b.reference, ',');

Здесь работает скрипка.