问题描述
假设我的SQL Server 2012数据库中有下表:
Suppose I have the following table in my SQL Server 2012 database:
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
而 2018-01-10
我的结果集将是:
And I want to get the average values for each sector for a specific FkId
, BUT BASED UPON THE TOTAL NUMBER OF DATES AVAILABLE IN TOTAL FOR THAT FkId. Meaning that if I wanted to get the average for FkId
= 1 for the dates, say, 2018-01-01
and 2018-01-10
my result set would be:
Sector AvgVal
---------------------------------
A (1+2+3+4) / 4 = 2.5
B (1+4) / 4 = 1.25
C (1+3+4) / 4 = 2
换句话说,不除以该部门可用的日期数,而是除以该日期范围内表中该日期范围内的日期总数code> FkId 。
In other words, not dividing by the number of dates available for that sector, but divided by the total number of dates in the table for that date-range for that FkId
.
我认为我可以通过以下方式使用CTE:
I figured I can do this with CTEs in the following way:
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
但是我真的希望有更好的方法。有什么想法吗?
But I'm really hoping there is a better way. Any thoughts?
推荐答案
尝试一下:
select dateCol,
fkid,
sector,
sum(value) over (partition by fkid, sector) /
(select count(distinct dateCol) from @MyTable where fkid = t.fkid)
from @MyTable t
或
select fkid,
sector,
sum(value) /
(select count(distinct dateCol) from @MyTable where fkid = t.fkid)
from @MyTable t
group by fkid, sector
这篇关于SQL Server-按所有组返回的记录数进行汇总的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!