MS Access按类别计算金额10百分位的查询优化求助

嘿，我完全懂你遇到的糟心情况——按类别分组计算百分位，原查询能用但慢到离谱，换了方式又得不到按类别划分的结果，确实头疼！先把你的需求和问题再理一遍：

我有一个MS Access表，包含Category（类别）和DollarAmount（金额）字段。我想做一个查询，按Category分组，然后显示每个类别下DollarAmount的第10百分位。

我用了下面的SQL，它能正常运行，但速度奇慢（我猜是因为子查询的原因）。我试过拆分查询，但那样得到的是整个数据集的第10百分位，而不是按类别计算的。有没有其他办法？

你的原查询代码：

SELECT
tblComplaints.Category,
Max(tblComplaints.[DollarAmount]) AS 10thPercentileCalc
FROM tblComplaints
WHERE (((tblComplaints.[DollarAmount])
In (SELECT TOP 10 PERCENT [DollarAmount]
FROM tblComplaints AS sub
WHERE sub.Category= tblComplaints.Category
ORDER BY [DollarAmount] ASC)))
GROUP BY tblComplaints.Category;

优化方案推荐 #

1. 用窗口函数实现（适合Access 2016及以上版本）

窗口函数是Access较新版本支持的特性，它可以一次性完成分组排序、计数的操作，比嵌套子查询效率高很多。核心思路是给每个类别下的金额按升序编号，同时计算每个类别的总记录数，然后找到对应第10百分位位置的金额：

SELECT 
    Category,
    DollarAmount AS 10thPercentileCalc
FROM (
    SELECT 
        Category,
        DollarAmount,
        -- 按类别分组，给金额升序编行号
        ROW_NUMBER() OVER (PARTITION BY Category ORDER BY DollarAmount ASC) AS RowNum,
        -- 计算每个类别的总记录数
        COUNT(*) OVER (PARTITION BY Category) AS TotalRows
    FROM tblComplaints
) AS RankedData
-- 筛选出对应第10百分位的行（CEILING取上整，和你原逻辑的TOP 10%最大值匹配）
WHERE RowNum = CEILING(TotalRows * 0.1)
GROUP BY Category, DollarAmount;

2. 给字段建索引（必做优化）

不管用哪种查询方式，给Category和DollarAmount字段创建联合索引，能大幅提升分组、排序的速度：

• 打开Access表的设计视图
• 点击「索引」按钮
• 创建一个新索引，设置索引字段为Category和DollarAmount（顺序可以先Category后DollarAmount）
• 保存后再运行查询，速度会有明显提升

3. 旧版本Access兼容方案（如果无法用窗口函数）

如果你用的是旧版Access（不支持窗口函数），可以尝试用关联子查询计算每个记录在所属类别中的排名，但记得一定要建索引，不然速度还是会慢：

SELECT 
    main.Category,
    Min(main.DollarAmount) AS 10thPercentileCalc
FROM tblComplaints AS main
WHERE (
    SELECT COUNT(*) 
    FROM tblComplaints AS sub
    WHERE sub.Category = main.Category AND sub.DollarAmount <= main.DollarAmount
) <= (
    SELECT COUNT(*) * 0.1 
    FROM tblComplaints AS sub
    WHERE sub.Category = main.Category
)
GROUP BY main.Category;

为什么原查询慢？ #

你的原查询里，外层每一条记录都会触发一次子查询去取对应类别的TOP 10%金额，相当于做了N次重复查询（N是总记录数），数据量一大就会特别卡。而窗口函数是一次性扫描整张表完成所有计算，效率自然高很多。

备注：内容来源于stack exchange，提问作者user23471091