Получить количество связанных строк в postgres с существующим боковым соединением

Этот запрос дает мне несколько строк из table1 вместе с соответствующими записями из table2 с использованием left join lateral. Я ограничил строки из таблицы 2 до 10 строк в каждой.

select t1.id, array_agg(t2.column1) 
from table1 t1 
left join lateral (select * from table2 where table1_id = t1.id order by column2 limit 10) t2 on true 
where t1.other = other_value
group by t1.id

Но как я могу включить общее количество count(*) всех записей в таблице 2, связанных с таблицей 1 (select count(*) from table2 where table1_id = t1.id). Поскольку я делаю боковое соединение, я не уверен, как добавить эти результаты.

Могу ли я повторно использовать боковое соединение, которое я уже делаю, или мне придется сделать отдельное боковое соединение, потому что первое имеет limit 10, а count(*) не требует ограничений? Как должен выглядеть запрос, чтобы он работал так? (Я думаю, что может быть способ сделать это, используя синтаксис среза массива из первого бокового соединения, но я думаю, что это было бы дорого, так как нужно было бы получить все строки только для того, чтобы получить их количество.)


postgresql sql count lateral-join
person user779159    schedule 27.04.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вы также можете использовать оконные функции для этого:

select t1.id, array_agg(t2.column1) 
from table1 t1 left join
     (select t2.*, count(*) over (partition by table1_id) as cnt,
             row_number() over (partition by table1_id order by column2) as seqnum
      from table2
     ) t2
     on t2.table1_id = t1.id and sequm <= 10
where t1.other = other_value 
group by t1.id;

Вы можете сделать это как отдельный файл lateral join.

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

С отдельным боковым соединением:

select t1.id, array_agg(t2.column1), t2c.cnt
from table1 t1 left join lateral
     (select *
      from table2
      where table1_id = t1.id
      order by column2
      limit 10
     ) t2
     on true left join lateral
     (select count(*) as cnt
      from table2
      where table1_id = t1.id
     ) t2c
     on true
where t1.other = other_value
group by t1.id, t2.cnt;

Или с одним боковым соединением без агрегации во внешнем запросе:

select t1.id, t2.column1s, t2.cnt
from table1 t1 left join lateral
     (select array_agg(t2.column1) as column1, max(cnt) as cnt
      from (select t2.*,
                   row_number() over (order by column2 desc) as seqnum,
                   count(*) over () as cnt
            from table2
            where table1_id = t1.id
           ) t2
      where seqnum <= 10
     ) t2
     on true left join 
where t1.other = other_value;

Это, пожалуй, лучший подход.

person Gordon Linoff    schedule 27.04.2020
comment
Спасибо, не могли бы вы также показать, как это сделать с отдельным боковым соединением? - person user779159; 27.04.2020
comment
Требуется ли для второго запроса t2c.cnt в group by? - person user779159; 27.04.2020
comment
@user779159 . . . Да. - person Gordon Linoff; 27.04.2020