Oracle聚合函数返回组的随机值? [英] Oracle aggregate function to return a random value for a group?

问题描述

标准SQL聚合函数max()将返回组中的最大值； min()将返回最低值.

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

Oracle中是否有聚合函数来从组中返回随机值?还是一些技巧来实现这一目标?

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

例如，给定表foo:

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

SQL查询

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

可能产生:

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.

推荐答案

您可以尝试以下操作

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.

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