Mysql根据季度计算增长

Question

我有一个包含两个表的数据库 - companies 和 reports。我想计算从 q1(第 1 季度)到 q2(第 2 季度)的变化。我尝试使用(以下)子查询，但是主查询失败了...

FROM
    (SELECT revenue FROM reports WHERE quarter = 'q2' AND fiscal_year = 2018) AS q,
    (SELECT revenue FROM reports WHERE quarter = 'q1' AND fiscal_year = 2017) AS lq

这是 DB Fiddle，可帮助您理解问题和架构:

https://www.db-fiddle.com/f/eE8SNRojn45h7Rc1rPCEVN/4

当前的简单查询。

SELECT 
    c.name, r.quarter, r.fiscal_year, r.revenue, r.taxes, r.employees
FROM 
    companies c
JOIN
    reports r 
ON
    r.company_id = c.id
WHERE
    c.is_marked = 1;

预期结果(这就是我需要的):

+---------+----------+----------------+----------+--------------+-----------+------------------+
|  Name   | Revenue  | Revenue_change |  Taxes   | Taxes_change | Employees | Employees_change |
+---------+----------+----------------+----------+--------------+-----------+------------------+
| ABC INC |    11056 | +54.77         | 35000.86 | -28.57%      |       568 | -32              |
| XYZ INC |     5000 | null           | null     | null         |        10 | +5               |
+---------+----------+----------------+----------+--------------+-----------+------------------+

非常感谢您帮助构建此查询。提前致谢。

Answer 1

使用 MySQL 8.0 窗口函数:

WITH cte AS (
  SELECT c.name, quarter, fiscal_year
   ,revenue,100*(revenue-LAG(revenue) OVER s)/NULLIF(revenue,0) AS change_revenue
   ,taxes,100*(taxes-LAG(taxes) OVER s)/NULLIF(taxes,0) AS change_taxes
   ,employees,employees-LAG(employees) OVER s AS change_employees
  FROM companies c
  JOIN reports r ON r.company_id = c.id
  WINDOW s AS (PARTITION BY r.company_id ORDER BY fiscal_year, quarter)
)
SELECT *
FROM cte
WHERE quarter = 'Q2';  -- only to get specific quarter
-- comment this condition to get quarter to quarter comparison 

db<>fiddle demo