mysql根据季度计算增长

elcex8rz 于 2021-06-18 发布在 Mysql

关注(0)|答案(6)|浏览(331)

我有一个数据库有两个表-公司和报告。我想计算从第一季度到第二季度的变化。我尝试使用（下面的）子查询，但是主查询失败了。。。

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               |
+---------+----------+----------------+----------+--------------+-----------+------------------+

我非常感谢您的帮助建立这个查询。提前谢谢。

mysql laravel

来源：https://stackoverflow.com/questions/53025787/mysql-calculate-growth-based-on-quarters

6条答案

按热度按时间

jgwigjjp1#

我让您了解我的方法，只使用一个join with group by子句和聚合函数。pecentajes计算的最后一步是使用第一种方法的派生表：

SELECT
    der.name, der.quarter, der.fiscal_year,
    der.revenue,
    100 * der.revenue_change / der.revenue AS revenue_change,
    der.taxes,
    100 * der.taxes_change / der.taxes AS taxes_change,
    der.employees,
    der.employees_change
FROM
(
    /* First approach (with raw changes) */
    SELECT
        c.name,
        MAX(r.quarter) AS quarter,
        ANY_VALUE(r.fiscal_year) AS fiscal_year,
        SUM(CASE WHEN r.quarter = "Q2" THEN r.revenue END) AS revenue,
        IF (COUNT(r.revenue) = 2,
            SUM(CASE WHEN r.quarter = "Q1" THEN (-1 * r.revenue) ELSE r.revenue END),
            NULL) AS revenue_change,
        SUM(CASE WHEN r.quarter = "Q2" THEN r.taxes END) AS taxes,
        IF (COUNT(r.taxes) = 2,
            SUM(CASE WHEN r.quarter = "Q1" THEN (-1 * r.taxes) ELSE r.taxes END),
            NULL) AS taxes_change,
        SUM(CASE WHEN r.quarter = "Q2" THEN r.employees END) AS employees,
        IF (COUNT(r.employees) = 2,
            SUM(CASE WHEN r.quarter = "Q1" THEN (-1 * r.employees) ELSE r.employees END),
            NULL) AS employees_change
    FROM
        companies AS c
    LEFT JOIN
        reports AS r ON r.company_id = c.id AND r.quarter IN ("Q1","Q2")
    GROUP BY
        c.id
) AS der

您可以在下一个链接中检查到最终查询的进度：
https://www.db-fiddle.com/f/2tuc1ggjusvnxsyghhgwc/3
泛化查询
我总结了前面关于不同财政年度季度比较方法的查询，如您在评论中所问，我将这样做：

SET @q1Year = 2017;
SET @q2Year = 2018;
SET @q1 = "Q1" COLLATE utf8mb4_unicode_ci;
SET @q2 = "Q2" COLLATE utf8mb4_unicode_ci;

SELECT
    der.name,
    der.compared_quarters,
    der.quarter,
    der.fiscal_year,
    der.revenue,
    100 * der.revenue_change / der.revenue AS revenue_change,
    der.taxes,
    100 * der.taxes_change / der.taxes AS taxes_change,
    der.employees,
    der.employees_change
FROM                                                       
(
    /* This query generate raw changes on revenue, taxes and employees */
    SELECT
        c.name,
        CONCAT(@q1,"-",@q1Year," vs ",@q2,"-",@q2Year) AS compared_quarters,
        @q2 AS quarter,
        @q2Year AS fiscal_year,
        SUM(IF(r.quarter = @q2, r.revenue, 0)) AS revenue,
        IF (COUNT(r.revenue) = 2,
            SUM(IF(r.quarter = @q1, -1 * r.revenue, r.revenue)),
            NULL) AS revenue_change,
        SUM(IF(r.quarter = @q2, r.taxes, 0)) AS taxes,
        IF (COUNT(r.taxes) = 2,
            SUM(IF(r.quarter = @q1, -1 * r.taxes, r.taxes)),
            NULL) AS taxes_change,
        SUM(IF(r.quarter = @q2, r.employees, 0)) AS employees,
        IF (COUNT(r.employees) = 2,
            SUM(IF(r.quarter = @q1, -1 * r.employees, r.employees)),
            NULL) AS employees_change
    FROM
        companies AS c
    LEFT JOIN
        reports AS r ON r.company_id = c.id
    AND
        ((r.quarter = @q1 AND r.fiscal_year = @q1Year) OR (r.quarter = @q2 AND r.fiscal_year = @q2Year))
    GROUP BY
        c.id
) AS der;

您可以在下一个链接中查看一个示例：
https://www.db-fiddle.com/f/2tuc1ggjusvnxsyghhgwc/4

赞(0）回复(0）举报 2021-06-18

ss2ws0br2#

它给了我你所需要的输出。

select 
    c.name, 
    r2.revenue, 
     concat(TRUNCATE(100 * (r2.revenue - r1.revenue) / r2.revenue,2),"%") as revenue_change,
    r2.taxes, 
   concat(TRUNCATE( 100 * (r2.taxes - r1.taxes) / r2.taxes,2),"%")  as taxes_change,
    r2.employees, 
    r2.employees - r1.employees as employees_change
from 
    companies c
    LEFT JOIN reports r1 ON (c.id = r1.company_id and r1.quarter = 'q1') 
    LEFT JOIN reports r2 ON (c.id = r2.company_id and r2.quarter = 'q2')

输出：

谢谢！！！

赞(0）回复(0）举报 2021-06-18

7qhs6swi3#

使用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<>小提琴演示

赞(0）回复(0）举报 2021-06-18

z2acfund4#

使用纯sql很难得到结果。但我做到了。
执行以下sql。我希望你能得到这个sql的帮助。

select 
    qd2.name as Name,
    qd2.Revenue as Revenue,
    qd2.Revenue - qd1.Revenue as Revenue_Change,
    qd2.Taxes as Taxes,
    (qd2.Taxes - qd1.Taxes) * 100 / qd1.Taxes as Taxes_Change,
    qd2.Employees as Employees,
    (qd2.Employees - qd1.Employees) as Employees_Change
from 
    (
        SELECT 
            (@cnt := @cnt + 1) AS rowNumber, 
            c.name as name, 
            r.revenue as Revenue,
            r.taxes as Taxes,
            r.employees as Employees
        FROM 
            companies c
        JOIN
            reports r 
        CROSS JOIN (SELECT @cnt := 0) AS dummy
        ON
            r.company_id = c.id and
            r.quarter = "q2"
        order by name
    ) as qd2
    JOIN
    (
        SELECT 
            (@cnt2 := @cnt2 + 1) AS rowNumber, 
            c.name as name, 
            r.revenue as Revenue,
            r.taxes as Taxes,
            r.employees as Employees
        FROM 
            companies c
        JOIN
            reports r 
        CROSS JOIN (SELECT @cnt2 := 0) AS dummy
        ON
            r.company_id = c.id and
            r.quarter = "q1"
        order by name
    ) as qd1
    ON qd1.rowNumber = qd2.rowNumber

结果如下

Name    Revenue Taxes   Employees   Revenue_Change  Taxes_Change    Employees_Change
ABC INC 11056   35000.86    568 6056    -22.221798  -32
XYZ LLC 5000    null    10  null    null    5

赞(0）回复(0）举报 2021-06-18

6l7fqoea5#

我试过了。我会这样做的。

SELECT 
  c.name, 
  reportsQ2.quarter, 
  reportsQ2.revenue as revenu1, 
  reportsQ1.revenue as revenue2, 
  (COALESCE(reportsQ2.revenue, 0) - COALESCE(reportsQ1.revenue, 0)) as difference
FROM 
    reports as reportsQ2 
LEFT JOIN 
    reports as reportsQ1 ON reportsQ1.company_id = reportsQ2.company_id
AND 
    reportsQ1.quarter = 'Q1'
LEFT JOIN
    companies as c ON c.id = reportsQ2.company_id
WHERE 
    reportsQ2.quarter = 'Q2'

所以我首先基本上选择了第二季度的所有报告。然后我以相同的公司id加入了第一季度。
在这一点上，您已经拥有了进行计算所需的所有信息。我曾经 COALESCE() 为了确保null值不会干扰计算，输出null而不是有效的数字。
最后，我加入了 companies 表以获取名称。
我对收入行进行了计算，并将它们输出为 difference .
希望有帮助！

赞(0）回复(0）举报 2021-06-18

13z8s7eq6#

如果您只需要比较两个四分之二，那么您可以用纯sql来完成。无需编程。
没有子查询，只需按季度两次将公司加入报表即可。

select 
    c.name, 
    r2.revenue, 
    100 * (r2.revenue - r1.revenue) / r2.revenue as revenue_change,
    r2.taxes, 
    100 * (r2.taxes - r1.taxes) / r2.taxes as taxes_change,
    r2.employees, 
    r2.employees - r1.employees as employees_change
from 
    companies c
    LEFT JOIN reports r1 ON (c.id = r1.company_id and r1.quarter = 'q1')
    LEFT JOIN reports r2 ON (c.id = r2.company_id and r2.quarter = 'q2')

看到了吗https://www.db-fiddle.com/f/6hwbpofswaiqgbpfzwkxhi/0

赞(0）回复(0）举报 2021-06-18

我来回答

mysql根据季度计算增长

6条答案

相关问题

热门标签

最新问答