查询以显示最大n％的值，并将其余值分组为“其他"值. [英] Query to display largest n% of values and group the rest as "other"

问题描述

我看到有人发布了一些与我希望在Access 2010中做的非常相似的事情.

I see someone posted something very similar to what I am looking to do in Access 2010.

对排名前80％的类别进行分组

我看到了答复，但对所使用的术语和所分配的标题感到困惑.我有5个供应商提供产品.我只关心前80％作为pareto分布，其余的可以归为其他"

I saw the response but am confused as to the nomenclature used and the titles assigned. I have 5 vendors that supply product. I only care about the top 80% as a pareto distribution and the remainder can be grouped as "Other"

4个供应商作为字段[供应商]:A1，A2，A3，A4

4 vendors as Field [vendors]: A1, A2, A3, A4

4个值作为字段[Lbs]:4000、5000、200、800

4 values as Field [Lbs]: 4000, 5000, 200, 800

查询返回:A1，A2，其他

Query returns: A1, A2, Other

感谢任何人都可以提供的帮助.

Thanks for any help anyone can provide.

推荐答案

在执行这样的计算时，我们需要注意出现平局时会发生什么，所以让我们使用以下[VendorData]

When performing calculations like this we need to be mindful of what happens when there is a tie, so let's use the following [VendorData]

vendors  lbs 
-------  ----
A1       2000
A2       3000
A3        200
A4        800
A5       2000
A6       2000

我们首先可以在Access中创建以下名为[VendorPct]的已保存查询

We can start by creating the following saved query named [VendorPct] in Access

SELECT 
    vendors, 
    lbs, 
    lbs_sum,
    lbs / lbs_sum * 100 AS lbs_pct
FROM
    (
        SELECT vendors, lbs, lbs_sum
        FROM
            VendorData,
            (
                SELECT Sum(lbs) AS lbs_sum FROM VendorData
            )
    )

它给了我们

vendors  lbs   lbs_sum  lbs_pct
-------  ----  -------  -------
A1       2000    10000       20
A2       3000    10000       30
A3        200    10000        2
A4        800    10000        8
A5       2000    10000       20
A6       2000    10000       20

现在，我们可以在Access中创建名为[VendorPctCumulative]的保存的查询

Now we can create a saved query in Access named [VendorPctCumulative]

SELECT 
    vendors, 
    Max(lbs) AS lbs_, 
    Max(lbs_pct) as lbs_pct_, 
    Sum(lbs_pct_other) AS lbs_pct_cumulative_
FROM
    (
            SELECT 
                vendors, 
                lbs, 
                lbs_pct, 
                lbs_pct AS lbs_pct_other 
            FROM VendorPct
        UNION ALL
            (
                SELECT 
                    v1.vendors, 
                    v1.lbs,
                    v1.lbs_pct,
                    v2.lbs_pct AS lbs_pct_other 
                FROM
                    VendorPct v1
                    INNER JOIN
                    VendorPct v2
                        ON (v2.lbs = v1.lbs AND v2.vendors < v1.vendors)
                            OR v2.lbs > v1.lbs
            )
    )
GROUP BY vendors
ORDER BY Sum(lbs_pct_other), vendors

产生

vendors  lbs_  lbs_pct_  lbs_pct_cumulative_
-------  ----  --------  -------------------
A2       3000        30                   30
A1       2000        20                   50
A5       2000        20                   70
A6       2000        20                   90
A4        800         8                   98
A3        200         2                  100

现在再保存一个名为[VendorPctCumulativeThreshold]的查询，以查找达到或超过80％阈值的第一个累积百分比:

Now one more saved query named [VendorPctCumulativeThreshold] to find the first cumulative percent that meets or exceeds the 80% threshold:

SELECT TOP 1 lbs_pct_cumulative_ 
FROM 
    (
        SELECT lbs_pct_cumulative_ 
        FROM VendorPctCumulative
        WHERE lbs_pct_cumulative_ >= 80
        ORDER BY lbs_pct_cumulative_
    )

即

lbs_pct_cumulative_
-------------------
                 90

我们可以将其与

    SELECT 
        vendors, 
        lbs_ AS lbs, 
        lbs_pct_ AS lbs_pct
    FROM
        VendorPctCumulative vpc
        INNER JOIN
        VendorPctCumulativeThreshold vpct
            ON vpc.lbs_pct_cumulative_ <= vpct.lbs_pct_cumulative_
UNION ALL
    SELECT 
        "other" AS vendors, 
        Sum(lbs_) AS lbs, 
        Sum(lbs_pct_) AS lbs_pct
    FROM
        VendorPctCumulative vpc
        INNER JOIN
        VendorPctCumulativeThreshold vpct
            ON vpc.lbs_pct_cumulative_ > vpct.lbs_pct_cumulative_
ORDER BY 3 DESC, 1

生产

vendors  lbs   lbs_pct
-------  ----  -------
A2       3000       30
A1       2000       20
A5       2000       20
A6       2000       20
other    1000       10

这篇关于查询以显示最大n％的值，并将其余值分组为“其他"值.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！