ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите разобраться с заданием по OVER
13 сообщений из 13, страница 1 из 1
Помогите разобраться с заданием по OVER
    #39770913
RapidSilver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RapidSilver
Гость
Есть кейс заданий для собеседование, с первыми двумя справился успешно, а вот третье не дается ни в какую.

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

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

Вводные:
Код: 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.
32.
33.
34.
35.
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

Вот до чего я дошел самостоятельно
Код: sql
1.
2.
3.
4.
5.
6.
7.
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. Цель выполнения - исключительно саморазвитие.На данную должность не претендую.

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

Заранее благодарю!
...
Рейтинг: 0 / 0
08.02.2019, 08:49
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770923
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
RapidSilverв последней строке для каждого клиента должна быть указана не сумма покупки, а остаток кредита.Т.е. тупо поле остатка кредита в структуре выходного набора.

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

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

Всё это проделываем в WITH, чтобы потом тупо отбросить записи, в которых посчитанный остаток отрицателен.
...
Рейтинг: 0 / 0
08.02.2019, 09:06
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770925
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
PS. Впрочем, особенность SUM(..) OVER (... ORDER BY ...) такова, что сумма сразу считается с накоплением, так что границы можно и не указывать.
...
Рейтинг: 0 / 0
08.02.2019, 09:12
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770926
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Код: 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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
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
;
...
Рейтинг: 0 / 0
08.02.2019, 09:16
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770931
RapidSilver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RapidSilver
Гость
aleks222
Код: 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.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
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
;



у вас получилось совсем не то что нужно. посмотрите в первом посте какого вида таблица должна получиться :)
...
Рейтинг: 0 / 0
08.02.2019, 09:30
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770933
RapidSilver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RapidSilver
Гость
AkinaRapidSilverв последней строке для каждого клиента должна быть указана не сумма покупки, а остаток кредита.Т.е. тупо поле остатка кредита в структуре выходного набора.

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

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

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

Все что вы написали я сделал. внимательно посмотрите на результат который должен получиться в последней колонке мы пишем цены за купленные товары. и только если на товаре не хватает кредита то вместо цены выдаем остаток.
...
Рейтинг: 0 / 0
08.02.2019, 09:33
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770939
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
RapidSilverесли на товаре не хватает кредита то вместо цены выдаем остаток.Ну для этого в WITH получаете два поля, отдельно получаете остатки, а в выходном наборе используете CASE для выбора того, что вывести - или цену, или остаток... делов-то.
...
Рейтинг: 0 / 0
08.02.2019, 09:38
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770942
RapidSilver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RapidSilver
Гость
Akina,

Теперь понял - пошел пробовать
...
Рейтинг: 0 / 0
08.02.2019, 09:42
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770956
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
RapidSilverу вас получилось совсем не то что нужно. посмотрите в первом посте какого вида таблица должна получиться :)

Не смеши мой sql server.
...
Рейтинг: 0 / 0
08.02.2019, 09:54
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770992
RapidSilver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RapidSilver
Гость
aleks222,

это текст задания целиком - ответ тоже часть задания. вы сделали остаток кредита последним столбцом. а надо последней строкой по юзверю
...
Рейтинг: 0 / 0
08.02.2019, 10:48
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39770999
zby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
zby
Участник
RapidSilveraleks222,

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

Вот и сделайте, это ваше задание, вообще...
...
Рейтинг: 0 / 0
08.02.2019, 11:01
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39771001
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
RapidSilver,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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;
...
Рейтинг: 0 / 0
08.02.2019, 11:06
| Ответить | Цитировать | Написать  
Помогите разобраться с заданием по OVER
    #39771025
RapidSilver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RapidSilver
Гость
О блин, ответ подъехал, а я как раз хотел выложить свой.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
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;



Всем огромное человеческое спасибо!
...
Рейтинг: 0 / 0
08.02.2019, 11:44
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите разобраться с заданием по OVER
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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