sql - 根据新货与返回分割购买

Question

我正在尝试编写一个查询，它可以选择一个特定的日期并计算这些客户中有多少人以前下过订单，有多少人是新的。为简单起见，这是表格布局:

id (auto) | cust_id | purchase_date
-----------------------------------  
1         | 1       | 2010-11-15  
2         | 2       | 2010-11-15  
3         | 3       | 2010-11-14  
4         | 1       | 2010-11-13  
5         | 3       | 2010-11-12  

我试图按日期选择订单，然后加入以前日期中同一 user_id 上的任何以前的订单，然后计算有多少订单，有多少没有。这是我失败的尝试:

SELECT SUM( 
CASE WHEN id IS NULL 
THEN 1 
ELSE 0 
END ) AS new, SUM( 
CASE WHEN id IS NOT NULL 
THEN 1 
ELSE 0 
END ) AS returning
FROM (

SELECT o1 . * 
FROM orders AS o
LEFT JOIN orders AS o1 ON ( o1.user_id = o.user_id
AND DATE( o1.created ) =  "2010-11-15" ) 
WHERE DATE( o.created ) <  "2010-11-15"
GROUP BY o.user_id
) AS t

Answer 1

给定一个引用数据 (2010-11-15)，那么我们对在该日期 (A) 下订单的不同客户的数量感兴趣，并且我们对其中有多少人以前下过订单感兴趣(B)，还有多少没有 (C)。显然，A = B + C。

Q1:引用日订单数

SELECT COUNT(DISTINCT Cust_ID)
  FROM Orders
 WHERE Purchase_Date = '2010-11-15';

Q2:引用日下单客户名单

SELECT DISTINCT Cust_ID
  FROM Orders
 WHERE Purchase_Date = '2010-11-15';

Q3:引用日期之前下过单的客户列表

SELECT DISTINCT o1.Cust_ID
  FROM Orders AS o1
  JOIN (SELECT DISTINCT o2.Cust_ID
          FROM Orders AS o2
         WHERE o2.Purchase_Date = '2010-11-15') AS c1
    ON o1.Cust_ID = c1.Cust_ID
 WHERE o1.Purchase_Date < '2010-11-15';

Q4:引用数据下过单的客户数量

SELECT COUNT(DISTINCT o1.Cust_ID)
  FROM Orders AS o1
  JOIN (SELECT DISTINCT o2.Cust_ID
          FROM Orders AS o2
         WHERE o2.Purchase_Date = '2010-11-15') AS c1
    ON o1.Cust_ID = c1.Cust_ID
 WHERE o1.Purchase_Date < '2010-11-15';

Q5:结合Q1和Q4

有几种方法可以进行合并。一种是在选择列表中使用 Q1 和 Q4 作为(复杂的)表达式；另一种方法是将它们用作 FROM 子句中的表，它们之间不需要连接，因为每个都是单行单列表，可以在笛卡尔积中连接。另一个是 UNION，其中每一行都标记有它计算的内容。

SELECT (SELECT COUNT(DISTINCT Cust_ID)
          FROM Orders
         WHERE Purchase_Date = '2010-11-15') AS Total_Customers,
       (SELECT COUNT(DISTINCT o1.Cust_ID)
          FROM Orders AS o1
          JOIN (SELECT DISTINCT o2.Cust_ID
                  FROM Orders AS o2
                 WHERE o2.Purchase_Date = '2010-11-15') AS c1
            ON o1.Cust_ID = c1.Cust_ID
         WHERE o1.Purchase_Date < '2010-11-15') AS Returning_Customers
  FROM Dual;

(我很高兴地假设 MySQL 有一个 DUAL 表 - 类似于 Oracle 的。如果没有，创建一个单列包含单行数据的表是微不足道的。更新 2:抨击 MySQL 5.5 Manual 表明支持但不需要“FROM Dual”；MySQL 很高兴没有 FROM 子句。)

更新 1:在关键位置添加限定符“o1.Cust_ID”以避免评论中指出的“不明确的列名”。