SQL Server rand()聚合 [英] SQL Server rand() aggregate

问题描述

问题:经纬度坐标表.两行可能具有相同的坐标.我们想要一个查询，该查询返回一组具有唯一坐标的行(在返回的行内).请注意，distinct不可用，因为我需要返回根据定义不同的id列.这种工作(@maxcount是我们需要的行数，intid是唯一的int id列):

Problem: a table of coordinate lat/lngs. Two rows can potentially have the same coordinate. We want a query that returns a set of rows with unique coordinates (within the returned set). Note that distinct is not usable because I need to return the id column which is, by definition, distinct. This sort of works (@maxcount is the number of rows we need, intid is a unique int id column):

select top (@maxcount) max(intid)
from Documents d
group by d.geoLng, d.geoLat

不幸的是，对于给定的坐标，它将总是返回同一行，这对我来说有点可惜.如果只有我们有rand()聚合，我们可以使用它代替max()....请注意，您不能将max()与newid()创建的导航结合使用.

It will always return the same row for a given coordinate unfortunately, which is bit of a shame for my use. If only we had a rand() aggregate we could use instead of max()... Note that you can't use max() with guids created by newid().

有什么想法吗? (如果您感兴趣的话，这里还有更多背景知识: http://www .itu.dk/〜friism/blog/?p = 121 )

Any ideas? (there's some more background here, if you're interested: http://www.itu.dk/~friism/blog/?p=121)

更新:完整解决方案此处

推荐答案

您可能可以通过lat和long的ROW_NUMBER函数为此使用CTE，然后对它使用rand().像这样:

You might be able to use a CTE for this with the ROW_NUMBER function across lat and long and then use rand() against that. Something like:

WITH cte AS
(
    SELECT
        intID,
        ROW_NUMBER() OVER
            (
                PARTITION BY geoLat, geoLng
                ORDER BY NEWID()
            ) AS row_num,
        COUNT(intID) OVER (PARTITION BY geoLat, geoLng) AS TotalCount
    FROM
        dbo.Documents
)
SELECT TOP (@maxcount)
    intID, RAND(intID)
FROM
    cte
WHERE
    row_num = 1 + FLOOR(RAND() * TotalCount)

这将始终返回前一组lat和lngs，但我无法将顺序随机化.也许有人可以继续使用这种方法.不过，它将在匹配的lat和lng组合内为您提供随机行.

This will always return the first sets of lat and lngs and I haven't been able to make the order random. Maybe someone can continue on with this approach. It will give you a random row within the matching lat and lng combinations though.

如果我以后有更多时间，我会尽力解决最后一个障碍.

If I have more time later I'll try to get around that last obstacle.

这篇关于SQL Server rand()聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！