sqlite - 使用 where 子句条件计算中位数 - sqlite
问题描述
我正在尝试从 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))
任何帮助都非常感谢!
解决方案
一种方法使用分析函数:
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;
推荐阅读
- c# - 如何成功地将双精度转换为字符串
- android - Android 中的 OnDemand 功能下载 SDK 大于 20MB?
- c# - 在 winforms 应用程序中从 Progarm.cs 运行控制台
- javascript - JavaScript 数组处理:我做错了什么?
- c++ - 使用 shortand 运算符进行字符串连接有区别吗?
- javascript - 猫鼬 findByIdAndUpdate 不起作用。{new: true} 包括
- spring - Spring 反应式中的 MonoProcessor 用于什么?
- oracle-apex - 在 Oracle APEX 19.2 中使用交互式网格堆叠主详细信息
- javascript - 在 pug 模板上使用 li 内的强标签
- ios - 通过上传工具上传缺少的所需 dSYM