Geek asks and answers

Many-to-Many Relationships

I have three tables: applications, permissions and application_permissions

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

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

Now I would like to find out for each permission how many percent of the total number of applications refer to it; And this is for both categories

Free:

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

Commercial:

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

I developed the following query, but it does not include rights identifiers without an 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')

How should I do it? I tried for several hours already (this request, different JOINs), but it eludes me: /

+3
source share
4 answers

Simplified. The first project was optimal.
To calculate everything 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 ( - ) - 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;
+4

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
+3

It works?

For case free:

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
+1
source

Here is the result in one query:

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

Or the result is only for free applications (respectively, commercial applications by changing the where clause):

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
+1
source

More articles:


All Articles