Saltar al contenido

cómo calcular la diferencia del valor de los campos de la primera fila y la última fila en cada grupo

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.

¡Haz clic para puntuar esta entrada!
(Votos: 0 Promedio: 0)



Utiliza Nuestro Buscador

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *