当前位置：首页 > news >正文

StarRocks 3.1.1 深度优化：GROUP BY 非聚合字段查询提速落地方案

news 2026/7/21 6:52:23

一、业务痛点 & 适用场景

在实时数仓建设、用户画像分析、交易数据统计的一线开发场景中，有一个需求高频且棘手：基于用户维度聚合统计交易总金额，同时精准保留每个用户最新一笔交易的明细维度信息。

简单来说就是：既要聚合汇总数据，又要精准保留分组内最新明细字段。

常规写法极易出现两类问题：直接 GROUP BY 会因非聚合字段触发语法报错；嵌套子查询、多层窗口函数的通用写法，又会造成全表扫描、计算逻辑冗余。随着业务数据量递增，查询延迟会持续飙升，直接影响数据报表、实时看板的展示效果。

本文基于StarRocks 3.1.1稳定版本，结合真实交易业务表场景，落地一套低冗余、可上线的 GROUP BY 非聚合字段查询优化方案，完美兼顾「数据聚合统计」与「最新明细留存」双重业务需求，在保障数据精准度的同时，大幅提升查询性能。

适用场景总结：用户交易汇总统计、用户最新行为画像、时序数据分组聚合、分组统计+明细留存的各类数仓查询场景。

二、前置环境说明

引擎版本：StarRocks 3.1.1
存储模型：OLAP 重复键模型（DUPLICATE KEY）
分区策略：日期范围分区
分发策略：用户ID哈希分片
核心优化思路：窗口函数精准筛选分组最新数据 + 条件聚合函数精简计算逻辑，彻底规避全量聚合带来的性能冗余问题

三、完整业务表结构（原样复刻）

本文实操基于生产级业务交易表，完整保留原生分区规则、分片策略、字段注释及属性配置，完全贴合线上真实环境，所有代码可直接复制复用。

CREATETABLE`biz_trade_part`(`dt`dateNULLCOMMENT"分区日期（分区键）",`trade_id`varchar(64)NULLCOMMENT"交易ID（非聚合字段）",`user_id`bigint(20)NULLCOMMENT"用户ID",`trade_type`tinyint(4)NULLCOMMENT"交易类型",`trade_amount`decimal(18,2)NULLCOMMENT"交易金额（聚合字段）",`trade_status`tinyint(4)NULLCOMMENT"交易状态（非聚合维度）",`remark`varchar(256)NULLCOMMENT"备注信息（冗余非聚合字段）",`create_time`datetimeNULLCOMMENT"创建时间")ENGINE=OLAPDUPLICATEKEY(`dt`,`trade_id`)COMMENT"OLAP"PARTITIONBYRANGE(`dt`)(PARTITIONp20260101VALUES[("0000-01-01"),("9999-01-02")))DISTRIBUTEDBYHASH(`user_id`)BUCKETS16ORDERBY(`dt`,`user_id`,`trade_type`)PROPERTIES("compression"="LZ4","datacache.enable"="true","enable_async_write_back"="false","replication_num"="1","storage_volume"="builtin_storage_volume");

四、初始化测试数据

本次插入5条模拟交易测试数据，覆盖不同日期、不同用户、多类交易类型及状态，高度还原真实业务数据特征，可精准验证优化后SQL的查询效果与数据准确性。

INSERTINTObiz_trade_partVALUES('2025-07-01','T001',10001,1,99.90,1,'正常消费','2025-07-01 10:00:00'),('2025-07-01','T002',10001,1,199.90,1,'正常消费','2025-07-01 10:05:00'),('2025-07-01','T003',10002,2,50.00,0,'待支付','2025-07-01 11:00:00'),('2025-07-02','T004',10001,2,299.00,1,'退款单','2025-07-02 09:30:00'),('2025-07-02','T005',10002,1,128.50,1,'正常消费','2025-07-02 14:20:00');

五、最终优化版可运行SQL（核心Demo）

以下是本次优化的核心可落地脚本，稍作调整后即可良好适配。核心设计思路：先通过窗口函数筛选出每个用户的最新交易数据，再借助条件聚合函数，实现「非聚合字段留存最新明细、金额字段全量汇总」的业务诉求，完美适配 StarRocks 3.1.1 执行引擎特性，最大限度缩减计算开销。

WITHtempAS(SELECTdt,trade_id,user_id,trade_type,trade_amount,trade_status,remark,create_time,-- 按用户分组，按日期倒序排序，取最新一条数据ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYdtDESC)ASrnFROMbiz_trade_partwheredtBETWEEN'2025-07-01'AND'2025-07-02')SELECTuser_id,MAX(IF(rn=1,dt,NULL))ASdt,MAX(IF(rn=1,trade_id,NULL))AStrade_id,MAX(IF(rn=1,trade_type,NULL))AStrade_type,MAX(IF(rn=1,trade_status,NULL))AStrade_status,MAX(IF(rn=1,remark,NULL))ASremark,MAX(IF(rn=1,create_time,NULL))AScreate_time,SUM(trade_amount)AStrade_amountFROMtempGROUPBYuser_id;