Sub-consultas, clausulas MIN y TOP

Mi primer contacto con un servidor de base de datos en lo profesional fue con SQL Server. Sin embargo cuando nos fuimos a procesamiento de datos entro como luz, el poderoso Oracle.

Sin embargo para fines académicos me hicieron una consulta, que en Oracle no hubiera tenido grandes problemas y quizás en SQL Server tampoco pero tiene sus diferencias que llevaron un poco de tiempo para investigar.

El problema es el siguiente:

Se tienen registros de préstamo de equipos de cómputo y el cliente ha solicitado que se haga un sistema para automatizar estos préstamos además que verifique los tiempos de uso de los equipos para balancear sus cargas de trabajo y lograr una durabilidad del inventario.

No tengo el modelo de exacto de la base de datos de los chicos pero es algo parecido a ésto:

USE [BdPrestamo]
GO

/****** Object: Table [dbo].[tequipos] Script Date: 4/8/2018 5:31:11 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tequipos](
[ninventario] [numeric](10, 0) NOT NULL,
[Marca] [varchar](100) NOT NULL,
[Modelo] [varchar](100) NOT NULL,
[NumSerie] [varchar](50) NOT NULL,
CONSTRAINT [PK_tequipos] PRIMARY KEY CLUSTERED
(
[ninventario] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Esta tabla digamos que es la tabla inventario y bueno con campos muy reducidos, pues es para ejemplificar las consultas. La siguiente es la tabla donde se registran los préstamos.

USE [BdPrestamo]
GO

/****** Object: Table [dbo].[TPrestamo] Script Date: 4/8/2018 5:34:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TPrestamo](
[nserie] [numeric](10, 0) NOT NULL,
[fechinicio] [datetime] NOT NULL,
[fechfinal] [datetime] NOT NULL,
[matricula] [numeric](6, 0) NOT NULL
) ON [PRIMARY]

GO

He hice unos registros de muestra, aquí 3 equipos:

ninventario | Marca | Modelo  | NumSerie
100000            | Acer      | Aspire      | asas122
200000            | HP        | Probook   | asas123
300000            |Dell       | Altitude    | asas124

y aquí una serie de registros de préstamo:

nserie  | fechinicio                          |fechfinal                            |matricula
100000 | 2018-01-22 13:00:00.000 |2018-01-22 13:50:00.000 |1
200000 |2018-01-22 15:00:00.000 |2018-01-22 18:00:00.000 |2
300000 |2018-01-22 17:00:00.000 |2018-01-22 18:00:00.000 |3
100000 |2018-01-23 13:00:00.000 |2018-01-23 17:30:00.000 |1
200000 |2018-01-23 18:00:00.000 |2018-01-23 20:15:00.000 |2
300000 |2018-01-23 16:30:00.000 |2018-01-23 17:50:00.000 |3
100000 |2018-01-24 17:50:00.000 |2018-01-24 19:30:00.000 |1
200000 |2018-01-24 16:05:00.000 |2018-01-24 18:30:00.000 |2
300000 |2018-01-24 20:00:00.000| 2018-01-24 21:10:00.000 |3

Hay aclarar que por las prisas no homogeneice las llaves por lo que nserie corresponde a ninventario, a los chicos les comento que cuando estamos en sub-consultas que tenemos que resolver de lo particular a lo general, lo primero que tenemos que hacer es sacar el tiempo que se uso cada equipo por usuario en base a las fechas de inicio y fin.

SQL Server tiene una función que se llama  DATEDIFF donde se da las 2 fechas (que tienen que ser DateTime)  y en que unidades será expresada la diferencia.

select nserie, DATEDIFF(MINUTE,FECHINICIO,FECHFINAL) AS TIMEP FROM TPRESTAMO

Y aquí fue donde me causo dificultad pues de aquí sigue agrupar por nserie y la consulta sería tomada como el query, pero en SQL Server requiere forzosamente que se le dicte el alias.

SELECT NSERIE, SUM(Q1.TIMEP) as timetot FROM
(select nserie, DATEDIFF(MINUTE,FECHINICIO,FECHFINAL) AS TIMEP FROM TPRESTAMO) as q1
GROUP BY nserie

Bueno ahora otra complejidad que quizás no recordaba, el menor de un conjunto de datos y necesitamos que computadora es, para ello hacemos una sub-consulta más, donde ordenamos por tiempo de uso y como por default es ascendente el primer registro será la computadora con menor tiempo de uso.

Pero bueno muchas veces mis alumnos se ponen finos, entonces surgía la pregunta ¿Cómo obtener el primer registro?. Aquí nos sirve la experiencia y googleamos como usar el ROWNUM de Oracle, entonces salió la clausula TOP.

SELECT TOP 1 Q2.NSERIE, MIN(Q2.TIMETOT) AS NEXTPC FROM
(SELECT NSERIE, SUM(Q1.TIMEP) as timetot FROM
(select nserie, DATEDIFF(MINUTE,FECHINICIO,FECHFINAL) AS TIMEP FROM TPRESTAMO) as q1
GROUP BY nserie) AS Q2 GROUP BY NSERIE ORDER BY 2

Y ya para traer los demás datos del equipo pues hacemos un INNER JOIN.

Es importante que esta opción no será óptima pues a medida que aumenten los préstamos de equipos la consulta tardará más, pudiera ser tener una columna en el inventario y cada actualización ajustar dicho dato, y así el tiempo de respuesta será menor en la tabla de inventario en lugar de la de préstamos.

Esperemos sigan teniendo éxito en el proyecto, nos seguimos leyendo.

Profesor Miguel Araujo.

Deja un comentario