SQL aggregate

Question

Let's say I have a simple table with three fields: "place", "user" and "bytes". Let's say that under some filter I want to group a "place" and for each "place", sum all the bytes for that place and randomly select a user for this place (evenly from all users that match the "where" filter and the corresponding "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 could not find such an aggregate function. Am I missing something? What could be a good way to achieve this?

Answer 1

If your RDBMS supports analytic 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() will be examples of something that can be replaced with random_function()

Answer 2

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

 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; 

Subquery entries are recorded in random order. External query groups by place , sums bytes and return the first random user, since the user is not in the aggregated function and not in the group by section.

Answer 3

I would change Martin's solution a bit:

 select place, sum(bytes), max(case when seqnum = 1 then user end) as random_user from (select place, bytes, row_number() over (partition by place order by newid()) as sequm from t ) t group by place 

(Where newid () is just one way to get a random number, depending on the database.)

For some reason, I prefer this approach because it still has an aggregate function in the outer query. If you summarize a bunch of fields, then this seems to me cleaner.

Answer 4

Using the special aggregation function, you can write expressions as simple as:

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

SELECT_AT_RAMDOM will be a custom aggregation function.

This is how the implementation in PostgreSQL .