Oracle SQL 使用 listaggs 生成随机输出 [英] Oracle SQL generate random output with listaggs

问题描述

我有值 30、31、32 和 33

I have the values 30, 31, 32 and 33

我如何生成一个输出(随机)，其中可能包含该列表的单个值、它们的 listaggs 或空值?

How could I generate a output (random) which may contain single values of that list, listaggs of them or nulls?

<头>

id	val
1	30、31
2	空
3	32
4	33
5	空
6	31、33
7	空
8	30

推荐答案

这里有一种方法 - 准随机生成字符串(使用 ora_hash 来实现)，而在一种完全确定性的、可重复的方式.如果您想获得不同(但相似)的结果，请使用 ora_hash 的第三个参数来提供一个不同于默认值(即 0)的种子.如果每次都想要不同的结果，请提供一个 dbms_random.value() 值作为种子；这仍然只需要一个随机"为整个查询生成的值.您还可以使用上限(在我的示例中为 280)来获得更多或更少的 null(以及更普遍的更短与更长的逗号分隔字符串).

Here is a way - generating the strings quasi-randomly (using ora_hash to do the trick), while in a perfectly deterministic, reproducible way. If you want to get different (but similar) results, use the third argument to ora_hash to provide a seed different from the default (which is 0). If you want different results every time, provide a dbms_random.value() value as the seed; this will still require just one "random" value to be generated for the entire query. You can also play with the upper bound (in my example, 280) to get more or fewer null (and shorter vs. longer comma-separated strings, more generally).

WITH data ( value ) AS (
  SELECT 30 FROM DUAL UNION ALL
  SELECT 31 FROM DUAL UNION ALL
  SELECT 32 FROM DUAL UNION ALL
  SELECT 33 FROM DUAL
),
ids ( id ) AS (
  SELECT LEVEL
  FROM   DUAL
  CONNECT BY LEVEL <= 8
)
select id, 
       ( select listagg(case when ora_hash(id * value, 1000) < 280 
                             then value end, ',')
                        within group(order by value)
         from   data
       ) as vals
from   ids
;

ID VALS           
-- ---------------
 1 33             
 2 32             
 3                
 4 30,32          
 5 30,31          
 6 32             
 7                
 8   

这篇关于Oracle SQL 使用 listaggs 生成随机输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！