연동 - php laravel mysql




MySQL은 쿼터를 기반으로 성장을 계산 (4)

두 테이블 ( 회사보고서) 이있는 데이터베이스가 있습니다. 나는 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               |
+---------+----------+----------------+----------+--------------+-----------+------------------+

이 쿼리를 작성하는 데 정말로 감사드립니다. 미리 감사드립니다.


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 <> 바이올린 데모


그것은 당신에게 필요한 것을 내게주었습니다.

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') 

산출:

감사!!!


단 2 분기 만 비교해야하는 경우 일반 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 참조 https://www.db-fiddle.com/f/6hwbPofSwAiqGBPFZWKxhi/0


순수 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




laravel