Lenguaje tsql como aproximación a escenarios BI
-
Upload
enrique-catala-banuls -
Category
Technology
-
view
139 -
download
0
description
Transcript of Lenguaje tsql como aproximación a escenarios BI
![Page 1: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/1.jpg)
@SQSummit13
@enriquecatala
@
Lenguaje TSQL como aproximación a
escenarios BI
300
REL30012
Enrique Catala Bañuls
MCT – Microsoft Active Professional – Technical Ranger
![Page 2: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/2.jpg)
EN CUMPLIMIENTO CON LA LEY 15/1999 DE PROTECCION DE DATOS DE
CARÁCTER PERSONAL, PONEMOS EN TU CONOCIMIENTO QUE
ESTA SESIÓN VA A SER GRABADA
POR SOLIDQ Y QUE ESTA GRABACIÓN PODRÍA SER UTILIZADA COMO MATERIAL
DE MARKETING Y HACERSE PUBLICA A TRAVÉS DE DIVERSOS MEDIOS, COMO
POR EJEMPLO NUESTRA PAGINA WEB.
TENIENDO EN CUENTA QUE TU IMAGEN PUEDE APARECER EN ESA GRABACIÓN,
SI NO DESEAS APARECER, ROGAMOS NOS LO COMUNIQUES POR LOS MEDIOS
QUE YA CONOCES.
COMUNICADO
2
![Page 3: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/3.jpg)
Objetivos de la sesión
1. Agrupación de conjuntos
2. Funciones CUBE y ROLLUP
3. Pivotado de datos
4. Funciones analíticas en SQL Server 2012
3
![Page 4: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/4.jpg)
DEMO
7
GROUPING SETS
![Page 5: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/5.jpg)
Objetivos de la sesión
1. Agrupación de conjuntos
2. Funciones CUBE y ROLLUP
3. Pivotado de datos
4. Funciones analíticas en SQL Server 2012
8
![Page 6: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/6.jpg)
DEMO
11
CUBE y ROLLUP
![Page 7: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/7.jpg)
Objetivos de la sesión
1. Agrupación de conjuntos
2. Funciones CUBE y ROLLUP
3. Pivotado de datos
4. Funciones analíticas en SQL Server 2012
12
![Page 8: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/8.jpg)
Pivotado de datosPIVOT
13
Rotar datos desde filas a columnas
Permite agregar múltiples valores a un único valor
k1 k2 c1
1 A v1
1 B v2
1 C v3
2 A v4
2 B v5
2 C v6
k1 A B C
1 v1 v2 v3
2 v4 v5 v6
PIVOT
![Page 9: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/9.jpg)
Pivotado de datosCaso práctico para Open Schema
14
Cada atributo almacenado en una fila aparte
PIVOT perfecto para representar datos y
manipular este escenario
-- OpenSchema
objectid attribute val
----------- --------- -----------
1 attr1 ABC
1 attr2 10
1 attr3 2008-01-01
2 attr2 12.300
2 attr3 X
2 attr4 Y
2 attr5 14.700
3 attr1 XYZ
3 attr2 20
3 attr3 2009-01-01
-- Desired Result:
objectid attr1 attr2 attr3 attr4 attr5
----------- ----- ------ ---------- ----- ------
1 ABC 10 2008-01-01 NULL NULL
2 NULL 12.300 X Y 13.700
3 XYZ 20 2009-01-01 NULL NULL
PIVOT
![Page 10: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/10.jpg)
Pivotar con GROUP BY
15
-- OpenSchema
objectid attribute val
----------- --------- -----------
1 attr1 ABC
1 attr2 10
1 attr3 2008-01-01
2 attr2 12.300
2 attr3 X
2 attr4 Y
2 attr5 14.700
3 attr1 XYZ
3 attr2 20
3 attr3 2009-01-01
-- Desired Result:
objectid attr1 attr2 attr3 attr4 attr5
----------- ----- ------ ---------- ----- ------
1 ABC 10 2008-01-01 NULL NULL
2 NULL 12.300 X Y 13.700
3 XYZ 20 2009-01-01 NULL NULL
PIVOT
SELECT objectid,MAX(CASE WHEN attribute = 'attr1' THEN val END) AS attr1,MAX(CASE WHEN attribute = 'attr2' THEN val END) AS attr2,MAX(CASE WHEN attribute = 'attr3' THEN val END) AS attr3,MAX(CASE WHEN attribute = 'attr4' THEN val END) AS attr4,MAX(CASE WHEN attribute = 'attr5' THEN val END) AS attr5
FROM dbo.OpenSchemaGROUP BY objectid;
Agrupación
(implícita)
Agregación
Distribución
![Page 11: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/11.jpg)
Pivotar con operador PIVOT
16
-- OpenSchema
objectid attribute val
----------- --------- -----------
1 attr1 ABC
1 attr2 10
1 attr3 2008-01-01
2 attr2 12.300
2 attr3 X
2 attr4 Y
2 attr5 14.700
3 attr1 XYZ
3 attr2 20
3 attr3 2009-01-01
-- Desired Result:
objectid attr1 attr2 attr3 attr4 attr5
----------- ----- ------ ---------- ----- ------
1 ABC 10 2008-01-01 NULL NULL
2 NULL 12.300 X Y 13.700
3 XYZ 20 2009-01-01 NULL NULL
PIVOT
SELECT objectid, attr1, attr2, attr3, attr4, attr5FROM dbo.OpenSchemaPIVOT(MAX(val)
FOR attributeIN([attr1],[attr2],[attr3],[attr4],[attr5])
) AS P;
Agrupación
(implícita)Agregación
Distribución
![Page 12: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/12.jpg)
Despivotado de datosUNPIVOT
17
Rotar datos de columnas a filas
k1 A B C
1 v1 v2 v3
2 v4 v5 v6
k1 k2 c1
1 A v1
1 B v2
1 C v3
2 A v4
2 B v5
2 C v6
UNPIVOT
![Page 13: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/13.jpg)
DEMO
19
PIVOT y UNPIVOT
![Page 14: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/14.jpg)
Objetivos de la sesión
1. Agrupación de conjuntos
2. Funciones CUBE y ROLLUP
3. Pivotado de datos
4. Funciones analíticas en SQL Server 2012
20
![Page 15: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/15.jpg)
Window functions¿Por qué las necesitamos?
21
¿Qué pasa si queremos obtener la suma y
el valor de la columna? (group y detalle)
select id_table, value,
sum(value) as [sum(value)]
from table1
Msg 8120, Level 16, State 1, Line 1
Column 'table1.id_table' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
id_table value
1 1
2 1
2 2
3 1
3 2
3 3
SUM(value)
1
3
6
id_table value sum(value)
1 1 1
2 1 3
2 2 3
3 1 6
3 2 6
3 3 6
Select sum(value) as [sum(value)]
from table1
En un motor relacional es correcta estaaproximación?
![Page 16: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/16.jpg)
Window functions¿Por qué necesitamos window functions?
22
La solución pasa por usar la cláusula OVER
select id_table,
value,
sum(value) over(partition by id_table)
from table1
id_table value sum(value)
1 1 1
2 1 3
2 2 3
3 1 6
3 2 6
3 3 6
![Page 17: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/17.jpg)
Window functionsEvolución
23
Cláusula OVER
![Page 18: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/18.jpg)
Window functionsDesglose
24
Partitioning
Ordering
Slicing/framing
![Page 19: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/19.jpg)
Window functionsConceptos clave
25
Partition
UNBOUNDED
FOLLOWING
UNBOUNDED
PRECEDING
CURRENT
ROW
![Page 20: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/20.jpg)
Window functionsConceptos clave: Particion
26
Grupo de filas con
“características”
similares dentro de
un conjunto
actid tranid
1 1
2 1
2 2
3 1
3 2
3 3
![Page 21: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/21.jpg)
Window functionsConceptos clave: Slicing/Framing
27
RANGE/ROWS
– ROWS | RANGE BETWEEN <B1> AND <B2>
– ROWS | RANGE <B1>
![Page 22: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/22.jpg)
Window functionsConceptos clave: Slicing/Framing
28
B1 and B2 can be– UNBOUNDED PRECEDING
– UNBOUNDED FOLLOWING
– CURRENT ROW
– FOR ROWS ONLY• <scalar expression> PRECEDING
• <sclara expression> FOLLOWING
Note– B1 <= B2 or NULL will be returned
• Except in COUNT() that 0 will be returned
![Page 23: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/23.jpg)
Window functionsConceptos clave
29
Partition
UNBOUNDED
FOLLOWING
UNBOUNDED
PRECEDING
CURRENT
ROW
![Page 24: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/24.jpg)
DEMO
30
Funciones analíticas
![Page 25: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/25.jpg)
ConclusionesObjetivos de la sesión
31
• Agrupación de conjuntos múltiple
• Conceptos CUBE y ROLLUP
• Pivotado de datos
• Funciones analíticas decentes en SQL
Server 2012
![Page 26: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/26.jpg)
¿Preguntas?
32
![Page 27: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/27.jpg)
¡Gracias!
Siéntate a comer con nosotros o tómate un café y aclara tus
dudas
33
@enriquecatala
Mentor
Enrique Catalá
![Page 28: Lenguaje tsql como aproximación a escenarios BI](https://reader033.fdocument.pub/reader033/viewer/2022052602/559c1c601a28abfb148b4640/html5/thumbnails/28.jpg)
Si quieres disfrutar de las mejores sesiones de
nuestros mentores de España y Latino América,
ésta es tu oportunidad.
http://summit.solidq.com/madrid/
Síguenos:
34