Есть ли способ гарантировать, что предложение WHERE происходит после DISTINCT?

Ответ 1

Вы можете сделать это без использования подзапроса, используя LEFT JOIN:

SELECT  c.id, c.comment_id_no, c.text, c.show, c.inserted_at
FROM    Comments AS c
        LEFT JOIN Comments AS c2
            ON c2.comment_id_no = c.comment_id_no
            AND c2.inserted_at > c.inserted_at
WHERE   c2.id IS NULL
AND     c.show = 'true';

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

SELECT  c.id, c.comment_id_no, c.text, c.show, c.inserted_at
FROM    (   SELECT  c.id, 
                    c.comment_id_no, 
                    c.text, 
                    c.show, 
                    c.inserted_at,
                    ROW_NUMBER() OVER(PARTITION BY c.comment_id_no 
                                      ORDER BY c.inserted_at DESC) AS RowNumber
            FROM    Comments AS c
        ) AS c
WHERE   c.RowNumber = 1
AND     c.show = 'true';

Поскольку вы пометили Postgresql, вы также можете использовать DISTINCT ON():

SELECT  *
FROM    (   SELECT  DISTINCT ON (c.comment_id_no) 
                    c.id, c.comment_id_no, c.text, c.show, c.inserted_at
            FROM    Comments AS c 
            ORDER By c.comment_id_no, inserted_at DESC
        ) x
WHERE   show = 'true';

Примеры по БД <> Fiddle

Ответ 2

Я думаю, что вы хотите:

select c.*
from comments c
where c.inserted_at = (select max(c2.inserted_at)
                       from comments c2
                       where c2.comment_id_no = c.comment_id_no
                      ) and
      c.show = 'true';

Я не понимаю, какое это имеет отношение к select distinct. Вы просто хотите последнюю версию комментария, а затем проверить, можете ли вы показать это.

РЕДАКТИРОВАТЬ:

В Postgres я бы сделал:

select c.*
from (select distinct on (comment_id_no) c.*
      from comments c
      order by c.comment_id_no, c.inserted_at desc
     ) c
where c.show

distinct on обычно довольно неплохими эксплуатационными характеристиками.

Ответ 3

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

И нет: "двойное управление версиями", предложенное @Josh_Eller в его комментарии, также не является хорошим решением: не только излишне усложнять запросы, но и намного дороже с точки зрения обработки и фрагментации табличного пространства.

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

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

Таким образом, лучшее решение (IMHO) - просто реализовать схему, которая вам действительно нужна для вашей основной задачи, и выделить слуховой аппарат отдельно в отдельной таблице и поддерживать с помощью триггера.

Это было бы намного больше:

• Надежный и простой: потому что вы всегда фокусируетесь на одной вещи (принципы единой ответственности и KISS).

• Быстро: Аудиторские операции могут выполняться в триггере после, поэтому каждый раз, когда вы выполняете INSERT, UPDATE или DELETE, любая возможная блокировка в транзакции все же освобождается, потому что ядро базы данных знает, что его результат не изменится.

• Эффективно: Т.е. обновление, конечно, вставит новую строку и пометит старую как удаленную. Но это будет сделано на низком уровне ядром базы данных, и более того: ваши слуховые данные будут полностью нефрагментированы (потому что вы только пишете туда: никогда не обновляйтесь). Таким образом, общая фрагментация всегда будет намного меньше.

При этом, как это реализовать?

Предположим, это простая схема:

create table comments (
    text text,
    mtime timestamp not null default now(),
    id serial primary key
);

create table comments_audit ( -- Or audit.comments if using separate schema
    text text,
    mtime timestamp not null,
    id integer,
    rev integer not null,
    primary key (id, rev)
);

... а затем эта функция и триггер:

create or replace function fn_comments_audit()
returns trigger
language plpgsql
security definer
    -- This allows you to restrict permissions to the auditory table
    -- because the function will be executed by the user who defined
    -- it instead of whom executed the statement which triggered it.
as $$
DECLARE
BEGIN

    if TG_OP = 'DELETE' then
        raise exception 'FATAL: Deletion is not allowed for %', TG_TABLE_NAME;
        -- If you want to allow deletion there are a few more decisions to take...
        -- So here I block it for the sake of simplicity ;-)
    end if;

    insert into comments_audit (
        text
        , mtime
        , id
        , rev
    ) values (
        NEW.text
        , NEW.mtime
        , NEW.id
        , coalesce (
            (select max(rev) + 1 from comments_audit where id = new.ID)
            , 0
        )
    );

    return NULL;

END;
$$;

create trigger tg_comments_audit
    after insert or update or delete
    on public.comments
    for each row
    execute procedure fn_comments_audit()
;

И это все.

Обратите внимание, что при таком подходе у вас всегда будут текущие данные комментариев в comments_audit. Вместо этого вы могли бы использовать регистр OLD и определять триггер только в операциях UPDATE (и DELETE), чтобы избежать его.

Но я предпочитаю этот подход не только потому, что он дает нам дополнительную избыточность (случайное удаление -in, если это было разрешено или триггер, когда случайно disabled- на главной таблице), тогда мы сможем восстановить все данные из аудитории. один) но также потому, что это упрощает (и оптимизирует) запросы истории, когда это необходимо.

Теперь вам нужно только вставлять, обновлять или выбирать (или даже удалять, если вы немного больше разрабатываете эту схему, то есть вставляете строку с нулями...) полностью прозрачным образом, как если бы это была не какая-либо слуховая система. И когда вам нужны эти данные, вам нужно только запросить таблицу слуховых аппаратов.

ПРИМЕЧАНИЕ. Кроме того, вы можете включить метку времени создания (ctime). В этом случае было бы интересно не допустить его изменения в триггере BEFORE, поэтому я пропустил его (опять же ради простоты), потому что вы уже можете догадаться по mtimes в слуховой таблице (даже если вы собираетесь использовать его). это в вашем приложении было бы очень желательно добавить его).

Ответ 4

Если вы используете Postgres 8.4 или выше, ROW_NUMBER() является наиболее эффективным решением:

SELECT *
FROM (
    SELECT c.*, ROW_NUMBER() OVER(PARTITION BY comment_id_no ORDER BY inserted_at DESC) rn
    FROM comments c
    WHERE c.show = 'true'
) x WHERE rn = 1

Иначе, это также может быть достигнуто с помощью условия WHERE NOT EXISTS, которое гарантирует, что вы показываете последний комментарий:

SELECT c.*
FROM comments c
WHERE 
    c.show = 'true '
    AND NOT EXISTS (
        SELECT 1 
        FROM comments c1 
        WHERE c1.comment_id_no = c.comment_id_no AND c1.inserted_at > c.inserted_at
    )

Ответ 5

Вы должны использовать group by чтобы получить последние идентификаторы, и присоединиться к таблице комментариев, чтобы отфильтровать строки, где show = false:

select c.* 
from comments c inner join (
  select comment_id_no, max(id) maxid
  from comments
  group by comment_id_no 
) g on g.maxid = c.id
where c.show = 'true'

Я предполагаю, что id столбца является уникальным и автоинкремент в таблице comments.
Посмотреть демо