17 Novembro 2017

SQL Server R Services

1. R, PARA ALÉM DE UMA SIMPLES LINGUAGEM

R, para além de uma linguagem de programação, é também um ambiente onde é possível explorar, manipular e representar dados com uma abordagem essencialmente estatística. Como tal, esta ferramenta está bastante vocacionada para o campo do Data Mining, visto que permite ao utilizador ter uma visão de como os dados se relacionam, comportam e tendem a comportar-se no futuro.

1.1. COMO FUNCIONA

A ferramenta R funciona com base em funções matemáticas e funções com fins preditivos. Estas funções estão normalmente armazenadas em packages, que necessitam de ser importados à priori, para se poder usufruir das suas funções.

O R possui também um repositório oficial de packages online, denominado por CRAN (The Comprehensive R Archive Network), onde toda a comunidade pode partilhar o seu trabalho e disponibilizar novas funcionalidades implementadas, assumindo que tudo cumpre com as regras do repositório.

2. R SERVICES

O SQL Server possui um Stored Procedure denominado por sp_execute_external_script, que lhe permite interpretar outras linguagens, como é o caso do R. Com a nova atualização, SQL Server 2017, terá também a capacidade de interpretar a linguagem Python.

Para se utilizar este Stored Procedure é necessário ter em atenção à sintaxe utilizada. A imagem que se segue mostra a estrutura que o código deve tomar.

 

2.1. ESTRUTURA DE CÓDIGO

2.1.1. LANGUAGE

Nesta primeira secção, é transmitido ao SQL Server qual a linguagem a processar na execução do script.

2.1.2. SCRIPT

O parâmetro @Script é onde o código é invocado, e o utilizador pode tirar partido de todas as funções que o R dispõe para potenciar o seu trabalho.

2.1.3. INPUT DATA

Onde definimos os dados a trabalhar. A query definida neste parâmetro vai ditar o DataSet que ficará sujeito a transformações sob as funções de R executadas no @Script.

2.1.4. OUTPUT DATA

Este parâmetro define qual a variável que é dada como o resultado do @Script, caso se pretenda que esse mesmo resultado seja dado como Output.

2.1.5. PARAMS

Caso se pretenda passar para o @Script variáveis definidas no exterior do Stored Procedure sp_execute_external_script, é necessário definir a metadata das mesmas, e transmitir ao SP qual o nome que as representará aquando o código de R for executado.

Neste caso as variáveis externas são @alfas, @betas e @gamas, e @a, @b e @s são as variáveis que as irão representar no parâmetro @script.

3. APLICAÇÃO

Para exemplo de aplicação desta ferramenta, neste artigo será descrito um caso de estudo que tem como objetivo determinar o número de unidades expedidas, de cada produto, de um armazém, em vários períodos temporais futuros.

3.1. ALGORITMO A UTILIZAR

Em primeira instância é necessário analisar o dataset no qual se irá trabalhar, conhecer os dados e o seu comportamento. Neste caso, estamos perante uma série temporal sasonal, e como tal o método escolhido para abordar a situação foi o algoritmo de Holt-Winters, também conhecido como Triple Exponential Smoothing.

3.2. HOLT-WINTERS

Este algoritmo aplica-se apenas a modelos de Séries Temporais Sasonais, ou seja, séries temporais que repitam o seu comportamento entre períodos constantes.

O algoritmo recebe 3 parâmetros, parâmetros estes que definem o peso do valor das componentes do período atual para com o valor das componentes do período anterior da série.

As 3 componentes do algoritmo são: Nível, Tendência e Componente Sazonal.

E são definidos da seguinte forma respetivamente, sendo que o ultimo valor é o resultado da previsão.

 

3.3. MODELO

Tendo definido qual o método a utilizar, agora é necessário construir o modelo que irá gerar as previsões pretendidas. Os dados serão divididos entre 2 grupos, grupo de treino, que irá ensinar o modelo a comportar-se no futuro, e grupo de teste, que permitirá comparar os dados previstos com a realidade.

Será também necessário descobrir quais os parâmetros que geram o melhor modelo possível, para os dados de treino.

Este pode ser um processo algo demorado, e como tal criou-se um método de cálculo automático, de forma a gerar modelos para os 10 produtos com mais unidades armazenadas. O método consiste em iterar para cada produto, modelos que têm alpha (componente de nível) e beta (componente de tendência) variáveis, e gamma (componente sasonal) constante. Por cada iteração é também calculada o MAE (Erro Absoluto Médio) do modelo, assim como o número de valores que se encontram numa janela de 10% de erro para com os valores do grupo de teste.

Todos estes valores, ou seja, número de produto, data, parâmetros do modelo, MAE e Quantidade de valores aceitáveis, são armazenados numa tabela que recebe estes valores por cada iteração do processo,
O código que segue, representa todo o raciocínio descrito em cima.

 

 

 

 

 

 

Uma vez executado todo este processo, temos em mão uma tabela que contém o melhor modelo associado a cada um dos produtos.

Portanto, para alcançar o objetivo pretendido, resta aplicar a previsão aos modelos obtidos, neste caso será feita uma previsão a 12 meses.

 

 

 

Terminando o processo, obtém-se uma tabela final que permite ao utilizador cruzar os dados previstos com os dados reais, presentes no modelo de teste, e com isto comprovar a qualidade do modelo implementado.

4. CONCLUSÃO

Como se pode ver ao longo deste artigo, o SQL Servidor R Services é uma ferramenta bastante útil e poderosa, que permite ir além de uma simples pesquisa nos dados. Tem uma forte interação com o convencional Transact-SQL e permite manipular os dados contidos nas Bases de Dados de uma forma deveras interessante. Pode ter talvez a desvantagem, de num primeiro contacto, não ser algo fácil e intuitivo, porém, uma vez ultrapassadas as dificuldades iniciais, é uma grande mais valia a ter em mente.

.

.

.

.
   Diogo Candeias
 Junior Consultant