MySQL을 사용하여 히스토그램 차트 만들기 – 튜토리얼-MySQL 튜토리얼-php.cn

MySQL로 히스토그램 차트를 작성하기 위한 통계 쿼리를 생성하려면 GROUP BY와 함께 COUNT() 함수를 사용하여 그룹화 제약 조건에 의해 생성된 지정된 범위 또는 범주 내의 값 발생 횟수를 계산할 수 있습니다.

특히 시계열 데이터의 경우 지난 30일 동안 일별 간격으로 등록된 사용자 수를 모니터링하는 등 히스토그램을 사용하는 사례가 많습니다. 우리는 관리 백엔드에서 이러한 쿼리를 사용하여 몇 가지 중요한 KPI를 모니터링합니다.

안타깝게도 SQL 데이터베이스는 히스토그램 표시를 기본적으로 지원한 적이 없지만 모든 종류의 지표를 추적하는 데 가장 많이 사용되는 차트 중 하나입니다.

이 기사에서는 이러한 목적으로 쿼리를 작성하고 몇 가지 제한 사항을 극복하는 방법을 보여 드리겠습니다. 각 판매 날짜와 시간을 포함하는 sale_date라는 열이 있는 sales라는 테이블이 있다고 가정해 보겠습니다. 월별 매출 분포를 보여주는 히스토그램 차트를 만들고 싶습니다.

SELECT DATE_FORMAT(sale_date, '%Y-%m') AS sales_month, COUNT(*) AS count FROM sales GROUP BY DATE_FORMAT(sale_date, '%Y-%m');

로그인 후 복사

다음 검색어:

DATE_FORMAT(sale_date, '%Y-%m')은 sale_date 열의 형식을 연월 형식으로 지정합니다(예: 2024년 1월의 경우 "2024-01").
COUNT(*)는 매월 발생한 판매 횟수를 계산합니다.
FROM sales는 데이터를 검색할 테이블을 지정합니다.
GROUP BY DATE_FORMAT(sale_date, '%Y-%m')은 형식이 지정된 판매 날짜를 기준으로 판매 데이터를 월간 간격으로 그룹화합니다.

이 쿼리를 사용하면 각 월간 간격 내 판매 발생 횟수를 확인할 수 있으므로 히스토그램 차트를 만들어 시간 경과에 따른 매출 분포를 시각화할 수 있습니다.

히스토그램 쿼리의 공백을 채우는 방법
이 쿼리를 실행하면 결과 집합에 일부 누락된 달이 표시될 수 있습니다. 특정 달에는 매출이 없어서 GROUP BY 함수가 해당 간격에 대한 데이터를 생성할 수 없기 때문일 수 있습니다.

기본 0 값으로 이러한 공백을 메우기 위해 쿼리를 어떻게 조정할 수 있나요? 그렇지 않으면 히스토그램이 불완전한 상태로 유지됩니다.

특정 달에 매출이 없더라도 결과 집합의 공백을 메우고 모든 달이 표시되도록 하려면 재귀 공통 테이블 표현식(CTE)을 사용하여 선택한 달력 간격에 대한 월을 동적으로 생성할 수 있습니다.

원하는 기간을 포함하는 일련의 날짜를 생성합니다. 그런 다음 판매 데이터와 함께 이 날짜 순서를 LEFT JOIN하여 결과 집합에 모든 달을 포함할 수 있습니다.

WITH RECURSIVE DateRange AS ( SELECT DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 11 MONTH), '%Y-%m-01') AS min_date, DATE_FORMAT(NOW(), '%Y-%m-01') AS max_date UNION ALL SELECT DATE_FORMAT(DATE_ADD(min_date, INTERVAL 1 MONTH), '%Y-%m-01'), max_date FROM DateRange WHERE DATE_ADD(min_date, INTERVAL 1 MONTH) <= max_date ) SELECT DATE_FORMAT(DateRange.min_date, '%Y-%m') AS sales_month, COUNT(sales.sale_date) AS count FROM DateRange LEFT JOIN sales ON DATE_FORMAT(DateRange.min_date, '%Y-%m') = DATE_FORMAT(sales.sale_date, '%Y-%m') GROUP BY sales_month ORDER BY sales_month;

로그인 후 복사

DateRange CTE(공통 테이블 표현식)는 판매 테이블의 최소 판매 날짜와 최대 판매 날짜 사이의 시간 범위를 포함하는 일련의 월을 반복적으로 생성합니다.

이 쿼리는 판매 테이블의 최소 및 최대 판매 날짜를 기반으로 선택한 달력 간격에 대한 월을 동적으로 생성하여 모든 월이 결과 집합에 표시되도록 합니다.

코드로 공백 채우기

SQL 솔루션은 개발자에게 다소 불편할 수 있으며, 더 많은 사용자 정의가 필요하므로 코드 기반 솔루션을 선호하게 될 수도 있습니다.

이 경우 간단한 세 단계를 통해 동일한 결과를 얻을 수 있습니다.

관심 있는 시간 간격으로 배열을 만들어 보세요.
GROUP BY 쿼리에서 결과를 가져옵니다.
병합하세요.

다음은 Laravel과 Carbon 라이브러리를 사용한 코드 조각입니다.

$dates = []; // Create the array with the time interval of your interests for( $day = now()->subDays(31); $day->startOfDay()->lte(now()); $day->addDay() ) { $dates[] = [ 'day' => $day->format('Y-m-d'), 'total' => 0, ]; } // Get the result from the GROUP BY query $sales = $product->sales()->select(DB::raw('DATE(sale_at) as day, CAST(SUM(qty) AS UNSIGNED) as total')) ->where('sale_at', '>=', now()->subDays(31)) ->groupBy('day') ->get(); // Merge them return array_map(function ($date) use ($sales) { foreach ($sales as $sale) { if ($date['day'] === $sale['day']) { return $sale; } } }, $dates);

로그인 후 복사

데이터베이스에 대한 더 많은 게시물을 읽고 싶다면 아래 기사를 확인하세요.