如何找出哪些帖子的评论最高，哪些帖子的评论最少？

Question

我对postgreSQl、SQL和数据库非常陌生，我希望你们能在这方面帮助我，我想知道哪些帖子的评论最多，哪些评论最少，用户也需要指定。

CREATE SCHEMA perf_demo;
SET search_path TO perf_demo;

-- Tables
CREATE TABLE users(
  id     SERIAL       -- PRIMARY KEY
, email  VARCHAR(40)  NOT NULL  UNIQUE
);

CREATE TABLE posts(
  id       SERIAL        -- PRIMARY KEY
, user_id  INTEGER       NOT NULL  -- REFERENCES users(id)
, title    VARCHAR(100)  NOT NULL  UNIQUE
);

CREATE TABLE comments(
  id       SERIAL        -- PRIMARY KEY
, user_id  INTEGER       NOT NULL  -- REFERENCES users(id)
, post_id  INTEGER       NOT NULL  -- REFERENCES posts(id)
, body     VARCHAR(500)  NOT NULL
);

-- Generate approx. N users
-- Note: NULL values might lead to lesser rows than N value.
INSERT INTO users(email)
WITH query AS (
    SELECT 'user_' || seq || '@' 
        || ( CASE (random() * 5)::INT
                WHEN 0 THEN 'my'
                WHEN 1 THEN 'your'
                WHEN 2 THEN 'his'
                WHEN 3 THEN 'her'
                WHEN 4 THEN 'our'
             END ) 
        || '.mail' AS email
    FROM generate_series(1, 5) seq              -- Important: Replace N with a useful value
)
SELECT email 
FROM query 
WHERE email IS NOT NULL;

-- Generate N posts
INSERT INTO posts(user_id, title)
WITH expanded AS (
    SELECT random(), seq, u.id AS user_id
    FROM generate_series(1, 8) seq, users u     -- Important: Replace N with a useful value
), 
shuffled AS (
    SELECT e.*
    FROM expanded e
    INNER JOIN (
        SELECT ei.seq, min(ei.random) FROM expanded ei GROUP BY ei.seq
    ) em ON (e.seq = em.seq AND e.random = em.min)
    ORDER BY e.seq
)
-- Top 20 programming languages: https://www.tiobe.com/tiobe-index/
SELECT s.user_id,
    'Let''s talk about (' || s.seq || ') '
    || ( CASE (random() * 19 + 1)::INT
            WHEN 1 THEN 'C'
            WHEN 2 THEN 'Python'
            WHEN 3 THEN 'Java'
            WHEN 4 THEN 'C++'
            WHEN 5 THEN 'C#'
            WHEN 6 THEN 'Visual Basic'
            WHEN 7 THEN 'JavaScript'
            WHEN 8 THEN 'Assembly language'
            WHEN 9 THEN 'PHP'
            WHEN 10 THEN 'SQL'
            WHEN 11 THEN 'Ruby'
            WHEN 12 THEN 'Classic Visual Basic'
            WHEN 13 THEN 'R'
            WHEN 14 THEN 'Groovy'
            WHEN 15 THEN 'MATLAB'
            WHEN 16 THEN 'Go'
            WHEN 17 THEN 'Delphi/Object Pascal'
            WHEN 18 THEN 'Swift'
            WHEN 19 THEN 'Perl'
            WHEN 20 THEN 'Fortran'
        END ) AS title
FROM shuffled s;

-- Generate N comments
-- Note: The cross-join is a performance killer. 
--       Try the SELECT without INSERT with small N values to get an estimation of the execution time.
--       With these values you can extrapolate the execution time for a bigger N value.
INSERT INTO comments(user_id, post_id, body)
WITH expanded AS (
    SELECT random(), seq, u.id AS user_id, p.id AS post_id
    FROM generate_series(1, 10) seq, users u, posts p   -- Important: Replace N with a useful value
), 
shuffled AS (
    SELECT e.*
    FROM expanded e
        INNER JOIN ( SELECT ei.seq, min(ei.random) FROM expanded ei GROUP BY ei.seq ) em ON (e.seq = em.seq AND e.random = em.min)
    ORDER BY e.seq
)
SELECT s.user_id, s.post_id, 'Here some comment: ' || md5(random()::text) AS body
FROM shuffled s;

如果有人能告诉我如何做到这一点请，我是新的SQL/postgres，任何帮助将不胜感激。举个例子也会很有帮助。

Answer 1

很好地粘贴整个dataset创建过程，这是它需要包含的东西，以使示例可复制。

让我们首先从如何join几个表开始:您有包含user_id的posts表，我们可以使用它与users连接，如下所示。

SELECT email, 
    users.id user_id, 
    posts.id post_id, 
    title
from posts join users 
    on posts.user_id=users.id;

这将列出与作者一起发表的文章。检查连接条件(在ON之后)，说明我们正在使用的字段。结果应如下所示：

      email       | user_id | post_id |                 title                  
------------------+---------+---------+----------------------------------------
 user_1@her.mail  |       1 |       5 | Let's talk about (5) Visual Basic
 user_1@her.mail  |       1 |       2 | Let's talk about (2) Assembly language
 user_3@her.mail  |       3 |       8 | Let's talk about (8) R
 user_3@her.mail  |       3 |       7 | Let's talk about (7) Perl
 user_4@her.mail  |       4 |       6 | Let's talk about (6) Visual Basic
 user_5@your.mail |       5 |       4 | Let's talk about (4) R
 user_5@your.mail |       5 |       3 | Let's talk about (3) C
 user_5@your.mail |       5 |       1 | Let's talk about (1) Ruby
(8 rows)

现在是使用comments表加入这个结果的时候了。由于post可以有注释，而且您希望显示所有的帖子，即使您没有注释，所以您应该使用LEFT OUTER JOIN (关于连接类型这里的更多信息)。

因此，让我们重写上面的内容，以包括comments

SELECT email, 
    users.id user_id, 
    posts.id post_id, 
    title,
    comments.body
from posts 
join users 
    on posts.user_id=users.id
left outer join comments
    on posts.id = comments.post_id
    ;

查看posts和基于post_id的comments之间的连接。

查询的结果是帖子、相关作者和评论的列表，如下所示

      email       | user_id | post_id |                 title                  |                        body                         
------------------+---------+---------+----------------------------------------+-----------------------------------------------------
 user_1@her.mail  |       1 |       5 | Let's talk about (5) Visual Basic      | 
 user_1@her.mail  |       1 |       2 | Let's talk about (2) Assembly language | 
 user_3@her.mail  |       3 |       8 | Let's talk about (8) R                 | Here some comment: 200bb07acfbac893aed60e018b47b92b
 user_3@her.mail  |       3 |       8 | Let's talk about (8) R                 | Here some comment: 66159adaed11404b1c88ca23b6a689ef
 user_3@her.mail  |       3 |       8 | Let's talk about (8) R                 | Here some comment: e5cc1f7c10bb6103053bf281d3cadb60
 user_3@her.mail  |       3 |       8 | Let's talk about (8) R                 | Here some comment: 5ae8674c2ef819af0b1a93398efd9418
 user_3@her.mail  |       3 |       7 | Let's talk about (7) Perl              | Here some comment: 5b818da691c1570dcf732ed8f6b718b3
 user_3@her.mail  |       3 |       7 | Let's talk about (7) Perl              | Here some comment: 88a990e9495841f8ed628cdce576a766
 user_4@her.mail  |       4 |       6 | Let's talk about (6) Visual Basic      | 
 user_5@your.mail |       5 |       4 | Let's talk about (4) R                 | Here some comment: ed19bb476eb220d6618e224a0ac2910d
 user_5@your.mail |       5 |       3 | Let's talk about (3) C                 | Here some comment: 23cd43836a44aeba47ad212985f210a7
 user_5@your.mail |       5 |       1 | Let's talk about (1) Ruby              | Here some comment: b83999120bd2bb09d71aa0c6c83a05dd
 user_5@your.mail |       5 |       1 | Let's talk about (1) Ruby              | Here some comment: b4895f4e0aa0e0106b5d3834af80275e
(13 rows)

现在，您可以开始对某个帖子的评论进行聚合和计数。您可以使用PG的聚合函数，我们在这里使用COUNT。

SELECT email, 
    users.id user_id, 
    posts.id post_id, 
    title,
    count(comments.id) nr_comments
from posts 
join users 
    on posts.user_id=users.id
left outer join comments
    on posts.id = comments.post_id
group by email, 
    users.id, 
    posts.id, 
    title
    ;

检查一下，我们正在计算comments.id字段，但是我们也可以执行一个count(*)，它只对行进行计数。还请检查是否将结果分组为email、users.id、post.id和title，这是我们在计数旁边显示的列。

结果应该类似于

      email       | user_id | post_id |                 title                  | nr_comments 
------------------+---------+---------+----------------------------------------+-------------
 user_3@her.mail  |       3 |       7 | Let's talk about (7) Perl              |           2
 user_5@your.mail |       5 |       3 | Let's talk about (3) C                 |           1
 user_5@your.mail |       5 |       1 | Let's talk about (1) Ruby              |           2
 user_3@her.mail  |       3 |       8 | Let's talk about (8) R                 |           4
 user_1@her.mail  |       1 |       5 | Let's talk about (5) Visual Basic      |           0
 user_5@your.mail |       5 |       4 | Let's talk about (4) R                 |           1
 user_4@her.mail  |       4 |       6 | Let's talk about (6) Visual Basic      |           0
 user_1@her.mail  |       1 |       2 | Let's talk about (2) Assembly language |           0
(8 rows)

这应该是你要找的结果。请记住，你展示的是来自users的用户，他写了这篇文章，而不是评论的人。要查看谁发表了评论，您需要更改连接条件。