使用where子句条件计算中位数- sqlite

Question

我试图计算中位数逗留和平均总支出(Room_Spend + Food_Spend)从下表在sqlite -

CREATE TABLE test (
Stay Int,
Residence Text,
Purpose TEXT,
Room_Spend INT,
Food_Spend INT);

INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (10, 'Italy', 'Business', 5, 5);   
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (2, 'Italy', 'Leisure', 0, 0);  
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (5, 'Italy', 'Leisure', 0, 0);  
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (10, 'Germany', 'Business', 0, 0);  
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (3, 'Germany', 'Business', 1, 1);
INSERT INTO test (Stay, Residence, Purpose, Room_Spend, Food_Spend) VALUES (5, 'Germany', 'Business', 1, 1);

我是新来的sql，这是我有:

SELECT AVG(Stay)
FROM (SELECT stay, Residence
FROM test
ORDER BY stay
LIMIT 2 - (SELECT COUNT(*) FROM test) % 2    -- odd 1, even 2
OFFSET (SELECT (COUNT(*) - 1) / 2))

任何帮助是非常感谢!

Answer 1

一种方法使用解析函数:

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY Stay) rn,
COUNT(*) OVER () AS cnt,
AVG(Room_Spend + Food_Spend) OVER () AS total_spent
FROM test
)
SELECT AVG(Stay) AS Stay, MAX(total_spent) AS total_spent
FROM cte
WHERE rn = (cnt / 2) + 1 AND cnt % 2 = 1 OR
rn IN (cnt / 2, cnt / 2 + 1) AND cnt % 2 = 0;