【ShardingSphere 使用聚合函数的问题记录】

最新推荐文章于 2025-03-20 09:05:12 发布

原创最新推荐文章于 2025-03-20 09:05:12 发布

· 232 阅读

0 ·

版权

文章标签：

#数据库

ShardingSphere 使用聚合函数的问题记录

问题一
- 二级目录
- - 三级目录

问题一

只使用聚合函数，ShardingSphere是支持的，结果集只返回一条数据

2025-01-20 00:55:34.881  INFO 24368 --- [io-18080-exec-2] ShardingSphere-SQL                       : Logic SQL: SELECT MAX(date) FROM t_stock_avg WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC
2025-01-20 00:55:34.881  INFO 24368 --- [io-18080-exec-2] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@61284181,
 tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@40376198), 
 tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@40376198, 
 projectionsContext=ProjectionsContext(startIndex=7, stopIndex=15, distinctRow=false, projections=[AggregationProjection(type=MAX, innerExpression=(date), alias=Optional.empty, derivedAggregationProjections=[], index=-1), 
 DerivedProjection(expression=date, alias=Optional[ORDER_BY_DERIVED_0])]), 
 groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@1d9709af, 
 orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4c87205, 
 paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@274f0f54, containsSubquery=false)
2025-01-20 00:55:34.882  INFO 24368 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT MAX(date) , date AS ORDER_BY_DERIVED_0 FROM t_stock_avg_1 WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC
2025-01-20 00:55:34.882  INFO 24368 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT MAX(date) , date AS ORDER_BY_DERIVED_0 FROM t_stock_avg_5 WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC
2025-01-20 00:55:34.882  INFO 24368 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT MAX(date) , date AS ORDER_BY_DERIVED_0 FROM t_stock_avg_8 WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC

Logic SQL: SELECT MAX(date) FROM t_stock_avg WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC

Actual SQL: test20241221 ::: SELECT MAX(date) , date AS ORDER_BY_DERIVED_0 FROM t_stock_avg_1 WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC
Actual SQL: test20241221 ::: SELECT MAX(date) , date AS ORDER_BY_DERIVED_0 FROM t_stock_avg_5 WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC
Actual SQL: test20241221 ::: SELECT MAX(date) , date AS ORDER_BY_DERIVED_0 FROM t_stock_avg_8 WHERE `code` IN('600015.XSHG','000001.XSHE','000008.XSHE') ORDER BY date DESC

使用了聚合函数，外边嵌套了其他函数，返回的结果是多条数据

2025-01-20 01:48:57.603  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Logic SQL: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd
2025-01-20 01:48:57.603  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@6a0ba5ca, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@197d27c1), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@197d27c1, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=39, distinctRow=false, projections=[ExpressionProjection(expression=DATE_FORMAT(max(date),'%Y-%m-%d'), alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@18244aa4, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@7e8c203c, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@491effd2, containsSubquery=false)
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_0
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_1
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_2
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_3
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_4
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_5
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_6
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_7
2025-01-20 01:48:57.604  INFO 16900 --- [io-18080-exec-2] ShardingSphere-SQL                       : Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_8

Logic SQL: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd

Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_0
......
......
Actual SQL: test20241221 ::: SELECT DATE_FORMAT(max(date),'%Y-%m-%d') FROM t_stock_macd_8