mysql - 当新客户与回访客户 SQL 中的日期范围发生更改时删除重复项

Question

I am checking new vs repeat customers for a period of three months. Query works perfectly when i run this for any single month but when see that for 2-3 months it shows me duplicate emailaddresses.

示例数据(显示错误)

month emailaddress      first_order       codate       customertype
2    xyz@hotmail.com     10/27/2015       01/10/2017     Repeat
3    xyz@hotmail.com     10/27/2015       01/10/2017     Repeat

所以基本上这个客户是在一月、二月和三月购买的，他的第一次购买是在 2015 年。所以这是正确的，他是回头客，但在最终输出中我只想看到他:月份 电子邮件地址 第一订单代码 客户类型1 xyz@hotmail.com 10/27/2015 01/10/2017 重复

请记住，要唯一定义客户，我们必须使用客户电子邮件，而不是客户 ID(因为它不是唯一的)

如有任何帮助，我们将不胜感激。下面的代码非常适合单月，但不适用于多个月。

SELECT 
A.MONTH,
A.emailAddress,
A.FIRST_ORDER_EMAIL as first_order,
A.first_order as codate,
CASE WHEN CAST(A.first_order AS DATE)  =  CAST(A.FIRST_ORDER_EMAIL AS DATE) THEN 'New' ELSE 'Repeat' END customerType
FROM
(
SELECT
DATEPART(mm, s.OrderDate) AS MONTH,
c.emailAddress,
 (SELECT min(o.OrderDate)
FROM Orders o
WHERE c.CustomerID=o.CustomerID
AND o.OrderDate BETWEEN '01/01/2017 00:00' AND '03/31/2017 23:59' AND o.OrderStatus NOT IN ('cancelled','Payment Declined','Returned'))AS first_order,
 (SELECT min(o.OrderDate)
FROM Orders o
INNER JOIN customers CO
ON CO.customerid = o.customerid 
WHERE c.emailAddress=CO.emailAddress
AND o.OrderDate BETWEEN '1/1/2010 00:00' AND '03/31/2017 23:59'
AND o.OrderStatus NOT IN ('cancelled','Payment Declined','Returned')) AS FIRST_ORDER_EMAIL
FROM orders s
JOIN orders p ON p.CustomerID = s.CustomerID
AND p.OrderID <= s.OrderID
JOIN customers c ON c.CustomerID = s.CustomerID
JOIN OrderDetails od ON od.OrderID = s.OrderID
WHERE 
 s.OrderDate BETWEEN '01/01/2017 00:00' AND '03/31/2017 23:59'
AND s.OrderStatus NOT IN ('cancelled','Payment Declined','Returned')
AND od.ProductPrice <> 0
AND od.ProductCode = 'xyz'
GROUP BY c.emailAddress,
c.CustomerID,
DATEPART(mm, s.OrderDate),
s.OrderDate
)A
GROUP BY 
A.MONTH,
A.emailAddress,
A.FIRST_ORDER_EMAIL,
A.first_order,
CASE WHEN CAST(A.first_order AS DATE)  =  CAST(A.FIRST_ORDER_EMAIL AS DATE) THEN 'New' ELSE 'Repeat' END

Answer 1

尝试使用 ROW_NUMBER() 确定每个电子邮件地址出现的第一条记录

WITH result AS
( SELECT a.[month],
         a.emailaddress,
         a.first_order_email AS first_order,
         a.first_order AS codate,
         CASE WHEN CAST(a.first_order AS DATE)  =  CAST(a.first_order_email AS DATE)
              THEN 'New' 
              ELSE 'Repeat' 
          END customerType,
         ROW_NUMBER() OVER (PARTITION BY a.emailaddress ORDER BY a.[month]) AS rnum
   FROM (SELECT DATEPART(mm, s.orderdate) AS [month],
                c.emailaddress,
                (SELECT min(o.orderdate)
                   FROM orders o
                  WHERE c.customerid = o.customerid
                    AND o.orderdate BETWEEN '01/01/2017 00:00' AND '03/31/2017 23:59' 
                    AND o.orderstatus NOT IN ('cancelled','Payment Declined','Returned'))AS first_order,
                (SELECT min(o.orderdate)
                   FROM orders o
                  INNER JOIN customers co
                     ON co.customerid = o.customerid 
                  WHERE c.emailaddress = co.emailaddress
                    AND o.orderdate BETWEEN '1/1/2010 00:00' AND '03/31/2017 23:59'
                    AND o.orderstatus NOT IN ('cancelled','Payment Declined','Returned')) AS first_order_email
           FROM orders s
           JOIN orders p 
             ON p.customerid = s.customerid
            AND p.orderid <= s.orderid
           JOIN customers c 
             ON c.customerid = s.customerid
           JOIN orderdetails od 
             ON od.orderid = s.orderid
          WHERE s.orderdate BETWEEN '01/01/2017 00:00' AND '03/31/2017 23:59'
            AND s.orderstatus NOT IN ('cancelled','Payment Declined','Returned')
            AND od.ProductPrice <> 0
            AND od.ProductCode = 'xyz'
          GROUP BY c.emailaddress,
                   c.customerid,
                   DATEPART(mm, s.orderdate),
                   s.orderdate
        ) a
  GROUP BY a.[month],
           a.emailaddress,
           a.first_order_email,
           a.first_order,
           CASE WHEN CAST(a.first_order AS DATE)  =  CAST(a.first_order_email AS DATE) 
                THEN 'New' 
                ELSE 'Repeat' 
            END
)

SELECT [month],
       emailaddress,
       first_order,
       codate,
       customerType
  FROM result
 WHERE rnum = 1