MariaDB [test]> select seq, avg(seq) over () from ( select seq from seq_1_to_5 ) as a ;
|
+-----+------------------+
|
| seq | avg(seq) over () |
|
+-----+------------------+
|
| 1 | 3.0000 |
|
| 2 | 3.0000 |
|
| 3 | 3.0000 |
|
| 4 | 3.0000 |
|
| 5 | 3.0000 |
|
+-----+------------------+
|
5 rows in set (0,001 sec)
|
|
MariaDB [test]> select seq, avg(seq) over () from ( select seq, rand() from seq_1_to_5 ) as a ;
|
+-----+------------------+
|
| seq | avg(seq) over () |
|
+-----+------------------+
|
| 0 | 5.0000 |
|
| 0 | 5.0000 |
|
| 0 | 5.0000 |
|
| 0 | 5.0000 |
|
| 0 | 5.0000 |
|
+-----+------------------+
|
5 rows in set (0,001 sec)
|
MariaDB [test]> explain extended select seq, avg(seq) over () from ( select seq, rand() from seq_1_to_5 ) as a ;
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary |
|
| 2 | DERIVED | seq_1_to_5 | index | NULL | PRIMARY | 8 | NULL | 5 | 100.00 | Using index |
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|
2 rows in set, 1 warning (0,001 sec)
|
|
Note (Code 1003): /* select#1 */ select `a`.`seq` AS `seq`,avg(`a`.`seq`) over () AS `avg(seq) over ()` from (/* select#2 */ select `test`.`seq_1_to_5`.`seq` AS `seq`,rand() AS `rand()` from `test`.`seq_1_to_5`) `a`
|
MariaDB [test]> explain extended select seq, avg(seq) over () from ( select seq from seq_1_to_5 ) as a ;
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|
| 1 | SIMPLE | seq_1_to_5 | index | NULL | PRIMARY | 8 | NULL | 5 | 100.00 | Using index; Using temporary |
|
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+
|
1 row in set, 1 warning (0,000 sec)
|
|
Note (Code 1003): select `test`.`seq_1_to_5`.`seq` AS `seq`,avg(`test`.`seq_1_to_5`.`seq`) over () AS `avg(seq) over ()` from `test`.`seq_1_to_5`
|
|