PostgreSQL: группировка и фильтрация таблицы с условием отсутствия

В PostgreSQL у меня есть таблица, которая абстрактно выглядит так:

╔═══╦═══╦═══╦═══╗
║ A ║ B ║ C ║ D ║
╠═══╬═══╬═══╬═══╣
║ x ║ 0 ║ y ║ 0 ║
║ x ║ 0 ║ x ║ 1 ║
║ x ║ 1 ║ y ║ 0 ║
║ x ║ 1 ║ z ║ 1 ║
║ y ║ 0 ║ z ║ 0 ║
║ y ║ 0 ║ x ║ 0 ║
║ y ║ 1 ║ y ║ 0 ║
╚═══╩═══╩═══╩═══╝

Я хочу преобразовать его в запросе в это:

╔═══╦═══╦══════╗
║ A ║ B ║  D   ║
╠═══╬═══╬══════╣
║ x ║ 0 ║ 1    ║
║ x ║ 1 ║ null ║
║ y ║ 0 ║ null ║
║ y ║ 1 ║ 0    ║
╚═══╩═══╩══════╝

… Такие, что:

  1. Строки входной таблицы сгруппированы по A и B, и

  2. Для каждой пары A и B:

    • Если во входной таблице есть строка, такая что A = C, то в выходной таблице есть строка (A, B, D), где D из той же строки, в которой A = C.

      Например, во входной таблице есть строка (x, 0, x, 1), в которой как A, так и C являются x. Это означает, что в выходной таблице есть строка (x, 0, 1), потому что D равно 1. Строка (x, 0, y, 0) (потому что в ней также есть A = x и B = 0) отбрасывается.

    • В противном случае, если такой строки не существует, то в выходной таблице есть строка (A, B, null).

      Например, входная таблица имеет две строки, в которых A = y и B = 0 - это (y, 0, z, 0) и (y, 0, x, 0). Ни в одной из этих строк не A = C. Это означает, что в выходной таблице есть строка (y, 0, null).

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


postgresql window-functions group-by subquery distinct-on
person jschoi    schedule 24.07.2019    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Чтобы получить по одной строке из каждой группы с одинаковым (A, B), есть простой, короткий и быстрый способ: _ 2_ - вообще не включает агрегатные функции, оконные функции или подзапросы:

SELECT DISTINCT ON (A, B)
       A, B, CASE WHEN A = C THEN D END AS D
FROM   tbl
ORDER  BY A, B, (A = C) DESC;

Дает точно желаемый результат.

db ‹> скрипт здесь

Предполагая, что все задействованные столбцы определены NOT NULL, или вам нужно сделать больше.

Последний ORDER BY элемент (A = C) DESC сортирует строку с A = C первой на группу. Это boolean выражение, и FALSE сортирует до TRUE. Если строк может быть несколько, добавьте еще ORDER BY элементов, чтобы разорвать связи.

CASE WHEN A = C THEN D END реализует ваше требование, чтобы D выводился только для данного условия. В противном случае получаем NULL (значение по умолчанию для CASE) , по желанию.

Детальное объяснение:

Для больших таблиц возможна дополнительная оптимизация производительности:

person Erwin Brandstetter    schedule 24.07.2019

arrow_upward
2
arrow_downward

Используйте CTE, который возвращает все строки, где A = C, и присоединяется к таблице:

with cte as (
  select * from tablename
  where "A" = "C"
)  
select distinct t."A", t."B", c."D"
from tablename t left join cte c
on c."A" = t."A" and c."B" = t."B"
order by t."A", t."B"

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

| A   | B   | D   |
| --- | --- | --- |
| x   | 0   | 1   |
| x   | 1   |     |
| y   | 0   |     |
| y   | 1   | 0   |
person forpas    schedule 24.07.2019