SQL Server: обнаружение изменений инвентаризации

У меня есть простая инвентарная таблица:

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t
(
    [date] DATE,
    Item VARCHAR(1),
    [Location] INT,
    Qty INT
)

INSERT INTO #t ([date], [Item], [Location], [Qty]) 
VALUES ('2017-11-16', 'A', 1, 5),
       ('2017-11-16', 'B', 1, 5),
       ('2017-11-16', 'B', 2, 10),
       ('2017-11-16', 'A', 3, 1),
       ('2017-11-16', 'C', 3, 2),
       ('2017-11-16', 'A', 4, 20),
       ('2017-11-15', 'A', 1, 5),
       ('2017-11-15', 'B', 1, 5),
       ('2017-11-15', 'B', 2, 10),
       ('2017-11-15', 'A', 3, 1),
       ('2017-11-15', 'C', 3, 8),
       ('2017-11-14', 'A', 1, 10),
       ('2017-11-14', 'B', 1, 1),
       ('2017-11-14', 'B', 2, 10),
       ('2017-11-14', 'A', 3, 1),
       ('2017-11-14', 'C', 3, 8)

Я хотел бы узнать дату (в предложении where), а также разницу в количестве местоположения-элемента-уровня для прошлого.

Таким образом, результат должен быть следующим:

+------------+------+----------+-----+------------+---------+
|    Date    | Item | Location | Qty | LastChange | LastQty |
+------------+------+----------+-----+------------+---------+
| 16.11.2017 | A    |        1 |   5 | 14.11.2017 |      10 |
| 16.11.2017 | B    |        1 |   5 | 14.11.2017 |       1 |
| 16.11.2017 | B    |        2 |  10 |            |         |
| 16.11.2017 | A    |        3 |   1 |            |         |
| 16.11.2017 | C    |        3 |   2 | 15.11.2017 |       8 |
| 16.11.2017 | A    |        4 |  20 |            |         |
+------------+------+----------+-----+------------+---------+

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

Я самостоятельно присоединился к инвентарной таблице. Однако у меня есть проблемы с поиском пункта для исключения нерелевантных наборов данных.

SELECT
    a.[date],
    a.Item,
    a.Location,
    a.qty,
    b.[date] LastChange,
    b.qty LastQty
FROM 
    #t a
LEFT JOIN 
    #t b ON a.Item = b.Item 
         AND a.location = b.location  
         AND b.date < a.date
WHERE   
    a.date = '2017-11-16'

sql-server inventory sql-server-2012 stock
person rasenkantenstein    schedule 16.11.2017    source источник
comment
Оконная функция может быть более эффективной, чем использование подзапросов. Ищите стержень, чтобы сделать это.   -  person Daniel E.    schedule 16.11.2017
comment
Какую версию SQL Server вы используете? Кроме того, оконные функции были введены для повышения производительности в таких случаях, так почему бы не использовать оконные функции?   -  person Giorgos Betsos    schedule 16.11.2017
comment
Достаточно справедливо, если это окажет положительное влияние, чем я склонен их использовать. Версия сервера 11.0.6598.0. Спасибо :-)   -  person rasenkantenstein    schedule 16.11.2017
comment
@JeanDoux: Вы имеете в виду использование функции поворота в MSSQL? Разве это не сработает только для одного столбца для поворота?   -  person rasenkantenstein    schedule 16.11.2017

Ответы (2)


arrow_upward
2
arrow_downward

Вам нужен дополнительный LEFT JOIN для устранения избыточных записей:

SELECT a.[date], a.Item, a.Location, a.qty,
       b.[date] LastChange, b.qty LastQty
FROM t AS a
LEFT JOIN t AS b 
   ON a.Item = b.Item AND a.location = b.location AND b.date < a.date AND a.qty != b.qty
LEFT JOIN t AS c 
   ON c.Item = b.Item AND c.location = b.location AND c.date < b.date    
WHERE   
    a.[date] = '2017-11-16' AND c.Item IS NULL

С использованием

LEFT JOIN t AS c 
   ON c.Item = b.Item AND c.location = b.location AND c.date < b.date

в сочетании с

WHERE   
    ... AND c.Item IS NULL

это все равно, что сказать: дайте мне те b записей, у которых нет других, c записей с более ранней датой.

Демо здесь

Использование оконной функции FIRST_VALUE:

;WITH CTE AS (
    SELECT [date], [Item], [Location], [Qty],
           FIRST_VALUE([date]) OVER (PARTITION BY [Item], [Location] 
                                     ORDER BY [date]) AS LastChange,
           FIRST_VALUE([Qty]) OVER (PARTITION BY [Item], [Location] 
                                    ORDER BY [date]) AS LastQty
    FROM t
)
SELECT [date], [Item], [Location], [Qty],
       IIF([Qty] != [LastQty], LastChange, NULL) AS LastChange,
       IIF([Qty] != [LastQty], LastQty, NULL) AS LastQty
FROM CTE
WHERE [date] = '2017-11-16'

Демо здесь

person Giorgos Betsos    schedule 16.11.2017
comment
Спасибо. Многому здесь научился. Оконные функции действительно должны работать быстрее! Спасибо. - person rasenkantenstein; 16.11.2017
comment
Однако есть одна проблема при использовании FIRST_VALUE. Промежуточные изменения не обнаруживаются. В вашей демонстрации вы можете увидеть это в статье C. Последнее изменение должно быть 15.11. не 14.11. - person rasenkantenstein; 16.11.2017
comment
@rasenkantenstein Итак, вы хотите обнаружить последнюю запись в этом случае? Что делать, если у вас несколько изменений, например ('2017-11-16', 'C', 3, 2), ` ('2017-11-16', 'C', 3, 8), ('2017-11-16', 'C', 3, 5)? - person Giorgos Betsos; 16.11.2017
comment
Привет, Гиоргос, точно. Дата предыдущего изменения и сумма этого изменения. Это таблица моментальных снимков, которая создается один раз в день, первичный ключ — дата+местоположение+элемент. Возможно только одно состояние. Однако оконные функции действительно были очень быстрыми. - person rasenkantenstein; 16.11.2017

arrow_upward
1
arrow_downward

Попробуйте этот запрос

DECLARE @ReportDate date='20171116'

SELECT
  curData.[date],
  curData.Item,
  curData.Location,
  curData.Qty,
  lastData.[date] LastChange,
  lastData.Qty LastQty
FROM
  (
    SELECT *
    FROM #t
    WHERE [date]=@ReportDate
  ) curData
OUTER APPLY
  (
    SELECT TOP 1 *
    FROM #t lastData
    WHERE lastData.Item=curData.Item
      AND lastData.Location=curData.Location
      AND lastData.[date]<curData.[date]
      AND lastData.Qty<>curData.Qty
    ORDER BY lastData.[date] DESC
  ) lastData
person Sergey Menshov    schedule 16.11.2017
comment
Я изменил свой ответ. В моей первой версии я забыл использовать условие lastData.Qty<>curData.Qty - person Sergey Menshov; 16.11.2017
comment
Привет, я никогда не использовал концепцию применения до сих пор. Я мог бы захотеть изучить это дальше. Благодарю вас! - person rasenkantenstein; 16.11.2017
comment
@rasenkantenstein - SQL - удивительный язык. Желаю успехов на этом пути! - person Sergey Menshov; 16.11.2017