BANCO DE DADOS II IMPLEMENTAO E OPTIMIZAO DE

BANCO DE DADOS II IMPLEMENTAÇÃO E OPTIMIZAÇÃO DE BANCOS DE DADOS Funções agregadas em janela. Cláusula WITH. Ms. C. Leoder Alemañy Socarrás

BASES DE DATOS ACTUALES ¿Como crêem vocês que serão os tempos de resposta em consultas que requeiram da execução de subconsultas? ¿Como poderiam melhorar estes tempos de resposta para recuperar a mesma informação?

OBJETIVOS Descrever a estrutura e funcionamento das funções agregadas. A cláusula WITH e a cláusula OVER.

RESUMEN Diseño de Bases de Datos: Modelo Entidad – Relación. � Normalización � Patrones de diseño � Transformación Implementación de Bases de Datos: Lenguaje SQL � DML (Select, Insert, Delete, Update) � DCL � DDL (Create, Drop, Alter) Funciones y Vistas.

RESUMO Implementação de Banco de Dados: Cláusula SELECT Select …. (Funciones Agregadas) From …. . Tabela 1 INNER JOIN Tabela 2…. . ON…. Where…. . (Subconsultas, LIKE…) Group By Having (condição com funções agregadas) Order By … ASC…. DESC…. Funções SUM Agregadas: COUNT, AVG, MAX, MIN,

FUNÇÕES AGREGADAS EM JANELA As funções agregadas executam um cálculo em um conjunto de valores e retornam um único valor. Com exceção de COUNT, as funções agregadas ignoram valores nulos. Estão disponíveis em sistemas gestores de bancos de dados como Oracle, SQL Server, Sybase y DB 2, Postgre. SQL a partir da versão 8. 4

¿POR QUE USAR FUNCIONES EM JANELAS? Empleado (empid, departamento, salario, edad)

PREGUNTA ¿Qual é a diferença do salário de cada um dos empregados com respeito à média de seu departamento?

SOLUCIÓN SELECT e 1. empid, e 1. departamento, e 1. salario, e 1. edad, (SELECT AVG(e 2. salario) FROM empleado e 2 WHERE e 2. departamento=e 1. departamento ) as promedio FROM empleado e 1;

¿É A SOLUÇÃO ÓPTIMA? Esta solução mostra-nos os dados da cada empregado com a média de salário do departamento ao que ele corresponde. No entanto com o uso de funções agregadas em janela a solução seria mais simple e os tempos de respostas seriam menores.

DIFERENÇAS ENTRE O USO DE FUNÇÕES JANELA E O USO DO GROUP BY Com o GROUP BY obtemos um resultado com uma fila pela cada valor diferente do atributo usado no GROUP BY.

FUNÇÕES JANELAS Uma função em janela devolve um valor pela a cada fila do resultado de uma consulta.

SINTAXE DAS FUNÇÕES JANELAS OVER( [PARTITION [ORDER BY expression [, . . . ]] BY expression [ASC|DESC][NULLS{FIRST|LAST}][, . . . ]] [Frame-Clause] )

PARÁMETROS Partition by: Realiza a separação lógica das N classes que compõem a partição criada de acordo à expressão definida. Order by: Especifica a maneira em que se ordenassem os dados dentro da classe. Este elemento é de muita importância num grupo importante de casos.

CLÁUSULA FRAME No frame define-se o conjunto de filas que tem-se em conta para computar o resultado da função analítica, naqueles casos em que se opere sobre uma parte dos dados e não sobre toda a classe. Especifica a maneira em que se ordenassem os dados dentro da classe. Este elemento é de vital importância num grupo importante de casos.

SINTAXE DE FRAME-CLAUSE [ RANGE | ROWS ] frame_start [ RANGE | ROWS ] BETWEEN frame_start AND frame_end E os valores que podem ser alocados são os seguintes: UNBOUNDED PRECEDING value PRECEDING CURRENT ROW value FOLLOWING UNBOUNDED FOLLOWING

EXEMPLO 1 Mostrar o empid, departamento, salário, idade e a média dos salários por departamento, para poder realizar a comparação. SELECT empid, departamento, salario, edad, avg (salario) OVER (PARTITION BY departamento) AS promedio FROM empleado;

RESULTADOS Mostrar o empid, departamento, salário, idade e a média dos salários por departamento, para poder realizar a comparação.

EJEMPLO 1. 1 Mostrar o nome, marca, peso, volumen, a média do peso e a média do volumen por marca, para poder realizar a comparação. SELECT nomb_prod, marca, peso, volumen, avg(peso) over (PARTITION BY marca) as peso_med, avg(volumen) over (PARTITION BY marca) as vol_med FROM producto;

RESULTADOS

EJEMPLO 2 Calcular a média dos salários tendo em conta a fila atual mais os salários das duas bichas seguintes que pertencem à partição. SELECT empid, departamento, salario, edad, avg (salario) OVER (PARTITION BY departamento ORDER BY ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING ) AS salario_medio FROM empleado

RESULTADOS

MAIS FUNÇÕES AGREGADAS row_number() percent_rank() last_value() rank() dense_rank() first_value()

ROW NUMBER Computa um número de filas sequencial começando com 1 na primeira tupla na cada partição e de acordo ao ordenamento das tuplas na partição. Listar os empregados pela cada tipo de departamento. SELECT departamento, salario, edad , ROW_NUMBER() OVER (PARTITION BY departamento) FROM empleado;

RESULTADOS

RANK É a faixa ou hierarquia que ocupa uma tupla R tendo em conta as tuplas que a precedem. A faixa se encontra duas tuplas numa posição empatada, atribui-lhes o mesmo número. Mostrar o lugar que ocupam os empregados ordenados pela idade. SELECT departamento, salario, edad , Rank() OVER (order by edad) FROM empleado;

RESULTADOS

RANK Mostrar o lugar que ocupam os empregados ordenados pela idade dentro de seu departamento. SELECT departamento, salario, edad , Rank() OVER (PARTITION BY departamento order by edad ) FROM empleado;

RESULTADOS

DENSERANK O DENSERANK (faixa densa) outorga um número de ordem à tupla em dependência de seu posto no ordenamento sem deixar saltos na numeração. Mostrar o lugar que ocupam os empregados ordenados pela idade. SELECT departamento, salario, edad , dense_Rank() OVER (order by edad ) FROM empleado;

RESULTADOS

DENSERANK Mostrar o lugar que ocupam os empregados ordenados pela idade dentro de seu departamento. SELECT departamento, salario, edad , dense_Rank() OVER (PARTITION BY departamento order by edad) FROM empleado;

RESULTADOS

PERCENT_RANK Devolve o ranking relativo (rank() – 1) / (total de filas - 1) SELECT departamento, salario, edad , percent_rank() OVER (order by edad ) FROM empleado;

RESULTADOS

PERCENT_RANK Mostrar o ranking relativo que ocupam os empregados ordenados pela idade dentro de seu departamento. SELECT departamento, salario, edad , percent_rank() OVER (PARTITION BY departamento order by edad ) FROM empleado;

RESULTADOS

LAST_VALUE Devolve o último valor do marco da janela para o atributo indicado. Mostrar os dados da cada empregado e a maior idade do departamento a que pertencem. SELECT departamento, salario, edad, last_value(edad) OVER (PARTITION BY departamento order by edad ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM empleado;

RESULTADOS

FIRST_VALUE Devolve o primeiro valor do marco da janela para o atributo indicado. Mostrar los datos de cada empleado y la menor edad del departamento a que pertenecen. SELECT departamento, salario, edad, first_value(edad) OVER (PARTITION BY departamento order by edad ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM empleado;

RESULTADOS

Conclusiones

CLÁUSULA WITH A cláusula WITH permite especificar uma ou mais subconsultas que serão referidas por seu nome na consulta principal. Uma propriedade útil das consultas WITH é que são avaliadas só uma vez pela cada execução da consulta principal, ainda se a consulta primária se refere a elas mais de uma vez.

CLÁUSULA WITH Consultas Recursivas. Optimización de Consultas.

OPTIMIZACIÓN DE CONSULTAS É uma consulta que faz referência a sua saída a partir do uso da cláusula with. WITH <alias_name> AS (sql_subquery_statement) SELECT column_list FROM <alias_name>[, tablename] [WHERE <join_condition>]

OPTIMIZAR O RENDIMENTO DE CONSULTAS ¿Qual é a diferença do salário da cada um dos empregados com respeito à média de seu departamento? SELECT e 1. empid, e 1. departamento, e 1. salario, e 1. edad, (select avg(e 2. salario) from empleado e 2 where e 2. departamento=e 1. departamento ) as media FROM empleado e 1;

UTILIZANDO LA CLÁUSULA WITH departamento_salario as (SELECT e 2. departamento, AVG(e 2. salario) as salario_promedio FROM empleado e 2 GROUP BY departamento) SELECT e 1. empid, e 1. departamento, e 1. salario, e 1. edad, departamento_salario_promedio FROM empleado e 1, departamento_salario WHERE departamento_salario. departamento = e 1. departamento;

RESULTADO

OPTIMIZAR EL RENDIMIENTO DE CONSULTAS Mostrar para a cada empregado, além da media do salário de seu departamento, a idade média no departamento da cada um. SELECT e 1. empid, e 1. departamento, e 1. salario, e 1. edad, (select avg(e 2. salario) from empleado e 2 where e 2. departamento=e 1. departamento ) as salario_promedio, (select avg(e 3. edad) from empleado e 3 where e 3. departamento=e 1. departamento ) as edad_promedio FROM empleado e 1;

UTILIZANDO LA CLÁUSULA WITH departamento_salario as (SELECT e 2. departamento, AVG(e 2. salario) as salario_promedio FROM empleado e 2 GROUP BY departamento), departamento_edad as (SELECT e 3. departamento, AVG(e 3. edad) as edad_promedio FROM empleado e 3 GROUP BY departamento) SELECT e 1. empid, e 1. departamento, e 1. salario, e 1. edad, departamento_salario_promedio, departamento_edad_promedio FROM empleado e 1, departamento_salario, departamento_edad WHERE departamento_salario. departamento = e 1. departamento AND e 1. departamento = departamento_edad. departamento;

RESULTADOS

PLANIFICADOR DE CONSULTAS Pode-se usar para mostrar o plano de execução que o planificador gera para uma consulta dada. Sintaxe: EXPLAIN PLAN FOR <query>

UTILIDAD DEL PLANIFICADOR O uso deste comando é importantíssimo para analisar a optimização de consultas quando se utilizam funciones de janelas ou a cláusula WITH. Para isso se deve analisar o plano de consulta com a resposta em sql básico e depois com a variante que se tenha eleito para a optimização dependendo do caso.

Conclusiones

ESTUDIO INDEPENDIENTE PERCENT_RANK LAST_VALUE FIRST_VALUE Consultas Recursivas utilizando la cláusula WITH