加入多对多关系

我有三个表：applications、permissions 和 applications_permissions

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

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

现在我想知道对于每个权限，总应用程序中有多少百分比引用它；这两个类别

Free:

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')

我该怎么做呢？ 我现在已经尝试了几个小时（该查询，杂项连接），但它让我困惑：/

^{Simplified. First draft was sup-optimal.}
To compute all in one query:

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（通用表表达式 - 带查询） http://www.postgresql.org/docs/current/interactive/queries-with.html- 自 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;