在mysql中按月分析季度/月份

I've loans data and I want to compare sales in different years based on quarter or month

我有贷款数据，我想根据季度或月份比较不同年份的销售额

My data looks like this

我的数据看起来像这样

disbursementdate | amount | product | cluster
2017-01-01       | 1000   | HL      | West
2018-02-01       | 1000   | PL      | East

So After querying, I'd ideally want the result to look like this

所以在查询之后，我理想地希望结果看起来像这样

 Quarter | 2017 | 2018
   Q1    | 1000 | 0
   Q2    | 100  | 1000

Similarly, it can be done for a monthly analysis as well

同样，也可以进行月度分析

I'm not averse to storing data in a different format either ... can split date in different field like month quarter year

我不反对以不同的格式存储数据......可以在不同的字段中分割日期，例如月份季度

I'm struggling with query

我正在努力解决查询问题

2 个解决方案

#1

You can use conditional aggregation:

您可以使用条件聚合：

select quarter(disbursementdate) as quarter,
       sum(case when year(disbursementdate) = 2017 then amount else 0 end) as amount_2017,
       sum(case when year(disbursementdate) = 2018 then amount else 0 end) as amount_2018
from 
group by quarter(disbursementdate) ;

If you wanted year/quarter on separate rows, you would do:

如果你想在不同的行上使用年/季，你会这样做：

select year(disbursementdate) as year, quarter(disbursementdate) as quarter,
       sum(amount)
from 
group by year(disbursementdate), quarter(disbursementdate) ;

#2

try this:

尝试这个：

    SELECT YEAR(disbursementdate) as _year, quarter, SUM(amount)
    FROM
       (
      SELECT disbursementdate, QUARTER(disbursementdate) as quarter, 
       amount,  product, cluster
       FROM mytable
   ) AS sub_query
  GROUP BY _year, quarter

the format is a bit different but it should be better for the analyses

格式有点不同，但分析应该更好

#1