计算平均日期差
计算平均日期差
提问于 2019-03-29 11:07:09
这是表的基本设置(只有相关列的DDL存在)。MySQL版本8.0.15
其目的是显示订单之间日期差异间隔的平均值。
CREATE TABLE final (
prim_id INT(11) NOT NULL AUTO_INCREMENT,
order_ID INT(11) NOT NULL,
cust_ID VARCHAR(45) NOT NULL,
created_at DATETIME NOT NULL,
item_name VARCHAR(255) NOT NULL,
cust_name VARCHAR(255) NOT NULL,
PRIMARY KEY (prim_id),
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=145699其他信息：
cust ID -> cust_name (one-to-many)
cust_ID -> order_ID (one-to-many)
order ID -> item_name (one-to-many)
order ID -> created_at (one-to-one)
prim_id -> *everything* (one-to-many)我考虑过使用min(created_at)和max(created_at)，但这将排除最老的和最新的订单之间的所有订单。我需要一个更精细的解决方案。
最终的结果应该是这样：
关于所有订单之间的平均时间间隔的信息(不仅仅是min和max，因为通常有很多次，超过两次)，在显示客户名称(cust_name)的列旁边。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-03-29 11:36:28
如果我做对了，您可以使用一个子查询来获取上一个订单的日期。使用datediff()获取日期之间的差异，使用avg()获取差异的平均值。
SELECT f1.cust_id,
avg(datediff(f1.created_at,
(SELECT f2.created_at
FROM final f2
WHERE f2.cust_id = f1.cust_id
AND (f2.created_at < f1.created_at
OR f2.created_at = f1.created_at
AND f2.order_id < f1.order_id)
ORDER BY f2.created_at DESC,
f2.order_id DESC
LIMIT 1)))
FROM final f1
GROUP BY f1.cust_id;编辑：
如果一个订单ID可以有更多行，就像KIKO提到的那样，我们需要从不同的订单集执行SELECT，如：
SELECT f1.cust_id,
avg(datediff(f1.created_at,
(SELECT f2.created_at
FROM (SELECT DISTINCT f3.cust_id,
f3.created_at,
f3.order_id
FROM final f3) f2
WHERE f2.cust_id = f1.cust_id
AND (f2.created_at < f1.created_at
OR f2.created_at = f1.created_at
AND f2.order_id < f1.order_id)
ORDER BY f2.created_at DESC,
f2.order_id DESC
LIMIT 1)))
FROM (SELECT DISTINCT f3.cust_id,
f3.created_at,
f3.order_id
FROM final f3) f1
GROUP BY f1.cust_id;如果对于具有不同客户ID或不同创建时间戳的订单可以有两行，则可能会失败。但在这种情况下，数据只是完全垃圾，需要在任何其他事情之前进行更正。
第二版：
或者获得每个顺序的最大创建时间戳(如果它们可能不同的话)：
SELECT f1.cust_id,
avg(datediff(f1.created_at,
(SELECT f2.created_at
FROM (SELECT max(f3.cust_id) cust_id,
max(f3.created_at) created_at,
f3.order_id
FROM final f3
GROUP BY f3.order_id) f2
WHERE f2.cust_id = f1.cust_id
AND (f2.created_at < f1.created_at
OR f2.created_at = f1.created_at
AND f2.order_id < f1.order_id)
ORDER BY f2.created_at DESC,
f2.order_id DESC
LIMIT 1)))
FROM (SELECT max(f3.cust_id) cust_id,
max(f3.created_at) created_at,
f3.order_id
FROM final f3
GROUP BY f3.order_id) f1
GROUP BY f1.cust_id;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55416105
复制相关文章
相似问题
腾讯云开发者
