商户交易按指定条件统计分析总结

其他废话就不说了，先看交易表结构

再来看统计的条件

简单解释一下条件，首先开始和结束时间是查询的时间条件  ，统计周期是代表的是多久执行一次统计，如果一次要统计多天，则代表需要按照日期对结果进行分组，支付渠道，单笔最小支付金额就是简单的where条件，最高奖励是是根据统计结果进行计算的，暂时先不考虑 ，重点还有一个单卡单日最大有效笔数（以3为例），也就是对一个商户而言，一张银行卡最多只能支付3，超出的次数不计入统计结果。后面的奖励规则同样先不考虑

首先统计的重点是对交易笔数进行统计,对一个商户而言，每天的交易笔数统计如下（简单的金额、支付方式,时间条件就直接加上），此处需要使用一个字符串截取函数来获取交易日期，然后对交易笔数通过日期进行分组

SELECT SUBSTRING(transaction_time,1,10) AS trade_date,COUNT(*) AS num 
FROM transaction_sum 
WHERE compnay_no = '890610158125950' 
AND pay_channel = '银联' AND transaction_amount >= 1.00 
AND transaction_time >= '2018-01-01 00:00:00' AND transaction_time < '2018-05-31 00:00:00'
GROUP BY datestr
ORDER BY datestr;

然后我们加上卡号限制的条件，分组条件中增加卡号字段

SELECT SUBSTRING(transaction_time,1,10) AS trade_date,card_no,COUNT(*) AS num 
FROM transaction_sum 
WHERE compnay_no = '890610158125950' 
AND pay_channel = '银联' AND transaction_amount >= 1.00 
AND transaction_time >= '2018-01-01 00:00:00' AND transaction_time < '2018-05-31 00:00:00'
GROUP BY datestr,card_no
ORDER BY datestr;

这时，每张每天的交易按银行卡分组的数据已经统计出来了

然后应该增加一个判断，如果笔数大于3 ，则修改返回结果为3，这里需要用到case when 搜索函数，具体怎么用大家参考博客转载的另一篇文章。

SELECT SUBSTRING(transaction_time,1,10) AS datestr,card_no,(case when count(*)< 3 then count(*) else 3 end) as num
FROM transaction_sum 
WHERE compnay_no = '890610158125950'
AND pay_channel = '银联' AND transaction_amount >= 1.00 
AND transaction_time >= '2018-01-01 00:00:00' AND transaction_time < '2018-05-31 00:00:00' 
GROUP BY datestr,card_no 
ORDER BY datestr;

现在统计的结果还是每天按照日期和卡号分组的，接下来我们应该将起转变为按照日期分组，去掉卡号分组，这里需要用到select子查询

SELECT datestr,sum(num) 
FROM 
    (SELECT SUBSTRING(transaction_time,1,10) AS datestr,card_no,(case when count(*)< 3 then count(*) else 3 end) as num
        FROM transaction_sum 
        WHERE compnay_no = '890610158125950' 
        AND pay_channel = '银联' AND transaction_amount >= 1.00 
        AND transaction_time >= '2018-01-01 00:00:00' AND transaction_time < '2018-05-31 00:00:00' 
        GROUP BY compnay_no,datestr,card_no 
    ) AS a 
GROUP BY datestr
ORDER BY datestr;

最后，将查询范围从一个商户扩展到一次可以查询N个商户

select compnay_no,datestr,sum(num) 
FROM 
    (SELECT compnay_no,SUBSTRING(transaction_time,1,10) AS datestr,card_no,(case when count(*)< 3 then count(*) else 3 end) as num
        FROM transaction_sum 
        WHERE compnay_no in('898610158125951','898610158126308') 
        AND pay_channel = '其他渠道' AND transaction_amount >= 1.00 
        AND transaction_time >= '2018-01-01 00:00:00' AND transaction_time < '2018-05-31 00:00:00' 
        GROUP BY compnay_no,datestr,card_no 
    ) AS a 
GROUP BY compnay_no,datestr
ORDER BY datestr;

好了，sql语句的查询到此告一段落

后续还有奖励金额的统计，虽然也可以借助sql函数实现，但是过程就比较复杂了，还是交给应用程序来做。上述过程，如有可以优化的地方，欢迎一起交流  QQ 2461271753