Oracle聚合函数返回组内随机值的方法？

Question

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

Oracle中是否有一个聚合函数可以从一组中返回一个随机值？或者有什么技巧可以实现这一点吗？

例如，给定表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

显然，最后一列是该组中的任意随机值。

- tpdi

---

Gary，因为我想要在[min，max]之间的一些随机值，而不是中位数。 - tpdi

Answer 1

您可以尝试类似以下的方法

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
/

在同一组内，将值以随机顺序排序并选择第一个。我可以想到其他方法，但都没有这种效率高。

- josephj1989

---

哇，我本来以为 "order by dbms_random.value" 会被解释为列号。但是测试表明这可行。谢谢。 - tpdi

Answer 2

你可以在想要提取随机元素的列前面添加一个随机字符串，然后选择该列的min()元素并去掉前缀字符串。

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)

以此方式，您可以避免使用聚合函数和指定两次group by，这在查询非常复杂/或者您正在手动输入带有长且变化的group by列列表的查询时可能非常有用。

- mic.sca