mysql - 如何获取当前和去年的信息

Question

我有下表。如果某一天没有线索，则不会输入数据。

Date        Center      Leads
1/1/2018    A           2
1/1/2018    B           1
1/2/2018    A           2
1/3/2018    A           4
1/3/2018    B           1
1/1/2019    A           3
1/3/2019    A           2

当我执行连接时，我想按中心获取今年和去年的领先优势。此外，如果今年没有潜在客户，但去年有一个条目，我想将今年作为 0 包含在结果集中。

结果集:

Date        Center      Leads     Leads-PastYear
1/1/2018    A           2         0
1/1/2018    B           1         0
1/2/2018    A           2         0
1/3/2018    A           4         0
1/3/2018    B           1         0
1/1/2019    A           3         2
1/1/2019    B           0         1
1/2/2019    A           0         2
1/3/2019    A           2         4
1/3/2019    B           0         1

我的查询:

SELECT 
  T1.Date, 
  T1.Center, 
  T1.Leads, 
  T2.Leads AS 'Leads-PastYear' 
FROM table T1
LEFT JOIN table T2 ON 
  DATE_SUB(T1.Date, INTERVAL 1 YEAR) = T2.Date AND
  T1.Center = T2.Center

UNION ALL 

SELECT 
  DATE_ADD(T2.Date, INTERVAL 1 YEAR) AS 'Date', 
  T2.Center, 
  T1.Leads, 
  T2.Leads AS 'Leads-PastYear' 
FROM table T1
RIGHT JOIN table T2 ON 
  DATE_ADD(T2.DATE, INTERVAL 1 YEAR) = T1.Date AND
  T2.Center = T1.Center
WHERE DATE_ADD(T2.Date, INTERVAL 1 YEAR) NOT IN (SELECT T3.Date FROM table T3) AND 
YEAR(DATE_ADD(T2.Date, INTERVAL 1 YEAR)) <= YEAR(CURRENT_DATE)

Answer 1

我正在考虑获取结果集中的所有日期，然后使用 JOIN 获取两年中的日期:

with dc as (
      select t.date, t.center
      from t
      where t.date >= datefromparts(year(getdate() - 1, 1, 1)
      union   -- on purpose to remove duplicates
      select dateadd(year, 1, t.date), t.center
      from t
      where t.date >= datefromparts(year(getdate() - 1, 1, 1) and
            t.date < datefromparts(year(getdate(), 1, 1)
     )
select dc.date, dc.center, t.leads,
       coalesce(tprev.leads, 0) as prev_year_leads
from dc left join
     t
     on t.date = dates.date and t.center = dc.center left join
     t tprev
     on tprev.date = dateadd(year, -1, dates.date) and
        tprev.center = dc.center;

我对使用窗口函数有点谨慎。闰年可能会导致问题。