Oracle: how to use SQL to capture the results come out-CodePudding

Complete data on the left, only the first on the right data, the need to generate the data, you can write in SQL?

CodePudding user response:

You find out what data relationship

CodePudding user response:

The source data look like?
What are the rules?
The result is what?

All said once

CodePudding user response:

Like this?

 with t1 as 
(
Select id 1, null c1 and c2 from 18.97/100 dual 
Union all 
Select 2 id, 0.04/100 c1, null from dual 
Union all 
Select 3 id, 0.02/100 c1, null from dual 
Union all 
Select 4 id, 0.02/100 c1, null from dual 
Union all 
Select 5 id, 0.02/100 - c1, null from dual 
Union all 
Select 6 id, 0.02/100 c1, null from dual 
) 
Select id, c1 and c2 
That case the when id=1 then c2 else (exp (sum (ln ((1 + NVL (c1, 0)) * (1 + NVL (c2, 0)))) over (order by id))) - 1 end 
The from t1

CodePudding user response:

Complement to describe, so to understand, and now have A Temp table, data screenshot below, the left column column A is assumed, listed on the right side of the B column, A column data is complete, B column data is only an initial value,
Now want to put the rest of B value calculation formula for Posting post screenshots

CodePudding user response:

reference ZBDZJX reply: 3/f

like this?

 with t1 as 
(
Select id 1, null c1 and c2 from 18.97/100 dual 
Union all 
Select 2 id, 0.04/100 c1, null from dual 
Union all 
Select 3 id, 0.02/100 c1, null from dual 
Union all 
Select 4 id, 0.02/100 c1, null from dual 
Union all 
Select 5 id, 0.02/100 - c1, null from dual 
Union all 
Select 6 id, 0.02/100 c1, null from dual 
) 
Select id, c1 and c2 
That case the when id=1 then c2 else (exp (sum (ln ((1 + NVL (c1, 0)) * (1 + NVL (c2, 0)))) over (order by id))) - 1 end 
The from t1

The results and I use EXCEL calculated have tiny difference

CodePudding user response:

Don't understand,,,

CodePudding user response:

reference 5 floor SYYDI reply:

Quote: refer to the third floor ZBDZJX response:

Like this?

 with t1 as 
(
Select id 1, null c1 and c2 from 18.97/100 dual 
Union all 
Select 2 id, 0.04/100 c1, null from dual 
Union all 
Select 3 id, 0.02/100 c1, null from dual 
Union all 
Select 4 id, 0.02/100 c1, null from dual 
Union all 
Select 5 id, 0.02/100 - c1, null from dual 
Union all 
Select 6 id, 0.02/100 c1, null from dual 
) 
Select id, c1 and c2 
That case the when id=1 then c2 else (exp (sum (ln ((1 + NVL (c1, 0)) * (1 + NVL (c2, 0)))) over (order by id))) - 1 end 
The from t1

The results and I have a tiny difference with EXCEL calculated

I tried, if you have enough digit decimal places, the result is the same,

CodePudding user response:

refer to 7th floor ZBDZJX response:

Quote: refer to fifth floor SYYDI response:

Quote: refer to the third floor ZBDZJX response:

Like this?
with t1 as
(
Select id 1, null c1 and c2 from 18.97/100 dual
Union all
Select 2 id, 0.04/100 c1, null from dual
Union all
Select 3 id, 0.02/100 c1, null from dual
Union all
Select 4 id, 0.02/100 c1, null from dual
Union all
Select 5 id, 0.02/100 - c1, null from dual
Union all
Select 6 id, 0.02/100 c1, null from dual
)
Select id, c1 and c2
That case the when id=1 then c2 else (exp (sum (ln ((1 + NVL (c1, 0)) * (1 + NVL (c2, 0)))) over (order by id))) - 1 end
The from t1

The results and I have a tiny difference with EXCEL calculated

I tried, if you have enough decimal digits after, the result is the same,

Is not the same, I bring into decimal places all calculate screenshot below,
Figure one for accurate results:

Figure 2. SQL results

A great god, and help to look at this statement is true a little look not to understand, thank you!

CodePudding user response:

refer to the eighth floor SYYDI response:

Quote: refer to 7th floor ZBDZJX response:

Quote: refer to the fifth floor SYYDI reply:

Quote: refer to the third floor ZBDZJX response:

Like this?
with t1 as
(
Select id 1, null c1 and c2 from 18.97/100 dual
Union all
Select 2 id, 0.04/100 c1, null from dual
Union all
Select 3 id, 0.02/100 c1, null from dual
Union all
Select 4 id, 0.02/100 c1, null from dual
Union all
Select 5 id, 0.02/100 - c1, null from dual
Union all
Select 6 id, 0.02/100 c1, null from dual
)
Select id, c1 and c2
That case the when id=1 then c2 else (exp (sum (ln ((1 + NVL (c1, 0)) * (1 + NVL (c2, 0)))) over (order by id))) - 1 end
The from t1

The results and I have a tiny difference with EXCEL calculated

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull