Запрашивать базу данных для различных значений и агрегировать данные на основе условия

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

CREATE TABLE users
(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE photos
(
  id INT PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  flag VARCHAR(255)
);

INSERT INTO users VALUES (1, 'Bob');
INSERT INTO users VALUES (2, 'Alice');
INSERT INTO users VALUES (3, 'John');

INSERT INTO photos VALUES (1001, 1, 'a');
INSERT INTO photos VALUES (1002, 1, 'b');
INSERT INTO photos VALUES (1003, 1, 'c');
INSERT INTO photos VALUES (1004, 2, 'a');
INSERT INTO photos VALUES (1004, 2, 'x');

Что мне нужно, так это извлечь каждое имя пользователя только один раз и значение флага для каждого из них. Значение флага должно отдавать предпочтение определенному, скажем, b. Итак, результат должен выглядеть так:

Bob    b 
Alice  a

Где Bob владеет фотографией с флагом b, а Alice — нет, а John не имеет фотографий. Для Alice вывод для значения флага не важен (a или x было бы так же хорошо), если у нее нет фотографии с пометкой b.

Самое близкое, что я нашел, это несколько запросов с самообъединением, в которых значение флага было бы агрегировано с использованием min() или max(), но я ищу конкретное значение, которое не является ни первым, ни последним. Более того, я обнаружил, что вы можете определить свои собственные агрегатные функции, но мне интересно, есть ли более простой способ обусловить запрос, чтобы получить требуемые данные.

Благодарю вас!


postgresql sql distinct-values
person VMC    schedule 07.10.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вот метод с агрегацией:

select u.name,
       coalesce(max(flag) filter (where flag = 'b'),
                min(flag)
               ) as flag
from users u left join
     photos p
     on u.id = p.user_id
group by u.id, u.name;

Тем не менее, более типичным методом был бы запрос определения приоритетов. Возможно:

select distinct on (u.id) u.name, p.flag
from users u left join
     photos p
     on u.id = p.user_id
order by u.id, (p.flag = 'b') desc;
person Gordon Linoff    schedule 07.10.2019
comment
Спасибо, сэр! Я только что узнал две новые вещи о SQL. - person VMC; 07.10.2019