mysql - 根据MySQL中的季度日期过滤出好的公司结果

Question

我有一张表 'QuarterlyReport'，其中包含多家公司的季度利润数据，其中包含以下列 'CompanyName'、'QuarterEndDate' & '利润'

|CompanyName|QuarterEndDate|Profit||---------------------------------||A          |2013-06-30    |29878 ||A          |2013-09-30    |33712 ||A          |2013-12-31    |60764 ||A          |2014-03-31    |260734||B          |2013-06-30    |-1234 ||B          |2013-09-30    |0     ||B          |2013-12-31    |20114 ||B          |2014-03-31    |-984  |...

I am trying to construct a MySQL query to see which company has a profit performance which increases in every consecutive quarters (Q4>Q3>Q2>Q1), with a 1 year (4 quarters) date range.

In the case of example above, only Company 'A' will meet this requirement and shall be return as the query's result

Currently I only able to construct query for (Q4>0 AND Q3>0 AND Q2>0) using follow MySQL query:

SELECT * FROM (SELECT q.CompanyName, q.QuarterEndDate, q.Profit FROM `QuarterlyReport` q) a
WHERE a.QuarterEndDate >= '2013-06-30' AND
a.QuarterEndDate < '2014-06-30' AND
a.CompanyName IN (SELECT CompanyName FROM `QuarterlyReport` WHERE 
a.CompanyName IN (SELECT Q4.CompanyName FROM `QuarterlyReport` AS Q4 WHERE Q4.QuarterEndDate = '2014-03-31' AND Q4.Profit > '0') AND
a.CompanyName IN (SELECT Q3.CompanyName FROM `QuarterlyReport` AS Q3 WHERE Q3.QuarterEndDate = '2013-12-31' AND Q3.Profit > '0') AND
a.CompanyName IN (SELECT Q2.CompanyName FROM `QuarterlyReport` AS Q2 WHERE Q2.QuarterEndDate = '2013-09-30' AND Q2.Profit > '0') AND
a.CompanyName IN (SELECT Q1.CompanyName FROM `QuarterlyReport` AS Q1 WHERE Q1.QuarterEndDate = '2013-06-30' AND Q1.Profit > '0')
GROUP BY a.CompanyName ORDER BY a.CompanyName ASC

谁能就如何归档我的目标查询提出一些想法？

Answer 1

你可以做到

SELECT * 
  FROM
(
  SELECT CompanyName, 
         MAX(CASE WHEN QUARTER(QuarterEndDate) = 2 THEN Profit END) q1,
         MAX(CASE WHEN QUARTER(QuarterEndDate) = 3 THEN Profit END) q2,
         MAX(CASE WHEN QUARTER(QuarterEndDate) = 4 THEN Profit END) q3,
         MAX(CASE WHEN QUARTER(QuarterEndDate) = 1 THEN Profit END) q4
    FROM QuarterlyReport
   WHERE QuarterEndDate >= '2013-06-30' AND QuarterEndDate < '2014-06-30'
   GROUP BY CompanyName
) q
 WHERE q1 < q2 AND q2 < q3 AND q3 < q4

输出:

| COMPANYNAME |    Q1 |    Q2 |    Q3 |     Q4 ||-------------|-------|-------|-------|--------||           A | 29878 | 33712 | 60764 | 260734 |

这是 SQLFiddle 演示