Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.6.5, 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
-
Ubuntu
Description
It's not possible to use a named WINDOW with the MEDIAN() window function.
This does not work, but should. It gets 1064 near 'types AS med
FROM items
WINDOW types AS (PARTITION BY type)
)
SEL...' at line 7 */
CREATE OR REPLACE TEMPORARY TABLE items (type INTEGER, value INTEGER); |
INSERT INTO items (type, value) VALUES (1,1), (1,3), (1,4), (2,10), (2,32), (2,42); |
WITH descriptive_statistics AS ( |
SELECT TYPE, |
ROW_NUMBER() OVER types AS rownum, |
MIN(value) OVER types AS MIN, |
MAX(value) OVER types AS MAX, |
AVG(value) OVER types AS AVG, |
MEDIAN(VALUE) OVER types AS med // <-gacks here. |
FROM items |
WINDOW types AS (PARTITION BY type) |
)
|
SELECT VERSION(), TYPE, MIN, MAX, AVG, med |
FROM descriptive_statistics |
WHERE rownum = 1 |
This does work:
CREATE TEMPORARY TABLE items (type INTEGER, value INTEGER); |
INSERT INTO items (type, value) VALUES (1,1), (1,3), (1,4), (2,10), (2,32), (2,42); |
WITH descriptive_statistics AS ( |
SELECT TYPE, |
ROW_NUMBER() OVER types AS rownum, |
MIN(value) OVER types AS MIN, |
MAX(value) OVER types AS MAX, |
AVG(value) OVER types AS AVG, |
MEDIAN(VALUE) OVER (PARTITION BY type) AS Med |
FROM items |
WINDOW types AS (PARTITION BY type) |
)
|
SELECT VERSION(), TYPE, MIN, MAX, AVG, med |
FROM descriptive_statistics |
WHERE rownum = 1 |
Attachments
Issue Links
- relates to
-
MDEV-12985 support percentile and median window functions
-
- Closed
-
-
-
- Open
-