SQL服务器:使用来自另一个表或视图
问题描述:
参数从表中加入平均数据我有一个年代由以下定义的两个表:SQL服务器:使用来自另一个表或视图
CREATE TABLE Portfolio.DailyStats
(
Date date NOT NULL,
NAV int NOT NULL,
SP500 decimal(8,4) NULL,
R2K decimal(8,4) NULL,
NetExp decimal(8,4) NULL,
GrossExp decimal(8,4) NULL,
)
GO
CREATE TABLE Portfolio.DailyPortfolio
(
BbgID varchar(30) NOT NULL,
Ticker varchar(22) NULL,
Cusip char(9) NULL,
SecurityDescription varchar(50) NOT NULL,
AssetCategory varchar(25) NOT NULL,
LSPosition char(3) NULL,
Ccy varchar(25) NOT NULL,
Quantity int NULL,
DeltaExpNet int NULL,
Issuer varchar(48) NOT NULL,
Date date NOT NULL,
PortfolioID AS BbgID + LSPosition + Convert(varchar(8), Date, 112) Persisted Primary Key
)
GO
我试图创建一个4列的观点,我可以看到每个发行人,请查看发行人在DailyPortfolio
表中的首次出现和最后出现的日期以及这些日期的DailyStats
表中的平均值NAV
。
SELECT
Issuer, MIN(Date) OpenDate, MAX(Date) CloseDate
FROM
Portfolio.DailyPortfolio
GROUP BY
Issuer
如何添加,使用NAV
列从DailyStats
表由MIN(Date)
和MAX(Date)
列定义的日期范围计算AverageNAV
第四列:所以这个观点的前三列将被定义在视图中。
我最后的观点应该是这个样子:
Issuer OpenDate CloseDate AverageNAV
:------|-----------|-----------|----------:
Issuer A 2/4/2015 11/9/2016 28234164
Issuer B 2/6/2015 5/19/2017 30446780
Issuer C 11/19/2015 10/11/2016 35789424
答
如果使用CTE来定义发行日期范围,您可以使用AVG在每个日期范围内获得的个人平均基地:
;WITH DateRange AS(
SELECT DP.Issuer, MIN(DP.Date) OpenDate, MAX(DP.Date) CloseDate
FROM Portfolio.DailyPortfolio DP
GROUP BY DP.Issuer
)
SELECT DR.Issuer, DR.OpenDate, DR.CloseDate, AVG(DS.NAV) AS AverageNAV
FROM DateRange DR
INNER JOIN Portfolio.DailyStats DS ON DS.Date BETWEEN DR.OpenDate AND DR.CloseDate
GROUP BY DR.Issuer, DR.OpenDate, DR.CloseDate
下面是与采样数据和输出完整的例子的代码:
DECLARE @DailyStats TABLE
(Date DATE NOT NULL,
NAV INT NOT NULL)
DECLARE @DailyPortfolio TABLE
(Issuer VARCHAR(48) NOT NULL,
Date DATE NOT NULL)
INSERT INTO @DailyPortfolio VALUES ('Max', '1/1/2017')
INSERT INTO @DailyPortfolio VALUES ('Max', '2/1/2017')
INSERT INTO @DailyPortfolio VALUES ('Max', '3/1/2017')
INSERT INTO @DailyPortfolio VALUES ('Max', '4/1/2017')
INSERT INTO @DailyPortfolio VALUES ('Scott', '1/1/2015')
INSERT INTO @DailyPortfolio VALUES ('Scott', '2/1/2017')
INSERT INTO @DailyPortfolio VALUES ('Scott', '3/1/2017')
INSERT INTO @DailyPortfolio VALUES ('Scott', '4/1/2017')
INSERT INTO @DailyStats VALUES ('1/1/2016', 100)
INSERT INTO @DailyStats VALUES ('2/1/2017', 200)
INSERT INTO @DailyStats VALUES ('3/1/2017', 300)
INSERT INTO @DailyStats VALUES ('3/3/2017', 400)
;WITH DateRange AS(
SELECT DP.Issuer, MIN(DP.Date) OpenDate, MAX(DP.Date) CloseDate
FROM @DailyPortfolio DP
GROUP BY DP.Issuer
)
SELECT DR.Issuer, DR.OpenDate, DR.CloseDate, AVG(DS.NAV) AS AverageNAV
FROM DateRange DR
INNER JOIN @DailyStats DS ON DS.Date BETWEEN DR.OpenDate AND DR.CloseDate
GROUP BY DR.Issuer, DR.OpenDate, DR.CloseDate
输出:
Issuer OpenDate CloseDate AverageNAV
Max 2017-01-01 2017-04-01 300
Scott 2015-01-01 2017-04-01 250
工程很好。谢谢! – MilesToGoBeforeISleep