如何在SQLite中找到第N个百分位数？

Question

我想要找到第N个百分位数。

例如：表格：htwt；列：name，gender，height，weight

结果：

| gender | 90% height | 90% weight |
| male   |        190 |         90 |
| female |        180 |         80 |

- Eric Tan

Answer 1

虽然SQLite在分析处理方面不是很强，但如果你的数据不是很大，你可以尝试使用ORDER BY、LIMIT 1和计算的OFFSET来模拟百分位数。请注意，OFFSET从零开始，所以你需要将其调整为一。

SELECT
  height AS 'male 90% height'
FROM table
WHERE gender='male'
ORDER BY height ASC
LIMIT 1
OFFSET (SELECT
         COUNT(*)
        FROM table
        WHERE gender='male') * 9 / 10 - 1;

- laalto

---

“大数据”是什么意思？在表格中，这里的解决方案适用于多少行数据的范围？您能详细说明一下偏移部分的计算方法吗？ - David

我对数据大小范围一无所知。如果它对你有用，那很好。偏移量是为了选择按高度排序的数据的90%行 - 还有另一个查询来检索“male_count”。 - laalto

Answer 2

我需要多个百分比值（10%，20%...100%），并通过以下方法解决：

WITH p AS (SELECT height, NTILE(10) OVER (ORDER BY height) AS percentile
           FROM table
           WHERE gender = 'male')
SELECT percentile, MAX(height) as height
FROM p
GROUP BY percentile;

这个解决方案需要SQLite 3.28.0或更高版本来支持NTILE窗口函数。

- sasha

---

请注意，此方法仅适用于所有箱子的大小相同或非常接近的情况。例如，我使用ntile（100）将我的表分成了100个箱子，最终在1-55号箱子中有7个样本，在56-100号箱子中有6个样本。因此，我的所有顶部箱子的大小仅为底部箱子的6/7（86％），这是相当大的差异。 - Martin Jambon