Dominando las funciones de ventana en TSQL

19 de Noviembre (12 pm GMT -5)

José L. Rivera

Resúmen:

Las funciones de ventana (TSQL window functions),

originalmente presentadas en SQL Server han probado

ser muy útiles para resolver escenarios complejos de

clasificación/ordenamiento y agregación. Las mejoras

que nos trae SQL Server 2012 hace imperativo conocer

su funcionamiento y aplicación. En esta sesión

discutiremos sus componentes principales y como

utilizar las mismas tomando como ejemplos situaciones

de la vida real.

Próximos Eventos

3 y 4 de Diciembre

Configurando transactional

replication para reporteo de una

forma segura

26 de Noviembre

Kenneth Ureña

Está por comenzar:

Dominando las funciones de ventana en TSQL

19 de Noviembre de 2014

Jose L. Rivera

MVP | MCITP | MCTS | MCSA

BI Consultant, Nagnoi LLC

PASS Global Spanish VC | PRPASS

Agenda

- Resumen de la funciones

- OVER

- Categorías de funciones

- Ordenamiento

- Agregación

- Analíticos

- Q & A

• Introducido en SQL Server 2005, mejoras significativas en

SQL Server 2012

• La función es aplicada a un conjunto de filas (rows)

• La especificación de la ventana es implementada usando

la clausula de OVER

• Todavía es trabajo en progreso!

Funciones de Ventana (Windows Functions)

Divide el conjunto en

pedazos (particiones) y la

operación es aplicada a cada

partición individualmente

Delimita las filas dentro de la

partición al especificar las

fronteras dentro de la

partición

La Clausula OVER

• Define el conjunto (ventana) sobre la cual la función será

aplicada

OVER (

<PARTITION BY clause>

<ORDER BY clause>

<ROW or RANGE clause>

)

Define el orden lógico de la

data para cada partición de

un conjunto

Operaciones basadas en conjuntos (Windowing)

ID AcctID TransDate TransAmt

1 1234 27/11/2012 $150.00

2 1234 27/11/2012 $22.00

3 5678 28/11/2012 $50.00

4 5678 28/11/2012 $150.00

5 5678 28/11/2012 $10.00

6 5678 29/11/2012 $120.00

7 0987 30/11/2012 $20.00

8 0987 30/11/2012 $100.00

9 0987 30/11/2012 $50.00

Aggregation Window:SUM(TransAmt) OVER(PARTITION BY TransDate)

Ranking Window:ROW_NUMBER() OVER(PARTITION BY TransDate

ORDER BY AcctID, ID)

AcctID TransDate TransAmt BalAmt

5678 28/11/2012 $50.00 $50.00

5678 28/11/2012 $150.00 $200.00

5678 28/11/2012 $10.00 $210.00

AcctID TransDate TransAmt BalAmt Rank

5678 28/11/2012 $50.00 $50.00 1

5678 28/11/2012 $150.00 $200.00 2

5678 28/11/2012 $10.00 $210.00 3

AcctID TransDate TransAmt

5678 28/11/2012 $50.00

5678 28/11/2012 $150.00

5678 28/11/2012 $10.00

ROW or RANGE (moldura)

• Define como nos movemos dentro de la ventana para

modificar la selección de las filas

• BETWEEN <window frame bound > AND <window frame bound >

Categorías de funciones

Orden Agregación Analítico

ROW_NUMBER()

RANK()

DENSE_RANK()

NTILE()

SUM() | AVG() | COUNT()

MIN() | MAX()

CHECKSUM_AGG

STDEV() | STDEVP()

VAR() | VARP()

LEAD() | LAG()

FIRST_VALUE() | LAST_VALUE()

CUME_DIST()

PERCENT_RANK()

PERCENTILE_DIST()

PERCENTILE_CONT()

Ordenamiento

• ROW_NUMBER()• Genera un numero secuencial continuo (comienza en 1)

• RANK()• Ordena la data (Olympic rank – en caso de empate)

• DENSE_RANK()• Ordena la data sin brincos

• NTILE()• Distribuye la data en grupos

No hay moldura

DEMOFunciones de Ordenamiento

Agregación

• SUM() | AVG() | COUNT() | COUNT_BIG() | MIN() | MAX()• Son las mas comunes, no hay sorpresas

• CHECKSUM_AGG• Calcula un checksum de los valores en el grupo

• STDEV() | STDEVP()• Desviación estándar estadística (regular o poblacional)

• VAR() | VAR()• Varianza estadística (regular o poblacional)

DEMOFunciones de Agregación

Analíticos

• LEAD() | LAG()

• Obtiene la data de la fila anterior o posterior en el mismo

conjunto

LEAD | LAG

(scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ]

order_by_clause )

No hay moldura

Analíticos

• FIRST_VALUE() | LAST_VALUE()

• Permite obtener el primer/ultimo valor en un conjunto

ordenado de valores

FIRST_VALUE | LAST_VALUE

( [scalar_expression] )

OVER ( [ partition_by_clause ]

order_by_clause

rows_range_clause )

Analíticos

• CUME_DIST()

• Calcula la distribución acumulativa de un valor en un grupo

de valores

No hay moldura

CUME_DIST()

OVER ( [ partition_by_clause ]

order_by_clause )

Analíticos

• PERCENT_RANK()

• Calcula el orden relativo de una fila dentro de un grupo de

filas

No hay moldura

PERCENT_RANK()

OVER ( [ partition_by_clause ]

order_by_clause )

Analíticos

• PERCENTILE_DIST()

• Calcula la percentila para un conjunto de valores ordenados

No hay moldura

PERCENTILE_DIST ( numeric_literal )

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )

OVER ( [ partition_by_clause ]

order_by_clause

rows_range_clause )

Analíticos

• PERCENTILE_CONT()

• Calcula la percentila basada en una distribución continua de

valores.

No hay moldura

PERCENTILE_CONT ( numeric_literal )

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )

OVER ( [ partition_by_clause ]

order_by_clause

rows_range_clause )

DEMOFunciones Analíticas

Importante!

• SQL Server 2005/2008/2008 R2• Ordenamiento

• PARTITION BY + ORDER BY

• Agregación

• Solo PARTITION BY

• SQL Server 2012+• Analíticos

• Agregación

• Se añaden ORDER BY

• Window Framing (ROWS | RANGE)

Manténgase conectado a nosotros!

Visítenos en http://globalspanish.sqlpass.org

/SpanishPASSVC

lnkd.in/dtYBzev

/user/SpanishPASSVC

/SpanishPASSVC

Outstanding Volunteer AwardDo you know a volunteer that has gone

above and beyond recently?

Nominate them for the monthly OVA.

VolunteerRecognition@sqlpass.org

PASS Volunteer Awards

PASSion AwardThe highest of PASS accolades presented

yearly to a volunteer.

Manténgase involucrado!

• Registrate HOY en sqlpass.org para una membresia gratuita

• Linked In: http://www.sqlpass.org/linkedin

• Facebook: http://www.sqlpass.org/facebook

• Twitter: @SQLPASS

• PASS: http://www.sqlpass.org

Configurando transactional replication para reporteo de una forma segura

26 de Noviembre (12 pm GMT -5)

Kenneth Ureña

Resúmen:

En esta sesión vamos a ver la forma de configurar

transactional replication, la seguridad necesaria y los

diferentes componentes que se involucran en el

funcionamiento de la misma. Al igual como las mejores

prácticas para escalabilidad y distribuciones de carga,

convirtiendo nuestro entorno replicado en un amigo de

distribución de carga, en lugar de una carga de la

administración.

Próximo Evento