临时替换SQL中的值或通过SQL提交值

I'm new to SQL and dont think I truly grasp it fully.

我是SQL新手，我不认为我能完全理解它。

My second selected item,after date, gives me (∑r*mv)/(∑mv) which is the weighted returns of the portfolio. The next two SUM columns are attempt to weight the returns based on my selected values.

日期后,我的第二个选择项给我(∑r * mv)/(∑mv)的加权收益的投资组合。接下来的两列是尝试根据所选的值对返回进行加权。

SELECT 
date, 
Sum(mv*retmtd)/Sum(mv),

SUM(DISTINCT retmtd*
        CASE asset
        WHEN 'SP500' then 200000
        WHEN 'UST2' then 200000
        WHEN 'FTSE' then 600000
        END)/1000000,

SUM( retmtd*
        CASE 
        WHEN asset ='SP500' AND mv IS NOT NULL then 200000
        WHEN asset ='UST2' AND mv IS NOT NULL then 200000
        WHEN asset ='FTSE' AND mv IS NOT NULL then 600000
        END)/
      SUM(  CASE 
        WHEN asset ='SP500' AND mv IS NOT NULL then 200000
        WHEN asset ='UST2' AND mv IS NOT NULL then 200000
        WHEN asset ='FTSE' AND mv IS NOT NULL then 600000
        END) 

FROM [dbo].[AssetRet]

WHERE
asset IN ('SP500','UST2','FTSE')
AND
date >=  '12/31/2000'

Is there anyway that I change set the value of mv manually (to say 200000) over the lifetime so I can see how different weighted portfolios would have performed. This is supposed to be dynamic and reusable so going creating a new column in the database is unfeasible if I want to do this over and over. None of my SQL attempts seems to get the correct answer.

无论如何，我在一生中手工修改了mv的值(到200000)，这样我就可以看到不同的加权组合是如何执行的。这应该是动态的和可重用的，所以在数据库中创建一个新的列是不可行的，如果我想反复这么做的话。我的SQL尝试似乎都没有得到正确的答案。

The purpose of this exercise is to create synthetic portfolios that would show how a portfolio would have performed if different weights had been given to different asset classes during their life in the portfolio. I hope this makes sense.

这个练习的目的是创建一个综合投资组合，如果在投资组合中不同的资产类别在其生命周期中被给予不同的权重，那么该组合将如何表现。我希望这说得通。

e.g instead of mv (market value of the position) being used to weigh that asset in the portfolio I would use a static hypothesized value (200000)

e。用g代替mv(头寸的市值)来衡量投资组合中的资产，我会使用静态假设值(200000)

This table is what Sum(mv*retmtd)/Sum(mv) returns which is correct

这个表格是正确的Sum(mv*retmtd)/Sum(mv)返回的值

Date      SP500     UST2     FTSE     MV     MV     MV       RESULT
3/31/10   0.34                        19878                  0.34
6/30/10   -0.11     0.12              19954  840712          0.116
6/30/13   -0.48     -1.30    -0.76    9914   934892 384193   -1.135

And this what I want the other querys to retrieve

这就是我想让其他的问题找回来的

Date      SP500     UST2     FTSE     MV     MV     MV       RESULT
3/31/10   0.34                        200000                 0.34
6/30/10   -0.11     0.12              200000 200000          0.006
6/30/13   -0.48     -1.30    -0.76    200000 200000 600000   -0.809

Is there a way to do this via SQL?

有办法通过SQL来实现这一点吗?

2 个解决方案

#1

enter code hereIs there any way to set the value of mv manually to 200000...

输入代码是否有办法将mv手动设置为200000…

Yes -- type 200000 instead of mv

是的，输入200000而不是mv

Are you thinking about a parameter maybe?

你在考虑参数吗?

i.e.:

例如:

        DECLARE @mv_ftse int
        DECLARE @mv_ust2 int
        DECLARE @mv_sp500 int
        SET @mv_ftse = 600000
        SET @mv_ust2 = 200000
        SET @mv_sp500 = 200000

    SELECT 
    date, 
    Sum(mv*retmtd)/Sum(mv),

    SUM(DISTINCT retmtd*
        CASE asset
        WHEN 'SP500' then @mv_sp500
        WHEN 'UST2' then @mv_ust2
        WHEN 'FTSE' then @mv_ftse
        END)/1000000,
...

#2

I found a solution to this problem that may be of use to anyone that stumbles across this question.

我找到了一个解决这个问题的办法，对任何遇到这个问题的人都有用。

By wrapping it in another query and using just a case statement in the wrapped query you can modify the data and then do the required data calculations...

通过在另一个查询中包装它并在包装查询中使用case语句，您可以修改数据，然后进行所需的数据计算……

SELECT 
x.date, 
Sum(x.hyp_mv* x.retmtd)/Sum(x.hyp_mv) as weightedreturns

FROM

    (
    SELECT 
    date,
    asset,
    retmtd,

    (CASE asset
     WHEN 'SP500' then 200000
     WHEN 'UST2' then 200000
     WHEN 'FTSE' then 600000
     END) as hyp_mv

    FROM [dbo].[AssetRet]

    WHERE
    asset IN ('SP500','UST2','FTSE')
    AND
    date >=  '12/31/2000' 
    )x

GROUP BY
x.date

ORDER BY
x.date

#1