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

Откуда:
Сообщений: 6
Есть кейс заданий для собеседование, с первыми двумя справился успешно, а вот третье не дается ни в какую.

сам текст задания.

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

Вводные:
declare @UserCredit table (
Id int IDENTITY(1,1),
UserId int,
Credit numeric(18,2)
);
 
insert into @UserCredit
values (1, 20), (2, 25);
 
declare @UserPurchase table (
Id int IDENTITY(1,1),
UserId int,
Cost numeric(18,2),
DT date,
Name varchar(50)
);
 
insert into @UserPurchase
values
 (1, 5, '24.04.2016', 'sku1'),
 (1, 6, '19.04.2016', 'sku2'),
 (1, 7, '22.04.2016', 'sku3'),
 (1, 8, '04.04.2016', 'sku4'),
 (1, 4, '18.04.2016', 'sku5'),
 (1, 5, '18.04.2016', 'sku6'),
 (1, 2, '29.04.2016', 'sku7');
 insert into @UserPurchase
values
 (2, 5, '24.04.2016', 'sku1'),
 (2, 6, '19.04.2016', 'sku2'),
 (2, 7, '22.04.2016', 'sku3'),
 (2, 8, '04.04.2016', 'sku4'),
 (2, 4, '18.04.2016', 'sku5'),
 (2, 2, '29.04.2016', 'sku7');
 

Результат запроса:

UserId DT Name Purchase/Rest
1 2016-04-29 sku7 2.00
1 2016-04-24 sku1 5.00
1 2016-04-22 sku3 7.00
1 2016-04-19 sku2 6.00
1 2016-04-18 sku6 0.00
2 2016-04-29 sku7 2.00
2 2016-04-24 sku1 5.00
2 2016-04-22 sku3 7.00
2 2016-04-19 sku2 6.00
2 2016-04-18 sku5 4.00
2 2016-04-04 sku4 1.00

Вот до чего я дошел самостоятельно
select 	c.Userid, p.dt, p.name, p.cost, 
		c.credit-sum(p.cost) OVER (PARTITION by c.userid ORDER by p.dt DESC, p.name DESC) as 'Purchase/Rest'
		
from  @UserCredit as c
INNER JOIN @UserPurchase as p on p.UserId=c.UserId

ORDER by c.Userid, p.dt DESC;

но как мне впихнуть в один столбец и значение цены и результат over в нужной строчке категорически непонятно. Сразу говорю, это мой первый опыт общения с T-SQl. Цель выполнения - исключительно саморазвитие.На данную должность не претендую.

Настоятельно прошу готового решения не писать. просто натолкните на правильный путь.

Заранее благодарю!
8 фев 19, 08:49    [21803999]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19486
RapidSilver
в последней строке для каждого клиента должна быть указана не сумма покупки, а остаток кредита.
Т.е. тупо поле остатка кредита в структуре выходного набора.

RapidSilver
запрос, результатом которого будет таблица с покупками всех клиентов,
отсортированная в обратном хронологическом порядке.
Ну тут ORDER BY без фантазий.

RapidSilver
В этой таблице должны отобразиться только те покупки, на которые хватит кредита.
Т.е. в указанном порядке нужно считать сумму с накоплением (ну, вернее, вычитать её из кредита, дабы получать сразу указанное выше значение поля остатка). Тут как раз и надобен SUM() OVER (). Партиция, само собой, по клиенту, порядок - указан, а границы - от начала (UNBOUNDED PRECEIDING) и до текущей записи (CURRENT RECORD).

Всё это проделываем в WITH, чтобы потом тупо отбросить записи, в которых посчитанный остаток отрицателен.
8 фев 19, 09:06    [21804011]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19486
PS. Впрочем, особенность SUM(..) OVER (... ORDER BY ...) такова, что сумма сразу считается с накоплением, так что границы можно и не указывать.
8 фев 19, 09:12    [21804014]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
aleks222
Member

Откуда:
Сообщений: 747
declare @UserCredit table (
Id int IDENTITY(1,1),
UserId int,
Credit numeric(18,2)
);
 
insert into @UserCredit
values (1, 20), (2, 25);
 
declare @UserPurchase table (
Id int IDENTITY(1,1),
UserId int,
Cost numeric(18,2),
DT date,
Name varchar(50)
);
 
insert into @UserPurchase
values
 (1, 5, '24.04.2016', 'sku1'),
 (1, 6, '19.04.2016', 'sku2'),
 (1, 7, '22.04.2016', 'sku3'),
 (1, 8, '04.04.2016', 'sku4'),
 (1, 4, '18.04.2016', 'sku5'),
 (1, 5, '18.04.2016', 'sku6'),
 (1, 2, '29.04.2016', 'sku7');
 insert into @UserPurchase
values
 (2, 5, '24.04.2016', 'sku1'),
 (2, 6, '19.04.2016', 'sku2'),
 (2, 7, '22.04.2016', 'sku3'),
 (2, 8, '04.04.2016', 'sku4'),
 (2, 4, '18.04.2016', 'sku5'),
 (2, 2, '29.04.2016', 'sku7');


with c as ( select * from @UserCredit ) 
   , p as ( select *, n = row_number() over(partition by UserId order by DT asc) from @UserPurchase )
   , pc as ( select p.UserId, p.Cost, p.DT, p.n, c.Credit, Rest = cast( c.Credit - p.Cost as money ) from p inner join c on p.UserId = c.UserId and p.Cost <= c.Credit where p.n = 1 
             union all
             select p.UserId, p.Cost, p.DT, p.n, pc.Credit, Rest = cast( pc.Rest - p.Cost as money ) from pc inner join p on p.UserId = pc.UserId and p.n = pc.n + 1 and p.Cost <= pc.Rest
           )
  select * from pc
    order by UserID, DT desc
;
   
8 фев 19, 09:16    [21804017]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
RapidSilver
Member

Откуда:
Сообщений: 6
aleks222
declare @UserCredit table (
Id int IDENTITY(1,1),
UserId int,
Credit numeric(18,2)
);
 
insert into @UserCredit
values (1, 20), (2, 25);
 
declare @UserPurchase table (
Id int IDENTITY(1,1),
UserId int,
Cost numeric(18,2),
DT date,
Name varchar(50)
);
 
insert into @UserPurchase
values
 (1, 5, '24.04.2016', 'sku1'),
 (1, 6, '19.04.2016', 'sku2'),
 (1, 7, '22.04.2016', 'sku3'),
 (1, 8, '04.04.2016', 'sku4'),
 (1, 4, '18.04.2016', 'sku5'),
 (1, 5, '18.04.2016', 'sku6'),
 (1, 2, '29.04.2016', 'sku7');
 insert into @UserPurchase
values
 (2, 5, '24.04.2016', 'sku1'),
 (2, 6, '19.04.2016', 'sku2'),
 (2, 7, '22.04.2016', 'sku3'),
 (2, 8, '04.04.2016', 'sku4'),
 (2, 4, '18.04.2016', 'sku5'),
 (2, 2, '29.04.2016', 'sku7');


with c as ( select * from @UserCredit ) 
   , p as ( select *, n = row_number() over(partition by UserId order by DT asc) from @UserPurchase )
   , pc as ( select p.UserId, p.Cost, p.DT, p.n, c.Credit, Rest = cast( c.Credit - p.Cost as money ) from p inner join c on p.UserId = c.UserId and p.Cost <= c.Credit where p.n = 1 
             union all
             select p.UserId, p.Cost, p.DT, p.n, pc.Credit, Rest = cast( pc.Rest - p.Cost as money ) from pc inner join p on p.UserId = pc.UserId and p.n = pc.n + 1 and p.Cost <= pc.Rest
           )
  select * from pc
    order by UserID, DT desc
;
   


у вас получилось совсем не то что нужно. посмотрите в первом посте какого вида таблица должна получиться :)
8 фев 19, 09:30    [21804027]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
RapidSilver
Member

Откуда:
Сообщений: 6
Akina
RapidSilver
в последней строке для каждого клиента должна быть указана не сумма покупки, а остаток кредита.
Т.е. тупо поле остатка кредита в структуре выходного набора.

RapidSilver
запрос, результатом которого будет таблица с покупками всех клиентов,
отсортированная в обратном хронологическом порядке.
Ну тут ORDER BY без фантазий.

RapidSilver
В этой таблице должны отобразиться только те покупки, на которые хватит кредита.
Т.е. в указанном порядке нужно считать сумму с накоплением (ну, вернее, вычитать её из кредита, дабы получать сразу указанное выше значение поля остатка). Тут как раз и надобен SUM() OVER (). Партиция, само собой, по клиенту, порядок - указан, а границы - от начала (UNBOUNDED PRECEIDING) и до текущей записи (CURRENT RECORD).

Всё это проделываем в WITH, чтобы потом тупо отбросить записи, в которых посчитанный остаток отрицателен.


Все что вы написали я сделал. внимательно посмотрите на результат который должен получиться в последней колонке мы пишем цены за купленные товары. и только если на товаре не хватает кредита то вместо цены выдаем остаток.
8 фев 19, 09:33    [21804031]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19486
RapidSilver
если на товаре не хватает кредита то вместо цены выдаем остаток.
Ну для этого в WITH получаете два поля, отдельно получаете остатки, а в выходном наборе используете CASE для выбора того, что вывести - или цену, или остаток... делов-то.
8 фев 19, 09:38    [21804039]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
RapidSilver
Member

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

Теперь понял - пошел пробовать
8 фев 19, 09:42    [21804042]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
aleks222
Member

Откуда:
Сообщений: 747
RapidSilver
у вас получилось совсем не то что нужно. посмотрите в первом посте какого вида таблица должна получиться :)


Не смеши мой sql server.
8 фев 19, 09:54    [21804058]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
RapidSilver
Member

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

это текст задания целиком - ответ тоже часть задания. вы сделали остаток кредита последним столбцом. а надо последней строкой по юзверю
8 фев 19, 10:48    [21804105]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
zby
Member

Откуда:
Сообщений: 34
RapidSilver
aleks222,

это текст задания целиком - ответ тоже часть задания. вы сделали остаток кредита последним столбцом. а надо последней строкой по юзверю


Вот и сделайте, это ваше задание, вообще...
8 фев 19, 11:01    [21804117]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
invm
Member

Откуда: Москва
Сообщений: 8802
RapidSilver,

with t as
(
 select
  c.Userid, p.dt, p.name, p.cost, 
  c.credit,
  sum(p.cost) over (partition by c.UserId order by p.dt desc, p.name desc) as rt
 from  @UserCredit as c
 inner join @UserPurchase as p on p.UserId=c.UserId
)
select
 UserId, DT, Name, case when credit >= rt then cost else credit - rt + cost end
from
 t
where
 credit - rt + cost >= 0
order by
 UserId, dt desc;
8 фев 19, 11:06    [21804121]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с заданием по OVER  [new]
RapidSilver
Member

Откуда:
Сообщений: 6
О блин, ответ подъехал, а я как раз хотел выложить свой.

сделал немного по другому

WITH t as(	SELECT 	c.Userid, p.dt, p.name, p.cost, 
			c.credit-sum(p.cost) OVER (PARTITION by c.userid ORDER by p.dt DESC, p.name DESC) as Rest		
			FROM  @UserCredit as c
			INNER JOIN @UserPurchase as p on p.UserId=c.UserId
		  )
SELECT UserId, DT, Name, 'p/r'= 
CASE WHEN rest+ cost >= cost THEN cost
	 WHEN rest+ cost < cost THEN rest +cost
END
FROM t WHERE Rest+cost >= 0
ORDER by Userid, dt DESC;


Всем огромное человеческое спасибо!
8 фев 19, 11:44    [21804176]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить