上週做了一個訂單資料統計的任務，統計的是訂單的新客戶數量，本文做一個解題過程的記錄和整理。
新客戶的定義
新客戶指的是選取時間段有訂單，時間段之前沒有訂單。
比如下面的訂單資料：
| 時間段 | 2月1日之前 | 2月1日 ~ 3月1日 |
|---|---|---|
| 客戶 | A,B,C | A,D,E |
在2月1日之前，有 A,B,C 三家企業下過訂單，而2月1號到3月1號有 A,D,E 企業下過訂單，找到存在2月1號到3月1號而不存在 2月1號之前的客戶，也就是 D,E企業就是新客戶。
訂單表 t_order 有如下欄位：
標識id、 訂單號order_sn、業務員 sales、客戶 company、下單時間order_time
統計某個時間段的新客戶數量（難度：簡單）
比如統計2月1日到3月1日的新客戶，時間段起始時間和結束時間分別用 begin 和 end 表示。
首先統計出 2月1日之前的客戶數，使用 group by 做去重處理 ：
select company from t_order where order_time < begin group by company
然後統計出2月1日到3月1日的客戶數：
select company from t_order where order_time >= begin and order_time <= end group by company
新客戶是存在2月1日到3月1日，不存在2月1日之前的客戶，也就是在2月1日到3月1日上去除2月1日之前的客戶，整合以上兩個 sql 語句，可得如下 sql：
select count(*) from
(select company from t_order where order_time >= begin and order_time <= end group by company) where company not in
(select company from t_order where order_time < begin group by company)
統計業務員的新客戶數量（難度：中等）
在上面的基礎上多新增業務員的細分統計，使用客戶 做分組，先統計出時間段之前的客戶：
select company from t_order where order_time < begin group by company
然後查詢時間段之內的下單客戶，使用業務員、客戶做分組：
select company,sales from t_order where order_time >= begin and order_time <= end group by company,sales
上圖展示時間段和時間段之前的客戶，相同的客戶使用關聯連線。其中沒有關聯的就是新客戶，也就是C才是新客戶。兩個查詢做連線查詢再使用業務員做分組查詢，可得到每個業務的新客戶數：
select toi1.sales,
sum( if(toi1.company is not null and toi2.company is null,1,0)) as new_customer
from
(select company,sales from t_order where order_time >= begin and order_time <= end group by company,sales)
toi1 left join
(select company from t_order where order_time < begin group by company) toi2 on toi1.company = toi2.company
group by toi1.sales
統計時間段內每天或者每月的新客戶（難度:困難）
上面兩個查詢都是在統計時間段的客戶的基礎上排除時間段之前的資料。統計每天或者每個月的，都需要每天和之前的做對比。這裡有兩個解決方案。
方案一：
步驟一：統計時間段每天或者每月的客戶
把客戶用 group_concat 拼接起來：
select substring(order_time,1,#{subTime}) as order_time,group_concat(distinct(company)) as companys
from t_order where order_time >= begin and order_time <= end
group by substring(order_time,1,#{subTime})
步驟二：統計每天之前的客戶
每一天都需要和前面的資料做比較，首先查詢到每天的客戶集合，遍歷每天的資料再查詢之前的資料，如果在當天的客戶而不在之前的客戶，就是新客戶。因為查詢要查詢很多次，所以查詢的時間會很長。
比如查詢 2月1日到2月3日的新客戶：
| 日期 | 公司集合 |
|---|---|
| 2月1日 | A,B |
| 2月2日 | B,D |
| 2月3日 | C,E |
上面有三條資料，都要迴圈三次查詢，如果時間段比較長，查詢耗時更長。
後面想到使用 union all 組合查詢，在上面查詢的基礎上，使用 foreach 遍歷每一條資料，每條資料都往前查詢資料的客戶集合：
<foreach collection="list" item="list" separator=" UNION ALL ">
select #{list.order_time} as order_time,group_concat(distinct (company )) as companys from
t_order_info
where order_type=1 and amount>0 and finish_subtype not in (3,6)
and substring(order_time,1,#{subTime}) < #{list.order_time}
and company in
<foreach collection="list.companys" item="company" open="(" close=")" separator=",">
#{company}
</foreach>
</foreach>
以上的 sql 實際應該是如下格式：
select order_time,company from t_order
union all
select order_time,company from t_order
union all
select order_time,company from t_order
使用 union all 聯合查詢，快了很多。
步驟三：步驟一的集合去掉步驟二的集合
包含在時間段之內的資料，去掉之前的集合，也就是新客戶的了。
group_concat拼接字元，會出現不完整的情況，這是因為超過了group_concat_max_len值，預設是1024，增加該值即可。
方案二：升級方案
下面是2月1日之前，以及2月1日到2月3日每天的客戶集合：
| 日期 | 2月1日之前 | 2月1日 | 2月2日 | 2月3日 |
|---|---|---|---|---|
| 公司 | A,B | C | A,D | C,D |
分析
首先看2月1日的資料，客戶C 是不存在2月1號之前的，所以2月1號的新客戶就是C。
然後看2月2日，要找到2月2日之前的資料。
2月2日之前就是2月1日之前 + 2月1日
所以2月2日之前的資料不需要再去資料庫查詢，把之前的資料累加起來。
解決方案
使用 set 集合存放資料，首先把 2月1號之前的資料放入set
2月1號之前 A 和 B 放入集合，set 不存在的就是新客戶。
首先2月1號的C不在set中，所以2月1號的新客戶是C。然後把C新增到集合中。
2月2日中的A在集合中,D不在集合中，所以2月2號的新客戶是D。D新增到集合中。
2月3日中的C和D都存在集合中，所以2月3日沒有新客戶。
如果覺得文章對你有幫助的話，請點個推薦吧!