sql-server - 将本周与前一周进行比较

Question

我有一个按周列出的成员列表，我需要将当前周与 SQL Server 中的前一周进行比较。第一张图片是数据在表中的样子，第二张图片是我想要的结果。我想也许每周做一次 CTE，然后比较它们。谢谢。

标签： sql-server

Answer 1

一种非常幼稚的方法，可以计算mem_id前一周不存在的所有值，new如下所示：

declare @t table(mem_id int,weeknum int, yearnum int);
insert into @t values(1,1,2020),(2,1,2020),(1,2,2020),(3,2,2020),(2,3,2020),(3,3,2020),(4,3,2020);

with p as
(
    select yearnum
          ,weeknum
          ,case when lag(weeknum,1) over (partition by mem_id order by yearnum,weeknum) = weeknum-1 then 0 else 1 end as p
    from @t
)
select yearnum
      ,weeknum
      ,sum(p) as new
      ,count(1) as total
from p
group by yearnum
        ,weeknum
order by yearnum
        ,weeknum;

输出

+---------+---------+-----+-------+
| yearnum | weeknum | new | total |
+---------+---------+-----+-------+
|    2020 |       1 |   2 |     2 |
|    2020 |       2 |   1 |     2 |
|    2020 |       3 |   2 |     3 |
+---------+---------+-----+-------+