Funções Lambda no SQL ... vamos pensar

Sobre o que será o artigo e, portanto, o nome implica.

Além disso, o autor explicará por que isso é necessário do ponto de vista dele, além de dizer que o SUBJ não é apenas uma tecnologia da moda, mas também "um negócio duplamente necessário - agradável e útil".

É sempre interessante ver como várias pessoas talentosas fazem algo (uma linguagem de programação, por que não), sabendo exatamente que problema estão resolvendo e que tarefas definem para si mesmas. E também testar sua criação em si mesmos. Não pode ser comparado com as criações monumentais de comitês gigantes, que colocam a manutenção da harmonia do universo em primeiro plano e quem a entende.

Compare, por exemplo, o destino de FORTRAN e PL / 1 . Quem agora se lembrará deste PL / 1.

Deste ponto de vista, o AWK , por exemplo, é muito bem-sucedido. Vale a pena dizer que em seu nome A é Alfred Aho , um dos autores de Dragon Book , W é Peter Weinberger , que participou do Fortran-77, K é Brian Kernigan , onde estaria sem ele. O idioma é destinado ao processamento de fluxos de texto dinâmicos em pipes entre processos.

A linguagem não tem tipo ( isso não é totalmente verdade ), sua sintaxe é muito semelhante a C, possui recursos de filtragem, matrizes associativas, eventos de início / fim de fluxo, evento de nova linha ...

O autor sempre ficou impressionado com essa linguagem também pelo fato de que seu intérprete não precisa ser instalado; em sistemas semelhantes ao UNIX, ele está sempre lá; no Windows, basta copiar o arquivo executável e tudo funciona. No entanto, este não é o caso.

No processo, o autor precisa usar o pacote SQL + AWK com bastante frequência, e é por isso. O SQL ainda é uma linguagem inicialmente declarativa projetada para controlar os fluxos de dados. Ele oferece oportunidades muito limitadas para trabalhar com o contexto de execução de consultas na forma de funções agregadas.

Como, por exemplo, criar um histograma bidimensional usando SQL?

--   100 x 100 SELECT count(), round(x, -2) AS cx, round(y, -2) AS cy FROM samples GROUP BY cx, xy 

Mas, digamos, usar GROUP BY implica classificação e não é um prazer barato se você tiver centenas de milhões (ou mais) de linhas.
Resumo: O processador SQL recorre à classificação quando não pode estimar o número de grupos após GROUP BY. No entanto, o agrupamento por valores calculados é (geralmente) exatamente o caso.

Portanto, você precisa fazer algo como:

 psql -t -q -c 'select x, y from samples' | gawk -f mk_hist2d.awk 

onde mk_hist2d.awk acumula estatísticas na matriz associativa e as exibe após a conclusão do trabalho

 # mk_hist2d.awk { bucket[int($2*0.01), int($3*0.01)]+=$1; } END { for (i=0; i < 500; i++) for (j=0; j < 500; j++) { if ((i, j) in bucket) print i*100." "j*100." "bucket[i, j]; else print i*100." "j*100." 0"; } } 

Há um MAS - o fluxo de dados completo deve ser enviado do servidor para a máquina em funcionamento, e isso não é tão barato.

É possível combinar de alguma forma o agradável com o útil - acumular estatísticas durante a execução da consulta SQL, mas sem recorrer à classificação? Sim, por exemplo, usando funções agregadas personalizadas.

Funções agregadas personalizadas

Subj está presente em diferentes sistemas, em todos os lugares é feito um pouco à sua maneira.

1. PostgreSQL A documentação está aqui . Mais detalhes aqui .
   É aqui que o saldo máximo da conta é calculado.
   E este é um exemplo que calcula o que há mais na coluna booleana - verdadeiro ou falso.
   
   Parece assim -
   
   

    CREATE AGGREGATE mode(boolean) ( SFUNC = mode_bool_state, STYPE = INT[], FINALFUNC = mode_bool_final, INITCOND = '{0,0}' ); 

   
   Aqui SFUNC é uma função que é chamada para todas as linhas no fluxo,
   o primeiro argumento é do tipo STYPE .
   
   FINALFUNC é usado para finalizar os cálculos e retorna o valor do agregado.
   INITCOND - inicialização do valor inicial do estado interno ( STYPE ), passado como o primeiro argumento.
   Dado que as funções podem ser escritas em C (o que significa que, para o estado interno, você pode usar a memória que é liberada automaticamente quando você fecha a solicitação), essa é uma ferramenta muito poderosa. Fora do escopo de seu uso, ainda é preciso poder ir.
2. MS SQL
   Anteriormente (2000), antes da solicitação, era necessário criar um objeto ActiveX, para agregar usando esse objeto.
   Agora (2016+) isso é feito no ambiente CLR. Você precisará criar uma função personalizada, criar e registrar uma montagem . Então você pode criar um agregado .
   Um exemplo de cálculo da média geométrica e da mesclagem de strings: com parâmetros adicionais e um tipo definido pelo usuário para armazenar um estado intermediário.
3. Oracle
   No Oracle, isso é feito usando o cartucho de dados agregado ODCIA (interface).
   Para criar seu próprio agregado, você precisa escrever um tipo personalizado que implemente 4 métodos
   - initialization (ODCIAggregateInitialize), static, deve criar uma instância do tipo desejado e retornar através do parâmetro
   - iterações (ODCIAggregateIterate), chamadas em cada linha de dados
   - mesclar (ODCIAggregateMerge), usado para mesclar agregados executados em paralelo
   - terminar (ODCIAggregateTerminate) - saída do resultado
   Exemplos: 1 , 2 , 3 , 4 .
4. DB2
   Não há maneira explícita de usar agregados customizados no DB2.
   Mas você pode inserir uma função padrão (embora MAX) em um tipo definido pelo usuário (em Java) e fazer com que o sistema execute consultas no formato
   
   

    CREATE TYPE Complex AS ( real DOUBLE, i DOUBLE ) … CREATE TABLE complexNumbers ( id INTEGER NOT NULL PRIMARY KEY, number Complex ) … SELECT sum..real, sum..i FROM ( SELECT GetAggrResult(MAX(BuildComplexSum(number))) FROM complexNumbers ) AS t(sum) 

O que é digno de nota em todos esses sistemas?

• De uma forma ou de outra, você precisará criar alguns objetos no banco de dados. Seja AGREGADO ou TIPO. No mínimo, são necessários direitos apropriados. E só quero adicionar alguns números no joelho.
  
• Pode ser necessário escrever algo em outra linguagem, seja C, C # ou Java.
  Para integrar o que está escrito no sistema, novamente, são necessários direitos. Mas tudo o que eu quero ...
  
• Dificuldade em inicializar. Suponha que você queira ler histogramas com diferentes tamanhos de cesto. Parece que é mais fácil - indicaremos o INITCOND desejado ao declarar o agregado (PostgreSQL) e todo o negócio. Porém, para cada tamanho da cesta, você precisará de seu próprio agregado, e para isso novamente os direitos são necessários.
  
  Aqui você pode recorrer a um truque sujo e retirar o processador de união da linha de inicialização (encaminhamento) e dos dados, construir o contexto não no construtor, mas quando a primeira linha for recebida.
  
• No entanto, mesmo com as limitações descritas, agregados personalizados permitem calcular qualquer coisa.
  
• É importante que os agregados possam ser paralelizados , pelo menos o PostgreSQL, e o Oracle (Enterprise Edition) pode fazer isso. Para isso, a verdade terá que aprender a serializar / desserializar estados intermediários e também congelá-los recebidos de diferentes fluxos.

Funções da janela

As funções da janela apareceram no padrão SQL: 2003 . No momento, eles são suportados por todos os sistemas acima. Em essência, as funções da janela são uma extensão do trabalho com unidades. E, é claro, funções agregadas personalizadas também funcionam em um contexto de janela.

A extensão é essa. E antes do SQL: 2003, as funções agregadas funcionavam em uma determinada janela, que era o conjunto de resultados inteiro ou sua parte, correspondendo à combinação de valores de campo da expressão GROUP BY. O usuário agora tem alguma liberdade para manipular essa janela.

A diferença é que os valores calculados usando as janelas são adicionados à saída em uma coluna separada e não exigem que todo o fluxo seja recolhido usando funções agregadas. Portanto, em uma solicitação, você pode usar vários agregados de janela, cada um em seu próprio contexto (janela). Antes, havia várias funções agregadas, mas todas funcionavam em uma janela.

Traços grandes

• Mais ()
  a janela é todo o conjunto de resultados. Digamos que a consulta ' select count (1) from Samples ' retorne 169. Nesse caso, executando ' select count (1) over () from Samples ', obtemos uma coluna que é escrita 169 vezes 169 vezes.
• OVER (PARTIÇÃO POR)
  é um análogo de GROUP BY, para cada combinação de valores é criada uma janela na qual as funções agregadas são executadas. Digamos que na tabela Samples uma coluna inteira seja val, os dados são números de 1 a 169.
  Em seguida, a consulta ' selecione count (1) over (partição por (12 + val) / 13) de Samples ' retornará uma coluna na qual o valor 13 é gravado 169 vezes.
  
• OVER (PEDIDO POR)
  pode ser combinado com PARTITION BY, permite alterar dinamicamente o tamanho da janela durante o cursor; nesse caso, a janela se estende do início do grupo à posição atual do cursor. Como resultado, para o grupo, o resultado não é o mesmo na coluna agregada, mas o próprio. Conveniente para calcular montantes acumulados. Resultado da consulta
  'select sum (val) over (order by val) from Samples ' será uma coluna na qual o enésimo elemento conterá a soma dos números naturais de 1 a n.
• SOBRE (ROWS)
  permite definir os quadros da janela, começando na posição do cursor ou no início / final do intervalo ORDER BY.
  
  Por exemplo, ' ... AS LINHAS 1 PRECEDEM ... ' significa que a janela consiste na linha atual e 1 antes dela. A ' ... LINHAS ENTRE 1 SEGUINTE E 2 SEGUINTE ... ' - a janela consiste em duas linhas imediatamente após o cursor.
  
  A CORRENTE ATUAL neste modo indica a posição atual do cursor. Por exemplo, ' LINHAS ENTRE LINHA ATUAL E SEGUINTE NÃO LIMITADA ' significa da linha atual até o final do intervalo.
• OVER (GAMA)
  difere de ROWS em que CURRENT ROW aqui significa como o início da janela, o início do intervalo de ORDER BY e como o final da janela - a última linha do intervalo ORDER BY.

A sintaxe para usar as funções da janela em diferentes sistemas é um pouco diferente.

Para resumir o exposto, permanece um sentimento um pouco doloroso de que os desenvolvedores, depois de analisarem a construção de vários relatórios em SQL, tenham destacado os casos mais comuns e os concretizado na sintaxe.

Funções de retorno de registro

Na saída das funções agregadas / janela, cada linha resultante corresponde a um determinado intervalo de linhas do fluxo de dados recebido. Na vida, essa correspondência nem sempre existe.

Por exemplo, é necessário construir uma matriz de covariância 10X10 (para isso, levaria 672X672). Isso pode ser feito de uma só vez, para isso executamos a função agregada escrita por nós com 10 parâmetros numéricos. O resultado de seu trabalho é um conjunto de registros de 10 linhas de 10 valores, cada elemento da matriz refere-se a todas as linhas do fluxo de entrada (não importa quantas existam).

Podemos dizer - então o que, no PostgreSQl, por exemplo, você pode retornar um array bidimensional de uma função (Ex: 'ARRAY [[1,2], [3,4]'). Ou apenas serialize a matriz em uma linha.

É bom, mas nem sempre é possível manter o tamanho do resultado dentro da estrutura aceitável para essa abordagem.


A matriz de covariância ainda não é um exemplo muito bom da dessincronização entre os fluxos de entrada e saída, pois todo o resultado é obtido simultaneamente no final. Suponha que você queira processar / compactar um fluxo de dados de origem. Ao mesmo tempo

• existem muitos dados, eles estão no “heap” sem índices, na verdade eles foram simplesmente 'rapidamente' gravados no disco
• você precisa classificá-los em diferentes categorias, que são relativamente poucas
• dentro das categorias, intervalos médios ao longo do tempo, armazenar apenas média, número de medições e variação
• tudo isso precisa ser feito rapidamente

Quais são as opções?

1. No SQL, a classificação por intervalo de tempo / categoria é necessária, o que contradiz o último ponto.
2. Se os dados já estiverem classificados por tempo (o que, de fato, não é garantido), e será possível transmitir esse fato ao processador SQL, você pode fazer isso com as funções da janela e uma passagem.
3. Escreva um aplicativo separado que fará tudo isso. Em PL / SQL ou, mais provavelmente, dado que há muitos dados, em C / C ++.
4. Funções que retornam registros. Talvez eles possam nos ajudar.

Mais detalhes sobre A.4. Existem dois mecanismos para isso - tabelas temporárias e funções de pipeline.

1. Funções do transportador.
   Esse mecanismo apareceu no Oracle (a partir de 9i, 2001) e permite que a função que retornou o conjunto de registros não acumule dados, mas calcule-os conforme necessário (por analogia com a sincronização de stdout e stdin de dois processos conectados via canal).
   I.e. Os resultados das funções em pipeline podem começar a ser processados ​​antes de sair dessa função. Para isso, basta dizer na função
   
   

     FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS … 

   
   e registrar linhas de resultado no corpo
   
   

    LOOP … out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); … END LOOP; 

   
   Como resultado, temos
   
   

    SELECT * FROM TABLE( refcur_pkg.f_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60))); 

   
   Agregados personalizados simplesmente não são necessários quando existem funções de pipeline.
   
   Bravo, Oracle!
   
   Há não muito tempo (2014), as funções de pipeline também apareciam no DB2 (IBM i 7.1 TR9, i 7.2 TR1).
2. Tabelas temporárias.
   Para começar, parece que nem o MS SQL nem o PostgreSQL podem retornar um cursor a partir de uma função agregada.
   
   Bem, vamos, por analogia com as funções do pipeline, pegar o cursor como parâmetro, processá-lo, adicioná-lo a uma tabela temporária e retornar o cursor a ele.
   
   No entanto, no MS SQL , não é possível passar o cursor para um procedimento armazenado por um parâmetro, apenas é possível criar um cursor no procedimento e retornar o parâmetro através da saída. O mesmo pode ser dito para o PostgreSQL.
   
   Bem, tudo bem, basta abrir o cursor, subtraí-lo, processar os valores, calcular o resultado, adicioná-lo à tabela temporária e renderizar o cursor.
   
   Ou, ainda mais simples, adicionamos os resultados da consulta a uma tabela temporária, processamos e retornamos os resultados através do cursor para outra tabela temporária.
   
   O que posso dizer? Primeiro, e mais importante, a leitura de dados através do cursor é mais lenta que o processamento no fluxo. Em segundo lugar, por que você precisa de um processador SQL, vamos ler tabelas com cursores, criar tabelas temporárias com nossas mãos, escrever lógica de junção em loops ... É como inserções de assembler em C / C ++, às vezes você pode se tratar, mas é melhor não abusar dele.

Portanto, tendo considerado uma pergunta com funções retornando conjunto de registros, chegamos a conclusões:

• Agregados personalizados realmente não nos ajudarão aqui.
• De qualquer forma, você precisará criar alguns objetos no banco de dados. Seja funções ou tabelas temporárias. No mínimo, são necessários direitos apropriados. E só quero processar alguns números.
  
• No entanto, mesmo com as limitações descritas, às vezes não é muito elegante, mas com esse método você pode resolver o problema.

O que mais

De fato, se já temos a oportunidade de resolver problemas, o que mais o autor precisa?
Na verdade, a máquina de Turing também pode calcular qualquer coisa, apenas não muito rápido nem muito conveniente.

Formulamos os requisitos da seguinte forma:

1. deve ser um operador relacional que possa ser usado em pé de igualdade com o restante (seleção, projeção, ...)
2. deve ser um operador que transforma um fluxo de dados em outro
3. não há sincronização entre os fluxos de entrada e saída
4. declaração de operador define a estrutura do fluxo de saída
5. o operador tem a capacidade de inicializar dinamicamente (na forma de uma função, mais precisamente seu corpo, especificado diretamente na definição do operador)
6. bem como um destruidor na forma de uma função (...)
7. bem como uma função (...) chamada toda vez que uma nova linha é recebida do fluxo de entrada
8. o operador possui um contexto de execução - um conjunto definido pelo usuário de variáveis ​​e / ou coleções necessárias para o trabalho
9. para executar esse operador, você não precisa criar objetos de banco de dados, não precisa de direitos adicionais
10. tudo o que é necessário para o trabalho é definido em um só lugar, em um idioma

Era uma vez, o autor criou um operador que estende o processador de fabricação própria do subconjunto implementado do TTM / Tutorial D. Agora, a mesma idéia está sendo proposta para SQL.

Vale a pena avisar, aqui o SQL termina e a improvisação começa. A sintaxe é deixada como estava no original; no final, o açúcar sintático pode ser qualquer coisa, não muda a essência.

Portanto, o operador de mastigação consiste em

1. Um cabeçalho que contém uma lista de campos de saída e seus tipos.
   Cada campo de saída (e entrada) é uma variável local.
   Ex: "mastigar {" var1 "float," var2 "número inteiro}" significa que haverá duas colunas no fluxo de saída - um ponto flutuante e um número inteiro
2. Corpos - uma lista de retornos de chamada para eventos, no momento - o início do fluxo, o fim do fluxo, a linha. Por sintaxe, as funções são próximas ao PL / SQL. A função predefinida __interrupt () é um análogo do PIPE, pega valores das variáveis ​​correspondentes às colunas de saída e os coloca no fluxo de saída. Se o buffer do fluxo de saída exceder, o trabalho do manipulador será pausado e o trabalho do lado receptor do fluxo começará.
   Ex: "gancho" init "{var1: = 0; var2: = -1; } "

A maneira mais fácil de mostrar exemplos.

• Um análogo da função agregada SUM.
  
  

   --  'select sum(val) from samples' -- select * from samples chew {“sum(val)” float} --    hook “init” { “sum(val)” := 0; --      } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; end if; } hook “finit” { call __interrupt(); --  PIPE } 

  
  Parece volumoso, mas é apenas um exemplo,
  não é necessário escrever um programa em C para adicionar alguns números.
• SUM + AVG
  
  

   --  'select sum(val), avg(val) from samples' -- select * from samples chew { “sum(val)” float, “avg(val)” float --       } hook “init” { “sum(val)” := 0; “avg(val)” := 0; var num integer; num := 0; --    ,       } hook “row” { if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then “avg(val)” := “sum(val)” / num; end if; call __interrupt(); } 

  
  Aqui chamamos atenção para o fato de que a soma ocorre apenas uma vez.
• SUM + GRUPO POR
  
  

   --  'select sum(val) from samples group by type' -- select * from --     ( samples val, type from samples order by type ) chew { “sum(val)” float } hook “init” { “sum(val)” := 0; var gtype integer; gtype := NULL; var num integer; --   num := 0; } hook “row” { if (gtype <> “type”) then __interrupt(); “gtype” := type; "sum(val)" := 0; num := 0; end if; if (not isnull("val")) then "sum(val)" := "sum(val)" + "val"; num := num + 1; end if; } hook “finit” { if (num > 0) then call __interrupt(); end if; } 

• ROW_NUMBER () OVER ()
  
  

   -- select row_number() over() as num, * from samples -- select * from samples chew { “num” integer, * --        --   '* except val1, ...valX',   TTM } hook “init” { num := 0; } hook “row” { num := num + 1; call __interrupt(); } 

É possível oferecer um exemplo no qual essa abordagem produz resultados que são fundamentalmente inatingíveis da maneira usual? Nós os temos.

Às vezes acontece que os dados estão quase classificados. Eles podem até ser completamente classificados, mas não se sabe ao certo.

Suponha que no exemplo acima (compactação do fluxo de dados) os dados sejam provenientes de fontes diferentes e, por vários motivos, possam ser ligeiramente misturados. I.e.uma linha de uma fonte com um carimbo de data / hora T1 pode estar no banco de dados após uma linha de outra fonte com o horário T2, enquanto T1 <T2.

Mesmo se garantirmos que a diferença entre T1 e T2 nunca excederá uma certa constante (escassa), não podemos prescindir da classificação aqui (da maneira tradicional).

No entanto, usando a abordagem proposta, é possível armazenar em buffer o fluxo de entrada e processar os dados do intervalo de tempo atual somente após a entrada ter recebido linhas com um registro de data e hora que excede pelo menos uma constante determinada pelo limite direito do intervalo.

Há um ponto muito importante aqui.

Só sabemos que os dados estão quase classificados.

Somente nós sabemos o valor dessa constante.

Essa constante é característica apenas para esse problema e talvez apenas para esse experimento.
E usamos esse truque por nossa própria responsabilidade para evitar a classificação.

Nosso conhecimento padrão da tarefa não existe de maneira padrão para informar o processador SQL, e é difícil de imaginar.

E o uso de funções lambda fornece uma maneira universal de forçar o processador SQL a fazer exatamente o que precisamos, exatamente onde precisamos.

Conclusão

O design proposto não parece muito difícil de implementar.

De qualquer forma, com PL / SQL válido.

A ideia em si é simples e intuitiva e não adiciona novas entidades ao idioma.

Esta é uma unidade única que, se necessário, substitui as funções agregadas e de janela, GROUP BY.

Um mecanismo que permite fazer sem classificações, onde não há como um processador SQL tradicional.

Mas o mais importante, é um mecanismo que lhe dá a liberdade de fazer o que quiser da maneira mais imperativa possível com os dados.

PS: agradecimentos a Dorofei Proleskovsky por participar da preparação do artigo.