sql - 每组的最小行数

Question

以下是我的查询:

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 的帖子，按他们的评分和日期排序。通过此查询中实现的窗口函数，我可以根据 Friends 表上的 MAX 字段限制为每个 friend 返回的行数。

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

我还想知道 SQL 是否是这些类型查询的最佳选择？我已经尝试过 Neo4j 图形数据库，虽然它看起来是一个很好的解决方案，但我宁愿避免使用 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) 组合具有以下条件:

1. id=2 的好友发布了 1 到 3 条帖子
2. id=3 的好友发布了 0 到 5 条帖子
3. id=4 的好友发布了 2 到 10 条帖子

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

Answer 1

Let's say I want to have 2-5 posts from you everyday, if you post that much. If you post only one, it is alright, and I will have the only one post.

您的解释in the comment仍然没有加起来。根据此解释，您的 min 数字将是噪音，没有任何效果。

这不是你写的，但这很有意义:

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

在此示例中，好友 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()在子查询中。混淆两者是一个常见的错误。

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

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

• DESC NULLS LAST仅因为评级和日期可能为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 提供平等的机会(先发帖先等等)。

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