Выберите последние строки с отдельным полем

У меня есть таблица со следующей схемой:

id itemid date        some additional data
1   1000  10/12/2020       a
2   1000  10/12/2020       b
3   1002  09/12/2020       c
4   1001  07/12/2020       d
5   1000  05/12/2020       e
6   1005  03/12/2020       f
7   1003  03/12/2020       g

В этой таблице уникальным является только поле id. Меня интересует получение строк, содержащих последние X различных itemid, упорядоченных по дате.

Например, в приведенном выше примере, если я хочу получить последние 3 различных itemid, я получу первые 4 строки, поскольку в первых 4 строках у нас есть три различных itemid: 1000, 1002 и 1001. не уверен, как добиться этого с помощью одного оператора SQL.


postgresql sql distinct top-n
person Alk    schedule 25.01.2021    source источник
comment
Я этого не понимаю. Если вы хотите получить разные идентификаторы элементов, то почему вы ожидаете, что первые 4 строки будут повторять itemid = 1000?   -  person a_horse_with_no_name    schedule 25.01.2021
comment
В таблице есть дополнительные поля, которые отличаются строками 1 и 2. Меня интересует получение всех данных, связанных с последними X различными идентификаторами элементов. Позвольте мне обновить схему.   -  person Alk    schedule 25.01.2021
comment
Просто примечание: в таблицах есть столбцы, а не поля.   -  person jarlh    schedule 25.01.2021
comment
да, мой плохой....   -  person Alk    schedule 25.01.2021

Ответы (2)


arrow_upward
1
arrow_downward

Если я правильно понимаю, вы хотели бы подсчитать количество различных идентификаторов элементов до каждой строки (по дате) и вернуть все строки, в которых количество равно трем.

Если бы Postgres поддерживал это, вы могли бы использовать:

select t.*
from (select t.*, 
             count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
      from (select t.*,
                   min(id) over (partition by itemid order by date desc) as min_id
            from t
           ) t
     ) t
where cnt_itemid <= 3;

Увы, Postgres не поддерживает COUNT(DISTINCT) в качестве оконной функции. Но вы можете рассчитать его, используя DENSE_RANK():

select t.*
from (select t.*, 
             count(*) over (filter where id = min_id) as cnt_itemid
      from (select t.*,
                   min(id) over (partition by itemid order by date) as min_id
            from t
           ) t
     ) t
where cnt_itemid <= 3;

Однако это возвращает все самые последние строки до 4-го элемента, поэтому у него есть дополнительные строки.

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

select t.*
from (select t.*, min(id) filter (where cnt_itemid = 3) over () as min_cnt_itemid_3
      from (select t.*, 
                   count(*) filter (where id = min_id) over (order by date desc) as cnt_itemid
            from (select t.*,
                         min(id) over (partition by itemid order by date desc) as min_id
                  from t
                 ) t
           ) t
     ) t
where id <= min_cnt_itemid_3;

Вы также можете сделать это, указав первое вхождение третьего элемента, а затем выбрав все строки до этой строки:

select t.*
from t join
     (select itemid, min(max_date) over () as min_max_date
      from (select t.itemid, max(date) as max_date
            from t
            group by t.itemid
            order by max(t.date) desc
            limit 3
           ) t
      ) tt
      on t.itemid = tt.itemid and t.date >= tt.min_max_date;

Эта скрипта показывает каждый из них.

person Gordon Linoff    schedule 25.01.2021
comment
Три - это просто число, которое я использовал для иллюстрации примера. Я пытаюсь получить все данные, касающиеся последних X различных идентификаторов элементов. Позвольте мне проиллюстрировать это примерами. Предположим, что идентификаторы элементов являются статьями, а строки в этой таблице представляют собой комментарии. по статьям, я хотел бы получить все комментарии (строки), сделанные к последним X статьям. Например - все комментарии к последним 20 статьям. - person Alk; 25.01.2021
comment
Таким образом, запрос будет упорядочивать таблицу по убыванию даты, продолжайте выбирать строки, пока вы не подсчитаете X различных идентификаторов элементов. - person Alk; 25.01.2021

arrow_upward
0
arrow_downward

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

select * from
(select t.*,
       conut(distinct item_id) over (order by date desc) as cnt
  from your_Table t) t
 where cnt <= 3
person Popeye    schedule 25.01.2021