TIDB group by 的支持

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】

【问题描述】

select * from table_a where
field1 = ‘a’
and field2 = ‘b’
group by field3;
报错: this is incompatible with sql_mode=only_full_group_byuery

支持的方式有两种:

  1. 关闭 TIDB 的 SQL 模式 ONLY_FULL_GROUP_BY

  2. sql 改造 ,在 group by 前 嵌套一层 select

select * from (
select * from table_a where
field1 = ‘a’
and field2 = ‘b’

)tmp
group by field3;

发现这种 方式可行的原因:
项目内对于group by 的应用场景一般是用于排序后 取 group by 下的分类的最新一条数据。
类似:
select * from (
select * from table_a where
field1 = ‘a’
and field2 = ‘b’
order by create_time desc
)tmp
group by field3;

疑问:
暂时没有明白这种sql 的嵌套可以支持的原理,希望大佬能解惑. 谢谢

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

问一下,方法2 这种嵌套方式,是在哪里看到的支持的

使用非 full group by 语法会导致结果集不稳定。
对于取分组内最新一条数据的需求,可以使用窗口函数,可参考下面语句(查询各部门最高工资):

SELECT * FROM
(SELECT depname, empno, salary, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) as row_index )
WHERE(row_index = 1) FROM empsalary

您好,没有官方文档的支持, 内部再做sql 兼容的时候,拿项目sql 在TIDB 上试运行的时候 嵌套sql 是
能够跑出结果的

您好,感谢分享,您这个是 oracle 的sql 语句,这个是可行的 ,mysql 的话实现这个功能,目前能想出的 方法就是通过 子查询的方式来实现 组内最新一条的数据需求了。不过子查询会有多次查询。影响查询效率

TiDB v3.0 版本开始提供窗口函数功能,官网文档 https://docs.pingcap.com/zh/tidb/stable/window-functions#窗口函数

感谢大佬, 已经使用窗口函数实现了这个功能~~

1赞

不过这种改写是为了能在 TIDB 与 mysql 上同时运行,mqsql 在8.0 后才有的窗口函数,目前的产线上多是用的5.x的版本,还是会存在兼容性问题。

附带下使用 窗口函数的改写方式:
SELECT * FROM (
select t.id as id ,t.manager_name as managerName,ttt.merchant_id as merchantId
,ROW_NUMBER() OVER w AS ‘row_number’
from a t
left join b tt on concat(’,’,tt.manager_ids,’,’) like concat(’%,’,t.id,’,%’)
left join c ttt on tt.id=ttt.id
where 1=1
and ttt.field_2=‘01’
and ttt.field_1= 1
and (ttt.field_3<= DATE_FORMAT(now(),’%Y%m%d%H%i%s’) or ttt.FUND_PUBLISH_DATE is null)

    WINDOW w AS (PARTITION BY t.id )
    
    ) tmp  WHERE tmp.row_number = 1
    ORDER BY tmp.id asc
1赞