Tradução da técnica

A técnica para resolver um problema em um RDBMS nem sempre se traduz bem em outro.

Eu tive problemas de desempenho com código semelhante ao que estava no artigo. Assim, meu comentário "Esta técnica vai matar o desempenho no Oracle".

Não vejo CAST(MULTISET())(a versão do Oracle do ARRAY_AGG) sendo muito usada. Como tal, o código pode ser insustentável pelo seu substituto após receber uma promoção.

Traduzindo termos

A tradução fica assim

• STRUCTem BigData é TYPEem Oracle
• ARRAY_AGGem BigData é CAST(MULTISET())em Oracle
• UNNESTem BigData é TABLE()em Oracle
• UNNESTde dados XML está XMLTABLE()no Oracle
• UNNESTdos dados JSON estão JSON_TABLE()no Oracle (12c+)

Você não pode criar a estrutura em tempo real. Você tem que defini-lo antes do tempo.

EXEMPLOS

Criando uma estrutura de uma única linha:

create type emp_t as object (
  EMPNO             NUMBER(4),    
  ENAME             VARCHAR2(10) ,
  JOB               VARCHAR2(9)  ,
  MGR               NUMBER(4)    ,
  HIREDATE          DATE         ,
  SAL               NUMBER(7,2)  ,
  COMM              NUMBER(7,2)  ,
  DEPTNO            NUMBER(2)  
);
/

Criando uma estrutura de um conjunto de linhas com base acima: ( nested table)

create type emp_tt as table of emp_t;
/

UsandoCAST(MULTISET())

select d.deptno
  ,cast(multiset(
           select * from scott.emp e where e.deptno=d.deptno
        ) as emp_tt) as emp_table
from dept d
;

UsandoTABLE()

with data as (
  select d.deptno
    ,cast(multiset(
             select * from scott.emp e where e.deptno=d.deptno
           ) as emp_tt) AS emp_table
  from dept d
)
select b.*
from data a, table( a.emp_table ) b
order by empno;

Solução da Oracle

Requisito de Negócios: Quero a primeira vez que um furacão atingiu sua categoria máxima junto com sua posição.

Outros identificadores inferidos para "GROUP BY" seriam estação, bacia, sub-bacia, além do nome do furacão.

Um método para resolver isso no Oracle é usar o Analytics.

Como não tenho acesso aos dados do furacão, terei que improvisar.

  select deptno as hurricane_name
        ,2017   as season
        ,'NA'   as basin
        ,'WP'   as subbasin
        ,sal    as category
        ,ename  as position
        ,rownum as time
  from scott.emp

A primeira coisa que fazemos é usar a função analítica RANK()para classificar todas as linhas por category(descendente) e time(crescente), mas particionar as classificações por season, basin, subbasinehurricane_name

  select h.*
    ,RANK() over (partition by season, basin, subbasin, huricane_name
                  order by category desc, time)
      as rank_score
  from huricane_data h

Por fim, escolheremos apenas os melhores (rank_score=1)

select *
from analyized_data
where rank_score=1
order by season, basin, subbasin, hurricane_name, time

Juntando tudo

with hurricane_data as (
  select deptno as hurricane_name
        ,2017   as season
        ,'NA'   as basin
        ,'WP'   as subbasin
        ,sal    as category
        ,ename  as position
        ,rownum as time
  from scott.emp
), analyized_data as (
  select h.*
    ,RANK() over (partition by season, basin, subbasin, hurricane_name
                  order by category desc, time) rank_score
  from hurricane_data h
)
select *
from analyized_data
where rank_score=1
-- place season/basin/subbasin filters here
order by season, basin, subbasin, hurricane_name, time;