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

目录

问题描述

示例数据表结构

orders 表结构

order_details 表结构

初始查询

问题分析

解决方案

修改后的查询

主要修改点

确保所有列都是聚合函数的结果：

总结

---

在使用 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() 可以避免重复累加问题，同时保持数据的准确性。