SQL Server-按所有组返回的记录数进行汇总 [英] SQL Server - Aggregate by number of records returned for all groups

问题描述

假设我的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-按所有组返回的记录数进行汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！