select max(t1.id) from
(
select id ,row_number() over(order by id desc)+id as nu from ods_yalla__rechargelog_incr where id <=49586518
)t1 where t1.nu != 49586519
使用开窗函数 ,查询出的结果会变已经出现了十几个不同的结果了 ? 是开窗函数不能用 还是什么原因?
exlan 执行计划 PLAN FRAGMENT 0
OUTPUT EXPRS:19: max
PARTITION: UNPARTITIONED
RESULT SINK
8:AGGREGATE (merge finalize)
| output: max(19: max)
| group by:
|
7:EXCHANGE
PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: UNPARTITIONED
STREAM DATA SINK
EXCHANGE ID: 07
UNPARTITIONED
6:AGGREGATE (update serialize)
| output: max(1: Id)
| group by:
|
5:Project
| <slot 1> : 1: Id
|
4:SELECT
| predicates: 17: row_number() + 1: Id != 49586519
|
3:ANALYTIC
| functions: [, row_number(), ]
| order by: 1: Id DESC
| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
2:MERGING-EXCHANGE
PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: RANDOM
STREAM DATA SINK
EXCHANGE ID: 02
UNPARTITIONED
1:SORT
| order by: <slot 1> 1: Id DESC
| offset: 0
|
0:OlapScanNode
TABLE: ods_yalla__rechargelog_incr
PREAGGREGATION: ON
PREDICATES: 1: Id <= 49586518
partitions=32/515
rollup: ods_yalla__rechargelog_incr
tabletRatio=256/256
tabletList=3169217,3169221,3169225,3169229,3169233,3169237,3169241,3169245,3169019,3169023 …
cardinality=964113
avgRowSize=8.0
numNodes=0
表内是不是有数据不断在导入呢？辛苦跑下analyze full table table_name;然后再重新看下结果吧。
发个explain costs
表数据 没有新增,任务停了测试 也是不准的
这个id是不是有重复的?