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

Откуда:
Сообщений: 11
У меня есть 2 таблицы
Balance и Statistica. Приведу также часть записей из этих таблиц.
3 строчки из таблицы Balance(ID , ID_Statistica, DateReport, Dogovor, Dolg):

('29', '12', '20141231', 'a', '100000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000'),
('30', '12', '20141231', 'b', '100000-1000-1000-1000-1000-1000-1000-1000-1000-1000-1000-1000'),
('31', '12', '20141231', 'c', '200000-10000-10000-10000-10000-10000-10000')

1 строчка из таблицы Statistica (ID,DateReport,Days, Stavka):

('12', '20141231', '16', '0.035*1.15*1.15*1.15*1.15/1.05/1.05/1.05/1.05/1.05/1.05/1.05')

Они связаны по внешнему ключу Balance.ID_Statistica=Statistica.ID. Количество записей более тысячи.
Мне нужно умножить Dolg на Stavka. Dolg и Stavka стал хранить в nvarhar(max). Как это сделать не пойму. Спасибо за помощь.
25 сен 18, 13:55    [21685430]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20535
Это в варчарах хранится выражение, которое надо вычислить. или это такой забубённый CSV?
25 сен 18, 14:04    [21685450]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
court
Member

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

создай в базе вот такую функцию
+ fnEvalJS
/*
нужно будет разрешить OLE Automation:

EXEC sp_configure 'OLE Automation', 1;
RECONFIGURE;
EXEC sp_configure;
*/

create FUNCTION [dbo].[fnEvalJS]
(
	@txt		varchar(4000)		
)
RETURNS varchar(8000) 
as
begin
	declare @Result varchar(8000)
	declare @object int, @object2 int
	declare @hr int

	set @txt='<body><script>document.write(' + @txt + ');</script></body>'

	exec @hr=sp_OACreate 'HTMLfile', @object out
	exec @hr=sp_OAMethod @object, 'write', null, @txt
	exec @hr=sp_OAGetProperty @object, 'body', @object2 out 
	exec @hr=sp_OAGetProperty @object2, 'innerText', @Result out 

	exec @hr=sp_OADestroy @object2
	exec @hr=sp_OADestroy @object
	
	return @Result 
end

и можешь не отбивать себе руки дальше хранить хранить всё в nvarhar-арах :)

;with Balance (ID , ID_Statistica, DateReport, Dogovor, Dolg) as ( 
	select * from (values
	('29', '12', '20141231', 'a', '100000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000'),
	('30', '12', '20141231', 'b', '100000-1000-1000-1000-1000-1000-1000-1000-1000-1000-1000-1000'),
	('31', '12', '20141231', 'c', '200000-10000-10000-10000-10000-10000-10000')) as Balance (ID , ID_Statistica, DateReport, Dogovor, Dolg) 
),
Statistica (ID,DateReport,Days, Stavka) as (
	select * from (values
	('12', '20141231', '16', '0.035*1.15*1.15*1.15*1.15/1.05/1.05/1.05/1.05/1.05/1.05/1.05')) as Statistica (ID,DateReport,Days, Stavka)
)

select 
	try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float) as xz
from Balance a inner join Statistica b on a.ID_Statistica=b.ID 

xz
2914,80237672557
3871,90166460561
6090,63183196389
25 сен 18, 14:14    [21685462]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

Откуда:
Сообщений: 11
Храню в nvarchar(max). Я так понял нужно преобразовать сначала, идеи мои иссякли)
25 сен 18, 14:15    [21685463]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

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

Спасибо, все работает!!! Возник вопрос, а можно с помощью этой функции посчитать столбик Dolg в таблице баланс, если нужно узнать сумму общего долга?
25 сен 18, 14:45    [21685494]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
court
Member

Откуда:
Сообщений: 2016
Pacifany
Возник вопрос, а можно с помощью этой функции посчитать столбик Dolg в таблице баланс, если нужно узнать сумму общего долга?
А кто нам может запретить ?! :)

;with Balance (ID , ID_Statistica, DateReport, Dogovor, Dolg) as ( 
	select * from (values
	('29', '12', '20141231', 'a', '100000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000'),
	('30', '12', '20141231', 'b', '100000-1000-1000-1000-1000-1000-1000-1000-1000-1000-1000-1000'),
	('31', '12', '20141231', 'c', '200000-10000-10000-10000-10000-10000-10000')) as Balance (ID , ID_Statistica, DateReport, Dogovor, Dolg) 
)

select 
	sum((try_cast([dbo].[fnEvalJS](a.Dolg) as float))) as xz
from Balance a
25 сен 18, 14:50    [21685504]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

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

Спасибо большое. Последний вопрос, а как мне вытащить кроме суммы DateReport и Days?
25 сен 18, 15:20    [21685550]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

Откуда:
Сообщений: 11
Мне нужно показать, сколько было сформировано резервов по договору “с”(столбец Dogobor) на конец декабря 2014 года, если доля резервов по данному продукту относительно общей суммы резервов составляет 14%; ставку резервирования по данному договору на конец декабря 2014. Показать в одном Окне результатов.(Резерв = Dolg*Stavka).
Научился как находить резерв с помощью функции, а дальше ни как. С подобным заданием не сталкивался никогда((( Буду благодарен за любую помощь.
25 сен 18, 17:26    [21685778]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

Откуда:
Сообщений: 11
Решил задачу, завернул в ABS:
select 	 
	ABS (sum((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float) *0.14))) as Rezerv,
	ABS ((try_cast([dbo].[fnEvalJS](b.Stavka) as float))) as Stavka
from Balance a 
inner join Statistica b on a.ID_Statistica=b.ID
where a.Dogovor='c' AND b.DateReport = '20141031'
group by b.Stavka



Один вопрос не закрытый, я пока не могу решить ее. Мне нужно показать минимальную ставку в году, за какой месяц она была(вывести по выборке столбец DateReport) и сумму резерва (Dolg*Stavka). Минимальную ставку я нахожу таким образом:
select 	 
	ABS (sum((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float) *0.14))) as Rezerv,
	ABS (min((try_cast([dbo].[fnEvalJS](b.Stavka) as float))) as Stavka
from Balance a inner join Statistica b on a.ID_Statistica=b.ID


А как по найденной ставки вытащить DataReport и посчитать резерв (Dolg*Stavka) я не могу понять. Вложенным селектом не получается, отдельную функцию писать для этого? Спасибо за помощь!
25 сен 18, 22:48    [21686028]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Pacifany
Мне нужно показать, сколько было сформировано резервов по договору “с”(столбец Dogobor) на конец декабря 2014 года, если доля резервов по данному продукту относительно общей суммы резервов составляет 14%; ставку резервирования по данному договору на конец декабря 2014. Показать в одном Окне результатов.(Резерв = Dolg*Stavka).
Научился как находить резерв с помощью функции, а дальше ни как. С подобным заданием не сталкивался никогда((( Буду благодарен за любую помощь.


Судя по ID к дате 20141231

Pacifany
(ID , ID_Statistica, DateReport, Dogovor, Dolg):
('29', '12', '20141231', 'a', '100000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000-3000'),

(ID,DateReport,Days, Stavka):
('12', '20141231', '16', '0.035*1.15*1.15*1.15*1.15/1.05/1.05/1.05/1.05/1.05/1.05/1.05')


у вас записей не очень много. Точнее, судя по количеству значений в Dolg у вас это помесячные срезы, коих всего 12 в году. В этом случае с функцией можно жить.

Но чисто FYI: тот, кто проектировал БД не знаком с базовыми (извините за тавтологию) понятиями как работают базы данных. Эти таблицы нарушают первую нормальную форму, поэтому вы и имеете проблему использования ваших значений.
26 сен 18, 01:42    [21686108]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

Откуда:
Сообщений: 11
В таблице статистика записей не так много, а в таблице баланс достаточно много. Вы и будете продолжать писать про 1НФ или предложите вариант решения?
26 сен 18, 10:10    [21686331]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
court
Member

Откуда:
Сообщений: 2016
Pacifany
Один вопрос не закрытый, я пока не могу решить ее. Мне нужно показать минимальную ставку в году, за какой месяц она была(вывести по выборке столбец DateReport) и сумму резерва (Dolg*Stavka). Минимальную ставку я нахожу таким образом:
select 	 
	ABS (sum((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float) *0.14))) as Rezerv,
	ABS (min((try_cast([dbo].[fnEvalJS](b.Stavka) as float))) as Stavka
from Balance a inner join Statistica b on a.ID_Statistica=b.ID



А как по найденной ставки вытащить DataReport и посчитать резерв (Dolg*Stavka) я не могу понять.

а "сумму резерва (Dolg*Stavka)" за все периоды ?
ты бы не "насиловал" свои повествовательные способности, а примером данных / результата изъяснялся, - лучше бы было всем

;with cte as (
	select
		DateReport, 	 
		sum(ABS ((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float) *0.14)))over() as Rezerv,
		ABS (min((try_cast([dbo].[fnEvalJS](b.Stavka) as float))) as Stavka
	from Balance a inner join Statistica b on a.ID_Statistica=b.ID
	group by DateReport)
select top 1 * from cte order by Stavka	
26 сен 18, 15:10    [21686858]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

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

Извините, сейчас уточню. Мне нужно показать минимальную ставку резерва в году; месяц, в котором была эта ставка; сумму сформированного резерва в этот месяц в одном окне результатов.

select 	 	
	ABS (min((try_cast([dbo].[fnEvalJS](b.Stavka) as float))) as Stavka
from Balance a inner join Statistica b on a.ID_Statistica=b.ID


Так вытягиваю минимальную ставку, а по ней не получается вытянуть дату и сумму резерва.
26 сен 18, 15:17    [21686869]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
court
Member

Откуда:
Сообщений: 2016
Pacifany
court,

Извините, сейчас уточню. Мне нужно показать минимальную ставку резерва в году; месяц, в котором была эта ставка; сумму сформированного резерва в этот месяц в одном окне результатов.

значит в этом запросе просто убираешь over() и всё

;with cte as (
	select
		DateReport, 	 
		sum(ABS ((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float) *0.14)))over() as Rezerv,
		ABS (min((try_cast([dbo].[fnEvalJS](b.Stavka) as float))) as Stavka
	from Balance a inner join Statistica b on a.ID_Statistica=b.ID
	group by DateReport)
select top 1 * from cte order by Stavka	

DateReport - задаёт день или месяц ?
если это день, то выбирать и группировать по format(DateReport, 'yyyyMM')

и зачем там везде ABS ?
26 сен 18, 15:25    [21686883]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

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

Я думаю ABS тут не нужен, просто найти минимальное значение и по нему вывести месяц(формат Date) и сумму резерва за этот месяц.
26 сен 18, 15:31    [21686895]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

Откуда:
Сообщений: 11
Изменил немного ваш запрос:
with cte as (
	select
		b.DateReport, 	 
		sum((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float))) as Rezerv,
		min((try_cast([dbo].[fnEvalJS](b.Stavka) as float)) as Stavka
	from Balance a inner join Statistica b on a.ID_Statistica=b.ID
	group by b.DateReport)
select top 1 * from cte order by b.Stavka	


не срабатывает, выдает "Неправильный синтаксис около ключевого слова "from".
26 сен 18, 15:36    [21686907]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Посетитель
Member

Откуда:
Сообщений: 1209
Pacifany
Изменил немного ваш запрос:
with cte as (
	select
		b.DateReport, 	 
		sum((try_cast([dbo].[fnEvalJS]('('+a.Dolg+')*'+b.Stavka) as float))) as Rezerv,
		min((try_cast([dbo].[fnEvalJS](b.Stavka) as float)) as Stavka
	from Balance a inner join Statistica b on a.ID_Statistica=b.ID
	group by b.DateReport)
select top 1 * from cte order by b.Stavka	



не срабатывает, выдает "Неправильный синтаксис около ключевого слова "from".


ну вы посчитайте скобочки для начала
26 сен 18, 15:37    [21686909]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
Pacifany
Member

Откуда:
Сообщений: 11
И правда, скобочку упустил, запарился уже. Спасибо вам большое, тема закрыта. Много научился у вас!
26 сен 18, 15:44    [21686915]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
uaggster
Member

Откуда:
Сообщений: 827
court
Pacifany,

создай в базе вот такую функцию
...

Спасибо, поржал было интересно, кроме шуток.
Я бы распарсил по знакам * и /, ну хотя бы с помощью преобразования в xml, а потом сложил бы логарифмы.
Ну, или написал бы CLR.
Буду знать, что так можно.
:-)
27 сен 18, 12:15    [21688012]     Ответить | Цитировать Сообщить модератору
 Re: Произведение двух столбцов из разных таблиц.  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
uaggster
Буду знать, что так можно.
Так можно только сисадминам.
27 сен 18, 12:29    [21688028]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить