加入多对多关系

Question

我有三个数据表：应用程序（applications）、权限（permissions）和应用程序权限（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')

我该怎么做？ 我已经尝试了几个小时（查询和混合连接），但我还是不明白 :/

- Tie-fighter

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

这包括没有任何附加应用程序的权限（LEFT JOIN）。

如果有可能存在未附加到任何权限的应用程序，则列表总和将不会达到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;

- Erwin Brandstetter

---

@Tie-fighter：如果你喜欢子查询，你一定会喜欢CTE。 :) 我在我的答案中添加了一个变体。 - Erwin Brandstetter

有没有一种简单的方法来进行额外的计算，比如排除那些未达到某个值阈值或差异阈值的行？（我尝试了HAVING，但它并没有奏效。你是用HAVING来做这个的吗？） - Tie-fighter

@Tie-fighter：听起来需要用到WHERE子句。如果这不起作用，请发一个新问题。 - Erwin Brandstetter

我突然有了一个想法，我会试一试 :) - Tie-fighter

小问题。这是一个派生表（Oracle中的内联视图），而不是子查询。在SQL中，向量子查询可以放置在允许多行的任何位置；标量子查询可以放置在允许单行的任何位置。两者都是单列。 - PerformanceDBA

Answer 2

使用 LEFT OUTER JOIN:

SELECT * FROM permissions LEFT OUTER JOIN
applications_permissions as rel on permissions.id = rel.permission_id LEFT OUTER JOIN
applications on rel.application_id = applications.id

- Ant P

---

如何在代码中加入 price = '0' 的条件？ - Tie-fighter

如果没有应用程序，那么应用程序的价格就不能为零，对吧？在这种情况下，不返回这些权限是正确的行为。或者，使用 WHERE price = 0 OR applications.id is null。 - Ant P

对吧？ 是的。 WHERE price = 0 OR applications.id is null 对于应该为0的权限返回了1！？

- Tie-fighter

Answer 3

这个有效吗？

对于“免费”情况：

SELECT p.id, (100::float * COUNT(p.id)/(SELECT COUNT(*) from Applications)) Percent
FROM Applications a, Permissions p, Applications_Permissions a_p
WHERE a.id = a_p.application_id AND p.id = a_p.permission_id AND a.price = 0
GROUP BY p.id
ORDER BY Percent DESC

- Aayush Kumar

---

它虽然可以工作，但每个许可证都返回100;那不是正确的。 - Tie-fighter

现在数值似乎是正确的。 附注：它只返回了213个权限中的178个。 - Tie-fighter

Answer 4

以下是一次查询的结果：

SELECT p.id
, p.name
, (CASE WHEN total.free=0 THEN NULL ELSE 100::float * sub.free::float / total.free::float END) AS percent_free
, (CASE WHEN total.comm=0 THEN NULL ELSE 100::float * sub.comm::float / total.comm::float END) AS percent_comm
FROM permissions AS p
LEFT JOIN (
  SELECT permission_id
  , SUM(CASE WHEN a.price<=0 THEN 1 ELSE 0 END) AS free
  , SUM(CASE WHEN a.price>0  THEN 1 ELSE 0 END) AS comm
  FROM applications_permissions AS pa
  JOIN applications AS a ON (pa.application_id=a.id)
  GROUP BY permission_id
) AS sub ON (p.id=sub.permission_id)
, (
  SELECT
    SUM(CASE WHEN price<=0 THEN 1 ELSE 0 END) AS free
  , SUM(CASE WHEN price>0  THEN 1 ELSE 0 END) AS comm
  FROM applications
) AS total

或者只返回免费应用程序的结果（通过更改 where 子句分别返回商业应用程序）：

SELECT p.id
, p.name
, (CASE WHEN total.nbr=0 THEN NULL ELSE 100::float * sub.nbr::float / total.nbr::float END) AS percent
FROM permissions AS p
LEFT JOIN (
  SELECT permission_id, COUNT(*) AS nbr
  FROM applications_permissions AS pa
  JOIN applications AS a ON (pa.application_id=a.id)
  WHERE (a.price<=0)
  GROUP BY permission_id
) AS sub ON (p.id=sub.permission_id)
, (
  SELECT COUNT(*) AS nbr
  FROM applications
  WHERE (price<=0)
) AS total

- Skrol29

---

错误：表“a”的FROM子句条目丢失 第16行：SUM(CASE WHEN a.price<=0 THEN 1 ELSE 0 END) AS free - Tie-fighter