logo标签列表

按日期范围分组和聚合

sqlsql-servert-sql
3
SELECT IsConfirmed, IsNetConfirmed, d.FullDate FROM Final.FactApplication f 
INNER JOIN final.DimOfferedDate d on f.OfferedDateKey= d.OfferedDateKey
WHERE d.CalendarYear in ('2013','2014','2015')

上面的代码返回以下示例数据。
IsConfirmed IsNetConfirmed  FullDate
----------------------------------------------  
1           0               2013-01-04 00:00:00.000   
1           1               2013-02-04 00:00:00.000
0           1               2013-03-04 00:00:00.000
1           0               2013-04-04 00:00:00.000

我希望能够对每年的IsConfirmed和IsNetConfirmed进行按日和月份汇总，以便得出以下结果。我需要对每年进行聚合，以便4/31/2012的总和包括1/1/2012-4/31/2012之间的数据。 enter image description here 这是我的代码 - 但我无法理解所有分组。请帮忙。
SELECT sum(IsConfirmed) AS ConfirmCount
    ,sum(IsNetConfirmed) AS NetConfirmCount
    ,year(d.fulldate) AS cyear
    ,month(d.fulldate) AS cmonth
    ,day(d.fulldate) AS cday
FROM final.FactApplicationHistory f
INNER JOIN final.DimOfferedDate d ON f.OfferedDateKey = d.OfferedDateKey
WHERE d.CalendarYear IN ('2013','2014','2015')
GROUP BY year(d.fulldate)
    ,month(d.fulldate)
    ,day(d.fulldate)
ORDER BY year(d.fulldate)
你需要想出一个透视表方法或者使用case语句来计算所有的总和。例如：sum(case when year(fulldate) = 2012 then IsConfirmed else 0 end) as 2012Confirmed。 - Andrew
@andrew，我需要按年份进行聚合，例如，4/31/2012的总和包括1/1/2012-4/31/2012之间的数据。我该怎么做？ - Raj
那么你是在寻找某种运行总计吗？因此，对于您的第一行，“2012确认”将包括2012-01-01至2012-04-15，“2013确认”将包括2013-01-01至2013-04-15，以此类推？ - Andrew
这就是我一直在寻找的短语。因此，2012年确认和2013年确认的4/15日应分别包括2012-01-01至2012-04-15和2013-01-01至2013-04-15。同样，2012年确认和2013年确认的7/15日应分别包括2012-01-01至2012-07-15和2013-01-01至2013-07-15。希望这可以帮到你。 - Raj
你正在使用哪个版本的SQL Server？ - Felix Pamittan
1
@wewesthemenace 我正在使用 SQL Server 2012。 - Raj
2个回答
1

运行总和很棘手。仅使用 group by 无法完成。有几种方法可以实现它。一种方法是将数据表与自身连接，在所有值小于或等于当前记录的记录上进行，并对数据点求和。您的查询尤其复杂，因为您还想透视数据。以下是使用表连接方法的操作方式：

;with temp (IsConfirmed, IsNetConfirmed, FullDate) AS
(
SELECT IsConfirmed, IsNetConfirmed, FullDate
FROM final.FactApplicationHistory f
INNER JOIN final.DimOfferedDate d 
ON f.OfferedDateKey = d.OfferedDateKey
WHERE d.CalendarYear IN ('2013','2014','2015')
)
, pivottable (cmonthday,cmonth,cday,ConfirmCount2013,NetConfirmCount2013,ConfirmCount2014,NetConfirmCount2014,ConfirmCount2015,NetConfirmCount2015) AS
(
    SELECT
        dateadd(day,cday,(DATEADD(month, cmonth, 0))) cmonthday,
        cmonth,
        cday,
        sum(isnull([2013],0)) ConfirmCount2013, 
        sum(isnull([2016],0)) NetConfirmCount2013, 
        sum(isnull([2014],0)) ConfirmCount2014, 
        sum(isnull([2017],0)) NetConfirmCount2014, 
        sum(isnull([2015],0)) ConfirmCount2015, 
        sum(isnull([2018],0)) NetConfirmCount2015
    FROM 
        (SELECT sum(IsConfirmed) AS ConfirmCount
            ,sum(IsNetConfirmed) AS NetConfirmCount
            ,year(d.FullDate) AS cyear
            ,year(d.FullDate)+3 AS cyear2
            ,month(d.FullDate) AS cmonth
            ,day(d.FullDate) AS cday
        FROM #temp d
        WHERE year(FullDate) IN ('2013','2014','2015')
        GROUP BY year(d.FullDate)
            ,month(d.FullDate)
            ,day(d.FullDate)
        ) ps
    PIVOT
    (
    SUM (ConfirmCount)
    FOR cyear IN
    ( [2013],[2014],[2015])
    ) AS pvt
    PIVOT
    (
    SUM (NetConfirmCount)
    FOR cyear2 IN
    ( [2016],[2017],[2018])
    ) AS pvt
    Group by cmonth,
        cday
)
select
    pivottable.cmonth,
    pivottable.cday,
    sum(RunningSums.ConfirmCount2013) ConfirmCount2013, 
    sum(RunningSums.NetConfirmCount2013) NetConfirmCount2013, 
    sum(RunningSums.ConfirmCount2014) ConfirmCount2014, 
    sum(RunningSums.NetConfirmCount2014) NetConfirmCount2014, 
    sum(RunningSums.ConfirmCount2015) ConfirmCount2015, 
    sum(RunningSums.NetConfirmCount2015) NetConfirmCount2015
from pivottable
join pivottable RunningSums
on RunningSums.cmonthday <= pivottable.cmonthday
group by pivottable.cmonth,pivottable.cday
order by pivottable.cmonth, pivottable.cday

我想这可能是一个光标实际上是个好主意的案例。您可以将输出数据透视表创建为表，然后迭代每个记录并使用运行总和更新每个值。对于具有数百万条记录的非常大的表，这可能比我的自连接方法更有效。

谢谢。你的解决方案肯定有效。然而，我最近发现了一些问题。IsConfirmed和IsNetConfirmed的值都可能在未来发生变化。因此，IsNetConfirmed可能在4/15时为1（这将返回sum(IsNetConfirmed)=1），但它可能在4/16被设置为0，这将给出一个总和为0的结果 :-( 这种情况往往发生在年底。那么我们的累计方法在这里是否无效？我不确定如何解决这个问题。我附上了Excel中一些旧数据的截图，展示了这种行为。 - Raj
我不确定我理解这个问题。你是说以前的报告显示数字在减少吗？如果是这样...这只能发生在您的数据中存在负值的情况下。 - Brian Pressler
Brian，你的解决方案符合累计求和的要求。所以，我将接受你的答案 :-) 我提出的问题可能需要我查看交易级别的数据并找出如何复制我附加的示例报告。非常感谢。 - Raj
1
首先，您需要生成所有可能的月份-日期和年份组合。例如，2013年您有1月1日至1月5日的日期，而2014年您有1月3日至1月6日的日期。您应该拥有2013年和2014年的1月1日至1月6日的日期。在获取所有日期后，将其与原始查询JOIN，以便每个新生成的日期都具有IsConfirmed和IsNetConfirmed的值。从那里，您可以获得累加总数。最后，您会想要PIVOT累加总数以实现所需的结果。
这是一种使用交叉表的动态方法。您可以阅读此文章进行参考。 SQL Fiddle 
DECLARE @sql1 VARCHAR(4000) = '',
        @sql2 VARCHAR(4000) = '',
        @sql3 VARCHAR(4000) = ''

SELECT @sql1 =
';WITH SampleData AS( -- Replace this CTE with the original query
    SELECT * FROM Data
)
,CrossDates AS( -- Generate date combinations
    SELECT
        YY, MM, DD,
        FullDate = DATEADD(DAY, DD - 1, DATEADD(MONTH, MM - 1, DATEADD(YEAR, YY - 1900, 0)))        
    FROM (
        SELECT DISTINCT
            MM = MONTH(FullDate),
            DD = DAY(FullDate)
        FROM SampleData
    )DM
    CROSS JOIN(
        SELECT DISTINCT YY = YEAR(FullDate) FROM SampleData
    )Y
)
,CteAllDates AS( -- Assign value for each newly generated date
    SELECT
        c.*,
        IsConfirmed = ISNULL(s.IsConfirmed, 0),
        IsNetConfirmed = ISNULL(s.IsNetConfirmed, 0)
    FROM CrossDates c
    LEFT JOIN SampleData s
        ON s.FullDate = c.FullDate
)
,RunningTotal AS( -- Compute running total
    SELECT
        YY = YEAR(FullDate),
        MM = MONTH(FullDate),
        DD = DAY(FullDate),
        Confirm = SUM(CAST(IsConfirmed AS INT)) OVER(PARTITION BY YEAR(FullDate) ORDER BY MONTH(FullDate), DAY(FullDate)),
        NetConfirm = SUM(CAST(IsNetConfirmed AS INT)) OVER(PARTITION BY YEAR(FullDate) ORDER BY MONTH(FullDate), DAY(FullDate))
    FROM CteAllDates
)
SELECT
      MM
    , DD
'

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN YY = ' + CONVERT(VARCHAR(4), YY) + ' THEN Confirm ELSE 0 END) AS [' + CONVERT(VARCHAR(4), YY) + ' Confirm]' + CHAR(10) +
'   , MAX(CASE WHEN YY = ' + CONVERT(VARCHAR(4), YY) + ' THEN NetConfirm ELSE 0 END) AS [' + CONVERT(VARCHAR(4), YY) + ' NetConfirm]' + CHAR(10)
FROM(
    SELECT DISTINCT YY = YEAR(FullDate)
    FROM(
        SELECT * FROM Data -- Replace this with the original query
    )d
)t

SELECT @sql3 = 
'FROM RunningTotal
GROUP BY MM, DD'

PRINT(@sql1 + @sql2 + @sql3)
EXEC(@sql1 + @sql2 + @sql3)

注意：

1. 使用您原始的查询替换SampleData。

2. 使用SUM() OVER()来获取累计总数。

3. 基本上，使用您原始的查询替换SELECT * FROM Data。

示例数据

IsConfirmed IsNetConfirmed FullDate
----------- -------------- -----------------------
1           0              2013-04-01 00:00:00.000
1           1              2013-04-02 00:00:00.000
0           1              2013-04-03 00:00:00.000
1           0              2013-04-04 00:00:00.000
1           0              2014-04-01 00:00:00.000
1           1              2014-04-02 00:00:00.000
0           1              2014-04-03 00:00:00.000
1           0              2014-04-05 00:00:00.000

Result

|----|----|--------------|-----------------|--------------|-----------------|
| MM | DD | 2013 Confirm | 2013 NetConfirm | 2014 Confirm | 2014 NetConfirm |
|----|----|--------------|-----------------|--------------|-----------------|
|  4 |  1 |            1 |               0 |            1 |               0 |
|  4 |  2 |            2 |               1 |            2 |               1 |
|  4 |  3 |            2 |               2 |            2 |               2 |
|  4 |  4 |            3 |               2 |            2 |               2 |
|  4 |  5 |            3 |               2 |            3 |               2 |
感谢@wewesthemenace提供的解决方案。然而，正如我在下面回复Brian时所概述的那样，我可能需要另一种方法来解决这个问题。 - Raj
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接