Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по LAG / 11 сообщений из 11, страница 1 из 1
08.09.2020, 20:51
    #39996709
Pallich
Pallich
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Добрый день.

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

Поясню, что имею ввиду.
Есть таблица Facts с полями: Дата, Товар, Магазин, Сумма

Нужно посчтать для каждого Товара и Магазина - Сумму но в (Дата-12мес).

Можно сделать джоином, тут проблем нет, все работает.
Но хочется попробовать с LAG.

Как получить нужное значение, НО с условием, что некоторые даты могут в прошлом быть пропущены (сдвиг указанный вручную -12 вытянет не то значение)?

Вот мои жалкие попытки:

with cte as (select
Date,
Tovar,
Magaz,
sum([Summ]) as Summ
from [Facts]
group by Date,Tovar,Magaz
)
select Date,
Tovar,
Magaz,
lag(Summ,12) over (order by [Period],Tovar,Magaz) as prev_value
from cte


Спасибо.

Microsoft SQL Server 2017 (RTM) Еxpress Edition (64-bit)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 21:06
    #39996712
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
with cte as (select
Date,
Tovar,
Magaz,
sum([Summ]) as Summ
from [Facts]
group by Date,Tovar,Magaz
)
select Date,
Tovar,
Magaz,
lag(Summ,12) over (order by [Period],Tovar,Magaz) as prev_value
from cte



А что такое у вас [Period]? У вас есть только Date,Tovar,Magaz и sum([Summ]) из СТЕ. Если [Period] это Date, то почему вы делаете отступ в lag 12 строк? Это же не является 12 месяцам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 21:07
    #39996713
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
LAG, точнее, window definition, не может отмерить ровно 12 месяцев. Я уж не говорю о том, что записи ровно 12 месяцев назад может и не быть, а может их быть и несколько.

Определение окна может выделить группу, с которой работать (PARTITION BY), записи, с которыми работать (ROWS / RANGE - отсчитав либо в штуках, либо от и до), указать их сортировку (ORDER BY), и, наконец, сказать, какую по счёту запись собственно брать.

Думаю, надо уточнить задачу, возможно, примером. Создайте адекватную модель в формате online fiddle, дайте ссылку, и покажите требуемый для него результат.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.09.2020, 23:25
    #39996735
Pallich
Pallich
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Akina, PizzaPizza,

На скрине пример данных таблицы Фактов.
Данные все помесячные (всегда на 1 число месяца).

Подскажите, как сделать запрос, который бы возвращал прошлогоднее значение для значения по дате 01.05.2020?

То есть я хочу для даты 01.05.2020, в разрезах Tovar и Мagaz получить значение прошлогоднее (=50).

Важно, что некоторые даты могут в прошлом пропущены.

Спасибо, если кто сможет помочь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 06:40
    #39996764
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Pallich

Важно, что некоторые даты могут в прошлом пропущены.


Значит это будет излишне сложный запрос.
LAG/LEAD позволяют выбирать значения соседних записей, но не позволяют писать условия какая по счету запись к какой будет выбираться.
Если уж ради спортивного интереса нужно LAG, то делайте календарик помесячный, соединяйте его по дате с вашими фактами, получите всегда 12 строк назад запись для товара.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 07:47
    #39996769
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Pallich
как сделать запрос, который бы возвращал прошлогоднее значение для значения по дате 01.05.2020?
Ну раз число везде первое, то тупо
Код: sql
1.
2.
FROM table t1
JOIN table t2 ON t1.Date = DATEADD(YEAR, 1, t2.Date)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 07:50
    #39996770
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Pallich
На скрине пример данных таблицы Фактов.
Хрен ли толку от фотографии?
Идёте на, скажем, https://dbfiddle.uk/?rdbms=sqlserver_2014, заводите DDL и INSERT, проверяете, что не сбоит, и даёте ссылку плюс требуемый вывод для этих данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 08:21
    #39996773
HandKot
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Pallich,

только тут проблема, если не будет данных за какой-нибудь месяц, то возьмется не предыдущий год, а пред предыдущий и так далее
Лучше сделать пересечение на каждый месяц c таблицей календарем , и тогда обычным LAG с параметром 12

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
declare @t table([Date] DateTime, Tovar  nvarchar(10), magaz nvarchar(10), summ int)

Insert into @t([Date], tovar, magaz, summ) Values 
 ('20190101', N'tovar1', N'magaz1', 10)
,('20190201', N'tovar1', N'magaz1', 20)
,('20190301', N'tovar1', N'magaz1', 30)
,('20190401', N'tovar1', N'magaz1', 40)
,('20190501', N'tovar1', N'magaz1', 50)
,('20190601', N'tovar1', N'magaz1', 60)
,('20190701', N'tovar1', N'magaz1', 70)
,('20190801', N'tovar1', N'magaz1', 80)
,('20190901', N'tovar1', N'magaz1', 90)
,('20191001', N'tovar1', N'magaz1', 100)
,('20191101', N'tovar1', N'magaz1', 110)
,('20191201', N'tovar1', N'magaz1', 120)
,('20200101', N'tovar1', N'magaz1', 130)
,('20200201', N'tovar1', N'magaz1', 140)
,('20200301', N'tovar1', N'magaz1', 150)
,('20200401', N'tovar1', N'magaz1', 160)
,('20200501', N'tovar1', N'magaz1', 170)


Select 
	Month(t.[Date])
	, t.[Date]
	, t.Tovar
	, t.magaz
	, t.summ
	, lag(t.summ, 1, null) Over (Partition By Month(t.[Date]) Order By t.[Date])
From
		@t t
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 11:57
    #39996857
Pallich
Pallich
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
HandKot,

Громадное спасибо!

Посл.вопрос.
Скажите, а как посчитать прошлог.значение сугубо для одной даты 01/05/2020?
Просто влепить в конце where не работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 12:05
    #39996862
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
Pallich,

Оберни в подзапрос
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.09.2020, 12:46
    #39996886
Pallich
Pallich
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вопрос по LAG
env,



select
Result.Date,
Result.Magaz,
Result.Tovar,
Result.Summ,
Result.PrevValue
from
(
Select
Month(t.[Date]) as' Month'
, t.[Date] as 'Date'
, t.Tovar as 'Tovar'
, t.magaz as 'Magaz'
, t.summ as 'Summ'
, lag(t.summ, 1, null) Over (Partition By Month(t.[Date]) Order By t.[Date]) as PrevValue
From @t t
) as Result
where cast(Result.Date as date) = '2020-05-01'


Есть!
Спасибо всем!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Вопрос по LAG / 11 сообщений из 11, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 141ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1685676]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 13ms
total:  141ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]