mysql - 日期范围每行动态变化

Question

我需要为查询结果的每一行指定一个特定的日期范围，例如:

    select 
    DATE_FORMAT(date_purchase, '%Y-%m')as Year_month,
    count(*) as Count
    from tickets 
    where date_purchase BETWEEN '2019-01-01' AND '2019-03-31'
group by Year_month

此查询给出结果:

Year_month | Count
2019-01    | 134
2019-02    | 154
2019-03    | 123

但是我需要再添加一列来与一年前的计数进行比较，预期结果将是这样的:

Year_month | Count | Count_One_Year_Ago_Per_Month
2019-01    | 134   | (count(*) between 2018-01 and 2019-01)
2019-02    | 154   | (count(*) between 2018-02 and 2019-02)
2019-03    | 123   | (count(*) between 2018-03 and 2019-03)

最好的方法是什么？

编辑:我的Mysql版本是5.7

Answer 1

如果您有所有月份的数据，最简单的方法是使用lag():

select t.*
from (select date_format(sa.date_purchase, '%Y-%m') as Year_month,
             count(*) as Count,
             lag(count(*), 12) over (order by min(sa.date_purchase) as count_year_ago
      from tickets t
      group by Year_month
     ) t
where year_month between '2019-01' and '2019-03';

编辑:

您还可以使用条件聚合:

select month(date_purchase),
       sum(year(date_purchase) = 2018) as cnt_2018,
       sum(year(date_purchase) = 2019) as cnt_2019
from tickets t
where date_purchase >= '2018-01-01' and
      date_purchase < '2019-04-01' and
      month(date_purchase) in (1, 2, 3)
group by month(date_purchase);

如果您想要一整年的计数，我认为您可能需要相关子查询:

select ym.*,
       (select count(*)
        from tickets t2
        where date_format(t2.date_purchase, '%Y-%m') < ym.year_month and
              date_format(t2.date_purchase + interval 1 year, '%Y-%m') >= ym.year_month
       ) as prev_cumulative_year
from (select date_format(sa.date_purchase, '%Y-%m') as Year_month,
             count(*) as Count
      from tickets t
      group by Year_month
     ) ym