Doris 中聚合函数使用常见问题及解决方案

在使用 Doris 进行数据分析时,聚合函数是不可或缺的一部分。然而,由于 Doris 的特定优化和语法要求,有时会遇到一些常见的问题,导致查询无法正确执行或返回预期结果。本文将介绍一个常见的聚合函数使用问题,并提供解决方案。

问题描述
假设我们有一个业务场景,需要统计某个时间段内的订单数量,并且需要包含一些额外的统计信息,如超期订单和重办订单的数量。我们希望在查询结果中包含这些统计信息,并且确保这些统计信息不会被重复累加。

示例数据表结构
假设有两个表:orders 和 order_details。

orders 表存储订单的基本信息。

order_details 表存储订单的详细信息,包括订单状态和处理状态。

orders 表结构

字段名

类型

描述

order_id

bigint

订单ID

create_tim

datetime

创建时间

status

int

订单状态

order_details 表结构

段名

类型

描述

order_id

bigint

订单ID

detail_id

bigint

详情ID

process_state

int

处理状态

is_overdue

int

是否超期

is_renewal

int

是否重办

初始查询

我们希望统计某个时间段内的订单数量,并包含超期订单和重办订单的数量。初始查询如下:

 
SELECT 
    SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) AS order_cnt_supervised, 
    SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) AS order_cnt_not_supervised, 
    SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) AS order_cnt_should_supervised, 
    CONCAT(IFNULL(ROUND(SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) * 100.0 / (SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END)), 2), 0), '%') AS supv_rate, 
    CONCAT(IFNULL(ROUND(SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) * 100.0 / (SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END)), 2), 0), '%') AS not_supv_rate, 
    od.overdue_cnt AS order_cnt_overdue, 
    od.renewal_cnt AS order_cnt_renewal 
FROM orders o 
LEFT JOIN (
    SELECT 
        order_id, 
        SUM(CASE WHEN is_overdue = 1 THEN 1 ELSE 0 END) AS overdue_cnt, 
        SUM(CASE WHEN is_renewal = 1 THEN 1 ELSE 0 END) AS renewal_cnt 
    FROM order_details 
    GROUP BY order_id
) od ON o.order_id = od.order_id 
WHERE o.create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

问题分析
上述查询在 MySQL 中可以正常执行,但在 Doris 中会报错。具体错误信息可能类似于:ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'od.overdue_cnt' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个问题的原因是 Doris 对 GROUP BY 子句有更严格的要求。在 Doris 中,所有非聚合列都必须包含在 GROUP BY 子句中,或者所有列都必须是聚合函数的结果。

解决方案
为了解决这个问题,我们可以使用 MAX() 聚合函数来确保 overdue_cnt 和 renewal_cnt 只被使用一次,避免重复累加。

修改后的查询

SELECT 
    SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt) AS order_cnt_supervised, 
    SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) AS order_cnt_not_supervised, 
    SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt) AS order_cnt_should_supervised, 
    CONCAT(IFNULL(ROUND((SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt)) * 100.0 / (SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt)), 2), 0), '%') AS supv_rate, 
    CONCAT(IFNULL(ROUND(SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) * 100.0 / (SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt)), 2), 0), '%') AS not_supv_rate, 
    MAX(od.overdue_cnt) AS order_cnt_overdue, 
    MAX(od.renewal_cnt) AS order_cnt_renewal 
FROM orders o 
LEFT JOIN (
    SELECT 
        order_id, 
        SUM(CASE WHEN is_overdue = 1 THEN 1 ELSE 0 END) AS overdue_cnt, 
        SUM(CASE WHEN is_renewal = 1 THEN 1 ELSE 0 END) AS renewal_cnt 
    FROM order_details 
    GROUP BY order_id
) od ON o.order_id = od.order_id 
WHERE o.create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

主要修改点
使用 MAX() 聚合函数:

MAX(od.overdue_cnt) AS order_cnt_overdue

MAX(od.renewal_cnt) AS order_cnt_renewal

这样可以确保 overdue_cnt 和 renewal_cnt 只被使用一次,避免重复累加。

确保所有列都是聚合函数的结果:
SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt) AS order_cnt_supervised

SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) AS order_cnt_not_supervised

SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN o.status = 0 THEN 1 ELSE 0 END) + MAX(od.overdue_cnt) + MAX(od.renewal_cnt) AS order_cnt_should_supervised

MAX(od.overdue_cnt) AS order_cnt_overdue

MAX(od.renewal_cnt) AS order_cnt_renewal

总结
在 Doris 中使用聚合函数时,需要注意以下几点:

数据类型匹配:确保过滤条件中的数据类型与表中的数据类型匹配。

聚合函数要求:确保所有非聚合列都包含在 GROUP BY 子句中,或者所有列都必须是聚合函数的结果。
使用 MAX() 聚合函数:在某些情况下,使用 MAX() 可以避免重复累加问题,同时保持数据的准确性。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值