mysql - TIMESTAMPDIFF 用于不止一种类型

Question

我有这个查询来向我展示付款之间的平均时间差异

SELECT TIMESTAMPDIFF(SECOND, MIN(x.ts), MAX(x.ts) )/(COUNT(DISTINCT(x.ts)) -1) 
as average_minutes_between_paypal_orders 
from (
select p.created_at as ts 
from payment_tokens p 
where status='processed' and payment_method = 'paypal' 
order by created_at desc limit 100
) x

问题是，我希望它单独呈现其他方法(例如“braintree”、“其他”等)之间的平均时间差异，并且我不想编写多个查询。

有没有办法在 1 个查询中单独呈现所有 payment_methods？

Answer 1

使用CASE进行所需计算的一种方法

SELECT 
TIMESTAMPDIFF(SECOND
              ,MIN(CASE WHEN x.payment_method = 'paypal' THEN x.ts END)
              ,MAX(CASE WHEN x.payment_method = 'paypal' THEN x.ts END) )
/(COUNT(DISTINCT(CASE WHEN x.payment_method = 'paypal' THEN x.ts END)) -1)  AS average_minutes_between_paypal_orders ,
TIMESTAMPDIFF(SECOND
              ,MIN(CASE WHEN x.payment_method = 'braintree' THEN x.ts END)
              ,MAX(CASE WHEN x.payment_method = 'braintree' THEN x.ts END) )
/(COUNT(DISTINCT(CASE WHEN x.payment_method = 'braintree' THEN x.ts END)) -1)  AS average_minutes_between_braintree_orders 
FROM (
SELECT p.created_at AS ts 
FROM payment_tokens p 
WHERE `status`='processed'
ORDER BY created_at DESC LIMIT 100
) `x`