mysql - SQL 查找两个计数的百分比

Question

我正在查看一个包含订单信息的数据库。我正在尝试确定从订购到发货超过 14 天的订单的百分比。我很难创建每月 % 的列。现在我有

 Select ol.orderid, ol.productid, Month(ol.shipdate) as monthShipped,
       Year(ol.shipdate)as yearShipped, 
       (Select Count(*) From orderline ol Where datediff(ol.shipdate,o.orderdate)> 14 Group By Month(ol.shipdate), Year(ol.shipdate))/Count(*) as percentage
 From orderline ol
 Join orders o 
    on ol.orderid = o.orderid
 Group By monthShipped, yearShipped
 Order By yearShipped, monthShipped 

我收到一条错误消息，指出我的子查询返回多于一行。我不想要多一行吗？如果我在子查询中删除了 Group By，那么我会得到值，但它远不正确。

Answer 1

第一个问题是发货日期适用于每个订单行。让我假设您指的是每个订单的最新发货日期。以下代码在订单级别获取此信息:

 Select ol.orderid, max(ol.shipdate) as lastshipdate
 From orderline ol
 Group By ol.orderid;

要获得差价，您需要订单:

 Select ol.orderid, datediff(o.orderdate, max(ol.shipdate) ) as diff
 From orders o join
      orderline ol
      on o.orderid = ol.orderid
 Group By ol.orderid;

最后，获取按月的数量和百分比:

select year(orderdate), month(orderdate),
       count(*) as numorders, sum(diff > 14) as numdelayed, avg(diff > 14) as p_dealyed
from (Select ol.orderid, datediff(o.orderdate, max(ol.shipdate) ) as diff, o.orderdate
      From orders o join
           orderline ol
           on o.orderid = ol.orderid
      Group By ol.orderid
     ) o
group by year(orderdate), month(orderdate)
order by 1, 2;