Este grupo especializado pasados muchos días de trabajo y recopilación de de datos, hallamos la respuesta, queremos que resulte de utilidad para tu trabajo.
Solución:
No está mostrando la consulta que está utilizando para obtener los resultados sin diff. Supongo que es algo como esto:
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value), -- or, if Value is an int, like this, perhaps:
-- AVG(CAST(Value AS decimal(10,2))
Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;
Además, no está explicando qué primero y último significar. En esta respuesta, se supone que primero representa primero en el grupo (de acuerdo con la Date valor) y, de manera similar, último medio lo último en el grupo.
Una forma de lanzar diff podría ser así:
Primero, agregue dos columnas agregadas más, minDate y maxDate, a la consulta original:
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value),
minDate = MIN(Date),
maxDate = MAX(Date),
Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
;
A continuación, vuelva a unir el conjunto de resultados agregado a la tabla original en minDate y en maxDate (por separado) para acceder al correspondiente Values:
SELECT
g.min,
g.max,
g.avg,
diff = last.Value - first.Value,
g.Date
FROM (
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value),
minDate = MIN(Date),
maxDate = MAX(Date),
Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
GROUP BY
DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
) g
INNER JOIN atable first ON first.Date = g.minDate
INNER JOIN atable last ON last .Date = g.maxDate
;
Tenga en cuenta que lo anterior asume Date valores (al menos aquellos que son los primeros o los últimos en sus horas correspondientes) para ser únicos, o obtendría más de una fila para algunas de las horas en la salida.
Una alternativa, si está en SQL Server 2005 o una versión posterior, podría ser utilizar funciones de ventana agregada MIN() OVER (...) y MAX() OVER (...) calcular Values correspondiente a cualquiera minDate o maxDate, antes de agregar todos los resultados de manera similar a como probablemente lo está haciendo ahora. Esto es de lo que estoy hablando específicamente:
WITH partitioned AS (
SELECT
Value,
Date,
GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
)
, firstlast AS (
SELECT
Value,
Date,
GroupDate,
FirstValue = CASE Date WHEN MIN(Date) OVER (PARTITION BY GroupDate) THEN Value END,
LastValue = CASE Date WHEN MAX(Date) OVER (PARTITION BY GroupDate) THEN Value END
FROM partitioned
)
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value), -- or, again, if Value is an int, cast it as a decimal or float
diff = MAX(LastValue) - MIN(FirstValue),
Date = GroupDate
FROM firstlast
GROUP BY
GroupDate
;
Como puede ver, la primera expresión de tabla común (CTE) simplemente devuelve todas las filas y agrega una columna calculada GroupDate, el que se utilizó posteriormente para agrupar / particionar. Por lo tanto, esencialmente solo asigna un nombre a la expresión de agrupación, y eso se hace para una mejor legibilidad / mantenimiento de toda la consulta, ya que luego se hace referencia a la columna más de una vez. Esto es lo que produce el primer CTE:
+-------+------------------+------------------+
| Value | Date | GroupDate |
+-------+------------------+------------------+
| 10 | 10/10/2010 10:00 | 10/10/2010 10:00 |
| 11 | 10/10/2010 10:15 | 10/10/2010 10:00 |
| 15 | 10/10/2010 10:30 | 10/10/2010 10:00 |
| 15 | 10/10/2010 10:45 | 10/10/2010 10:00 |
| 17 | 10/10/2010 11:00 | 10/10/2010 11:00 |
| 18 | 10/10/2010 11:15 | 10/10/2010 11:00 |
| 22 | 10/10/2010 11:30 | 10/10/2010 11:00 |
| 30 | 10/10/2010 11:45 | 10/10/2010 11:00 |
+-------+------------------+------------------+
El segundo CTE agrega dos columnas más al resultado anterior. Utiliza funciones de agregado de ventanas. MIN() OVER ... y MAX() OVER ... para emparejar contra Date, y donde se lleva a cabo el partido, el correspondiente Value se devuelve en una columna separada, ya sea FirstValue o LastValue:
+-------+------------------+------------------+------------+-----------+
| Value | Date | GroupDate | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
| 10 | 10/10/2010 10:00 | 10/10/2010 10:00 | 10 | NULL |
| 11 | 10/10/2010 10:15 | 10/10/2010 10:00 | NULL | NULL |
| 15 | 10/10/2010 10:30 | 10/10/2010 10:00 | NULL | NULL |
| 15 | 10/10/2010 10:45 | 10/10/2010 10:00 | NULL | 15 |
| 17 | 10/10/2010 11:00 | 10/10/2010 11:00 | 17 | NULL |
| 18 | 10/10/2010 11:15 | 10/10/2010 11:00 | NULL | NULL |
| 22 | 10/10/2010 11:30 | 10/10/2010 11:00 | NULL | NULL |
| 30 | 10/10/2010 11:45 | 10/10/2010 11:00 | NULL | 30 |
+-------+------------------+------------------+------------+-----------+
En este punto, todo está listo para la agregación final. los min, max, y avg las columnas se agregarán igual que antes, y diff ahora se puede obtener fácilmente como el agregado FirstValue restado del agregado LastValue. Como puede ver en el conjunto de resultados anterior, puede usar varias funciones para obtener FirstValue y LastValue para el grupo: podría ser MIN, MAX, SUM, AVG – cualquiera si esto fuera suficiente, porque solo hay un valor en cada grupo.
Sin embargo, el SELECT principal, como puede ver, se aplica específicamente MAX() sobre LastValue y MIN() sobre FirstValue. Eso es intencional. Es porque esta segunda sugerencia realmente no requiere Date ser único, como lo hizo el primero, pero, en caso de que minDate o maxDate tiene más de uno asociado Value, resultaría en FirstValue o LastValue que contiene más de un valor por grupo, algo como esto:
+-------+------------------+------------------+------------+-----------+
| Value | Date | GroupDate | FirstValue | LastValue |
+-------+------------------+------------------+------------+-----------+
| 9 | 10/10/2010 10:00 | 10/10/2010 10:00 | 9 | NULL |
| 10 | 10/10/2010 10:00 | 10/10/2010 10:00 | 10 | NULL |
| 11 | 10/10/2010 10:15 | 10/10/2010 10:00 | NULL | NULL |
| 15 | 10/10/2010 10:30 | 10/10/2010 10:00 | NULL | NULL |
| 15 | 10/10/2010 10:45 | 10/10/2010 10:00 | NULL | 15 |
| 17 | 10/10/2010 11:00 | 10/10/2010 11:00 | 17 | NULL |
| 18 | 10/10/2010 11:15 | 10/10/2010 11:00 | NULL | NULL |
| 22 | 10/10/2010 11:30 | 10/10/2010 11:00 | NULL | NULL |
| 30 | 10/10/2010 11:45 | 10/10/2010 11:00 | NULL | 30 |
| 33 | 10/10/2010 11:45 | 10/10/2010 11:00 | NULL | 33 |
+-------+------------------+------------------+------------+-----------+
Supuse que en esta situación sería más natural tomar la diferencia entre los el mejor último valor y el menos primero uno. Sin embargo, debe saber mejor qué regla aplicar aquí, por lo que solo cambiará la consulta en consecuencia.
Puede probar ambas soluciones en SQL Fiddle:
-
# 1
-
# 2
Actualizar
A partir de SQL Server 2012, también puede utilizar las funciones FIRST_VALUE y LAST_VALUE y sustituirlas por las expresiones CASE en el firstlast CTE en mi última consulta anterior, así:
FirstValue = FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
LastValue = LAST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
En este caso, no importará si usa MIN o MAX sobre FirstValue y LastValue más tarde (en el SELECT principal): cada columna tendrá exactamente el mismo valor (primero o último Value en consecuencia) en todas las filas del mismo GroupDate grupo, y así MIN() y MAX() devolvería resultados idénticos en cada caso.
De hecho, puedes conseguir diff directamente en el firstlast CTE y luego, en la consulta principal, simplemente agréguelo usando MIN / MAX o agréguelo a GROUP BY y haga referencia a él sin agregación, así:
WITH partitioned AS (
SELECT
Value,
Date,
GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM atable
)
, firstlast AS (
SELECT
Value,
Date,
GroupDate,
diff = LAST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM partitioned
)
SELECT
min = MIN(Value),
max = MAX(Value),
avg = AVG(Value),
diff,
Date = GroupDate
FROM firstlast
GROUP BY
GroupDate,
diff
;
Avanzando un paso más, podría obtener min, max y avg en firstlast también, en lugar de la consulta principal, usando las funciones de la ventana de contraparte:
min = MIN(Value) OVER (PARTITION BY GroupDate),
max = MAX(Value) OVER (PARTITION BY GroupDate),
avg = AVG(Value) OVER (PARTITION BY GroupDate),
Con estas tres columnas adicionales y el cambio anterior, el firstlast CTE devolverá un conjunto de filas como este para su ejemplo:
+-------+------------------+------------------+-----+-----+-------+------+
| Value | Date | GroupDate | min | max | avg | diff |
+-------+------------------+------------------+-----+-----+-------+------+
| 10 | 10/10/2010 10:00 | 10/10/2010 10:00 | 10 | 15 | 12.75 | 5 |
| 11 | 10/10/2010 10:15 | 10/10/2010 10:00 | 10 | 15 | 12.75 | 5 |
| 15 | 10/10/2010 10:30 | 10/10/2010 10:00 | 10 | 15 | 12.75 | 5 |
| 15 | 10/10/2010 10:45 | 10/10/2010 10:00 | 10 | 15 | 12.75 | 5 |
| 17 | 10/10/2010 11:00 | 10/10/2010 11:00 | 17 | 30 | 21.75 | 13 |
| 18 | 10/10/2010 11:15 | 10/10/2010 11:00 | 17 | 30 | 21.75 | 13 |
| 22 | 10/10/2010 11:30 | 10/10/2010 11:00 | 17 | 30 | 21.75 | 13 |
| 30 | 10/10/2010 11:45 | 10/10/2010 11:00 | 17 | 30 | 21.75 | 13 |
+-------+------------------+------------------+-----+-----+-------+------+
Note como GroupDate, min, max, avg y diff – las columnas que realmente necesita para el conjunto final – simplemente se repiten en todas las filas que pertenecen al mismo grupo. Eso significa que puedes deshacerte de Value y Date, renombrar GroupDate para Date, reorganice ligeramente las columnas, aplique DISTINCT al conjunto resultante, y ha eliminado el último SELECT:
WITH partitioned AS (
SELECT
Value,
Date,
GroupDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)
FROM
atable
)
SELECT DISTINCT
min = MIN(Value) OVER (PARTITION BY GroupDate),
max = MAX(Value) OVER (PARTITION BY GroupDate),
avg = AVG(Value) OVER (PARTITION BY GroupDate),
diff = LAST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(Value) OVER (PARTITION BY GroupDate ORDER BY Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
Date = GroupDate
FROM
partitioned
;
Finalmente, también es posible mover el GroupDate cálculo en el mismo alcance donde min, max etc. se calculan. Puede usar CROSS APPLY para eso y así evitar la necesidad de anidar una consulta por completo; en otras palabras, de esta manera puede deshacerse de la partitioned CTE también. los completo consulta se vería así:
SELECT DISTINCT
min = MIN(t.Value) OVER (PARTITION BY x.GroupDate),
max = MAX(t.Value) OVER (PARTITION BY x.GroupDate),
avg = AVG(t.Value) OVER (PARTITION BY x.GroupDate),
diff = LAST_VALUE(t.Value) OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
- FIRST_VALUE(t.Value) OVER (PARTITION BY x.GroupDate ORDER BY t.Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
Date = x.GroupDate
FROM
atable AS t
CROSS APPLY (SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, Date), 0)) AS x (GroupDate)
;
y devolver los mismos resultados. También puede probarlo en SQL Fiddle.
Si piensas que te ha sido de ayuda este artículo, agradeceríamos que lo compartas con otros desarrolladores de esta forma nos ayudas a dar difusión a nuestro contenido.