Блог

Я пытаюсь вычислить изменение позиции на основе предыдущих строк ROW_NUMBER() по сравнению с текущей ROW_NUMBER()

В моем запросе используется рекурсивный cte с предложением ROW_NUMBER () НАД .., которое аккуратно выдает мне номер строки результата по его году.

 WITH positions AS (
    SELECT 
        [incidents].state_id, [incidents].year_id, MAX(number_of_incidents) AS total_incidents_in_year,
        ROW_NUMBER() OVER(PARTITION BY [incidents].year_id ORDER BY MAX(number_of_incidents) DESC) AS position
    FROM
        [incidents]
    INNER JOIN
        years AS dy ON dy.year_id = [incidents].year_id
    INNER JOIN
        states AS ds on ds.state_id = [incidents].state_id
    GROUP BY
        [incidents].state_id, [incidents].year_id
)
  

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

 SELECT
    ds.state_name, ds.state_id, [before].total_incidents_in_year, dy.year,
    [before].position AS before_position, [after].position AS after_position,
    ([before].position - [after].position) AS change
FROM
    positions AS [before]
LEFT JOIN 
    positions AS [after] ON [before].position = [after].position   1 AND [before].state_id = [after].state_id AND [before].year_id = [after].year_id
INNER JOIN
    years AS dy ON dy.year_id = [before].year_id
INNER JOIN
    states AS ds on ds.state_id = [before].state_id
ORDER BY
    [before].year_id ASC, [before].total_incidents_in_year DESC
  

К сожалению, это не работает, потому что позиция [after] всегда равна нулю.

Это немного сложно объяснить, поэтому я включил ссылку на sqlfiddle :http://www.sqlfiddle.com /#!18/c7e57e/1

—

Это изображение наглядно объясняет, чего я пытаюсь достичь

В 2011 году Миннесота была на позиции 1, в 2012 году Миннесота была на позиции 3, изменение составило 2

В 2011 году Айова находится на позиции 6, в 2012 году Айова находится на позиции 4, изменение равно -2

В 2011 году Южная Дакота находится на позиции 5, в 2012 году Южная Дакота находится на позиции 5, изменение равно 0

Спасибо

Разобрался.

Я неправильно пытался объединить с помощью ROW_NUMBER(), что привело бы к несоответствующему объединению, потому что номера строк не обязательно правильно совпадали бы с идентификатором состояния.

После изменения его на join в year , что является правильным способом вычисления изменения из года в год, все сошлось.

 WITH positions AS (
    SELECT 
        [incidents].state_id, dy.year, MAX(number_of_incidents) AS total_incidents_in_year,
        ROW_NUMBER() OVER(PARTITION BY dy.year ORDER BY MAX(number_of_incidents) DESC) AS position
    FROM
        [incidents]
    INNER JOIN
        years AS dy ON dy.year_id = [incidents].year_id
    INNER JOIN
        states AS ds on ds.state_id = [incidents].state_id
    GROUP BY
        [incidents].state_id, dy.year
)
SELECT
    ds.state_name, ds.state_id, [before].total_incidents_in_year, dy.year,
    [before].position AS before_position,
    ([before].position - [after].position) AS change
FROM
    positions AS [before]
LEFT JOIN 
    positions AS [after] ON [before].state_id = [after].state_id AND [before].year = [after].year   1
INNER JOIN
    years AS dy ON dy.year = [before].year
INNER JOIN
    states AS ds on ds.state_id = [before].state_id
ORDER BY
    [before].year ASC, [before].total_incidents_in_year DESC
  

http://www.sqlfiddle.com /#!18/c7e57e/11