SQL随机聚合 [英] SQL random aggregate

问题描述

说我有一个简单的表格，其中包含3个字段：位置，用户和字节。假设，在某个过滤器下，我想按地点分组，并针对每个地点，求和该地点的所有字节，然后随机选择该地点的用户（从适合该地点的所有用户中统一选择） 哪里过滤器和相关的地方）。如果有一个从中随机选择聚合函数，我会做：

Say I have a simple table with 3 fields: 'place', 'user' and 'bytes'. Let's say, that under some filter, I want to group by 'place', and for each 'place', to sum all the bytes for that place, and randomly select a user for that place (uniformly from all the users that fit the 'where' filter and the relevant 'place'). If there was a "select randomly from" aggregate function, I would do:

SELECT place, SUM(bytes), SELECT_AT_RANDOM(user) WHERE .... GROUP BY place;

...但是我找不到这样的聚合函数。我想念什么吗？

...but I couldn't find such an aggregate function. Am I missing something? What could be a good way to achieve this?

推荐答案

如果RDBMS支持解析功能。

If your RDBMS supports analytical functions.

WITH T
     AS (SELECT place,
                Sum(bytes) OVER (PARTITION BY place) AS Sum_bytes,
                user,
                Row_number() OVER (PARTITION BY place ORDER BY random_function()) AS RN
         FROM   YourTable
         WHERE  .... )
SELECT place,
       Sum_bytes,
       user
FROM   T
WHERE  RN = 1; 

对于SQL Server Crypt_gen_random（4）或 NEWID（）可以代替 random_function（）

For SQL Server Crypt_gen_random(4) or NEWID() would be examples of something that could be substituted in for random_function()

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