假设我的 SQL Server 2012 数据库中有下表:
MyTable:
DateCol FkId Sector Value
--------------------------------------------
2018-01-01 1 A 1
2018-01-02 1 A 2
2018-01-03 1 A 3
2018-01-04 1 A 4
2018-01-01 1 B 1
2018-01-04 1 B 4
2018-01-01 1 C 1
2018-01-03 1 C 3
2018-01-04 1 C 4
2018-01-01 2 A 1
...
我想获得特定领域每个部门的平均值FkId
, 但基于该 FkId 的可用日期总数。这意味着如果我想得到平均值FkId
= 1 表示日期,例如2018-01-01
and 2018-01-10
我的结果集是:
Sector AvgVal
---------------------------------
A (1+2+3+4) / 4 = 2.5
B (1+4) / 4 = 1.25
C (1+3+4) / 4 = 2
换句话说,不是除以该扇区的可用日期数,而是除以表中该日期范围内的日期总数FkId
.
我想我可以通过以下方式使用 CTE 来做到这一点:
DECLARE @FkId INT = 1,
@StartDate DATE = '2018-01-01',
@EndDate DATE = '2018-01-10'
DECLARE @MyTable TABLE
(
DateCol DATE,
FkId INT,
Sector VARCHAR(1),
Value FLOAT
);
INSERT INTO @MyTable (DateCol, FkId, Sector, Value)
VALUES
('2018-01-01', 1, 'A', 1),
('2018-01-02', 1, 'A', 2),
('2018-01-03', 1, 'A', 3),
('2018-01-04', 1, 'A', 4),
('2018-01-01', 1, 'B', 1),
('2018-01-04', 1, 'B', 4),
('2018-01-01', 1, 'C', 1),
('2018-01-03', 1, 'C', 3),
('2018-01-04', 1, 'C', 4),
('2018-01-01', 2, 'A', 1);
WITH NumDates AS
(
SELECT
Sector,
COUNT(DateCol) AS cnt
FROM
@MyTable
WHERE
DateCol BETWEEN @StartDate AND @EndDate
AND FkId = @FkId
GROUP BY
Sector
),
MaxNumDates AS
(
SELECT
MAX(cnt) AS MaxNum
FROM
NumDates
)
SELECT
Sector,
SUM(Value) / MaxNum AS AvgVal
FROM
@MyTable
JOIN
MaxNumDates ON 1 = 1
WHERE
DateCol BETWEEN @StartDate AND @EndDate
AND FkId = @FkId
GROUP BY
Sector, MaxNum
但我真的希望有更好的方法。有什么想法吗?