TIDB group by 的支持

#1

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

【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

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

#3

使用非 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
#4

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

#5

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

#6

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

#7

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

1赞
#8

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

#9

附带下使用 窗口函数的改写方式：
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赞