--DROP TABLE kx_buy_number;

--CREATE TABLE kx_buy_number AS
SELECT

mm.user_rank, mm.goumai freq, sum(mm.mai_number) freq_number
FROM (SELECT

nn.user_rank,
CASE nn.pinci
WHEN 1 THEN
'购买一次的会员'
WHEN 2 THEN
'购买两次的会员'
WHEN 3 THEN
'购买三次的会员'
WHEN 4 THEN
'购买四次的会员'
WHEN 5 THEN
'购买五次的会员'
ELSE
'购买五次以上的会员'
END goumai,
nn.mai_number
FROM (SELECT

a.user_rank, count(a.user_id) mai_number, pinci
FROM (SELECT

user_id, user_rank, count(order_sn) pinci
FROM kx_order_number_f
where add_time between '20150105' and '20150217'
GROUP BY user_id,

user_rank) a
GROUP BY pinci,

a.user_rank) nn) mm

GROUP BY mm.goumai,

mm.user_rank