php - Mysql Percentage According to Group

Question

There is a table like a bottom.

+-------+---------+----------+-------+
| type  | ip      | line     | count |
+-------+---------+----------+-------+
| x     | 1       | EN       | 10    |
| x     | 1       | FR       | 10    |
| x     | 2       | EN       | 5     |
| x     | 5       | EN       | 5     |
| y     | 3       | CH       | 10    |
| y     | 3       | PT       | 10    |
| y     | 4       | PT       | 10    |
| y     | 6       | EN       | 10    |
+-------+---------+----------+-------+

I am trying to calculate percentage of lines according to ip's which inside types. Actually count don't use for calculate the percentage, i only looking number of specific line and divide it total number of line. So, i desire to see result like this, i am giving example beside the table.

+-------+---------+----------+
| line  | type    | percent  |
+-------+---------+----------+
| EN    | x       | %100     |--> There are 3 EN and 3 ip inside the X, So 3/3=%100
| FR    | x       | %33      |
| CH    | y       | %33      |
| PT    | y       | %66      |
| EN    | y       | %33      |
+-------+---------+----------+

So, i try but i couldn't reach that resul in sql.

标签： phpmysqlgroup-bypercentage

Answer 1

考虑以下...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,line CHAR(2) NOT NULL
,type CHAR(1) NOT NULL
,ip INT NOT NULL
);

INSERT INTO my_table (line,type,ip) VALUES
('EN','x',1),
('FR','x',1),
('EN','x',2),
('EN','x',5),
('CH','y',3),
('PT','y',3),
('PT','y',4),
('EN','y',6);

SELECT line, type, COUNT(*) line_count FROM my_table GROUP BY line,type;
+------+------+------------+
| line | type | line_count |
+------+------+------------+
| CH   | y    |          1 |
| EN   | x    |          3 |
| EN   | y    |          1 |
| FR   | x    |          1 |
| PT   | y    |          2 |
+------+------+------------+

SELECT type, COUNT(DISTINCT ip) ip_count FROM my_table GROUP BY type;
+------+----------+
| type | ip_count |
+------+----------+
| x    |        3 |
| y    |        3 |
+------+----------+

然后结合以上...

SELECT a.line
     , a.type 
     , a.line_count/b.ip_count * 100 pct
  FROM
     ( SELECT line, type, COUNT(*) line_count FROM my_table GROUP BY line,type) a
  JOIN
     ( SELECT type, COUNT(DISTINCT ip) ip_count FROM my_table GROUP BY type) b
    ON b.type = a.type;
    
+------+------+----------+
| line | type | pct      |
+------+------+----------+
| CH   | y    |  33.3333 |
| EN   | x    | 100.0000 |
| EN   | y    |  33.3333 |
| FR   | x    |  33.3333 |
| PT   | y    |  66.6667 |
+------+------+----------+