51 lines
1.6 KiB
Plaintext
51 lines
1.6 KiB
Plaintext
# PRN in range(0,n)
|
|
create or replace function prn(n) as
|
|
trunc(random() * n)::BIGINT;
|
|
|
|
create or replace function random_array(n, twenty) as
|
|
list_transform(range(0,n), x-> 1+prn(twenty));
|
|
|
|
create or replace function random_matrix(m, n, twenty) as (
|
|
list_transform(range(0,m), x -> random_array(n, twenty))
|
|
);
|
|
|
|
# Scanning the matrix row-wise, return a list of the cells up to
|
|
# but excluding n, or else [].
|
|
# WARNING: The matrix `matrix` should be materialized before entry!
|
|
# Note the type declaration for `matrix`.
|
|
create or replace function matrix_up_to(matrix, n) as (
|
|
if (length(matrix) = 0, [],
|
|
(with recursive matrix_t as (select matrix::BIGINT[][] as m),
|
|
cte1 as (
|
|
select 1 as ix,
|
|
coalesce(list_position(m[1], n), 0) as p,
|
|
[] as s
|
|
from matrix_t
|
|
union all
|
|
select ix+1 as ix,
|
|
if (p = 0, coalesce(list_position(m[ix+1], n), 0),
|
|
-1) as p,
|
|
(s || if (@p = 1, [],
|
|
if (p>0, m[ix][:p-1],
|
|
m[ix] ) )) as s
|
|
from cte1, matrix_t
|
|
where p != -1 and
|
|
ix <= length(m)
|
|
)
|
|
-- for verification: select ((last(s), any_value(matrix)))
|
|
select last(s)
|
|
from cte1
|
|
) )
|
|
);
|
|
|
|
.mode list
|
|
.header off
|
|
|
|
# Because of the way DuckDB inlines functions, the matrix produced
|
|
# by random_matrix() must be materialized before it can be properly handled.
|
|
# Using DuckDB V1.1, this can be done using SET VARIABLE:
|
|
|
|
set variable matrix = random_matrix(10,5, 20);
|
|
|
|
select matrix_up_to( getvariable('matrix'), 20 ) as head;
|