January 21, 2020 at 12:31 pm
Hello,
I have this loop which creates a bunch of matrices based on the month_nbr (in this case 1-36).
What i want to do is to store the 36 results from this loop into separate tables with the names
Matrix_1, matrix_2 and so on. I need these tables stored as i will multiply them using matrix multiplication later on.
This is the basic code which outputs the 36 results (which i struggle to store)
declare @P as int = 0
WHILE @p <= 36
begin
set @p=@p+1
select x_group, [1], [2], [3], [4], [5],[6], [7] from #predicted_transition_matrix where month_nbr=@p
end
How hard can it be.. spent 3 hours googling and trying stuff out. but im still going nowhere.
Any suggestions ?
January 21, 2020 at 12:55 pm
im not 100% sure what you exactly want however, i would recommend you reading Jeff Moden article on tally tables and remove your loop.
***The first step is always the hardest *******
January 21, 2020 at 1:13 pm
If you're after 36 individual matrices a loop may indeed be the way to go. However, you can't just create loop to execute a SELECT statement with 36 different values, you'll need something like this.
DECLARE
@SQL NVARCHAR(300)
,@P TINYINT =1;
WHILE @P <= 36
BEGIN
SET @SQL = N'x_group, [1], [2], [3], [4], [5],[6], [7] from #predicted_transition_matrix where month_nbr=@p' -- Create a new query for each value of @p
EXEC sp_executesql @SQL,N'@p TINYINT', @P -- Actually executes the SQL created above
SET @P +=1
END
In most cases, a loop isn't needed but for multiple executions of something, it can do the trick.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 21, 2020 at 2:50 pm
Can't you multiply two matrices together from the same table?
I don't see why you need two different tables?
January 21, 2020 at 3:41 pm
Thanks Neil. Will be helpful further on. Im new working in SSMS coming from a SAS / python world.
Yeah, as you mention Jonathan, I could have multiplied from same table. Thats what i went on to do, but stupid as I am I then realised that I actually need to use the product from the first multiplication to move forward.
I would use the inital result to calculate the next one. So now I'm trying to figure out how to create something to do that instead..
January 21, 2020 at 4:17 pm
Yeah, as you mention Jonathan, I could have multiplied from same table. Thats what i went on to do, but stupid as I am I then realised that I actually need to use the product from the first multiplication to move forward.
I would use the inital result to calculate the next one. So now I'm trying to figure out how to create something to do that instead..
Can't you insert the next result onto the same table, then query it from that table?
January 21, 2020 at 5:11 pm
Can you create 1 table with a BatchKey or some identifier to identify which records were loaded with which path. Instead of Matrix_1 and Matrix_2 tables, have a MatrixID of 1 or 2
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2020 at 11:34 pm
Maybe successively cross apply with partial joins? Something like:
with
matrix_1(month_nbr, x_group, [1], [2], [3]) as (
select 1, 'grp a', 1, 1, 1
union all
select 1, 'grp a', 1, 1, 1
union all
select 1, 'grp b', 1, 1, 1
union all
select 1, 'grp b', 1, 1, 1
union all
select 2, 'grp a', 1, 1, 1),
matrix_2(x_group, const) as (
select 'grp a', 12.2
union all
select 'grp b', 10.4),
matrix_3(month_nbr, const) as (
select 1, 2.3
union
select 2, 1.7)
select
m1.*, m2.*, m3.*
from
matrix_1 m1
cross apply
(select
m1.[1]*m2.const, m1.[2]*m2.const, m1.[3]*m2.const
from
matrix_2 m2
where
m1.x_group=m2.x_group) m2([1], [2], [3])
cross apply
(select
m2.[1]*m3.const, m2.[2]*m3.const, m2.[3]*m3.const
from
matrix_3 m3
where
m1.month_nbr=m3.month_nbr) m3([1], [2], [3]);
Also, maybe if you unpivot the #predicted_transition_matrix table so that the columns labelled 1-7 became row values it becomes easier to calculate?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply