MySQL 사용법, 여러 회사 성과 비교하기위한 비교 쿼리

MySQL 사용법 중의 하나로 여러 회사의 지표를 동시해 비교할 수 있는 비교 데이터를 뽑는 방안에 대해서 살펴보겠습니다.

Table of contents 목차 보이기

기본 쿼리

우선 요청하신 기본 쿼리는 다음과 같습니다.

SELECT quarter_fy, (
    SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Intel'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ) AS Intel,
  ( SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Micron'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ) AS Micron
FROM company AS t
GROUP BY quarter_fy
ORDER BY quarter_fy ASC
Code language: PHP (php)

기본 쿼리에서 회사를 추가하고 싶다면

위에서 소개한 기본 쿼리에서 다른 회사를 추가해 비교하고 싶다면, 예를 들어 나이키를 추가하고 싶다면 아래와 같은 쿼리를 SELECT문에 추가합니다.

( SELECT revenue_yoy
  FROM `company` AS s
  WHERE s.company = 'Nike'
    AND s.quarter_fy = t.quarter_fy
    AND date IS NOT NULL
) AS Nike
Code language: PHP (php)

이 쿼리를 추가한 내용은 아래와 같습니다.

SELECT quarter_fy, (
    SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Intel'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ) AS Intel,
  ( SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Micron'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ) AS Micron,

/*  
아래 부분이  Nike라는 회사를 추가할때 추가된 쿼리문입니다.
*/
  ( SELECT revenue_yoy
  FROM `company` AS s
  WHERE s.company = 'Nike'
    AND s.quarter_fy = t.quarter_fy
    AND date IS NOT NULL
) AS Nike
/*  
추가된 쿼리문 끝
*/

FROM company AS t
GROUP BY quarter_fy
ORDER BY quarter_fy ASC
Code language: PHP (php)

시계열 데이터에 null 값이 있으면

기준이 되는 컬럼에 null 값이 있다면, 아래는 quarter_fy에 null 값이 있는 경우 WHERE quarter_fy IS NOT NULL을 추가합니다.

SELECT quarter_fy, (
    SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Intel'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ) AS Intel,
  ( SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Micron'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ) AS Micron,
  ( SELECT revenue_yoy
  FROM `company` AS s
  WHERE s.company = 'Nike'
    AND s.quarter_fy = t.quarter_fy
    AND date IS NOT NULL
) AS Nike
FROM company AS t
WHERE quarter_fy IS NOT NULL
GROUP BY quarter_fy
ORDER BY quarter_fy ASC
Code language: PHP (php)

NULL 값을 빈 공백으로 출력하려면

NULL 값은 데이터가 없는 데이터이므로 빈 공백으로 출력하고 싶으시면 IFNULL(…, ”) 으로 변경합니다.

SELECT quarter_fy,
  IFNULL((
    SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Intel'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ), '') AS Intel,
  IFNULL((
    SELECT revenue_yoy
    FROM `company` AS s
    WHERE s.company = 'Micron'
      AND s.quarter_fy = t.quarter_fy
      AND date IS NOT NULL
  ), '') AS Micron,
  IFNULL(( SELECT revenue_yoy
  FROM `company` AS s
  WHERE s.company = 'Nike'
    AND s.quarter_fy = t.quarter_fy
    AND date IS NOT NULL
  ), '') AS Nike
FROM company AS t
WHERE quarter_fy IS NOT NULL
GROUP BY quarter_fy
ORDER BY quarter_fy ASC
Code language: PHP (php)