sql - 每组最小行数

以下是我的查询:

SELECT *
FROM (
    SELECT f.max, f.min, p.user_id, p.id, p.title, p.rating,
    RANK() OVER (
        PARTITION BY p.user_id
        ORDER BY p.rating DESC, p.id DESC
    ) AS rnk
    FROM posts AS p
    INNER JOIN friends AS f ON (p.user_id = f.friend_id)
    WHERE f.user_id=1
) AS subq
WHERE (subq.rnk <= subq.max)
LIMIT 10

它搜索我 friend 的帖子，按他们的评分和日期排序。这个查询中实现的窗口函数让我可以根据 MAX 表上的 Friends 字段限制为每个 friend 返回的行数。

但是，我还有一个字段 MIN ，它用于指定我希望从给定 friend 的查询中获得的最小帖子数。这怎么可能？

我还想知道 SQL 是否是这些类型查询的最佳选择？我已经尝试过 Neo4j Graph 数据库，虽然它看起来是一个不错的解决方案，但我宁愿避免使用 2 个单独的数据库。

SQLFiddle

架构:

CREATE TABLE friends(
    user_id int,
    friend_id int,
    min int,
    max int
);

CREATE TABLE posts(
   id int,
   title varchar(255),
   rating int,
   date date,
   user_id int
);

假设我们有以下数据:

INSERT INTO friends VALUES
  (1,2,1,3)
, (1,3,0,5)
, (1,4,2,10);

INSERT INTO posts VALUES
  (1,  'posts1',  2,  now(), 2)
, (2,  'posts2',  1,  now(), 2)
, (3,  'posts3',  5,  now(), 2)
, (4,  'posts4',  2,  now(), 2)
, (5,  'posts5',  11, now(), 2)
, (6,  'posts6',  7,  now(), 2)
, (7,  'posts7',  3,  now(), 2)
, (8,  'posts8',  4,  now(), 3)
, (9,  'posts9',  1,  now(), 3)
, (10, 'posts10', 0,  now(), 3)
, (11, 'posts11', 7,  now(), 3)
, (12, 'posts12', 3,  now(), 3)
, (13, 'posts13', 2,  now(), 3)
, (14, 'posts14', 4,  now(), 4)
, (15, 'posts15', 9,  now(), 4)
, (16, 'posts16', 0,  now(), 4)
, (17, 'posts17', 3,  now(), 4)
, (18, 'posts18', 2,  now(), 4)
, (19, 'posts19', 1,  now(), 4)
, (20, 'posts20', 2,  now(), 4);

因此，如果可能，我希望看到具有以下条件的 (post_id, title, rating, date, friend_id) 组合:

来自 friend 的 1 到 3 个帖子，其中 id =2

来自 friend 的 0 到 5 个帖子，id =3

来自 friend 的 2 到 10 个帖子，id =4

所以基本上，如果我的 friend_id=2 friend 发布了 1 篇或更多文章，我想要至少 2 篇。如果他发了 3 篇以上的文章，我要不超过 3 篇。

最佳答案

你的解释 in the comment 仍然不成立。根据此解释，您的 min 编号将是没有影响的噪音。

这不是你写的，但这是有道理的:

给定最大的帖子显示位置(外部 LIMIT )，我想首先从每个 friend 那里获取 min 帖子(如果有)。如果之后有空闲插槽，请填写每个 friend 最多 max 帖子。

在这个例子中，如果还有更多的空位可用，那么 friend 2 的 1 ( min ) 帖子具有最高优先级，另外 2 ( max - min ) 帖子。

如果每个优先级没有足够的空位，那么哪些职位会被削减将是任意的。我继续假设应该首先选择每个人的第一篇文章，等等。

其余的仍然是任意的，但如果您设法制定要求，则可以轻松解决。

SELECT *
FROM   friends f
,  LATERAL (
   SELECT *
        , row_number() OVER (ORDER  BY rating DESC NULLS LAST, id DESC) AS rn
   FROM   posts p
   WHERE  user_id = f.friend_id  -- LATERAL reference
   ORDER  BY rating DESC NULLS LAST, date DESC NULLS LAST
   LIMIT  f.max  -- LATERAL reference
   ) p
WHERE  f.user_id = 1
ORDER  BY (p.rn > f.min)  -- minimum posts from each first
        , p.rn
LIMIT  10;  -- arbitrary total maximum

SQL Fiddle.

笔记

假设 friends.user_id 和 posts.id 为主键。那里缺少您的表定义。

所有其他列都应该定义为 NOT NULL 才有意义。

使用 LATERAL 联接在子查询中仅选择每个 friend 的 max 帖子:

What is the difference between LATERAL and a subquery in PostgreSQL?

在子查询中使用 row_number() , not rank() 。混淆两者是一个常见的错误。

您提到了 date 但它没有显示在您的查询中。也许你真的想要:

, row_number() OVER (ORDER  BY rating DESC NULLS LAST
                             , date   DESC NULLS LAST) AS rn

DESC NULLS LAST 仅因为 rating 和 date 可能为 NULL:

PostgreSQL sort by datetime asc, null first?

在 Postgres 中，你可以在 ORDER BY 中使用一个简单的 bool 表达式:

ORDER  BY (p.rn > f.min), p.rn

SQL select query order by day and month

Sorting null values after all others, except special

这将每个 friend 的 min 帖子放在首位。第二项( p.rn )给每个 friend 一个平等的机会(先发帖等)。

不要使用 date 作为标识符。它是标准 SQL 中的保留字，也是 Postgres 中的基本类型名称。

关于sql - 每组最小行数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30001333/