Oracle aggregate function to return a random value

问题:

The standard SQL aggregate function max() will return the highest value in a group; min() will return the lowest.

Is there an aggregate function in Oracle to return a random value from a group? Or some technique to achieve this?

E.g., given the table foo:

group_id value
1        1
1        5
1        9
2        2
2        4
2        8

The SQL query

select group_id, max(value), min(value), some_aggregate_random_func(value)
from foo
group by group_id;

might produce:

group_id  max(value), min(value), some_aggregate_random_func(value)
1        9            1           1
2        8            2           4

with, obviously, the last column being any random value in that group.

回答1:

You can try something like the following

select deptno,max(sal),min(sal),max(rand_sal) 
from(
select deptno,sal,first_value(sal) 
     over(partition by deptno order by dbms_random.value) rand_sal
from emp)
group by deptno
/

The idea is to sort the values within group in random order and pick the first.I can think of other ways but none so efficient.

回答2:

You might prepend a random string to the column you want to extract the random element from, and then select the min() element of the column and take out the prepended string.

select group_id, max(value), min(value), substr(min(random_value),11)
from (select dbms_random.string('A', 10)||value random_value,foo.* from foo)

In this way you cand avoid using the aggregate function and specifying twice the group by, which might be useful in a scenario where your query is very complicated / or you are just exploring the data and are entering manually queries with a lengthy and changing list of group by columns.