如何从查询结果中选择最后x%的行？

Question

我希望从一个查询结果中按照特定的方式排序，选择最后 1/x 部分行。如何实现？

我想到了以下类似的方法：

SELECT avg(smilies_count)
FROM posts AS p
WHERE time >= (???) -- I only want the last 25% of posts in this thread
GROUP BY thread_id; -- each thread can have more than 1 post, but I still only
                    -- want to consider the last 25% of posts in my average

但是我不太确定在???中放什么不会导致表达式非常复杂。

编辑

我已经尝试过放置

SELECT min(p2.time)
FROM posts AS p2
WHERE p2.thread_id = p.thread_id
ORDER BY p2.time DESC
LIMIT count(*) / 4

在???中，但它只给了我。

Error: misuse of aggregate function count()

- wrongusername

---

你的posts表中有一个post_id列吗？并且解决方案应该仅限于SQLite吗？ - pkmiec

@Dooh 是的，我有一个 post_id，只要在 SQLite 中也能正常工作，任何解决方案都可以。 - wrongusername

Answer 1

我猜您基本上想要每个主题的最后25%帖子，以后的操作由您决定。

如果我没错的话，那么这段代码应该适用于您（针对MS-SQL编写，应该很容易移植到SQLite）：

CREATE TABLE posts (
    post_id INT,
    thread_id INT
)

INSERT INTO posts(post_id, thread_id) VALUES (1, 1)
INSERT INTO posts(post_id, thread_id) VALUES (2, 2)
INSERT INTO posts(post_id, thread_id) VALUES (3, 2)
INSERT INTO posts(post_id, thread_id) VALUES (4, 3)
INSERT INTO posts(post_id, thread_id) VALUES (5, 3)
INSERT INTO posts(post_id, thread_id) VALUES (6, 3)
INSERT INTO posts(post_id, thread_id) VALUES (7, 3)
INSERT INTO posts(post_id, thread_id) VALUES (8, 3)
INSERT INTO posts(post_id, thread_id) VALUES (9, 3)
INSERT INTO posts(post_id, thread_id) VALUES (10, 3)
INSERT INTO posts(post_id, thread_id) VALUES (11, 3)

SELECT src.*
FROM (
    SELECT post_number = (
        SELECT 1 + COUNT(*)
        FROM posts pp 
        WHERE p.post_id > pp.post_id 
        AND p.thread_id = pp.thread_id
    ), 
    post_id,
    thread_id
    FROM posts p
) src
JOIN (
    SELECT thread_id, cnt = COUNT(*)
    FROM posts
    GROUP BY thread_id
) counts
ON src.thread_id = counts.thread_id
WHERE (CONVERT(FLOAT, src.post_number) / CONVERT(FLOAT, counts.cnt)) >= 0.75

请注意，这不是一个高效的查询，主要是由于获取post_number的子查询引起的。如果DBMS支持，可以使用OVER子句以更好的方式编写它。

- pkmiec

---

非常感谢，终于让它工作了。我想SQL可能不是最适合这个任务的语言，哈哈。 - wrongusername

Answer 2

这是一个版本，以防您需要最后25％的所有帖子：

select
  avg(1.0 * smilies_count) avg_count,
from (select top 25% * from posts order by time desc) last_posts

这是针对每个主题帖的最后25％的另一个内容：

select
  avg(1.0 * smilies_count) avg_smilies
from (
  select
    thread_id, post_id, smilies_count,
    row_number() over (partition by thread_id order_by time desc) row_num
  from posts
) p
join (select thread_id, count(*) cnt from posts group by thread_id) c on
  p.thread_id = c.thread_id
where
  p.row_num < 0.25 * c.cnt
group by
  p.thread_id

- pkuderov

---

我认为您错过了问题中隐含的要求，即获取每个主题的最后25％帖子（从示例查询中的“按主题ID分组”）。 - pkmiec

@Dooh 不确定它是否隐含在“group by”子句中，但没问题 :) - pkuderov

@pkuderov，您能解释一下last_post是什么吗？ - Wolfpack'08

@Wolfpack'08，这是所有帖子中的最后25%。 - pkuderov