代码之家  ›  专栏  ›  技术社区  ›  Sören Kuklau Keith Boynton

平衡受前100名限制的不同类型的结果记录

  •  1
  • Sören Kuklau Keith Boynton  · 技术社区  · 15 年前

    给一张桌子 Records 用柱子 id int , Type int Name varchar(50)

    SELECT id, Type, Name
    FROM Records
    WHERE Name LIKE '%Foo%'
    

    为了稍微调整性能,我只想给出有限的结果;目前仅通过添加 TOP 100 我同意这项声明。但是,这可能会导致某些类型的记录表示不足或根本不表示,如以下查询所示:

    SELECT Type, COUNT(Type) FROM
        (SELECT id, Type, Name
        FROM Records
        WHERE Name LIKE '%Foo%') x
    GROUP BY Type
    ORDER BY Type
    

    没有 前100名 ,我可能会得到:

    42  5
    49  1
    50  1
    52  1
    59  1
    76  40
    87  567
    90  3
    

    有了它:

    42  5
    49  1
    50  1
    52  1
    59  1
    76  26
    87  65
    

    这可能导致用户得出结论,没有类型为的记录 90 存在。

    我更喜欢 TOP 表现不同:至少给我一个结果 任何 键入有一些的,然后继续添加到其中,直到达到计数。例如。, 42 76 87 结果会更少,但是 我会出现的。

    理想情况下,我还希望为用户提供一个“x更多此类结果”的UI元素。

    我必须放弃吗 顶部 为了达到这个目的?

    3 回复  |  直到 15 年前
        1
  •  3
  •   Shannon Severance    15 年前
    WITH RecordsWithRn AS (
    SELECT id, Type, Name,
       ROW_NUMBER() OVER (PARTITION BY Type ORDER BY ... intra-type ordering ...) as rn
            FROM Records
            WHERE Name LIKE '%Foo%') 
    SELECT TOP 100 id, Type, Name
       FROM RecordsWithRn
       ORDER BY RN, ... inter-type ordering ...
    

    这会给你一百张唱片。假设少于100种类型,每种类型至少有一种。使用ROW_NUMBER()的ORDER BY来控制类型中记录的ROW_编号顺序。最后的ORDER BY,ORDER BY先前指定的行号,然后添加其他条件(如果愿意),以控制每个行号处类型之间的记录顺序。

    要获取未显示类型的记录数,请执行以下操作:

    WITH RecordsWithRn AS (
       SELECT id, Type, Name,
          ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type) as rn,
          COUNT(*) OVER (PARTITION BY Type) as CountType
       FROM Records
       WHERE Name LIKE '%Foo%')
    , Top100Records as (
       SELECT TOP 100 id, Type, Name, CountType
       FROM RecordsWithRn
       ORDER BY RN)
    select Id, Type, Name, 
        CountType - (COUNT(*) over (PARTITION BY Type)) as CountTypeNotIncluded
    from Top100Records
    
        2
  •  1
  •   Sören Kuklau Keith Boynton    15 年前
    WITH cte AS (
    SELECT id, Type, Name,
       ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type) as rn
            FROM Records
            WHERE Name LIKE '%Foo%') 
    SELECT *
       FROM cte
       WHERE rn <= 100;
    

    每种类型最多可获得100条记录。请注意,这并不意味着查询速度会更快,事实上它可能会更慢。它取决于可用的索引、表中的数据以及客户端处理结果的速度。

    WITH totals AS (
        SELECT Type, COUNT(*) AS count
                FROM Records
                WHERE Name LIKE '%Foo%'
         GROUP BY Type) 
    SELECT * FROM totals;
    

    然后将两个结果合并:

    WITH cte AS (
        SELECT id, Type, Name,
           ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type) as rn
                FROM Records
                WHERE Name LIKE '%Foo%')
    , totals AS (
            SELECT Type, COUNT(*) AS count
                    FROM Records
                    WHERE Name LIKE '%Foo%'
             GROUP BY Type) 
        SELECT *
           FROM cte c 
           JOIN totals t on c.Type = t.Type
           WHERE c.rn <= 100;
    
        3
  •  0
  •   Michael Todd    15 年前

    你可以打两个电话。第一个是[type]和count([type])。这将提供一个独特的 全部的 可用类型以及它们的计数,以向用户提供更多信息。之后,根据用户的请求按类型进行调用。