Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img

Cursores, Agrupaciones, Subconsultas, SELF JOIN frente a la cláusula OVER



A la hora de diseñar consultas, para extraer datos de SQL Server, podemos optar por varias vías:

- Una de ellas es crear una solución iterativa, desempeñándola con la aplicación (bien sea con un programa T-SQL) de tipo cursor, como por ejemplo, para obtener todas las sumas de las ventas que ha realizado un determinado empleado, entre dos fechas… aunque sea una consulta simple, se pude complicar, si a ello le sumamos obtener información de todos los empleados, el % de las ventas frente a todos los compañeros, etc… Para lo que abriríamos un cursor y empezaríamos a iterar. Este tipo de soluciones, conllevan una pérdida de rendimiento brutal, y no aconsejo que os pongáis a diseñar una solución con cursores. Es una forma sencilla y rápida de destrozar el potencial de un servidor SQL Server. Olvidémonos de usar cursores, ya que tenemos que pensar en conjuntos. Cuando nos paremos a pensar en una solución a nuestras consultas, no hay que dejar de pensar que SQL Server es un motor de base de datos relacional que trabaja con conjuntos de datos, que son los que queremos explotar haciendo consultas T-SQL, y una forma mala de hacerlo es utilizando cursores.

- Otra opción es usar consultas agrupadas, si optamos por agrupar, cuando hablo de agrupación estoy pensando en una suma, en una media, en mínimos…en operaciones que son de tipo agrupado. Se tendrán que hacer en determinados momentos cuando tengamos que dar una solución, el inconveniente de estas operaciones es la imposibilidad de mezclar los cálculos del agrupado y el nivel de detalle, hasta ahora era casi imposible que a la hora de obtener una suma, obtener también el nivel de detalle de donde viene esa suma…

-Otra forma que podemos adoptar es hacer subconsultas,  el problema de estas son la complejidad, si no se hacen bien, añaden complejidad al calculo que se quiere llegar a realizar… Las subconsultas, bien sean, con WITH (consultas CTE), o con la utilización de subquerys, que nos ofrecen la oportunidad de sacar datos agrupados con el detalle de la información del agrupamiento, tenemos que intentar evitarlas… Las subconsultas que se hacen dentro de los campos a mostrar en el Select son muy ineficientes y las subconsultas que se hacen detrás del from suelen ser complejas y difíciles de entender

-Otra opción, es hacer Joins… si hacemos un join, dado un determinado problema, puede llegar el caso que podamos consultar o cruzar una tabla consigo misma. Este tipo de escenarios, como por ejemplo para obtener descendientes de…, o agrupados hasta un determinado momento de una fecha sobre la información que se tiene en una tabla… Todo este tipo de información que se puede obtener a partir de SELF JOIN, generalmente son consultas de complejidad cuadrática.

Tenemos que llegar a la conclusión que tenemos que pensar en conjuntos, cuando trabajemos con SQL Server, tenemos que generar una solución desarrollada en conjuntos, bien sea mediante consultas agrupadas, mediante Joins, o mediante subconsultas… que al final sean consultas donde no aparece el efecto cursor.

No olvidemos que como conjunto, entendemos una reunión de objetos “sin ningún” orden y con una propiedad común. (SQL Server tiene la cláusula ORDER BY por que no te garantiza el orden en los conjuntos…)

Para dar solución a lo anterior, a consultas con agregados y detalles de los mismos, aparece en SQL Server 2012 la cláusula OVER, que optimiza las soluciones anteriores… Con la clausula OVER el motor optimiza el tiempo de ejecución y los bloqueos de las filas, ya que el motor trabaja sobre los datos ya obtenidos, en vez de volver a recuperarlos de origen.

     Como ejemplo, os mostrare la clausula OVER con las funciones:

RANK() Si se especifica una expresión numérica, la función Rank determina el rango basado en uno de la tupla especificada mediante la evaluación de la expresión numérica especificada con la tupla. Si se especifica una expresión numérica, la función Rank asigna el mismo rango a las tuplas con valores duplicados del conjunto.

DENSE_RANK () Devuelve el rango de filas dentro de la partición de un conjunto de resultados, sin ningún espacio en la clasificación. El rango de una fila es uno más el número de rangos distintos anteriores a la fila en cuestión.

NTILE () Distribuye las filas de una partición ordenada en un número especificado de grupos. Los grupos se numeran a partir del uno. Para cada fila, NTILE devuelve el número del grupo al que pertenece la fila.

CREATE TABLE Personas (ID INT IDENTITY, PRIMERNOMBRE VARCHAR(50) NOT NULL, SEGUNDONOMBRE VARCHAR(50) NOT NULL)

GO

 

DELETE FROM dbo.Personas

GO

 

INSERT INTO dbo.Personas VALUES('JUAN','AVILA')

INSERT INTO dbo.Personas VALUES('ALDO','LADERAS')

INSERT INTO dbo.Personas VALUES('ALDO','LADERAS')

INSERT INTO dbo.Personas VALUES('ALDO','LADERAS')

INSERT INTO dbo.Personas VALUES('JUAN','VICTORIO')

INSERT INTO dbo.Personas VALUES('VICTOR','MORENO')

INSERT INTO dbo.Personas VALUES('ADRIAN','LUNA')

INSERT INTO dbo.Personas VALUES('ADRIAN','LUNA')

INSERT INTO dbo.Personas VALUES('ADRIAN','AQUINO')

INSERT INTO dbo.Personas VALUES('JUAN','SAAVEDRA')

INSERT INTO dbo.Personas VALUES('JUAN','SAAVEDRA')

INSERT INTO dbo.Personas VALUES('JUAN','SAAVEDRA')

INSERT INTO dbo.Personas VALUES('JUAN','SAAVEDRA')

INSERT INTO dbo.Personas VALUES('LUIS','SATTUI')

INSERT INTO dbo.Personas VALUES('DANIEL','VILLARAN')

INSERT INTO dbo.Personas VALUES('VICTOR','HUAMAN')

INSERT INTO dbo.Personas VALUES('VICTOR','HUAMAN')

INSERT INTO dbo.Personas VALUES('MIGUEL','HUAMAN')

INSERT INTO dbo.Personas VALUES('LUIS','SANCHEZ')

 

-- Ejemplo sin partición:

 

  SELECT PRIMERNOMBRE,SEGUNDONOMBRE,

  ROW_NUMBER() OVER(ORDER BY PRIMERNOMBRE,SEGUNDONOMBRE) AS ROW_NUMBER,

  RANK() OVER(ORDER BY PRIMERNOMBRE,SEGUNDONOMBRE) AS RANK,

  DENSE_RANK() OVER(ORDER BY PRIMERNOMBRE,SEGUNDONOMBRE) AS DENSE_RANK,

  NTILE(3) OVER(ORDER BY PRIMERNOMBRE,SEGUNDONOMBRE) AS 'NTILE(3)'

  FROM dbo.Personas ORDER BY PRIMERNOMBRE

 

 

-- Ejemplo con partición

 

  SELECT PRIMERNOMBRE,SEGUNDONOMBRE,

  ROW_NUMBER() OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE)

  AS ROW_NUMBER,

  RANK() OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE) AS RANK,

  DENSE_RANK() OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE)

  AS DENSE_RANK,

  NTILE(3) OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE) AS 'NTILE(3)'

  FROM dbo.Personas ORDER BY PRIMERNOMBRE

      Para sacar solo los JUAN y quitar los repetidos:

  SELECT PRIMERNOMBRE,SEGUNDONOMBRE,

  ROW_NUMBER() OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE)

  AS ROW_NUMBER,

  RANK() OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE) AS RANK,

  DENSE_RANK() OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE)

  AS DENSE_RANK,

  NTILE(3) OVER(PARTITION BY PRIMERNOMBRE ORDER BY SEGUNDONOMBRE) AS 'NTILE(3)'

  FROM dbo.Personas where PRIMERNOMBRE = 'JUAN'

  Group by PRIMERNOMBRE, SEGUNDONOMBRE ORDER BY PRIMERNOMBRE

 

Apunte y recopilación por Norman M. Pardell

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.


 

publicado por normanmpardell a las 20:16 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Tomo I. Memoria RAM. Optimización de sistemas de 32 y 64 bits. SQL Server 2008.
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad