SQL random aggregate

Question

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?

Answer 1

With a custom aggregate function, you could write expressions as simple as:

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

SELECT_AT_RAMDOM would be the custom aggregate function.

Here is precisely an implementation in PostgreSQL.

Answer 2

I think your question is DBMS specific. If your DBMS is MySql, you can use a solution like this:

SELECT place_rand.place, SUM(place_rand.bytes), place_rand.user as random_user
FROM
  (SELECT place, bytes, user
   FROM place
   WHERE ...
   ORDER BY rand()) place_rand
GROUP BY
  place_rand.place;

The subquery orders records in random order. The outer query groups by place, sums bytes, and returns first random user, since user is not in an aggregate function and neither in the group by clause.

Answer 3

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; 

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