By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH station AS (
SELECT 1 AS id FROM dual UNION ALL
SELECT 2 FROM dual UNION ALL
SELECT 3 FROM dual UNION ALL
SELECT 4 FROM dual UNION ALL
SELECT 5 FROM dual UNION ALL
SELECT 6 FROM dual
),
cte AS (
SELECT s.*,
ROW_NUMBER() OVER (ORDER BY id) rn,
COUNT(*) OVER () cnt
FROM station s
)
SELECT AVG(1.0*id)
FROM cte
WHERE
(MOD(cnt, 2) = 0 AND rn IN (FLOOR(cnt/2), FLOOR(cnt/2) + 1)) OR
(MOD(cnt, 2) = 1 AND rn = FLOOR(cnt/2) + 1);
| AVG(1.0*ID) |
|---|
| 3.5 |