时间戳

首页 > 解决方案 > 如何计算已批准订单的美元金额占所下订单总美元金额的百分比？

mysql - 如何计算已批准订单的美元金额占所下订单总美元金额的百分比？

问题描述

我有两个表，订单和记录如下。

Orders 表有三列：

order_id     seller     order_price
  123         abc           50 
  456         abc           75
  789         def           60

Records 表也有三列，其中记录了对订单所做的任何更改：

order_id       log_made_at          record
  123       2018-08-05 11:00:00     order approved
  123       2018-08-05 12:00:00     shipping city updated
  123       2018-08-05 12:00:10     order declined
  456       2018-08-05 12:10:00     order approved
  789       2018-08-05 12:20:10     order declined

“记录”列存储字符串值，例如“订单已批准”、“订单被拒绝”、“发货城市更新”等。“log_made_at”列存储创建记录的时间戳。

如果我想计算每个卖家的已批准订单的美元金额占已下订单的总美元金额（已批准订单的美元金额）/（已下订单的总金额）的百分比，我应该使用什么查询？我很难隔离最后一条记录是“订单已批准”的订单，以将其价格用作已批准订单的美元金额。

我想要使​​用上面的示例数据的结果如下所示：

seller     approved_order_dollar_amount_percentage
  abc                   0.6
  def                   0.0

我如何得到上述计算：卖家 abc 有两个订单（123 和 456），每个订单的价格标签分别为 50 和 75，因此他的总订单价格为 125。但是，只有订单 456 的最终日志为“订单已批准”由于订单 123 最终被拒绝。所以卖家abc最终批准的订单金额百分比是75/125 = 0.6。

我尝试运行的查询是这个，但它一直给我错误的数字：

SELECT order_price
        FROM orders o
        INNER JOIN records r ON o.order_id = r.order_id
        WHERE r.log_made_at IN (
            SELECT MAX(log_made_at) 
            FROM records
            GROUP BY order_id)
        AND r.record = 'order approved'
    )/SUM(total_order_price) AS approval_rate_by_sum

标签： mysqlsqlsumaveragegreatest-n-per-group

解决方案

首先，对卖家批准的订单进行汇总，然后将结果作为表格计算批准订单占总订单的百分比。

select t.seller, 
      (t.orders_approved / (select sum(o.order_price) 
                            from orders o 
                            where o.seller=t.seller)
      ) approved_order_dollar_amount_percentage
from
  (select o.seller, sum(o.order_price) orders_approved
   from records r inner join orders on (o.order_id=r.order_id)
   where
    r.record = 'order approved'
    and
    r.log_mad_at = (
       select max(r2.log_mad_at)
       from records r2
       where 
        r2.order_id=r.order_id
      )
   group by o.seller
  ) t

推荐阅读