复购率

最新推荐文章于 2024-07-08 19:35:04 发布

hackcoder

最新推荐文章于 2024-07-08 19:35:04 发布

阅读量4.7k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/hackcoder/article/details/49285925

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"></span><pre name="code" class="html">SELECT *,
count(temp2.uid) as 复购人数
FROM(
SELECT m.*, temp.merchantId, temp.uid
FROM merchant m,(
SELECT order1.merchantId, order1.uid
FROM `order` order1
WHERE order1.uid IN(
SELECT DISTINCT(o.uid)
FROM `order` o
WHERE DATE(o.createDate)>= DATE('2015-10-06 00:00:00')
AND DATE(o.createDate)<= DATE('2015-10-13 23:59:59')
AND o. STATUS>= 3
AND o. STATUS<= 6
AND o.merchantId= order1.merchantId)
AND DATE(order1.createDate)>= date('2015-10-14 00:00:00')
AND DATE(order1.createDate)<= DATE('2015-10-21 23:59:59')
AND order1. STATUS>= 3
AND order1. STATUS<= 6) AS temp
WHERE temp.merchantId= m.id
GROUP BY temp.merchantId, temp.uid
HAVING count(*)>= 6) AS temp2
GROUP BY temp2.id

计算的是一段时间的在后面一个时间段重复购买的人数（不重复）