v17 - sql server management studio express

¿Hay alguna manera de acceder al valor de "fila anterior" en una instrucción SELECT?

Question

Necesito calcular la diferencia de una columna entre dos líneas de una tabla. ¿Hay alguna forma de que pueda hacer esto directamente en SQL? Estoy usando Microsoft SQL Server 2008.

Estoy buscando algo como esto:

SELECT value - (previous.value) FROM table

Imaginando que la variable "anterior" hace referencia a la última fila seleccionada. Por supuesto, con un seleccionar así voy a terminar con n-1 filas seleccionadas en una tabla con n filas, eso no es probable, en realidad es exactamente lo que necesito.

¿Es eso posible de alguna manera?

Answer 1

IZQUIERDA ÚNASE a la tabla en sí misma, con la condición de unión calculada para que la fila coincidente en la versión unida de la tabla sea una fila anterior, para su definición particular de "anterior".

Actualización: Al principio estaba pensando que querría mantener todas las filas, con NULL para la condición donde no había una fila anterior. Al volver a leerlo, solo quiere que las filas se seleccionen, por lo que debería unirse internamente en lugar de unirse a la izquierda.

Actualizar:

Las versiones más nuevas de Sql Server también tienen las funciones de ventana LAG y LEAD que se pueden usar para esto también.

Answer 2

La respuesta seleccionada solo funcionará si no hay lagunas en la secuencia. Sin embargo, si está utilizando una identificación autogenerada, es probable que haya lagunas en la secuencia debido a las inserciones que se retrotraeron.

Este método debería funcionar si tienes lagunas

declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by  primarykey

select t1.value - t2.value from @temp  t1
join @temp  t2 
on t1.tempid = t2.tempid - 1

Answer 3

Oracle, PostgreSQL, SQL Server y muchos más motores RDBMS tienen funciones analíticas llamadas LAG y LEAD que hacen esto mismo.

En SQL Server antes de 2012, necesitaría hacer lo siguiente:

SELECT  value - (
        SELECT  TOP 1 value
        FROM    mytable m2
        WHERE   m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
        ORDER BY 
                col1, pk
        )
FROM mytable m1
ORDER BY
      col1, pk

, donde COL1 es la columna por la que está ordenando.

Tener un índice en (COL1, PK) mejorará en gran medida esta consulta.

Answer 4

SQL no tiene una noción de orden incorporada, por lo que debe solicitar una columna para que esto tenga sentido. Algo como esto:

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

Si sabe cómo pedir cosas pero no cómo obtener el valor anterior dado el actual (por ejemplo, si desea ordenar alfabéticamente) entonces no sé de una manera de hacerlo en SQL estándar, pero la mayoría de las implementaciones de SQL tendrán extensiones para hacerlo

Esta es una forma de que el servidor SQL funcione si puede ordenar filas de modo que cada una sea distinta:

select  rank() OVER (ORDER BY id) as ''Rank'', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

Si necesita romper vínculos, puede agregar tantas columnas como sea necesario a ORDER BY.

Answer 5

Use la función de lag :

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

Las secuencias utilizadas para Ids pueden omitir valores, por lo que Id-1 no siempre funciona.

Answer 6

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
    value
  FROM table
)
SELECT
  curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1

Answer 7

select t2.col from (
select col,MAX(ID) id from 
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2