Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Следующая дата с использованием локальных переменных  [new]
Pim.
Member

Откуда:
Сообщений: 77
Всем доброго времени суток!

Исходная таблица:
AssetId IsArchive Changed
158463 0 2013-04-25 16:02:36.927
158463 0 2013-04-15 10:54:11.610
158463 0 2013-02-14 16:15:38.537
159430 0 2015-02-19 12:59:00.867
159429 0 2015-02-19 12:56:40.770
158901 0 2015-01-11 00:44:26.240
158901 0 2014-11-12 11:07:13.327
158901 0 2014-05-07 14:27:10.070


Нужно для каждого Changed данного AssetId определить следующую дату (NextChanged) и состояние (NextIsArchive), используя при этом локальные переменные, и если такого нет (т.е. Null), то указать текущее время и предыдущее состояние.

Локальные переменные, потому что решение будет оптимальнее, чем с Join и никакие записи не потеряются.

Таблица уже отсортирована по AssetId, Changed DESC.
21 мар 15, 18:55    [17414305]     Ответить | Цитировать Сообщить модератору
 Re: Следующая дата с использованием локальных переменных  [new]
Pim.
Member

Откуда:
Сообщений: 77
Код представляю себе как-то так (делал по аналогии с MySQL)

USE Intraservice4
DECLARE @next_date VARCHAR(100)
DECLARE @id INT
SET @next_date=''
SET @id=0
SELECT 
m.AssetId,
m.Changed,
m.IsArchive,
CASE 
	WHEN NextChanged IS NULL THEN CURRENT_TIMESTAMP()
	ELSE NextChanged
END as NextChanged
FROM
(
	SELECT 
	t.AssetId,
	t.InventoryNumber,
	t.IsArchive,
	t.Changed,
	CASE
		WHEN @id=t.AssetId THEN @next_date
		ELSE NULL AND @id=t.AssetId
	END as NextChanged
	@next_date = Changed
	FROM
	(
		SELECT TOP(100)PERCENT
		AssetId,
		InventoryNumber,
		IsArchive,
		Changed
		FROM 
		AssetHistory
		WHERE 
		TypeId=23 --ATM 
		AND Changed IS NOT NULL
		ORDER BY AssetId, Changed DESC
	) t
) m
21 мар 15, 19:04    [17414331]     Ответить | Цитировать Сообщить модератору
 Re: Следующая дата с использованием локальных переменных  [new]
Добрый Э - Эх
Guest
Pim.,

lead/lag over() если версия сервера позволяет.
21 мар 15, 19:22    [17414391]     Ответить | Цитировать Сообщить модератору
 Re: Следующая дата с использованием локальных переменных  [new]
Pim.
Member

Откуда:
Сообщений: 77
Добрый Э - Эх,

Если бы все было так просто... К сожалению, не позволяет (2008 R2).

Задачу то можно решить и через JOIN, но у меня лично возникает 2 проблемы:
  • Во-первых, теряется последняя дата. Т.е. нет возможности для последней даты сделать Next - текущее время.
  • Во-вторых, нет возможности вывести значения состояния, т.к. если их включать в список, то надо будет их обязательно включать в GROUP BY, следовательно, он будет группировать по тем полям, по которым этого делать не нужно, из-за чего результат получается неверным.

    SELECT
    t.InventoryNumber,
    --t.IsArchive,
    t.Changed,
    MIN(t.NextChanged)
    --t.NextIsArchive
    FROM
    (
    SELECT 
    ah1.InventoryNumber,
    --ah1.IsArchive,
    ah1.Changed,
    ah2.Changed as NextChanged
    --ah2.IsArchive as NextIsArchive
    FROM 
    AssetHistory ah1
    LEFT JOIN AssetHistory ah2 ON ah1.AssetId=ah2.AssetId
    WHERE 
    ah1.TypeId=23
    AND ah1.Changed IS NOT NULL
    AND ah2.Changed IS NOT NULL
    AND ah2.Changed>ah1.Changed
    )t
    GROUP BY
    t.InventoryNumber,
    t.Changed
    
  • 21 мар 15, 19:29    [17414423]     Ответить | Цитировать Сообщить модератору
     Re: Следующая дата с использованием локальных переменных  [new]
    SERG1257
    Member

    Откуда:
    Сообщений: 2880
    Ваш джойн должен возращать одну или ни одной строки (в случае максимальной Changed)
    Тогда можно будет обойтись без группировки

    Я надеюсь комбинация AssetId,Changed уникальна?
    Добавьте условие типа
    and a2.Changed=(select min(Changed) from AssetHistory a3 where a1.AssetId=a3.AssetId and a2.Changed>a3.Changed)

    Чуть более геморно (синтаксически), но более надежно при отсутствии unique ограничения будет джойнить с первой строкой из набора
    типа
    join (select top 1 id, AssetId, Changed from AssetHistory a2 where a1.AssetId=a2.AssetId and a1.Changed>a2.Changed) b on a1.id=b.id где id - первичный ключ
    21 мар 15, 20:05    [17414571]     Ответить | Цитировать Сообщить модератору
     Re: Следующая дата с использованием локальных переменных  [new]
    SERG1257
    Member

    Откуда:
    Сообщений: 2880
    упс, последний джойн должен выглядеть как left join
    и в подзапрос надо добавить order by AssetId, Changed
    21 мар 15, 20:15    [17414621]     Ответить | Цитировать Сообщить модератору
     Re: Следующая дата с использованием локальных переменных  [new]
    Pim.
    Member

    Откуда:
    Сообщений: 77
    SERG1257
    Ваш джойн должен возращать одну или ни одной строки (в случае максимальной Changed)

    Это понятное дело. Поэтому я и хотел через переменные делать.
    SERG1257
    Я надеюсь комбинация AssetId,Changed уникальна?
    Добавьте условие типа
    and a2.Changed=(select min(Changed) from AssetHistory a3 where a1.AssetId=a3.AssetId and a2.Changed>a3.Changed)

    Уникальна. Такое условие вообще ничего не выводит...
    SERG1257
    Чуть более геморно (синтаксически), но более надежно при отсутствии unique ограничения будет джойнить с первой строкой из набора
    типа
    join (select top 1 id, AssetId, Changed from AssetHistory a2 where a1.AssetId=a2.AssetId and a1.Changed>a2.Changed) b on a1.id=b.id где id - первичный ключ

    В From же нельзя внешнюю адресацию использовать, он ее просто не поймет... Собственно, ошибку и выдает.
    21 мар 15, 20:42    [17414742]     Ответить | Цитировать Сообщить модератору
     Re: Следующая дата с использованием локальных переменных  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9842
    Pim.
    В From же нельзя внешнюю адресацию использовать, он ее просто не поймет
    Можно
    select
     t.*, isnull(a.Changed, getdate()), isnull(a.IsArchive, t.IsArchive)
    from
     MyTable t outer apply
     (select top (1) IsArchive, Changed from MyTable where AssetId = t.AssetId and Changed > t.Changed order by Changed) a
    
    21 мар 15, 20:57    [17414792]     Ответить | Цитировать Сообщить модератору
     Re: Следующая дата с использованием локальных переменных  [new]
    SERG1257
    Member

    Откуда:
    Сообщений: 2880
    >Уникальна.
    Ну и замечательно, к сожалению у вас будет лишний скан таблицы(индекса) на предмет поиска подходящей строки

    Еще одним вариантом решения будет скалярный поздапрос для получения одного значения
    select t.AssetId,
    	t.InventoryNumber,
    	t.IsArchive,
    	t.Changed,
    (select  isnull(min(Changed),CURRENT_TIMESTAMP()) from AssetHistory a2 where ...) as NextChanged,
    
    ..
    без джойнов.

    К сожалению этот поздапрос выводит только одно поле, для другого поля надо делать еще один поздапрос (с еще один скан)

    Грязный способ обойти это ограничение будет засовывание результата в форматированную текстовую строку и распарсивание этой строки в верхнем запросе.
    select
     cast(left(scalar_sub_query,24) as datetime) as NextChanged,
     cast(right(scalar_sub_query),3) as int) as IsArchive,
     other_fields
    from
    (
      select  other_fields, 
      (select top 1 cast(Changed as char(24)+cast(IsArchive as char(3)) from AssetHistory where ... order by Changed) as scalar_sub_query
      from AssetHistory 
    ) as src
    


    > Такое условие вообще ничего не выводит
    Я надеюсь что вы не тупо скопировали условие, ибо я ничего не проверял, а творчески его осмыслили
    21 мар 15, 21:15    [17414858]     Ответить | Цитировать Сообщить модератору
     Re: Следующая дата с использованием локальных переменных  [new]
    Pim.
    Member

    Откуда:
    Сообщений: 77
    Всем спасибо! Заработало как надо.

    SERG1257
    > Такое условие вообще ничего не выводит
    Я надеюсь что вы не тупо скопировали условие, ибо я ничего не проверял, а творчески его осмыслили

    Конечно нет )) Там проблема в логике была.

    Итог:
    WITH tmp as (SELECT AssetId,InventoryNumber as ATM, Changed, IsArchive  FROM AssetHistory WHERE TypeId=23 AND Changed IS NOT NULL)
    SELECT
    t.*, 
    isnull(a.Changed, getdate()) as NextChanged, 
    isnull(a.IsArchive, t.IsArchive) as NextIsArchive
    FROM
     tmp t OUTER apply
     (SELECT TOP (1) IsArchive, Changed FROM tmp WHERE AssetId = t.AssetId AND Changed > t.Changed ORDER BY Changed) a
    ORDER BY t.AssetId, t.Changed
    
    21 мар 15, 21:29    [17414908]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить