查找在mysql中返回的订单百分比

Question

我试着找出SKU订单中退货的百分比。到目前为止，我的情况如下：

我的桌子是订单和RMA

订单表：

create table Orders (OrderID int not null, CustomerID int, SKU VARCHAR(20), Description VARCHAR(50), primary key (OrderID), foreign key (CustomerID) references Customers(CustomerID));

查询确定，0行受影响(0.06秒)

    +-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| OrderID     | int(11)     | NO   | PRI | NULL    |       |
| CustomerID  | int(11)     | YES  | MUL | NULL    |       |
| SKU         | varchar(20) | YES  |     | NULL    |       |
| Description | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

RMA表：

create table RMA (RMAID int not null, OrderID int, Step VARCHAR(50), Status VARCHAR(15), Reason VARCHAR(15), primary key (RMAID), foreign key (OrderID) references Orders(OrderID));

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| RMAID   | int(11)     | NO   | PRI | NULL    |       |
| OrderID | int(11)     | YES  | MUL | NULL    |       |
| Step    | varchar(50) | YES  |     | NULL    |       |
| Status  | varchar(15) | YES  |     | NULL    |       |
| Reason  | varchar(15) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 

mysql> select SKU, Description, count(SKU) as Returned,
    -> (COUNT(Orders.SKU)* 100 / (SELECT COUNT(*) FROM Orders)) AS Percent
    -> from Orders join RMA using (OrderID)
    -> group by SKU order by Percent desc;

我还运行了一个查询，查找已订购的SKU %

mysql> select SKU, Description, count(SKU) as Ordered,
    -> (COUNT(Orders.SKU)* 100 / (SELECT COUNT(*) FROM Orders)) AS Percent
    -> From Orders Group by SKU order by Percent desc;

+------------+----------------------------------------------------+---------+---------+
| SKU        | Description                                        | Ordered | Percent |
+------------+----------------------------------------------------+---------+---------+
| BAS-48-1 C | Basic Switch 10/100/1000 BaseT 48 port             |    8385 | 22.0670 |
| ENT-48-40F | Enterprise Switch 40GigE SFP+ 48 port              |    6186 | 16.2798 |
| ENT-48-10F | Enterprise Switch 10GigE SFP+ 48 port              |    4329 | 11.3927 |
| BAS-08-1 C | Basic Switch  10/100/1000 BaseT 8 port             |    4285 | 11.2769 |
| ENT-24-10F | Enterprise Switch 10GigE SFP+ 24 Port              |    4275 | 11.2506 |
| ADV-24-10C | Advanced Switch 10GigE Copper 24 port              |    4178 | 10.9953 |
| ADV-48-10F | Advanced Switch 10 GigE Copper/Fiber 44 port coppe |    4174 | 10.9848 |
| ENT-24-40F | Enterprise Switch 40GigE SFP+ 24 port              |    2152 |  5.6635 |
| BAS-24-1 C | Basic Switch 10/100/1000 BaseT 24 port             |      34 |  0.0895 |
+------------+----------------------------------------------------+---------+---------+
9 rows in set (0.00 sec)

这些数据给出了SKU返回的百分比和按SKU订购的百分比，但我想显示的是，按SKU顺序返回的百分比按百分比下降。

我想要的结果是：

SKU        | Ordered  | Returned | Percent_Returned
BAS-48-1 C    8385       8282          99

我以为我拿到了。下面的内容正确地显示了返回的列，但我不知道为什么有序列会增加。

 mysql> SELECT SKU,
    -> count(SKU) AS Ordered, count(RMA.OrderID) AS Returned,
    -> round(100*count(RMA.OrderID) / count(*)) AS Percent_Returned
    -> FROM Orders
    -> LEFT JOIN RMA USING (OrderID)
    -> GROUP BY SKU
    -> ORDER BY Percent_Returned DESC;
+------------+---------+----------+------------------+
| SKU        | Ordered | Returned | Percent_Returned |
+------------+---------+----------+------------------+
| ENT-48-10F |    4346 |     4287 |               99 |
| BAS-08-1 C |    4308 |     4248 |               99 |
| BAS-48-1 C |    8422 |     8282 |               98 |
| ENT-24-10F |    4296 |     4231 |               98 |
| ENT-24-40F |    2159 |     2121 |               98 |
| ENT-48-40F |    6212 |     6118 |               98 |
| ADV-24-10C |    4189 |     4122 |               98 |
| ADV-48-10F |    4198 |     4124 |               98 |
| BAS-24-1 C |      34 |       33 |               97 |
+------------+---------+----------+------------------+
9 rows in set (0.06 sec)

Answer 1

如果我正确地理解了这个问题，下面的查询应该会产生所需的输出：

SELECT SKU, 
       count(*) AS Ordered, count(DISTINCT RMA.OrderID) AS Returned,
       round(100*count(DISTINCT RMA.OrderID) / count(*)) AS Percent_Returned     
  FROM Orders 
       LEFT JOIN RMA USING (OrderID) 
GROUP BY SKU
ORDER BY Percent_Returned DESC;

使用CTE更容易读懂：

WITH totals AS (
    SELECT SKU, count(*) AS Ordered, count(DISTINCT RMA.OrderID) AS Returned
    FROM Orders LEFT JOIN RMA USING (OrderID) 
    GROUP BY SKU
)
SELECT SKU, Ordered, Returned, round(100*Returned / Ordered) AS Percent_Returned
  FROM totals
 ORDER BY Percent_Returned;