客户RFM分析

好久没写SQL,写了一下，然后让开发在线上跑了一下数据，开发说你这个不需要子查询就可以搞定，本来想装逼一下，被啪啪打脸

SELECT
tab.a AS 客户名称,
FROM_UNIXTIME(MAX(tab.d) / 1000, '%Y-%m-%d') AS 最近下单时间,
COUNT(tab.a) AS 最近三月成交次数,
AVG(tab.c) AS 最近三月平均成交金额
FROM
(SELECT
z.delivery_name a,
f.receive_total_price-f.pay_total_price c,
z.create_time d
FROM
jy_order_customer z JOIN jy_order_v2 f ON z.order_code = f.order_code
WHERE z.create_time > UNIX_TIMESTAMP('2019-05-27 00:00:00')*1000
AND z.delivery_name NOT LIKE ("%钢银%")
AND z.STATUS = 80
AND f.transit_type IN (0,2)) AS tab
GROUP BY a
ORDER BY COUNT(tab.a) DESC;
接下来有时间把分析过程写一遍