1create table t1 ( 2pk int primary key, 3a int, 4b int, 5c char(10), 6d decimal(10, 3), 7e real 8); 9insert into t1 values 10( 1, 0, 1, 'one', 0.1, 0.001), 11( 2, 0, 2, 'two', 0.2, 0.002), 12( 3, 0, 3, 'three', 0.3, 0.003), 13( 4, 1, 2, 'three', 0.4, 0.004), 14( 5, 1, 1, 'two', 0.5, 0.005), 15( 6, 1, 1, 'one', 0.6, 0.006), 16( 7, 2, NULL, 'n_one', 0.5, 0.007), 17( 8, 2, 1, 'n_two', NULL, 0.008), 18( 9, 2, 2, NULL, 0.7, 0.009), 19(10, 2, 0, 'n_four', 0.8, 0.010), 20(11, 2, 10, NULL, 0.9, NULL); 21select pk, row_number() over () from t1; 22pk row_number() over () 231 1 242 2 253 3 264 4 275 5 286 6 297 7 308 8 319 9 3210 10 3311 11 34explain FORMAT=JSON select pk, row_number() over () from t1; 35EXPLAIN 36{ 37 "query_block": { 38 "select_id": 1, 39 "window_functions_computation": { 40 "sorts": { 41 "filesort": { 42 "sort_key": "pk" 43 } 44 }, 45 "temporary_table": { 46 "table": { 47 "table_name": "t1", 48 "access_type": "index", 49 "key": "PRIMARY", 50 "key_length": "4", 51 "used_key_parts": ["pk"], 52 "rows": 11, 53 "filtered": 100, 54 "using_index": true 55 } 56 } 57 } 58 } 59} 60explain FORMAT=JSON select row_number() over (), pk from t1; 61EXPLAIN 62{ 63 "query_block": { 64 "select_id": 1, 65 "window_functions_computation": { 66 "sorts": { 67 "filesort": { 68 "sort_key": "`row_number() over ()`" 69 } 70 }, 71 "temporary_table": { 72 "table": { 73 "table_name": "t1", 74 "access_type": "index", 75 "key": "PRIMARY", 76 "key_length": "4", 77 "used_key_parts": ["pk"], 78 "rows": 11, 79 "filtered": 100, 80 "using_index": true 81 } 82 } 83 } 84 } 85} 86select row_number() over () from (select 4) as t; 87row_number() over () 881 89select min(a) over (), max(a) over (), a, row_number() over () 90from t1 91where a = 0; 92min(a) over () max(a) over () a row_number() over () 930 0 0 1 940 0 0 2 950 0 0 3 96select a, min(a) over (), max(a) over (), row_number() over () 97from t1 98where a = 0; 99a min(a) over () max(a) over () row_number() over () 1000 0 0 1 1010 0 0 2 1020 0 0 3 103select min(a) over () + 1, max(a) over (), row_number() over () 104from t1 105where a = 0; 106min(a) over () + 1 max(a) over () row_number() over () 1071 0 1 1081 0 2 1091 0 3 110select min(a) over () + a, max(a) over (), row_number() over () 111from t1 112where a = 1; 113min(a) over () + a max(a) over () row_number() over () 1142 1 1 1152 1 2 1162 1 3 117select a + min(a) over (), max(a) over (), row_number() over () 118from t1 119where a = 1; 120a + min(a) over () max(a) over () row_number() over () 1212 1 1 1222 1 2 1232 1 3 124select a + min(a) over () from t1 where a = 1; 125a + min(a) over () 1262 1272 1282 129create view win_view 130as (select a, min(a) over () from t1 where a = 1); 131select * from win_view; 132a min(a) over () 1331 1 1341 1 1351 1 136drop view win_view; 137create view win_view 138as (select a, max(a + 1) over () from t1 where a = 1); 139select * from win_view; 140a max(a + 1) over () 1411 2 1421 2 1431 2 144drop view win_view; 145drop table t1; 146