描述如下:table1:
id   int,
date  smalldatetime,
price1  real,
price2  realtable2:
id int
date smalldatetime,
bl1 decimal(5,3),
jg1 real,
bl2 decimal(5,3)select id,date, fun(price1,date,id), fun(price2,date,id) from table1fun

  @price real,
  @date smalldatetime,
  @id int
)
as
return real
begin
  select @price = @price + bl1*jg1 - bl2
  from table2
  where id = @id
    and date > @date
  return @price 
endfun函数就是为了累积算出date大于参数的对应id的price,
但是这样在select中没个字段都调用一次fun,如果table1表中有很多字段都要调fun,这样会重复的做fun里面的相同条件的select语句。有没有办法调用一次fun,传多个price参数,算出多个price,返回到外面的select多列中。请问谁有优化的办法.
ps:table1可能会有上千万条记录, table2也有可能有几万到十几万条记录

解决方案 »

  1.   


    请问这个算存储吗@@?
    最近工作忙疯了,我做梦都梦到在写code...
      

  2.   

    直接用子查询写试试
    SELECT ID,[DATE]
    ,price1+ISNULL((SELECT SUM(bl1*jg1 - bl2) FROM TABLE2 T2 WHERE T2.id=T1.id AND T2.[DATE]>T1.[DATE]),0)
    ,price2+ISNULL((SELECT SUM(bl1*jg1 - bl2) FROM TABLE2 T2 WHERE T2.id=T1.id AND T2.[DATE]>T1.[DATE]),0)
    FROM TABLE1 T1在T2上建个索引
    CREATE INDEX INX_T2_ID_DATE ON T2(ID,DATE) INCLUDE(BL1,JG1,BL2)
      

  3.   


    select @price = @price + bl1*jg1 - bl2
      from table2
      where id = @id
      and date > @date
    这段实际的逻辑不是求和,是我例子举的不好,我现在去修改,应该是
    select @price = (@price + bl1*jg1 - bl2)/(1+bl2+bl1)
      from table2
      where id = @id
      and date > @date
    所以楼上的写法好像不行
      

  4.   

    我的想法是select id,date, fun(price1,date,id), fun(price2,date,id) from table1
    在此查询中多个列调用fun的地方,能够用一次select就把需要的东西查出来。因为相同的一行数据调用的fun函数里的查询条件是一样的。但是没办法写出这样的代码,谁有办法?
      

  5.   


    Select #1.id,(#1.price1+#2.bl1*jg1 -bl2)/(1+bl2 +bl1) As price1,(#1.price2+#2.bl1*jg1 -bl2)/(1+bl2 +bl1) as price2 
    From #1 Inner Join #2 On #1.id =#2.id And #1.date<#2.date 
    Where Not Exists(Select 1 From #2 as s2 Where s2.id=#2.id and s2.date>#2.date)
    不知道这样的效率如何
    我不知道你的table2表有多少条记录符合>table1.date的记录,所以增加了一个取最大日期的Where条件
      

  6.   

    SET SHOWPLAN_TEXT ON
    再运行你的查询,发上来看看
      

  7.   

      |--Compute Scalar(DEFINE:([Expr1006]=[stock_history_db].[dbo].[fn_GetPreEdPrice]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[Expr1016],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1007]=[stock_history_db].[dbo].[fn_GetPreEdPrice]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[Expr1017],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1008]=[stock_history_db].[dbo].[fn_GetPreEdPrice]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[Expr1018],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1009]=[stock_history_db].[dbo].[fn_GetPreEdPrice]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[Expr1019],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1010]=[stock_history_db].[dbo].[fn_GetPreEdPrice]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[Expr1020],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1011]=[stock_history_db].[dbo].[fn_GetPreEdVolumn]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[stock_history_db].[dbo].[oneminute_history].[amt_volumn] as [a].[amt_volumn],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1012]=[stock_history_db].[dbo].[fn_GetPreEdVolumn]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[stock_history_db].[dbo].[oneminute_history].[amt_wp] as [a].[amt_wp],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade]), [Expr1013]=[stock_history_db].[dbo].[fn_GetPreEdVolumn]([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock],[stock_history_db].[dbo].[oneminute_history].[amt_np] as [a].[amt_np],[stock_history_db].[dbo].[oneminute_history].[date_trade] as [a].[date_trade])))
           |--Merge Join(Inner Join, MERGE:([b].[id_stock])=([a].[id_stock]), RESIDUAL:([stock_history_db].[dbo].[oneminute_history].[id_stock] as [a].[id_stock]=[stock_history_db].[dbo].[lookup_stockid].[id_stock] as [b].[id_stock]))
                |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[id_et]))
                |    |--Clustered Index Seek(OBJECT:([stock_history_db].[dbo].[lookup_stockid].[PK__lookup_stockid__567ED357] AS [b]), SEEK:([b].[id_stock] > (995) AND [b].[id_stock] < (1000)) ORDERED FORWARD)
                |    |--Clustered Index Seek(OBJECT:([stock_history_db].[dbo].[lookup_etid].[PK__lookup_etid__26CFC035] AS [c]), SEEK:([c].[id_et]=[stock_history_db].[dbo].[lookup_stockid].[id_et] as [b].[id_et]) ORDERED FORWARD)
                |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[oneminute_history].[val_preclose] as [a].[val_preclose],0), [Expr1017]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[oneminute_history].[val_open] as [a].[val_open],0), [Expr1018]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[oneminute_history].[val_high] as [a].[val_high],0), [Expr1019]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[oneminute_history].[val_low] as [a].[val_low],0), [Expr1020]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[oneminute_history].[val_close] as [a].[val_close],0)))
                     |--Clustered Index Seek(OBJECT:([stock_history_db].[dbo].[oneminute_history].[PK_oneminute_history] AS [a]), SEEK:([a].[id_stock] > (995) AND [a].[id_stock] < (1000)) ORDERED FORWARD)
      

  8.   

      UDF: [stock_history_db].[dbo].[fn_GetPreEdPrice]
        -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description: <Description, ,>
    -- =============================================
    CREATE FUNCTION [dbo].[fn_GetPreEdPrice]
    (
    -- Add the parameters for the function here
    @id_stock int,
    @price real,
    @date_ed smalldatetime
    )
    RETURNS real
    AS
    BEGIN select @price = (@price+ pgbl*pgjg - fhbl*0.9)/(1 + sgbl + pgbl) 
    from dbo.ed_info
    where id_stock = @id_stock
    and date_ed > @date_ed
      

  9.   

                |--Compute Scalar(DEFINE:([Expr1003]=(([@price]+[Expr1004])-[Expr1005])/[Expr1006]))
                     |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[ed_info].[pgbl]*[stock_history_db].[dbo].[ed_info].[pgjg],0), [Expr1005]=CONVERT_IMPLICIT(real(24),[stock_history_db].[dbo].[ed_info].[fhbl]*(0.9),0), [Expr1006]=CONVERT_IMPLICIT(real(24),((1.)+[stock_history_db].[dbo].[ed_info].[sgbl])+[stock_history_db].[dbo].[ed_info].[pgbl],0)))
                          |--Clustered Index Seek(OBJECT:([stock_history_db].[dbo].[ed_info].[EDINFO_Primekey]), SEEK:([stock_history_db].[dbo].[ed_info].[id_stock]=[@id_stock] AND [stock_history_db].[dbo].[ed_info].[date_ed] > [@date_ed]) ORDERED FORWARD)
      

  10.   


    ALTER FUNCTION fun(
    -- Add the parameters for the function here
      @price1 real,  
      @price2 real,
      @price3 real,
      @date smalldatetime,
      @id int
    )
    RETURNS 
     @tmptb TABLE 
    (
    -- Add the column definitions for the TABLE variable here
    id int,
    date smalldatetime,
    price1 real,
            price2 real,
            price3 real, )
    AS
    BEGIN
    -- Fill the table variable with the rows for your result set
    -- Add the SELECT statement with parameter references here
    select @price1 =  (@price1 + bl1*jg1 - bl2)/(1+bl2+bl1)  ,
    @price2 = (@price2 + bl1*jg1 - bl2)/(1+bl2+bl1) ,
    @price3= (@price3 + bl1*jg1 - bl2)/(1+bl2+bl1)  
    from table2
    where id= @id
    and date> @date

    insert into  @tmptb
    values
    (
    @id,
    @date,
    @price1,
    @price2,
    @price3 ) RETURN 
    ENDselect a.id, a.date, b.price1, 
    b.price2,
    b.price3
     from table1 a
    cross  apply dbo.fn_GetPrePriceTb(a.id,a.date,
    a.price1, a.price2, a.price3) b后来想到的办法,用cross apply做,每行调用一次表值函数,这样不用每列多次调用查询函数,但是实际好像也改善不大,谁能分析下原因?
      

  11.   

    一次计算肯定要花长时间,这样的需求,如果table2只是流水记录,不需修改,最好table2中再加一字段来保存累计值,添加记录时就将此值计算好,每次查询直接调用.
      

  12.   


    不,懂;帮,顶;学,习;赚,分。
    虽然这么回贴可能会被删除。
    谁他妈的删除了就是个王八蛋。
    强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
    有本事,就封杀我!!!理由在这里:
    他妈的一帮管理员有神经病啊???
    http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
      

  13.   


    不,懂;帮,顶;学,习;赚,分。
    虽然这么回贴可能会被删除。
    谁他妈的删除了就是个王八蛋。
    强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
    有本事,就封杀我!!!理由在这里:
    他妈的一帮管理员有神经病啊???
    http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
      

  14.   

    没认真看后面的补充,直接关联不行
    我暂停cxmcxm说法,直接算好存储
      

  15.   

    这是个办法,但是每次table2有新记录的时候,前面的累加值都要修改一次,也就是说每次更新table2,table都要刷新一次,更新的效率也很低
      

  16.   

    看你的函数,是计算是按时间顺序来累加,如果在多数情况下,添加的记录都是时间最大的,累计值只要上一记录的累计值再加最后记录的计算值即可,少数不是时间最大的,后边的记录必须重新计算.
    table1不用修改,查询时读table2的数据即可.
    此方法计算量更大,不过是将时间摊分,满足人的使用习惯需求.如果添加记录的时间是随机的,不是按从小到大的,或者中间的记录经常要修改,那此方法就不能用.