我正在嘗試從下面的 sqlite 表格中計算平均逗留時間和平均總花費（Room_Spend Food_Spend） -
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))
任何幫助都非常感謝！
uj5u.com熱心網友回復：
一種方法使用分析函式：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/311410.html
上一篇：有什么方法可以獲取由于執行單個陳述句而失敗的特定外鍵約束？
下一篇：如何將資料從一列復制到另一列？