Неразличимые пары Postgresql в нескольких столбцах

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

В качестве простого примера:

Таблица 1:

column1 - column2
1 – 1
1 – 2
2 – 1
2 – 1 
3 - 3
4 - 5

возвращаться:

1 - 1
1 - 2

Существует множество примеров, показывающих различимость (например, этот), но я ищу неотличимость.

select distinct (column2, column1) my_distinct from table1;

Если это не должно быть очень эффективно, просто выполните два вложенных запроса GROUP BY - COUNT.

— 29.03.2024 15:02

... или оберните свой distict запрос в GROUP BY - COUNT.

— 29.03.2024 15:04

sql postgresql

29.03.2024 14:45

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Вы можете использовать CTE, чтобы получить все значения column1, которые вы ищете, а затем отфильтровать таблицу:

Схема (PostgreSQL v15)

CREATE TABLE t (
  column1 INTEGER,
  column2 INTEGER
);

INSERT INTO t
  (column1, column2)
VALUES
  (1, 1),
  (1, 2),
  (2, 1),
  (2, 1),
  (3, 3),
  (4, 5);

Запрос №1

WITH cte AS (
  SELECT column1
  FROM t
  GROUP BY 1
  HAVING COUNT(DISTINCT column2) > 1
)
SELECT column1, column2
FROM t
JOIN cte USING (column1);

столбец1 столбец2 1 1 1 2

В качестве альтернативы вы можете создать список значений column2 (для каждого столбца 1) и отменить его вложение, чтобы избежать повторного сканирования таблицы.

Запрос №2

SELECT column1, unnest(array_agg(column2)) AS column2
FROM t
GROUP BY 1
HAVING COUNT(DISTINCT column2) > 1;

столбец1 столбец2 1 1 1 2

Посмотреть на DB Fiddle

Вам даже не нужен CTE, вы можете написать ...unnest(array_agg(column2)) AS column2... в запросе, который вы фактически используете в своем CTE.

— 29.03.2024 15:34

Ах, да. Хороший, спасибо.

— 29.03.2024 16:17

29.03.2024 15:05

Ответ принят как подходящий

Это ванильный способ сделать это. Просто существует для проверки записей, в которых есть другое значение в столбце2 для того же столбца1.

Рабочий пример: https://dbfiddle.uk/Zf9vTS9K

CREATE TABLE t (
  column1 INTEGER,
  column2 INTEGER
);

INSERT INTO t
  (column1, column2)
VALUES
  (1, 1),
  (1, 2),
  (2, 1),
  (2, 1),
  (3, 3),
  (4, 5);

select * 
  from t t1
 where exists
         (
           select 1
             from t t2
            where t1.column1 = t2.column1
              and t1.column2 <> t2.column2
         );

29.03.2024 16:24

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

К сожалению, COUNT(DISTINCT не реализован для оконных функций, поэтому вам придется взломать его с помощью DENSE_RANK и MAX.

with ranked as (
    select *,
      dense_rank() over (partition by column1 order by column2) as dr 
    from t
),
maxed as (
    select *,
      max(dr) over (partition by column1) as max_dr
    from ranked
)
select
  column1,
  column2
from maxed
where max_dr > 1;

Другой вариант в вашем конкретном случае — сравнить счетчики с двумя разными разделами.

Идея состоит в том, что если в разбиении больше строк только на column1, чем на column1 и column2, то должно существовать как минимум два различных значения column2.

Если в разделе column1 ровно одна строка, то обе возвращают 1.
Если в нем несколько одинаковых строк, обе снова возвращают одно и то же значение.
Если существует несколько значений column2, независимо от того, дублируются они или нет, тогда общее секционирование вернет более высокое значение, чем секционирование column2.

with counted as (
    select *,
      count(*) over (partition by column1, column2) as c1,
      count(*) over (partition by column1) as c2
    from t
)
select
  column1,
  column2
from counted
where c1 < c2;

db<>рабочий пример

29.03.2024 16:54