来自不同输出的日期差异
来自不同输出的日期差异
提问于 2012-02-23 21:01:20
我试着找出客户需要多长时间才能在我的eshop中再次下单。
列表eshop_flat_sales_order的名称
customer_email created_at status
------------------------------------
a(at)a.com 12.1.10 complete
b(at)a.com 14.2.10 cancelled
c(at)a.com 16.1.10 complete
a(at)a.com 18.1.10 complete
c(at)a.com 18.1.10 complete
b(at)a.com 20.1.10 complete使用查询
SELECT *
FROM eshop_flat_sales_order
ORDER BY customer_email我会得到所有的电子邮件与日期在订单中。如下所示：
customer_email created_at status
------------------------------------
a(at)a.com 12.1.10 complete
a(at)a.com 18.1.10 complete
b(at)a.com 14.2.10 cancelled
b(at)a.com 20.1.10 complete
c(at)a.com 16.1.10 complete
c(at)a.com 18.1.10 complete现在，如果能得到一个查询，告诉我需要多长时间才能再次订购一个(At)a.com，那就太好了。在本例中，它将是6天。对于c(at)a.com，这将是2天。最后，我需要所有这些日期的平均值，但我应该管理它:)
非常感谢你的回答
回答 4
Stack Overflow用户
发布于 2012-02-23 21:51:59
下面的查询使用JOIN返回每个客户的最新差距(以天为单位)：
SELECT e1.customer_email, DATEDIFF(e1.created_at, e2.created_at) AS gap
FROM eshop_flat_sales_order e1
LEFT JOIN eshop_flat_sales_order e2
ON e2.customer_email = e1.customer_email
AND e2.created_at < e1.created_at
LEFT JOIN eshop_flat_sales_order e3
ON e3.customer_email = e1.customer_email
AND e3.created_at < e2.created_at
WHERE e3.customer_email IS NULL
ORDER BY e1.customer_email此查询假定created_at为DATE字段。
它将返回gap的NULL，其中客户只有一个订单。如果您不想返回只有一个订单的客户的结果，请将第一个连接从LEFT JOIN更改为JOIN。
下面是另一个版本，它只考虑complete订单：
SELECT e1.customer_email, DATEDIFF(e1.created_at, e2.created_at) AS gap
FROM eshop_flat_sales_order e1
LEFT JOIN eshop_flat_sales_order e2
ON e2.customer_email = e1.customer_email
AND e2.created_at < e1.created_at
AND e2.status = 'complete'
LEFT JOIN eshop_flat_sales_order e3
ON e3.customer_email = e1.customer_email
AND e3.created_at < e2.created_at
AND e3.status = 'complete'
WHERE e1.status = 'complete'
AND e3.customer_email IS NULL
ORDER BY e1.customer_email这将显示所有客户的所有相应订单之间的所有差距：
SELECT e1.customer_email, DATEDIFF(e1.created_at, e2.created_at) AS gap
FROM eshop_flat_sales_order e1
JOIN eshop_flat_sales_order e2
ON e2.customer_email = e1.customer_email
AND e2.created_at < e1.created_at
AND e2.status = 'complete'
LEFT JOIN eshop_flat_sales_order e3
ON e3.customer_email = e1.customer_email
AND e3.created_at < e1.created_at
AND e3.created_at > e2.created_at
AND e3.status = 'complete'
WHERE e1.status = 'complete'
AND e3.customer_email IS NULL
ORDER BY e1.customer_email ASC, e1.created_at DESC这将显示每个客户的平均差距：
SELECT e1.customer_email, AVG(DATEDIFF(e1.created_at, e2.created_at)) AS gap
FROM eshop_flat_sales_order e1
JOIN eshop_flat_sales_order e2
ON e2.customer_email = e1.customer_email
AND e2.created_at < e1.created_at
AND e2.status = 'complete'
LEFT JOIN eshop_flat_sales_order e3
ON e3.customer_email = e1.customer_email
AND e3.created_at < e1.created_at
AND e3.created_at > e2.created_at
AND e3.status = 'complete'
WHERE e1.status = 'complete'
AND e3.customer_email IS NULL
GROUP BY e1.customer_emailStack Overflow用户
发布于 2012-02-23 21:10:52
要获得请求的结果，请尝试以下操作：
SELECT
customer_email,
DATEDIFF(MAX(date), MIN(date))
FROM eshop_flat_sales_order
GROUP BY customer_emailStack Overflow用户
发布于 2012-02-23 22:15:02
编辑:此查询将为您提供客户、上一个订单和上一个订单。检查它是否反映了您的需求。如果是，只需对列执行一次datediff：
with last_order as
(
select customer_email, max(created_at) as max_order, max (ID) as max_id
from eshop_flat_sales_order
where status='complete'
group by customer_email
)
Select customer_email, MAX_ORDER,
(select top 1 created_at
from eshop_flat_sales_order o
where customer_email=lo.customer_email and ID!= lo.max_id and status='complete'
order by created_at desc) as PREVIOUS_ORDER
from last_order LO 您是否总是为每个客户提供2条记录？如果是，请尝试以下操作：
select customer_email, datediff(day, min(created_at),max(created_at))
from eshop_flat_sales_order
where status='complete'
group by customer_email
having count(*)=2页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/9413594
复制相关文章
相似问题
腾讯云开发者
