mysql - 查找从提交申请到创建订单的平均时间

Question

我有两张 table requisition_headers和order_headers 。我有兴趣查找从提交申请的时间 ( requisition_headers.submitted_at ) 到创建订单的时间 ( orders.headers_created_at ) 所花费的平均时间，其中 requisition_headers.status <> 'draft' .

我希望结果如下:

Avg_Req_To_PO_Cycle_Time = 3.2 Days

我有以下脚本，但它不起作用:

SELECT Database() as Customer,
       AVG(timestampdiff(requisition_headers.submitted_at,order_headers.created_at)) AS REQ_PO_Cycle_Time
  FROM order_headers 
  LEFT JOIN requisition_headers ON order_headers.requisition_header_id = requisition_headers.id
 WHERE requisition_headers.status <> 'draft'

有什么想法吗？

--更新--

我将查询更改为以下内容，现在得到了 229491.71 的响应，我的问题是 - 是天、小时、分钟、秒吗？

SELECT DATABASE() AS CUSTOMER,
AVG(TIME_TO_SEC(TIMEDIFF(order_headers.created_at,requisition_headers.submitted_at))) as Cycle_time
FROM order_headers LEFT JOIN requisition_headers ON order_headers.requisition_header_id = requisition_headers.id

其中 requisition_headers.status <> '草稿'

Answer 1

确保您知道系统函数在使用时返回什么。 TimestampDiff 函数以您在第一个参数中指定的单位返回两个日期之间的差值。您没有指定该单位，所以我不知道您会得到什么。我收到编译错误。

在第二次尝试中，您使用 TimeDiff 返回一个间隔值，然后将 Avg 的结果转换为秒。因此，如果您想要小数天的结果，只需除以一天的秒数即可。

在获取日期时，您还可以使用左连接。起初我以为您想要获取所有请购单，无论订单是否已创建。但是您以错误的顺序加入表格。但是，假设这是您的意图，如果尚未创建订单，您将把 NULL 作为参数之一。您将得到 NULL 作为答案，因此您什么也得不到。如果您想使用左连接，那么您应该为任何缺失的创建日期指定一个替代日期——也就是说，在以正确的顺序获取表格之后。

这里有两个选项。忽略尚未使用常规内连接创建的订单。另一个包括那些但替换当前日期和时间。

通过询问日期之间的分钟数，除以一天中的分钟数即可找到以天为单位的最终答案。

SQLFiddle

SELECT  Customer,
        AVG( timestampdiff( minute, r.submitted_at,
             o.created_at)) / (24 * 60 )AS REQ_PO_Cycle_Time
FROM    requisition_headers r
JOIN    order_headers o
    ON  o.requisition_header_id = r.id
WHERE   r.status <> 'draft'
group by Customer;

SELECT  Customer,
        AVG( timestampdiff( minute, r.submitted_at,
             IfNull( o.created_at, CurDate()))) / (24 * 60 )AS REQ_PO_Cycle_Time
FROM    requisition_headers r
LEFT JOIN order_headers o
    ON  o.requisition_header_id = r.id
WHERE   r.status <> 'draft'
group by Customer;