SQL使用CASE语句实现分段统计数据

SQL使用CASE语句实现分段统计数据

9 May 2017

最近写定时任务脚本时,碰到了一个需求,要统计不同金额区间的充值人数,虽然一个简单的CASE语句就能搞定,不过一开始看到这个需求还是愣了好一会儿,这个语句该怎么写?如何去重?于是把它记录下来,以备后面使用。

 

需求规则:统计每天各个游戏不同金额区间的充值人数和订单数目

代码:

SELECT 
    DATE_FORMAT( FROM_UNIXTIME( `spend_time` ) , '%Y%m%d' ) AS `date`,
    `gid`,
    `sid`,
    SUM( CASE WHEN `spend_money`>=0 AND `spend_money`<=100 THEN 1 ELSE 0 END ) AS `rang_0`,
    SUM( CASE WHEN `spend_money`>=100 AND `spend_money`<=200 THEN 1 ELSE 0 END ) AS `rang_1`,
    SUM( CASE WHEN `spend_money`>=200 AND `spend_money`<=500 THEN 1 ELSE 0 END ) AS `rang_2`
FROM `spend_log` 
WHERE ( `spend_time` BETWEEN 1494172800 AND 1494259199 ) 
GROUP BY `date`,`gid`

查询结果如下:

以上是未去重的,表示各区间的订单数量,如果要去重,统计充值人数,可以这么查:

SELECT 
    DATE_FORMAT( FROM_UNIXTIME( `spend_time` ) , '%Y%m%d' ) AS `date`,
    `gid`,
    `sid`,
    COUNT(DISTINCT ( CASE WHEN `spend_money`>=0 AND `spend_money`<=100 THEN `uid` ELSE 0 END )) AS `rang_0`,
    COUNT(DISTINCT ( CASE WHEN `spend_money`>=100 AND `spend_money`<=200 THEN `uid` ELSE 0 END )) AS `rang_1`,
    COUNT(DISTINCT ( CASE WHEN `spend_money`>=200 AND `spend_money`<=500 THEN `uid` ELSE 0 END )) AS `rang_2`
FROM `spend_log` 
WHERE ( `spend_time` BETWEEN 1494172800 AND 1494259199 ) 
GROUP BY `date`,`gid`

 

很简单的两个查询,但是突然用到的时候,还是会让人蒙逼半天的。。。