sql - 如何从查询中选择最后 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()

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 子句以更好的方式编写。