代码之家  ›  专栏  ›  技术社区  ›  ercan

使用row_number()随机获取记录时出现问题(SQL Server 2005)

  •  2
  • ercan  · 技术社区  · 14 年前

    我想从一张表中随机获得1000条记录,因此我使用:

    SELECT top 1000 
           mycol1
         , mycol2
         , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
    FROM mytable
    

    但是,我不想看到 rn 在我的结果集中,我这样做:

    SELECT mycol1
         , mycol2
    FROM (
        SELECT top 1000 
               mycol1
             , mycol2
             , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
        FROM mytable
    ) a
    

    当我这样做时,结果不再随机出现。他们就像我刚说的10000强 没有 使用row_number()随机化。

    当我将查询更改为

    SELECT mycol1
         , mycol2
         , rn
    FROM (
        SELECT top 1000 
               mycol1
             , mycol2
             , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
        FROM mytable
    ) a
    

    它们又是随机的。

    我想SQL Server会做一些优化,说“嘿,这家伙不需要这个列 不管怎样,那就忽略它吧”。但在这种情况下,这会导致意外的行为。有什么办法可以避免这种情况吗?

    PS:我使用了row_number()技巧,因为myTable有10兆。行和

    SELECT top 10000 *
    FROM mytable
    ORDER BY NEWID()
    

    永远运行,而使用row_number()只需30秒。

    3 回复  |  直到 12 年前
        1
  •  2
  •   Robin Vessey    14 年前

    你也可以尝试在一些小的where子句中使用rn字段,比如

    其中,外部查询中的rn>0可能会强制编译器使rn字段通过。

    另外,我认为,如果你想随机抽取数百万条记录的样本,你的整体查询将是一个问题。这只会抓取“第一次离开磁盘”的记录块,这些记录虽然不能保证是相同的,但往往会超过不相同的10000条。

    我建议在最小值(primarykey)和最大值(primarykey)之间创建一组10000个随机数,然后在(…)中执行where primarykey或类似操作。

        2
  •  1
  •   TFD    14 年前

    添加类似 其中rn不为空 到外部查询,所以 它包含在查询计划中,但未进行优化

        3
  •  0
  •   Cymricus    12 年前

    我也在同一个问题上挣扎。我用交叉应用和Top解决了这个问题。记住,交叉应用将我的外部表拉入派生表的范围,我知道必须有一种方法来做到这一点。

    以下代码将根据制造商添加3(*)个随机相关产品。

    INSERT INTO     ProductGroup (
                        ParentId,
                        ChildId
                    )
    SELECT          DISTINCT
                    P.ProductId,
                    CandidateInner.ChildId
    FROM            ProductRelated PR 
    JOIN            Product P
    ON              PR.ChildId = P.ProductId
    CROSS APPLY     
                    (   
                        SELECT      DISTINCT TOP 3
                                    NewId() AS RandId,
                                    Product.ManufacturerId,
                                    ProductRelated.ChildId 
                        FROM        ProductRelated 
                        JOIN        Product 
                        ON          Product.ProductId = ProductRelated.ChildId
                        WHERE       ManufacturerId IS NOT NULL
                        AND         Product.ManufacturerId = P.ManufacturerId
                        ORDER BY    NewId()
                    ) CandidateInner
    LEFT JOIN       (
                        SELECT      DISTINCT TOP 100 PERCENT
                                    ParentId,
                                    COUNT(DISTINCT ChildId) AS Ct
                        FROM        ProductGroup 
                        GROUP BY    ParentId
                        HAVING      COUNT(DISTINCT ChildId) >= 3
                    ) AlreadyGrouped
    ON              P.ProductId = AlreadyGrouped.ParentId
    WHERE           P.ProductId <> CandidateInner.ChildId
    AND             AlreadyGrouped.ParentId IS NULL
    ORDER BY        P.ProductId
    

    *请注意,在以下两种情况下,插入的值将少于3:

    1)如果制造商有3种相关产品 2)(有问题)其中,随机前三名返回相同的产品。

    (1)以上是不可避免的。

    我处理上述(2)的方法是运行两次,然后删除重复项。这仍然不是100%,但从统计上看,这已经足够满足我的要求了。这是一个夜间运行的脚本,但我仍然喜欢快速地将<gt;应用到交叉点之外-任何拉入范围的操作都会导致由制造商联接导致的派生表扫描,即使拉入范围内也意味着(2)不再是一个问题,但它的速度慢得让人头疼,而不是立即应用属性。R指数。