sql - 计算跨越多个日期范围的记录

Question

ACCOUNT   Amount     DATE
1     50        01-2010
1     100       03-2010
1     100       02-2011
2     100       01-2011
2     50        05-2011
2     50        09-2011
3     100       03-2012
3     100       03-2013

是否有一种查询结构可以让我计算跨越当前和过去一年的不同账户？例如，账户 1 有 2011 年和 2010 年的金额，因此它应该在 2011 年计算一次。账户 2 只有 2011 年的金额，因此不会被计算在内，而账户 3 有 2013 年和 2012 年的金额，因此它被计算为 1 2013 年以下:

 2010   2011    2012    2013                
 0      1       0       1

Answer 1

首先，您需要知道您拥有帐户数据的年份:

select account, year(date) as yr
from t
group by account, year(date)

接下来，您需要查看两年是否按顺序排列。你可以在 2012 年以滞后/领先的方式做到这一点。相反，我们将只使用自连接:

with ay as (
     select account, year(date) as yr
     from t
     group by account, year(date)
    )
select ay.account, ay.yr
from ay join
     ay ayprev
     on ay.account = ayprev.account and
        ay.yr = ayprev.yr + 1

接下来，如果你想按年计算账户数量，只需将其放入一个聚合中即可:

with ay as (
     select account, year(date) as yr
     from t
     group by account, year(date)
    )
select yr, count(*) as numaccounts
from (select ay.account, ay.yr
      from ay join
           ay ayprev
           on ay.account = ayprev.account and
              ay.yr = ayprev.yr + 1
     ) ayy
group by yr