SQL过去4周逐周汇总

Question

我有一个原始数据，如下表所示。我想得到(汇总)前4周data.Can任何人指导我如何通过SQL Select查询得到这一点。

原始数据表：

Week    Year    Category    Weekly Total
9       2017      Motor           8
8       2017      Car             7
8       2017      Motor           5
7       2017      Car             4
6       2017      Car             8
5       2017      Car             16
5       2017      Motor           15
4       2017      Car             8
3       2017      Car             5
2       2017      Car             3
1       2017      Car             12
52      2016      Car             8
51     2016       Car             6

预期输出：

Week    Year    4Weeks Total
9       2017        32
8       2017        55
7       2017        51
6       2017        52
5       2017        47
4       2017        28
3       2017        28
2       2017        29
1       2017        26
52      2016        14
51      2016        6

Answer 1

您可以使用apply

with t as (
      select week, year, sum(weeklytotal) as weeklytotal
      from t
      group by week, year
     )
select t.week, t.year, t4.total4
from t outer apply
     (select sum(t4.weeklytotal) as total4
      from (select t4.*
            from t t4
            where t4.year < t.year or
                  (t4.year = t.year and t4.week <= t.week)
            order by t4.year desc, t4.week desc
           ) t4
     ) t4;

这假设(就像您的示例数据中一样)您拥有每周的数据。

Answer 2

您可以在CTE中找到聚合。之后，您可以在相关子查询中找到所需的4周总和。

with cte
as (
    select week, year, sum(weekly_total) total
    from your_table
    group by week, year
    )
select week, year, (
        select sum(total)
        from (
            select top 4 total
            from cte t2
            where t2.year * 100 + t2.week <= t1.year * 100 + t1.week
            order by year desc, week desc
            ) x
        )
from cte t1;

产生：

week    year    total
9       2017    32
8       2017    55
7       2017    51
6       2017    52
5       2017    47
4       2017    28
3       2017    28
2       2017    29
1       2017    26
52      2016    14
51      2016    6

Demo