sql - 如何从具有计数的父表中选择第 n 个子元素

Question

假设我有两个表:

create table parents (id integer, name varchar, child_count integer);
create table children (id integer, name varchar, parent_id integer);

假设 parent 与子女的比例约为 1:1000。

并且假设 child_count 定期更新(或者通过 PostgreSQL 中的物化 View 可用)，例如使用此更新语句:

update parents p set child_count = pc.count
from (select p.id id, count(c.*) count
    from parents p join children c on p.id = c.parent_id
    group by p.id) as pc
where p.id = pc.id;

我想在所有 child 中找到第 n 个 child ，其中 child 首先按 parent 姓名排序，然后按 child 姓名排序。基本上，我想要这个查询:

select c.*
from children c join parents p on p.id = c.parent_id
order by p.name, c.name
limit 42 offset 42;

...除了这是一个昂贵的查询，需要扫描子项，但我知道使用 child_count 字段可以更便宜地完成它 - 有可能过时的风险，但我并不担心那个。

有没有办法编写一个查询(最好是 postgresql)，使用父级的 child_count 从所有父级中选择第 n 个子级？

或者是否有更好的数据结构化方法？我会考虑使用范围(无论是 postgresql native 还是使用两个单独的列)，除非它们似乎难以更新。我可能想使用不同的排序顺序选择第 n 个 child (但始终使用 parent 排序，然后是 child )。

我目前最好的想法是将所有父级缓存在内存中，并在我自己发现正确的父级后使用限制/偏移查询，但也可能有大量的父级。

Answer 1

好像是 row_number window function (并查看 this 和 this)可能正是您所需要的:

SELECT x.*
FROM (  SELECT  p.id AS parent_id,
                p.name AS parent_name,
                c.id AS child_id,
                c.name AS child_name,
                row_number() OVER ( ORDER BY p.name, c.name ) AS ordinal
        FROM    children c
                JOIN parents p ON c.parent_id = p.id
    ) AS x
WHERE x.ordinal = 42
ORDER BY p.name, c.name;

事实上，对于上述查询，您甚至不需要保留 child_count 列(尽管您可能出于其他原因想要保留)。