PostgreSQL的限制柱至3％的值

Question

SELECT u.id, 
      'Shift' AS which, 
      se.created_at 
    FROM users AS u 
    JOIN schedule_elements AS se ON se.owner_id = u.id 
    UNION ALL 
      (SELECT u.id, 
         'Like' AS which, 
         ll.created_at 
      FROM users AS u 
      JOIN likes AS ll ON ll.owner_id = u.id 
      UNION 
      SELECT u.id, 
         'Comment' AS which, 
         cm.created_at 
      FROM users AS u 
      JOIN comments AS cm ON cm.owner_id = u.id) 
    ORDER BY ID DESC , created_at DESC 


The output looks like: 
id, which, created_at 
555, shift, <date> 
555, shift, <date> 
555, comment, <date> 
555, shift, <date> 
555, like, <date> 
333, shift, <date> 
333, shift, <date> 
333, comment, <date> 
333, shift, <date> 
111, like, <date> 
111, shift, <date> 
111, shift, <date> 

输出具有5个条目ID 555，和4 333我要过滤这个查询，使得我只有顶部3条目555，顶部3 333，等等。

Answer 1

使用限制条款https://www.postgresql.org/docs/current/static/queries-limit.html

(SELECT u.id,'Shift' AS which,se.created_at 
    FROM users AS u 
    JOIN schedule_elements AS se ON se.owner_id = u.id ORDER BY u.id DESC , u.created_at DESC LIMIT 3) 
    UNION 
    (SELECT u.id,'Like' AS which,ll.created_at 
    FROM users AS u 
    JOIN likes AS ll ON ll.owner_id = u.id ORDER BY u.id DESC , u.created_at DESC LIMIT 3) 
    UNION 
    (SELECT u.id,'Comment' AS which,cm.created_at 
    FROM users AS u 
    JOIN comments AS cm ON cm.owner_id = u.id ORDER BY u.id DESC , u.created_at DESC LIMIT 3) 

Answer 2

也许你需要的是这样的。对于CTE中的p，我使用您的查询。在p2中，我通过窗口函数为所有行设置了row_number，之后每个用户只能显示第一个/最后一个3。

WITH p AS (
    SELECT u.id, 
      'Shift' AS which, 
      se.created_at 
    FROM users AS u 
    JOIN schedule_elements AS se ON se.owner_id = u.id 
    UNION ALL 
    SELECT u.id, 
      'Like' AS which, 
      ll.created_at 
    FROM users AS u 
    JOIN likes AS ll ON ll.owner_id = u.id 
    UNION ALL 
    SELECT u.id, 
     'Comment' AS which, 
     cm.created_at 
    FROM users AS u 
    JOIN comments AS cm ON cm.owner_id = u.id 
), p2 AS (
    SELECT *, 
     row_number() OVER (PARTITION BY id ORDER BY created_at DESC) AS rank 
    FROM p 
    WHERE which IS NOT NULL 
) 
SELECT id, which, created_at --, rank 
FROM p2 
WHERE rank <= 3 
ORDER BY ID DESC , created_at DESC;