描述如下: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也有可能有几万到十几万条记录
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也有可能有几万到十几万条记录
请问这个算存储吗@@?
最近工作忙疯了,我做梦都梦到在写code...
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)
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
所以楼上的写法好像不行
在此查询中多个列调用fun的地方,能够用一次select就把需要的东西查出来。因为相同的一行数据调用的fun函数里的查询条件是一样的。但是没办法写出这样的代码,谁有办法?
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条件
再运行你的查询,发上来看看
|--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)
-- =============================================
-- 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
|--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)
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做,每行调用一次表值函数,这样不用每列多次调用查询函数,但是实际好像也改善不大,谁能分析下原因?
不,懂;帮,顶;学,习;赚,分。
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
有本事,就封杀我!!!理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
不,懂;帮,顶;学,习;赚,分。
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
有本事,就封杀我!!!理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
我暂停cxmcxm说法,直接算好存储
table1不用修改,查询时读table2的数据即可.
此方法计算量更大,不过是将时间摊分,满足人的使用习惯需求.如果添加记录的时间是随机的,不是按从小到大的,或者中间的记录经常要修改,那此方法就不能用.