如何创建显示过去 4 个季度数据的视图? [英] How to create a view that show last 4 quarters data?

问题描述

我的环境是 5.1.72-community.

My environment is 5.1.72-community.

我有一个名为 production 的表，例如:

I have a table named production, like:

 id, person_id, num, p_year, p_quarter
 1, 1, 43, 2018, 2 
 2, 1, 92, 2018, 1
 3, 1, 108, 2017, 4
 4, 2, 41, 2018, 2
 ...

到 '2018-04-14' 时，我们知道季度 = 2，年 = 2018.我定义季度_1 是当年的当前季度，季度_2 是上一季度，以此类推.现在我想创建一个像这样的视图:

By the time of '2018-04-14', we know quarter=2, and year=2018. I define quarter_1 is current quarter of current year, and quarter_2 is previous quarter, ans so on. Now I want to create a view like:

 person_id, num_quarter_1, num_quarter_2, num_quarter_3, num_quarter_4
 1, 43, 92, 108, 0
 2, 41, 0, 0, 0

这样，如果那个季度没有num，就填0.

As that, if there no num in that quarter, fill 0.

请问如何创建该视图?

推荐答案

嗯.如果我将最后四个季度"解释为数据中的最后四个季度"，那么这里是一种方法.

Hmmm. If I interpret "last four quarters" as "the last four quarters that are in the data", then here is one method.

首先，您可以通过以下方式获得四个季度:

First, you can get the four quarters by doing:

select distinct p_year, p_quarter
from production p
order by p_year desc, p_quarter desc
limit 4;

您可以使用变量枚举它们:

You can enumerate them using variables:

select p_year, p_quarter, (@rn := @rn + 1) as enum
from (select distinct p_year, p_quarter
      from production p
      order by p_year desc, p_quarter desc
      limit 4
     ) p cross join
     (select @rn := 0) params

然后，您可以在查询中使用它来透视数据:

Then, you can use this in a query to pivot the data:

select p.person_id,
       sum(case when seqnum = 1 then num else 0 end) as num_quarter_1,
       sum(case when seqnum = 2 then num else 0 end) as num_quarter_2,
       sum(case when seqnum = 3 then num else 0 end) as num_quarter_3,
       sum(case when seqnum = 4 then num else 0 end) as num_quarter_4
from production p join
     (select p_year, p_quarter, (@rn := @rn + 1) as seqnum
      from (select distinct p_year, p_quarter
            from production p
            order by p_year desc, p_quarter desc
            limit 4
           ) p cross join
           (select @rn := 0) params
     ) yq
     using (p_year, p_quarter)
group by p.person_id;

如果您根据当前季度定义季度，您可以做一些非常相似的事情:

If you are defining the quarters based on the current quarter, you can do something quite similar:

select p.person_id,
       sum(case when seqnum = 1 then num else 0 end) as num_quarter_1,
       sum(case when seqnum = 2 then num else 0 end) as num_quarter_2,
       sum(case when seqnum = 3 then num else 0 end) as num_quarter_3,
       sum(case when seqnum = 4 then num else 0 end) as num_quarter_4
from production p join
     (select year(curdate()) as p_year, quarter(curdate()) as p_quarter, 1 as seqnum union all
      select year(curdate() - interval 1 quarter) as p_year, month(curdate() - interval 1 quarter) as p_quarter, 2 as seqnum union all
      select year(curdate() - interval 2 quarter) as p_year, month(curdate() - interval 2 quarter) as p_quarter, 3 as seqnum union all
      select year(curdate() - interval 2 quarter) as p_year, month(curdate() - interval 3 quarter) as p_quarter, 4 as seqnum           
     ) yq
     using (p_year, p_quarter)
group by p.person_id;

还有其他方法，例如:

select person_id,
       sum(case when year(curdate()) = p_year and quarter(curdate()) = p_quarter
                then num else 0
           end) as num_quarter_1,
       sum(case when year(curdate() - interval 1 quarter) = p_year and quarter(curdate() - interval 1 quarter) = p_quarter
                then num else 0
           end) as num_quarter_2,
       sum(case when year(curdate() - interval 2 quarter) = p_year and quarter(curdate() - interval 2 quarter) = p_quarter
                then num else 0
           end) as num_quarter_3,
       sum(case when year(curdate() - interval 3 quarter) = p_year and quarter(curdate() - interval 3 quarter) = p_quarter
                then num else 0
           end) as num_quarter_4
from production p
group by person_id;

这篇关于如何创建显示过去 4 个季度数据的视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！