Java 統計新客户

語言: CN / TW / HK

時間 2022-05-19 08:59:00 博客園精華區

上週做了一個訂單數據統計的任務，統計的是訂單的新客户數量，本文做一個解題過程的記錄和整理。

新客户的定義

新客户指的是選取時間段有訂單，時間段之前沒有訂單。

比如下面的訂單數據：

時間段	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日沒有新客户。

如果覺得文章對你有幫助的話，請點個推薦吧!

「其他文章」