doris 版本3.0.3
explain
SELECT MAX(CASE WHEN row_num = 0 THEN close_price END) AS day_close,
MAX(CASE WHEN row_num = 0 + 1 THEN close_price END) AS last_day_close,
MAX(CASE WHEN row_num = 0 THEN time END) AS day_close_time,
MAX(CASE WHEN row_num = 0 + 1 THEN time END) AS last_day_close_time
FROM
( SELECT close_price,
ROW_NUMBER() OVER (
ORDER BY time DESC) AS row_num,
time FROM tb_quote_realtime_302 WHERE symbol = 'TSLA'
and exchange_id = 302
AND date >= DATE_SUB(CURDATE(), INTERVAL 10 DAY)
AND time >= DATE_SUB(NOW(), INTERVAL 10 DAY)
and trading_session_type = '2'
LIMIT 7 ) t
WHERE
row_num BETWEEN 0 AND 1
PLAN FRAGMENT 0
OUTPUT EXPRS:
day_close[#45]
last_day_close[#46]
day_close_time[#47]
last_day_close_time[#48]
PARTITION: UNPARTITIONED
HAS_COLO_PLAN_NODE: true
VRESULT SINK
MYSQL_PROTOCAL
8:VAGGREGATE (merge finalize)(505)
| output: max(partial_max(if((row_num = 0), close_price, NULL))[#41])[#45], max(partial_max(if((row_num = 1), close_price, NULL))[#42])[#46], max(partial_max(if((row_num = 0), time, NULL))[#43])[#47], max(partial_max(if((row_num = 1), time, NULL))[#44])[#48]
| group by:
| sortByGroupKey:false
| cardinality=1
| distribute expr lists:
|
7:VAGGREGATE (update serialize)(501)
| output: partial_max(if((row_num = 0), close_price, NULL)[#37])[#41], partial_max(if((row_num = 1), close_price, NULL)[#39])[#42], partial_max(if((row_num = 0), time, NULL)[#38])[#43], partial_max(if((row_num = 1), time, NULL)[#40])[#44]
| group by:
| sortByGroupKey:false
| cardinality=1
| distribute expr lists:
|
6:VSELECT(483)
| predicates: (row_num[#28] >= 0), (row_num[#28] <= 1)
| final projections: time[#29], close_price[#30], row_num[#31], if((row_num = 0)[#33], close_price[#30], NULL), if((row_num = 0)[#33], time[#29], NULL), if((row_num = 1)[#32], close_price[#30], NULL), if((row_num = 1)[#32], time[#29], NULL)
| final project output tuple id: 6
| intermediate projections: time[#27], close_price[#26], row_num[#28], (row_num[#28] = 1), (row_num[#28] = 0)
| intermediate tuple id: 5
|
5:VEXCHANGE
offset: 0
limit: 7
PLAN FRAGMENT 1
PARTITION: UNPARTITIONED
HAS_COLO_PLAN_NODE: false
STREAM DATA SINK
EXCHANGE ID: 05
UNPARTITIONED
4:VANALYTIC(468)
| functions: [row_number()]
| order by: time[#27] DESC NULLS LAST
| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| limit: 7
| distribute expr lists:
|
3:VMERGING-EXCHANGE
offset: 0
distribute expr lists:
PLAN FRAGMENT 2
PARTITION: RANDOM
HAS_COLO_PLAN_NODE: false
STREAM DATA SINK
EXCHANGE ID: 03
UNPARTITIONED
2:VSORT(453)
| order by: time[#27] DESC
| algorithm: full sort
| offset: 0
| distribute expr lists:
|
1:VPartitionTopN(448)
| functions: row_number
| order by: time[#25] DESC
| has global limit: true
| partition limit: 7
| partition topn phase: TWO_PHASE_LOCAL_PTOPN
| distribute expr lists:
|
0:VOlapScanNode(433)
TABLE: chief_quote.tb_quote_realtime_302(tb_quote_realtime_302), PREAGGREGATION: ON
PREDICATES: ((((symbol[#2] = 'TSLA') AND (CAST(exchange_id[#1] AS double) = 302)) AND ((date[#0] >= '2025-11-29') AND (time[#17] >= '2025-11-29 13:04:29'))) AND ((trading_session_type[#3] = 2) AND (DORIS_DELETE_SIGN[#18] = 0)))
partitions=3/25 (p20251124000000,p20251201000000,p20251208000000)
tablets=3/30, tabletList=97516976,101825180,107002244
cardinality=608514, avgRowSize=99.95763, numNodes=1
pushAggOp=NONE
final projections: close_price[#4], time[#17]
final project output tuple id: 1
========== STATISTICS ==========
planed with unknown column statistics
偶发，本身表的总数据量不大3831993，出现时引起大量的sql执行报Nereids cost too much time
