IT Note

시계열 데이터 일정 간격 집계

엘라스틱서치 시계열데이터 날짜별, 시간별 등 기준 일정 간격으로 집계하는법을 알아봅시다.

달력기준 일정 간격 집계

GET [인덱스]/_search
{
    "query": {
        "match_all": {}
	},
	"size": 0,
	"aggs": {
		"my_buckets": {
			"date_histogram": {
				"field": "timetmp",
				"calendar_interval": "month",
				"format": "yyyy-MM"         
			}
		}
	}
}

my_buckets: 버켓 이름

field: 간격을 측정할 필드

format: 날짜 포맷

calendar_interval: 달력 간격

시간기준 일정 간격 집계

GET [인덱스]/_search
{
    "query": {
        "match_all": {}
	},
	"size": 0,
	"aggs": {
		"my_buckets": {
			"date_histogram": {
				"field": "timetmp",
				"fixed_interval": "1h",
				"format": "yyyy-MM-dd/hh:mm:ss"
			}
		}
	}
}

my_buckets: 버켓 이름

field: 간격을 측정할 필드

format: 날짜 포맷

fixed_interval : 시간 간격

일정 간격 데이터 통계

GET [인덱스]/_search
{
    "query": {
        "match_all": {}
	},
	"size": 0,
	"aggs": {
		"my_buckets": {
			"date_histogram": {
				"field": "timetmp",
				"fixed_interval": "1h",
				"format": "yyyy-MM-dd/hh:mm:ss"         
			},
			"aggs":{
				"data_sum":{
					"sum" : { "field": "egg" }
				}
			}
		}
	}
}

각각의 date_histogram 데이터를 통계내고 싶으면 위와 같이 aggs 를 이용하여  통계낼수 있습니다.

기타

GET [인덱스]/_search
{
    "query": {
        "match_all": {}
	},
	"size": 0,
	"aggs": {
		"my_buckets": {
			"date_histogram": {
				"field": "timetmp",
				"fixed_interval": "6h",
				"extended_bounds": {
					"min": "2024-03-26T00:00:00"
					"max": "2024-03-26T23:59:59"
				}
			}
		}
	}
}

extended_bounds 를 이용하여 histogram 범위를 확장하면 중간에 Null값으로 인해 표시되지 않는 데이터도 표시할 수 있습니다.

extended_bounds 적용 전 데이터

"buckets":
[
    {
        "key_as_string": "2024-03-26T00:00:00.000Z",
        "key": 1711411200000,
        "doc_count": 4245
    },
    {
        "key_as_string": "2024-03-26T12:00:00.000Z",
        "key": 1711454400000,
        "doc_count": 1862
    }
]

extended_bounds 적용 후 데이터

"buckets":
[
    {
        "key_as_string": "2024-03-26T00:00:00.000Z",
        "key": 1711411200000,
        "doc_count": 4245
    },
    {
        "key_as_string": "2024-03-26T06:00:00.000Z",
        "key": 1711432800000,
        "doc_count": 0
    },
    {
        "key_as_string": "2024-03-26T12:00:00.000Z",
        "key": 1711454400000,
        "doc_count": 1862
    },
    {
        "key_as_string": "2024-03-26T18:00:00.000Z",
        "key": 1711476000000,
        "doc_count": 0
    }
]

마무리

잘못된 점이 있거나 궁금한 점이 있다면 언제든지 문의해주시기 바랍니다!

ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.

에러 환경

Client OS: Windows10

Server OS: Ubuntu 22.04.2 LTS

MySQL: 8.0.30

에러 원인

JSON_CONTAINS 함수 사용할 때 인자값이 잘못되어 발생하는 에러입니다.

대부분 작은 따옴표('), 큰 따옴표(")를 잘못써서 발생합니다.

해결 방안

JSON_CONTAINS('컬럼', '1')

함수 파라미터를 전달할 때 작은 따옴표(')를 사용합니다.

JSON_CONTAINS('컬럼','"문자열"')

문자열을 전달할 때는 큰 따옴표(") 를 사용하고 다시 작음 따옴표(')로 감싸줘야 합니다. 

마무리

잘못된 점이 있거나 궁금한 점이 있다면 언제든지 문의해주시기 바랍니다!

Json, 다중 Json 데이터 WHERE문 검색하기

MySQL 5.7 버전부터는 Json 타입의 데이터를 사용할 수 있습니다. Json 타입의 데이터를 검색하는 방법에 대해 알아봅시다.

테스트용 테이블 만들기

테이블 생성

# 테이블 생성 #
CREATE TABLE json_test (
	name varchar(8),
	data json
)

Query OK, 0 rows affected (0.01 sec)

# 테이블 확인 #
DESC json_test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(8) | YES  |     | NULL    |       |
| data  | json       | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

데이터 추가

# 데이터 추가 #
INSERT INTO json_test(name, data) VALUE ('test0','{"id":0, "value": { "name": "a", "array": [0, 2] } }');
INSERT INTO json_test(name, data) VALUE ('test1','{"id":1, "value": { "name": "b", "array": [0, 3] } }');
INSERT INTO json_test(name, data) VALUE ('test2','{"id":2, "value": { "name": "c", "array": [1, 2] } }');

# 데이터 확인 #
SELECT * FROM json_test;
+-------+----------------------------------------------------+
| name  | data                                               |
+-------+----------------------------------------------------+
| test0 | {"id": 0, "value": {"name": "a", "array": [0, 2]}} |
| test1 | {"id": 1, "value": {"name": "b", "array": [0, 3]}} |
| test2 | {"id": 2, "value": {"name": "c", "array": [1, 2]}} |
+-------+----------------------------------------------------+
3 rows in set (0.00 sec)

테스트용 테이블 검색하기

Json 검색

SELECT * FROM json_test
WHERE data->"$.id" = 0;
+-------+----------------------------------------------------+
| name  | data                                               |
+-------+----------------------------------------------------+
| test0 | {"id": 0, "value": {"name": "a", "array": [0, 2]}} |
+-------+----------------------------------------------------+
1 row in set (0.00 sec)

Json 타입 컬럼명 -> "$.필드이름" 형식을 이용하여 Json 데이터에 접근할 수 있습니다.

다중 Json 검색

SELECT * FROM json_test
WHERE data->"$.value.name" = "c";
+-------+----------------------------------------------------+
| name  | data                                               |
+-------+----------------------------------------------------+
| test2 | {"id": 2, "value": {"name": "c", "array": [1, 2]}} |
+-------+----------------------------------------------------+
1 row in set (0.00 sec)

Json 타입 컬럼명 -> "$.필드이름.필드이름" 형식을 이용하여 Json 속 Json 데이터에 접근할 수 있습니다.

Json 속 배열 검색

SELECT * FROM json_test
WHERE data->"$.value.array[0]" = 0;
+-------+----------------------------------------------------+
| name  | data                                               |
+-------+----------------------------------------------------+
| test0 | {"id": 0, "value": {"name": "a", "array": [0, 2]}} |
| test1 | {"id": 1, "value": {"name": "b", "array": [0, 3]}} |
+-------+----------------------------------------------------+
2 rows in set (0.00 sec)

Json 타입 컬럼명 -> "$.필드이름[n]" 형식을 이용하여 Json 속 배열 n번째 데이터에 접근할 수 있습니다.

Json 속 배열 전체 내 검색

SELECT * FROM json_test
WHERE JSON_CONTAINS(data->"$.value.array",'2');
+-------+----------------------------------------------------+
| name  | data                                               |
+-------+----------------------------------------------------+
| test0 | {"id": 0, "value": {"name": "a", "array": [0, 2]}} |
| test2 | {"id": 2, "value": {"name": "c", "array": [1, 2]}} |
+-------+----------------------------------------------------+
2 rows in set (0.00 sec)

JSON_CONTAINS(배열,값) 함수를 이용하여 특정값 배열이 있는 데이터를 찾을 수 있습니다.

마무리

잘못된 점이 있거나 궁금한 점이 있다면 언제든지 문의해주시기 바랍니다!