加入多对多关系 [英] Joining many-to-many relationships

问题描述

我有三个表:applications、permissions和applications_permissions

I have three tables: applications, permissions and applications_permissions

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

对于应用程序，有两类:免费和商业应用程序(价格 = '0' 和价格 != '0')

For applications there are two categories: free and commercial ones (price = '0' and price != '0')

现在我想知道每个权限有多少应用程序引用它；这两个类别

Now I would like to know for every permission how many percent of total applications reference it; And this for both category

免费:

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

商业:

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

我已经计算出以下查询，但它不包括没有应用程序的权限 ID:/

I have worked out the following query, but it does not include permission ids with no application :/

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

我该怎么做?我已经尝试了几个小时(那个查询，misc JOINs)，但它让我望而却步:/

How do I do this? I've been trying for a few hours now (that query, misc JOINs) but it eludes me :/

推荐答案

^{简化.初稿非常理想.}
在一个查询中计算所有:

^{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'.

Assuming that your price is actually a numeric column - so 0 instead of '0'.

这包括根本没有附加应用程序的权限(LEFT JOIN).

This includes permissions that have no attached applications at all (LEFT JOIN).

如果可以存在未附加到任何权限的应用程序，则列表相加不会达到 100%.

If there can be applications that aren't attached to any permissions the lists will not add up to 100 %.

我做一次总计数 (ct) 并在子查询中将其转换为 float.剩下的计算可以用整数算法来完成，只有最后的/ct将数字转换为浮点数.这是最快和最精确的.

I do the total count (ct) once and cast it to float in a subquery. The rest of the calculation can be done with integer arithmetic, only the final / ct converts the number to a floating point number. This is fastest and most precise.

如果您对更多新事物持开放态度:尝试使用 CTE(通用表表达式 - WITH 查询) - 自 PostgreSQL 8.4 起可用.
它更干净，可能会稍微快一点，因为我在一个 CTE 中完成了两个计数并且有一个更便宜的 GROUP BY - 这两个都可以用子查询来完成:

If your are open to yet more new stuff: Try the same with CTEs (Common Table Expressions - WITH queries) - available since PostgreSQL 8.4.
It's cleaner and probably slightly faster, because I do both counts in one CTE and have a cheaper GROUP BY - both of which could be done with subqueries just as well:

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;

这篇关于加入多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！