作者:优美rosner_704 | 来源:互联网 | 2022-12-01 11:37
我有一个包含两个表的数据库 - 公司和报告.我想计算从q1(第1季度)到第2季度(第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 |
+---------+----------+----------------+----------+--------------+-----------+------------------+
我非常感谢您帮助构建此查询.提前致谢.
1> Lukasz Szozd..:
使用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 <>小提琴演示