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

Откуда:
Сообщений: 15
Добрый вечер, помогите решить интересную задачку.
ЕЕ необходимо решить одним запросом, не динамически, т.е. вырожение должно начинаться со слов with или select

Дана таблица @Goods, содержащая информацию о товаре на дату (без времени).
Столбец "Date" - дата прихода/расхода
Столбец "Quant" - количество

Данные в таблице:
---------------------------------
|Date |Остаток
---------------------------------
|2011-02-07 00:00:00.000 |NULL
|2011-02-08 00:00:00.000 |10
|2011-02-09 00:00:00.000 |11
|2011-02-10 00:00:00.000 |12
|2011-02-11 00:00:00.000 |8
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11
|2011-02-15 00:00:00.000 |9

Необходимо сделать запрос, отображающий информацию об остатке на начало тех дней, в которые товародвижение отсутствовало.

Результат должен быть в таком виде:
---------------------------------
|Date |Остаток
---------------------------------
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
24 июн 13, 21:02    [14476519]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
neorad
Member

Откуда:
Сообщений: 15
Пока что додумался до этого, даты привел к цифрам и просматриваю следующий день на совпадение кол-ва:

select DISTINCT t1.`Date` from @Goods as t1
join @Goods as t2 on t1.`Quant`=t2.`Quant`
where (REPLACE(left(t1.`Date`,10),'-','')=REPLACE(left(t2.`Date`,10),'-','')+1)

Выводит все дни, кроме первого, не могу сообразить как его прикрутить.
24 июн 13, 21:06    [14476530]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31425
neorad
Добрый вечер, помогите решить интересную задачку.
Что интересного, примитивный хзапрос, если в таблице есть записи по всем дням

"Выбрать товары, для которые в предыдущий день количество было таким-же"
select * 
from @Goods g
where exists(
select * 
from @Goods p
where p.id_товара = g.id_товара
    and p.Date = dateadd(dd, -1, g.Date)
    and p.Остаток = g.Остаток
)
24 июн 13, 21:09    [14476536]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31425
neorad
Выводит все дни, кроме первого, не могу сообразить как его прикрутить.
Это нормально, ведь в первый день количество товара поменялось, так что его и не надо показывать.
24 июн 13, 21:10    [14476542]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
neorad
Member

Откуда:
Сообщений: 15
немного неточно дал описание
Результат должен быть в таком виде:
---------------------------------
|Date |Остаток
---------------------------------
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11


alexeyvg

"Выбрать товары, для которые в предыдущий день количество было таким-же"
select * 
from @Goods g
where exists(
select * 
from @Goods p
where p.id_товара = g.id_товара
    and p.Date = dateadd(dd, -1, g.Date)
    and p.Остаток = g.Остаток
)


у меня нет поля p.id_товара = g.id_товара
А еще возвращает все даты, а нужно последевательность дат, данный запрос вернет:
|2011-02-09 00:00:00.000 |11
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11

а необходимо:
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11
24 июн 13, 21:19    [14476575]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
declare @t table (d datetime, q int);

insert @t
values
('20110207 00:00:00.000', null),
('20110208 00:00:00.000', 10),
('20110209 00:00:00.000', 11),
('20110210 00:00:00.000', 12),
('20110211 00:00:00.000', 8),
('20110212 00:00:00.000', 11),
('20110213 00:00:00.000', 11),
('20110214 00:00:00.000', 11),
('20110215 00:00:00.000', 9);

with x as
(
 select
  d, q,
  row_number() over (order by d) -
  row_number() over (partition by q order by d) as r
 from
  @t
),
y as
(
 select
  d, q,
  count(*) over (partition by q, r) as c
 from
  x
)
select
 d, q
from
 y
where
 c > 1
order by
 d;
24 июн 13, 22:29    [14476883]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31425
neorad
у меня нет поля p.id_товара = g.id_товара
Ну и не надо этого условия, раз нет ИД товара

neorad
А еще возвращает все даты, а нужно последевательность дат, данный запрос вернет:
|2011-02-09 00:00:00.000 |11
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11

а необходимо:
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11
Мой запрос вернёт как раз то, что было изначально нужно:
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11

Если нужны 3 строки, с первой, можно модифицировать:
declare @t table (d datetime, q int);

insert @t
values
('20110207 00:00:00.000', null),
('20110208 00:00:00.000', 10),
('20110209 00:00:00.000', 11),
('20110210 00:00:00.000', 12),
('20110211 00:00:00.000', 8),
('20110212 00:00:00.000', 11),
('20110213 00:00:00.000', 11),
('20110214 00:00:00.000', 11),
('20110215 00:00:00.000', 9);

select * 
from @t g
where exists(
    select * 
    from @t p
    where p.d = dateadd(dd, -1, g.d)
      and p.q = g.q
  )
or
  exists(
    select * 
    from @t p
    where p.d = dateadd(dd, 1, g.d)
      and p.q = g.q
  )

Конечно, вариант invm ещё лучше, ранжирующие и статистические функции как раз для такого и предназначены
24 июн 13, 22:55    [14476989]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
neorad,

DECLARE @t TABLE (d DATETIME ,q INT);

INSERT @t VALUES
('20110207 00:00:00.000',NULL),
('20110208 00:00:00.000' ,10),
('20110209 00:00:00.000' ,11),
('20110210 00:00:00.000' ,12),
('20110211 00:00:00.000' ,8),
('20110212 00:00:00.000' ,11),
('20110213 00:00:00.000' ,11),
('20110214 00:00:00.000' ,11),
('20110215 00:00:00.000' ,9);

SELECT x.*
FROM   @t AS x
       OUTER APPLY(
          SELECT TOP 1 * 
          FROM   @t AS y
          WHERE  y.d BETWEEN x.d -1 AND x.d + 1
          AND    y.d <> x.d
          AND    y.q = x.q
      ) AS y
WHERE  y.d IS NOT NULL
25 июн 13, 00:30    [14477158]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
neorad
Member

Откуда:
Сообщений: 15
Bator, Благодарю !
Работает!
25 июн 13, 06:50    [14477344]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
neorad
Работает!
И благополучно перестанет как только в датах появятся разрывы.
25 июн 13, 09:58    [14477840]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
iap
Member

Откуда: Москва
Сообщений: 46999
Bator
neorad,

DECLARE @t TABLE (d DATETIME ,q INT);

INSERT @t VALUES
('20110207 00:00:00.000',NULL),
('20110208 00:00:00.000' ,10),
('20110209 00:00:00.000' ,11),
('20110210 00:00:00.000' ,12),
('20110211 00:00:00.000' ,8),
('20110212 00:00:00.000' ,11),
('20110213 00:00:00.000' ,11),
('20110214 00:00:00.000' ,11),
('20110215 00:00:00.000' ,9);

SELECT x.*
FROM   @t AS x
       OUTER APPLY(
          SELECT TOP 1 * 
          FROM   @t AS y
          WHERE  y.d BETWEEN x.d -1 AND x.d + 1
          AND    y.d <> x.d
          AND    y.q = x.q
      ) AS y
WHERE  y.d IS NOT NULL
А зачем OUTER APPLY, если WHERE y.d IS NOT NULL?
Просто CROSS APPLY - и WHERE не надо?
25 июн 13, 10:09    [14477925]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
invm
neorad
Работает!
И благополучно перестанет как только в датах появятся разрывы.

Судя по постановке задаче - если возникнет, то у них будут и другие проблемы (-:
(Додумывать за другого не всегда правильно, можно много чего нафантазировать)

iap
А зачем OUTER APPLY, если WHERE y.d IS NOT NULL?
Просто CROSS APPLY - и WHERE не надо?

Согласен. Еще можно через exists
25 июн 13, 13:26    [14479765]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Bator
Судя по постановке задаче - если возникнет, то у них будут и другие проблемы (-:
(Додумывать за другого не всегда правильно, можно много чего нафантазировать)
Так в постановке и не оговорена непрерывность значений дат.
25 июн 13, 13:40    [14479866]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
invm
Bator
Судя по постановке задаче - если возникнет, то у них будут и другие проблемы (-:
(Додумывать за другого не всегда правильно, можно много чего нафантазировать)
Так в постановке и не оговорена непрерывность значений дат.


Внимательно читаем задание...
И понимаем, что если бы были разрывы, то вместо:
автор
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11
|2011-02-15 00:00:00.000 |9

было бы:
автор
|2011-02-12 00:00:00.000 |11
|2011-02-15 00:00:00.000 |9


Отсюда можно с великой вероятностью сделать вывод, что их система очень любит данные на каждую дату, об этом косвенно говорит строчка
автор
|2011-02-07 00:00:00.000 |NULL

Учитывая это и то, что сначала под требования попадали 2 строки, потом оказалось, что 3 строки, то если у них появятся пропуски в датах, то у них будут куда большие проблемы.
Последний аргумент: если бы были пропуски, то ТС бы их скорее всего в примере бы показал.
25 июн 13, 16:15    [14481323]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Bator
Внимательно читаем задание...
И понимаем, что если бы были разрывы, то вместо:
автор
|2011-02-12 00:00:00.000 |11
|2011-02-13 00:00:00.000 |11
|2011-02-14 00:00:00.000 |11
|2011-02-15 00:00:00.000 |9

было бы:
автор
|2011-02-12 00:00:00.000 |11
|2011-02-15 00:00:00.000 |9
Это всего лишь предположение.

Строго говоря, задача в постановке ТС нерешаема, ибо наличие неизменного остатка за N дней не означает отсутствие товародвижения за эти же дни.
25 июн 13, 16:57    [14481714]     Ответить | Цитировать Сообщить модератору
 Re: SQL запрос - выбрать последовательность дней.  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
invm
Строго говоря, задача в постановке ТС нерешаема, ибо наличие неизменного остатка за N дней не означает отсутствие товародвижения за эти же дни.

Я пытался Вам на это и указать (-:
25 июн 13, 17:57    [14482092]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить