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

Откуда:
Сообщений: 146
Подскажите
SELECT t.*
              FROM 
              ( select CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType FROM  [Action].[dbo].[promo_data] 
              where  [IsPromo] = 1 group by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType having count(*) >1  ) as x
              left outer join [Action].[dbo].[promo_data] as t on  x.ItemRelation = t.[ItemRelation] and x.[DocumentNum] = t.[DocumentNum] 
              and x.[DocumentYear] = t.[DocumentYear]
              and x.[CustomerName] = t.[CustomerName]
              and x.[CustomerType] = t.[CustomerType]


есть категория ispromo(0-нет, 1 -есть)
Как сделать чтобы если в страте
CustomerName ItemRelation DocumentNum DocumentYear до 4 включительно целочисленных значений по SaleCount по ispromo=1
, то вернуть только пять предшествующих значений по SaleCount по ispromo=0 и не нулей (т.е. тоже целочисленных)
например
SaleCount	IsPromo
9 0
0 0
1 0
4 0
4 0
0 0
4 0
1 1
2 1
0 1
4 1
0 1


тут 3 целочисленных значения по ispromo=1
1,2,4
и надо вернуть до 5 включительно предшествующих значений по ispromo=0, но не нулей (целых значений может быть меньше 5)
т.е.
4,4,4,1,9

если же у нас 5 целочисленных по ispromo=1, то вернуть все значения по ispromo=0
по sale count

Но если у нас по ispromo=0, SaleCount имеет только нули, вернуть все значения страты, внезависимости сколько у нас целочисленных SaleCount по ispromo=1 .

Сообщение было отредактировано: 19 июн 18, 17:41
19 июн 18, 17:40    [21504109]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox,

думаю, сегодня вам смело можно вручить "переходящий вымпел дружины" за самое путанное и непонятно объяснение задачи...
19 июн 18, 17:44    [21504131]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox,

и вообще меня удивляет... вроде как по смыслу SaleCount - натуральное число. Как натуральное может быть нецелочисленным. Или про что вы?
19 июн 18, 17:45    [21504139]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
целочисленное это не 0 и не отрицательное
-1,-2 уже не подходят
19 июн 18, 18:13    [21504288]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36972
Kontox
целочисленное это не 0 и не отрицательное
-1,-2 уже не подходят
Это вы сами придумали или подсказал что?

Если погуглить 30 секунд, то выясняется, что
http://www.webmath.ru/poleznoe/formules_18_7.php
Целыми числами называются все натуральные числа, все числа противоположные им по знаку и нуль.


Сообщение было отредактировано: 19 июн 18, 18:53
19 июн 18, 18:53    [21504386]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
ну, я просто имел ввиду не нулевые и не отрицательные.
19 июн 18, 18:56    [21504389]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox,

у вас запрос - без сортировки. чем вы определяете порядок следования строк? почему вы решили, что строки вернутся именно "примерно" в таком порядке? у меня нет уверенности в этом . более того, я думаю, что мой сервер будет склонен возвращать данные в таком порядке:

9 0
4 1
4 0
2 1
4 0
1 1
4 0
0 1
1 0
0 1
0 0
0 0
19 июн 18, 19:14    [21504412]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
они упорядочены по дате , просто столбец с датой, я не вставил, но они идут строго по дате по возрастанию.
19 июн 18, 19:20    [21504416]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
т.е. запрос возвращает мне все строго по дате по возрастанию , я проверял
19 июн 18, 19:23    [21504418]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox,

окау. идем дальше....
такие ситуации возможны:
9	0
0 0
1 0
4 0
4 0
0 0
4 0
1 1
2 1
0 1
4 1
0 1
9 0
0 0
1 0
4 0
4 0
0 0
4 0
1 1
2 1
0 1
4 1
0 1
как в этом случае обрабатывать чередующиеся группы ? с какого конца в принципе начинается "анализ данных" - сверху? Снизу? С первой группы с промо 0? С первой группы с промо 1?
Понимаете, насколько расплывчато вы сформулировали условия своей хотелки...
19 июн 18, 19:24    [21504419]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox
т.е. запрос возвращает мне все строго по дате по возрастанию , я проверял
без предложения ордер бай - это всего лишь иллюзия, зависящая от плана выполнения запроса. Завтра сервер решит делать реверс-скан по индексу - и вы получите обратный порядок следования строк. А послезавтра ваш админ снесет кластерный индекс в таблице - и вы получите кашу из кучи в результате полного её скана.
но дело ваше...
19 июн 18, 19:26    [21504422]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
Да такое может быть. в таком случае order by Ispromo.
19 июн 18, 19:40    [21504440]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox,

а первый столбец как? или порядок тут неважен? что тогда понимается под "предшествующими" значениями из стартового поста?
19 июн 18, 19:47    [21504449]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
а первый столбец как? или порядок тут неважен? что тогда понимается под "предшествующими" значениями из стартового поста?

страта должна идти
CustomerName ItemRelation DocumentNum DocumentYear

предыдущие, это предыдущие значения по salecount по ispromo=0
т.е. за пять значений, пока не начнется категория ispromo=1
19 июн 18, 19:51    [21504464]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kontox,

тогда, всё что вам нужно - это CASE и SUM() OVER(PARTITION BY ... ORDER BY ...) (Если версия сервера позволяет - 2012 и выше)
19 июн 18, 20:22    [21504490]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
про case when я подозревал,но не понимаю как эту конструкцию в запрос вставить. Можете показать?
19 июн 18, 20:34    [21504501]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Lepsik
Member

Откуда: glubinka
Сообщений: 4256
Kontox,

SELECT CASE WHEN 2 > 1 THEN 3 ELSE 4 END AS test
20 июн 18, 00:12    [21505166]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
SELECT t.*
              FROM 
              ( select CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType FROM  [Action].[dbo].[promo_data] 
              where  [IsPromo] = 1 group by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType having count(*) >1  ) as x
              Outer apply(Select top 5 * From [Action].[dbo].[promo_data] as t 
                                Where  x.ItemRelation = t.[ItemRelation] and x.[DocumentNum] = t.[DocumentNum] 
                                     and x.[DocumentYear] = t.[DocumentYear]
                                     and x.[CustomerName] = t.[CustomerName]
                                     and x.[CustomerType] = t.[CustomerType]
                                     and t.[IsPromo] = 0) t
20 июн 18, 05:26    [21505450]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
С проверкой на цепочку IsPromo = 1 (от 4 штук) и выбирает до 5 пяти предыдущих IsPromo = 0 (если в предыдущих встречается IsPromo = 1, более ранние не берет):

Select t.* From (
Select CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType,
sum(cast(IsPromo as int)) over (Partition by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType Order by Date rows 3 FOLLOWING) as MinPromoNext,
lag(IsPromo) over (Partition by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType Order by Date ) as ForCheckStartPormo
From @promo_data) x
cross apply (
Select top 5 *,
          max(t.IsPromo) over (Order by Date RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  as FollowingMaxPromo
  From @promo_data t
Where t.CustomerName=x.CustomerName
and t.[ItemRelation]=x.[ItemRelation]
and t.[DocumentNum]=x.[DocumentNum]
and t.[DocumentYear]=x.[DocumentYear]
and t.CustomerType=x.CustomerType
and t.Date< x.StartPromo
and t.SaleCount > 1
Order by t.Date desc) t
Where x.SumPromoNext = 4 
    and x.ForCheckStartPormo = 0 
    and t.FollowingMaxPromo = 0
20 июн 18, 06:25    [21505464]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Если длина цепочки IsPromo=1 должна быть <=4:

Select t.* From (
Select CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType,IsPromo,
Min(IsPromo) over (Partition by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType Order by Date rows 4 FOLLOWING) as MinPromoNext,
lag(IsPromo) over (Partition by CustomerName,[ItemRelation], [DocumentNum], [DocumentYear],CustomerType Order by Date ) as ForCheckStartPormo
From @promo_data) x
cross apply (
Select top 5 *,
          max(t.IsPromo) over (Order by Date RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  as FollowingMaxPromo
  From @promo_data t
Where t.CustomerName=x.CustomerName
and t.[ItemRelation]=x.[ItemRelation]
and t.[DocumentNum]=x.[DocumentNum]
and t.[DocumentYear]=x.[DocumentYear]
and t.CustomerType=x.CustomerType
and t.Date< x.StartPromo
and t.SaleCount > 1
Order by t.Date desc) t
Where x.IsPromo = 1
    and x.MinPromoNext = 0
    and x.ForCheckStartPormo = 0 
    and t.FollowingMaxPromo = 0
20 июн 18, 06:31    [21505468]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
Kopelly,

тестовые данные, на которых запросы писали, показать можете? почему-то не покидает чувство, что вы всё сделали через чур сложно, с большим количеством лишних вычислений.
20 июн 18, 06:47    [21505472]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Щукина Анна,
declare @promo_data table 
(KeyID int,
OrderID int,
SaleCount int,
IsPromo int)

Insert into @promo_data Values 
(1,1,9,0),
(1,2,0,0),
(1,3,1,0),
(1,4,4,1),
(1,5,4,0),
(1,6,0,0),
(1,7,4,0),
(1,8,1,1),
(1,9,2,1),
(1,10,0,1),
(1,11,4,1),
(1,12,4,1),
(1,13,4,0),
(1,14,0,1);

Select * From (
Select KeyID,OrderID,
sum(cast(IsPromo as int)) over (Partition by KeyID Order by OrderID ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING) as SumPromoNext,
lag(IsPromo) over (Partition by KeyID Order by OrderID ) as ForCheckStartPormo
From @promo_data) x
cross apply (
Select top 5 *,
max(t.IsPromo) over (Order by OrderID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  as FollowingMaxPromo
From @promo_data t
Where t.KeyID = x.KeyID
  and t.OrderID < x.OrderID
  and t.SaleCount > 1
Order by t.OrderID desc
) t
Where x.SumPromoNext = 4 
  and x.ForCheckStartPormo = 0 
  and t.FollowingMaxPromo = 0
20 июн 18, 06:50    [21505475]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Ошибку нашел: Во всех запросах "and t.SaleCount > 1" нужно исправить на "and t.SaleCount > 0"!
20 июн 18, 06:53    [21505477]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27753
Kontox
ну, я просто имел ввиду не нулевые и не отрицательные.

То есть натуральные
20 июн 18, 07:53    [21505512]     Ответить | Цитировать Сообщить модератору
 Re: Вернуть целочисленные значения по заданному условию.  [new]
Kontox
Member

Откуда:
Сообщений: 146
Все ок, отработало, без вас бы не справился
25 июн 18, 19:20    [21519670]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить