Есть ли способ получить доступ к значению "предыдущей строки" в инструкции SELECT?

Мне нужно рассчитать разницу столбца между двумя строками таблицы, есть ли способ сделать это прямо в SQL? Я использую Microsoft SQL Server 2008.

Я ищу что-то вроде этого:

SELECT value - (previous.value) FROM table

Представьте, что "предыдущая" переменная ссылается на последнюю выбранную строку. Конечно, что с подобным выбором я закончил бы с n-1 строками, выбранными в таблице с n строками, что, вероятно, не является именно тем, что мне нужно.

Возможно ли это каким-то образом?

Ответ 1

SQL не имеет встроенного понятия порядка, поэтому вам нужно заказать какой-то столбец, чтобы это имело смысл. Что-то вроде этого:

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

Если вы знаете, как заказывать вещи, но не как получить предыдущее значение с учетом текущего (EG, вы хотите заказать по алфавиту), то я не знаю, как это сделать в стандартном SQL, но большинство SQL реализации будут иметь расширения для этого.

Вот как работает SQL-сервер, если вы можете упорядочить строки таким образом, чтобы каждый из них отличался:

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

Если вам нужно разорвать связи, вы можете добавить столько столбцов, сколько необходимо для ORDER BY.

Ответ 2

Oracle, PostgreSQL, SQL Server и многие другие системы RDBMS имеют аналитические функции, называемые LAG и LEAD, которые делают именно это.

В SQL Server до 2012 года вам нужно будет сделать следующее:

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

где COL1 - это столбец, который вы заказываете.

Наличие индекса на (COL1, PK) значительно улучшит этот запрос.

Ответ 3

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

Ответ 4

Используйте функцию lag:

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

Последовательности, используемые для идентификаторов, могут пропускать значения, поэтому Id-1 не всегда работает.

Ответ 5

LEFT ПРИСОЕДИНИТЕСЬ к таблице, при условии, что условие соединения выполнено так, что строка, согласованная в объединенной версии таблицы, является одной строкой предыдущей, для вашего конкретного определения "предыдущий".

Обновление: сначала я думал, что вы хотите сохранить все строки, с NULL для условия, когда предыдущей строки не было. Прочитав это снова, вы просто хотите, чтобы эти строки были выбраны, поэтому вы должны использовать внутреннее соединение, а не левое соединение.

Update:

В новых версиях Sql Server также есть функции LAG и LEAD Windowing, которые также могут быть использованы для этого.

Ответ 6

Выбранный ответ будет работать только в том случае, если в последовательности нет пробелов. Однако, если вы используете автогенерированный идентификатор, скорее всего, будут пробелы в последовательности из-за вставленных вставок.

Этот метод должен работать, если у вас есть пробелы

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

Ответ 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