mysql - 如何使用MYSQL查询将客户分为4个等级

Question

我们存储客户数据，如姓名、用户 ID 和他们在不同订单上花费的总金额，现在我想根据客户到目前为止的总金额将他分为 1 到 4 级。下面是我正在使用的脚本，但它需要很多时间，有没有更好的方法来做到这一点？ dateCreate 字段没有索引。

    public function getBiggestSpenders($customerUserId){
        global $db, $database;
        $sql = "SELECT userId, SUM(total) AS Total, ORD.dateCreate 
            FROM $database.`order` ORD
            WHERE year(ORD.dateCreate) >= '2013' 
            group by ORD.userId 
            order by Total DESC";
        $result = $db->getTable($sql);
        $numRows = count($result);
        $flag=0;
        for($i=0;$i<$numRows && $flag==0;$i++){
            $userId = $result[$i]['userId'];
            if($userId==$customerUserId){
                $position = $i;
                $Total = $result[$i]['Total'];
                $flag=1;
            }
        }
        $quartile = $this->getQuartiles($numRows, $position);
        if($quartile==1)
            return $quartile;
        else
            return 0;   
    }
   public function getQuartiles($numRows, $position){
        $total      = $numRows;
        $segment    = round($total / 4);    
        $Quartile   = floor($position / $segment) + 1;
        return $Quartile;
    }

谢谢!

Answer 1

为了提高速度，您可以在dateCreate列上创建索引，并使用以下条件让MySQL使用它:

WHERE ORD.dateCreate >= '2013-01-01'

就分组而言，您可以使用CASE语句根据支出来定义分组，例如:

SELECT userId, SUM(total) AS Total,
CASE 
 WHEN Total >= 2000 then 1
 WHEN Total >= 1000 AND Total <2000 THEN 2
 WHEN Total >=500 AND Total < 1000 THEN 3
 ELSE 4
END as `rank`,
ORD.dateCreate 
FROM $database.`order` ORD
WHERE ORD.dateCreate >= '2013-01-01' 
group by ORD.userId 
order by Total DESC