SQL在GROUP BY中执行计算 - SQL perform a Calculation inside a GROUP BY

SQL perform a Calculation inside a GROUP BY

本文关键字：执行计算 BY GROUP SQL | 更新日期: 2023-09-27

下面是我的代码:

$query = DB::table('notification');
        $query->leftJoin('rule', 'notification.rule_id', '=', 'rule.rule_id');
        $query->leftJoin('agent', 'notification.agent_id', '=', 'agent.agent_id')
                    ->leftJoin('department', 'agent.department_id', '=', 'department.department_id')
                    ->select(
                        DB::raw("coalesce(department.name, '$this->defaultDepartment') as tag"),
                        DB::raw('count(*) as count'),
                        //DB::raw('rule.quotient * count') as score
                        )
                    ->groupBy('tag')
                    ->orderBy('count', 'desc');

我想计算每个部门的分数:是部门内座席对规则的所有风险评分之和(按部门分组)。

仅供参考，计算单个代理评分是(规则)。商数*规则的计数)即规则出现的次数

我想提供一个db结构，但是我认为这个查询清除了它。

我该怎么做呢?

对

假设您的查询是正确的，则laravel查询如下所示:

$query = DB::table('notification');
        $query->leftJoin('rule', 'notification.rule_id', '=', 'rule.rule_id');
        $query->leftJoin('agent', 'notification.agent_id', '=', 'agent.agent_id')
                    ->leftJoin(DB::raw('(Select coalesce(department.name, '$this->defaultDepartment') as tag, count(*) as count from department)as department'),function($join){
                            $join->on('agent.department_id', '=', 'department.department_id')
                        ;})
                    ->groupBy('tag')
                    ->orderBy('count', 'desc');

更新:

$query = DB::table('notification');        
$query->leftJoin('agent', 'notification.agent_id', '=', 'agent.agent_id')
            ->leftJoin(DB::raw('(Select coalesce(department.name, '$this->defaultDepartment') as tag, count(*) as count from department)as department'),function($join){
                    $join->on('agent.department_id', '=', 'department.department_id');
                ;})
$query->leftJoin(DB::raw('(Select (quotient * department.count) as score from rule) as rule'),function ($join){
                $join->on('rule', 'notification.rule_id', '=', 'rule.rule_id');
            });
        ->groupBy('tag')
        ->orderBy('count', 'desc');