postgresql - 总结许多产品的评级

Question

我有一个表 project_product 和一个表 project_consummation。人们消费产品，可能会给产品打 1 到 10 分。

请允许我使用一些 ASCII 艺术来说明:

+--------------------------+     +-------------------------+
| project_product          |     | project_consummation    |
|--------------------------|     |-------------------------|
| id   integer primary key |-\   | id integer primary key  |
| name varchar             |  \->| product_id integer      |
| ...                      |     | rating     integer      |
| various other fields...  |     | user_id    integer      |
+--------------------------+     | ...                     |
                                 | various other fields... |
                                 +-------------------------+

现在我想要一个产品投票的概览。当然可以有没有 rating 值(例如 NULL)的 consummation，所以这些必须被忽略。

结果应该是这样的(从 1 到 10 的每个评分都应该有自己的列，指示给产品这个评分的人数，以及评分总数 num_ratings 和也许稍后一些中位数、标准差等):

 product_id | rating1 | rating2 | ... |rating10 | num_ratings 
------------+---------+---------+-----+---------+-------------
       1002 |         |         | ... |       1 |           1
       1014 |       4 |         | ... |       2 |           6
       1015 |       2 |       1 | ... |       1 |           4

我创建了一个非常笨拙的“解决方案”，因为我为每个评级列都做了一个LEFT OUTER JOIN(我只会显示前 3 列，但我确定你会看到这变得一团糟):

SELECT p.id AS product_id,
       rating1, rating2, rating3,
       COALESCE(rating1, 0) + COALESCE(rating2, 0) + COALESCE(rating3, 0) AS num_ratings
  FROM project_product p
  LEFT OUTER JOIN
       ( SELECT product_id,
                count(*) AS rating1
           FROM project_consummation c
          WHERE rating = 1 
          GROUP BY product_id
       ) c1 ON p.id = c1.product_id
  LEFT OUTER JOIN
       ( SELECT product_id,
                count(*) AS rating2
           FROM project_consummation c
          WHERE rating = 2 
          GROUP BY product_id
       ) c2 ON p.id = c2.product_id
  LEFT OUTER JOIN
       ( SELECT product_id,
                count(*) AS rating3
           FROM project_consummation c
          WHERE rating = 3 
          GROUP BY product_id
       ) c3 ON p.id = c3.product_id

关于更好的代码，尤其是更好的性能，什么是更好的解决方案？

Answer 1

这样做:

select
        p.id product_id,
        count(case when c.rating = 1 then 1 else null end) rating1,
        count(case when c.rating = 2 then 1 else null end) rating2,
        count(case when c.rating = 3 then 1 else null end) rating3,
        count(case when c.rating = 4 then 1 else null end) rating4,
        count(case when c.rating = 5 then 1 else null end) rating5,
        count(case when c.rating = 6 then 1 else null end) rating6,
        count(case when c.rating = 7 then 1 else null end) rating7,
        count(case when c.rating = 8 then 1 else null end) rating8,
        count(case when c.rating = 9 then 1 else null end) rating9,
        count(case when c.rating = 10 then 1 else null end) rating10,
        count(c.rating) num_ratings
    from project_product p
    left join project_consummation c on c.product_id = p.id
        group by p.id
        order by p.id;

或更短的评分形式:

select
            p.id product_id,
            count(nullif(c.rating = 1, false)) rating1,
            count(nullif(c.rating = 2, false)) rating2,
            count(nullif(c.rating = 3, false)) rating3,
            count(nullif(c.rating = 4, false)) rating4,
            count(nullif(c.rating = 5, false)) rating5,
            count(nullif(c.rating = 6, false)) rating6,
            count(nullif(c.rating = 7, false)) rating7,
            count(nullif(c.rating = 8, false)) rating8,
            count(nullif(c.rating = 9, false)) rating9,
            count(nullif(c.rating = 10, false)) rating10,
            count(c.rating) num_ratings
        from project_product p
        left join project_consummation c on c.product_id = p.id
            group by p.id
            order by p.id;