加入多对多关系

Question

我有三个表：应用程序，权限和applications_permissions

|------------|   |------------------------|   |-----------|
|applications|   |applications_permissions|   |permissions|
|------------|   |------------------------|   |-----------|
| id         | <-| application_id         |   | id        |
| price      |   | permission_id          |-> | name      |
|------------|   |------------------------|   |-----------|

对于应用程序，有两类：免费和商业类（价格=‘0’和价格！=‘0’）

现在，我想为每个许可都知道有多少百分比的应用程序引用了它。而这两个类别

自由：

id, percentage
1 , 20.0230
2 ,  0.0000
3 ,  0.0312
...

商业的：

id, percentage
1 , 18.0460
2 ,  0.0000
3 ,  0.0402
...

我已经计算出以下查询，但其中不包含没有应用程序的权限ID：/

SELECT (SELECT name FROM permissions WHERE id = applications_permissions.permission_id) AS "name",
        100::float * COUNT(*)/(SELECT COUNT(name) FROM applications WHERE price = \'0\') AS "percent"
  FROM applications, applications_permissions
  WHERE applications.id = applications_permissions.application_id 
    AND applications.price = \'0\'
  GROUP BY applications_permissions.permission_id
  ORDER BY percent DESC')

我该怎么做呢？我已经尝试了几个小时了（该查询，其他联接），但这使我难以捉摸：/

Answer 1

简化了。初稿是最优的。
要在一个查询中计算所有内容：

SELECT p.id
     ,(100 * sum((a.price > 0)::int)) / cc.ct AS commercial
     ,(100 * sum((a.price = 0)::int)) / cf.ct AS free
FROM  (SELECT count(*)::float AS ct FROM applications WHERE price > 0) AS cc
     ,(SELECT count(*)::float AS ct FROM applications WHERE price = 0) AS cf
      ,permissions p
LEFT   JOIN applications_permissions ap ON ap.permission_id = p.id
LEFT   JOIN applications a ON a.id = ap.application_id
GROUP  BY 1, cc.ct, cf.ct
ORDER  BY 2 DESC, 3 DESC, 1;

假设您的价格实际上是一个数字列-0而不是'0'。

这包括permissions完全没有附加applications的（LEFT JOIN）。

如果可能applications有未附加permissions的列表，则这些列表的总和不会达到100％。

我只进行ct一次总计数（），并将其转换float为子查询中的值。其余的计算可以使用整数算术完成，只有最后一个/ ct将数字转换为浮点数。这是最快，最精确的。

与CTE相同

如果您愿意接受更多新东西：尝试使用CTE（公用表表达式-
WITH查询）

-从PostgreSQL 8.4开始可用。
它更干净，而且可能稍微快一点，因为我都在一个CTE中工作，而且价格便宜GROUP BY-两者都可以通过子查询来完成：

WITH  c AS (
    SELECT sum((a.price > 0)::int) AS cc
          ,sum((a.price = 0)::int) AS cf
    FROM   applications
    ), p AS (
    SELECT id
          ,sum((a.price > 0)::int) AS pc
          ,sum((a.price = 0)::int) AS pf
    FROM   permissions p
    LEFT   JOIN applications_permissions ap ON ap.permission_id = p.id
    LEFT   JOIN applications a ON a.id = ap.application_id
    GROUP  BY 1
    )
SELECT p.id
     ,(100 * pc) / cc::float AS commercial
     ,(100 * pf) / cf::float AS free
FROM   c, p
ORDER  BY 2 DESC, 3 DESC, 1;