我有三个表: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;更多推荐
加入多对多关系
发布评论