MS Access 2010 SQL按组性能的前N个查询(续2) [英] MS Access 2010 SQL Top N query by group performance issue (continued2)

问题描述

我已改写上一个问题 MS Access 2010 SQL按组性能查询的前N个查询(续)，因为我认为以前没有明确描述上下文.我上一个问题的答案没有提供分组结果的前n名.改写的问题更为笼统.我现在所有数据都放在一个表中.

I have rephrased a previous question MS Access 2010 SQL Top N query by group performance issue (continued) as I believe the context was not clearly described before. The anwwer to my previous question did not provide the top n by group result. The rephrased question is more generic. I have now all data in one table.

这是我的情况:我有一个表格(分析)，其中包含各种类别(类别)的产品(零件号).每个产品都有一个价格(价值).该查询的目的是显示每个类别中价格最高的10种产品.该表包含15000条记录，并且将继续增长.

Here is my situation: I have a table (Analysis) that contains products (Partnumber) of various categories (Category). Every product has a price (Value). The objective of the query is to show the 10 products with the highest price of each category The table contains 15000 records and will continue to grow.

这是查询:

  SELECT 
    a.Location,
    a.Category,
    a.Partnumber
    a.Value

  FROM Analysis a

  WHERE a.Partnumber IN (
    SELECT TOP 10 aa.Partnumber
    FROM Analysis aa
    WHERE aa.Category = a.Category
    ORDER BY aa.Value DESC
  )


  ORDER BY 
    a.Category;

这是我的问题:我当前的查询可以处理表中的1000条记录(响应时间为3秒).使用15000条记录，查询会无限长地运行.如何重建查询以显着提高性能?

Here is my question: My current query works with 1000 records in the table (respond time 3 seconds). With 15000 records the query runs endlessly long. How can I rebuild the query to significantly improve performance?

我之前的问题的答案是不使用列表中的操作.但这消除了按组给出前n条记录的功能.该查询给出了所有记录的前n个.

The answer to my previous question was to not use the in-list operation. But this eliminated function to give the top n records by group. The query gave the top n of all records.

推荐答案

对于名为[Analysis]的表中的示例数据

For sample data in a table called [Analysis]

ID  Location   Category  Partnumber  Value
--  ---------  --------  ----------  -----
 1  here       cat1      part001         1
 2  there      cat1      part002         2
 3  wherever   cat1      part003         3
 4  someplace  cat2      part004         4
 5  nowhere    cat2      part005         5
 6  unknown    cat2      part006         6

排名查询"

SELECT 
    a1.ID,
    a1.Location,
    a1.Category,
    a1.Partnumber,
    a1.Value,
    COUNT(*) AS CategoryRank
FROM
    Analysis a1
    INNER JOIN
    Analysis a2
        ON a1.Category = a2.Category
            AND a1.Value <= a2.Value
GROUP BY
    a1.ID,
    a1.Location,
    a1.Category,
    a1.Partnumber,
    a1.Value

返回

ID  Location   Category  Partnumber  Value  CategoryRank
--  ---------  --------  ----------  -----  ------------
 1  here       cat1      part001         1             3
 2  there      cat1      part002         2             2
 3  wherever   cat1      part003         3             1
 4  someplace  cat2      part004         4             3
 5  nowhere    cat2      part005         5             2
 6  unknown    cat2      part006         6             1

因此，如果您只希望每个类别中的前2个项目，只需将上述查询包装在SELECT ... WHERE中

so if you only want the top 2 items in each category just wrap the above query in a SELECT ... WHERE

SELECT *
FROM
(
        SELECT 
            a1.ID,
            a1.Location,
            a1.Category,
            a1.Partnumber,
            a1.Value,
            COUNT(*) AS CategoryRank
        FROM
            Analysis a1
            INNER JOIN
            Analysis a2
                ON a1.Category = a2.Category
                    AND a1.Value <= a2.Value
        GROUP BY
            a1.ID,
            a1.Location,
            a1.Category,
            a1.Partnumber,
            a1.Value
) AS RankingQuery
WHERE CategoryRank <= 2
ORDER BY Category, CategoryRank

给你

ID  Location  Category  Partnumber  Value  CategoryRank
--  --------  --------  ----------  -----  ------------
 3  wherever  cat1      part003         3             1
 2  there     cat1      part002         2             2
 6  unknown   cat2      part006         6             1
 5  nowhere   cat2      part005         5             2

注意:确保索引[Category]和[Value]字段以获得最佳性能.

Note: Ensure that the [Category] and [Value] fields are indexed for best performance.

这篇关于MS Access 2010 SQL按组性能的前N个查询(续2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！