Elasticsearch와 MySQL에서의 날짜별 필드 합계 계산하기

데이터 분석을 하다 보면, 특정 기간 동안 특정 필드의 합계를 날짜별로 계산하는 경우가 자주 있습니다. 이번 포스트에서는 Elasticsearch와 MySQL에서 같은 작업을 어떻게 수행하는지를 비교하면서 설명하겠습니다.

먼저 다음과 같은 판매 데이터가 있다고 가정해봅시다:

#sales 인덱스:
[
  { "sale_id": 1, "date": "2023-01-01", "category": "Electronics", "amount": 200 },
  { "sale_id": 2, "date": "2023-01-02", "category": "Electronics", "amount": 150 },
  { "sale_id": 3, "date": "2023-01-07", "category": "Books", "amount": 20 },
  { "sale_id": 4, "date": "2023-02-01", "category": "Books", "amount": 25 },
  { "sale_id": 5, "date": "2023-02-02", "category": "Books", "amount": 30 },
  { "sale_id": 6, "date": "2023-02-07", "category": "Electronics", "amount": 300 }
]

Elasticsearch에서의 합계 계산

Elasticsearch에서는 date_histogram과 sum 집계를 이용해 날짜별 amount 합계를 계산할 수 있습니다. 다음 쿼리를 살펴봅시다.

#GET /sales/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "date": {
              "gte": "2023-01-01",
              "lte": "2023-02-28"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "sales_per_day": {
      "date_histogram": {
        "field": "date",
        "interval": "day",
        "format": "yyyy-MM-dd",
        "min_doc_count": 0
      },
      "aggs": {
        "amount_sum": {
          "sum": {
            "field": "amount"
          }
        }
      }
    }
  }
}

이 쿼리는 먼저 bool 쿼리의 filter 조건을 사용하여 2023년 1월 1일부터 2023년 2월 28일까지의 데이터만 선택합니다. 그런 다음 date_histogram 집계를 사용하여 날짜별로 데이터를 분할하고, 각 날짜 버킷 내에서 amount의 합계를 계산하는 sum 집계를 적용합니다.

MySQL에서의 합계 계산

MySQL에서는 GROUP BY와 SUM 함수를 사용하여 동일한 결과를 얻을 수 있습니다. 아래 쿼리를 확인해 보세요.

SELECT DATE(date) as sales_date, SUM(amount) as total_amount
FROM sales
WHERE date BETWEEN '2023-01-01' AND '2023-02-28'
GROUP BY sales_date
ORDER BY sales_date;

이 쿼리는 먼저 WHERE 절을 사용하여 2023년 1월 1일부터 2023년 2월 28일까지의 데이터를 필터링합니다. 그런 다음 GROUP BY 절을 사용하여 날짜별로 데이터를 그룹화하고, 각 그룹에서 amount의 합계를 계산하는 SUM 함수를 적용합니다.

결론

이번 시간에는 date_histogram을 간단하게 소개한 지난 내용에서 한 발자국 더 나아가 날짜별로 특성 필드값의 합계를 계산하는 쿼리를 작성해 봤습니다. 다음 시간에는 elasticsearch 고유한 항목의 개수를 계산하는 cardinality 라고 하는 집계 방식을 다루는 내용에 대한 포스트를 작성해 보겠습니다. 감사합니다 :)