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屋!