08. Elasticsearch - aggregation (집합 조회)

이전 밀리세컨드 단위 데이터들을 뽑는데 부하가 심해 어떻게 통계 차트를 그릴지 고민을 하다가 초단위로 데이터를 뽑으려 함.

 

엘라스틱서치에서 aggregaion 이란 집합 개념으로 지원해주는 기능이 있음.

 

여러 단위를 지정할 수 있으며, 나는 초단위 날짜 집합 데이터가 필요한 상황.

 

date_histogram을 통해 초, 분, 시간 등의 원하는 날짜 단위의 통계 데이터를 뽑을 수 있음.

 

나는 초단위 평균값을 원했으므로 다음과 같이 예시 쿼리를 사용.

 

GET /memd_80/_search?
{
    "size": 0,
    "query": {
        "range": {
            "Time": {
              "gte": "2022-08-04T15:00:00",
              "lte": "2022-08-04T15:01:00"
            }
        }
    },
    "aggs": {
        "aregation": {
            "date_histogram": {
                "field": "Time",
                "calendar_interval": "second"
            },
            "aggs": {
              "Voltage_R": {
                "avg": {
                  "field": "ENERGY.Voltage_R"
                }
              },
              "Voltage_S": {
                "avg": {
                  "field": "ENERGY.Voltage_S"
                }
              },
              "Voltage_T": {
                "avg": {
                  "field": "ENERGY.Voltage_T"
                }
              }
            }
        }
    }
}

 

결과값으로는 다음과 같이 나옴.

 

{
  "took" : 119,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1337,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "aregation" : {
      "buckets" : [
        {
          "key_as_string" : "2022-08-04T15:00:00.000Z",
          "key" : 1659625200000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.206500313498758
          },
          "Voltage_T" : {
            "value" : 15.206363938071512
          },
          "Voltage_R" : {
            "value" : 15.206363938071512
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:01.000Z",
          "key" : 1659625201000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.216181841763584
          },
          "Voltage_T" : {
            "value" : 15.213317957791416
          },
          "Voltage_R" : {
            "value" : 15.2134088602933
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:02.000Z",
          "key" : 1659625202000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.197954871437766
          },
          "Voltage_T" : {
            "value" : 15.197954871437766
          },
          "Voltage_R" : {
            "value" : 15.199954769828103
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:03.000Z",
          "key" : 1659625203000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.175999641418457
          },
          "Voltage_T" : {
            "value" : 15.175999641418457
          },
          "Voltage_R" : {
            "value" : 15.177909504283559
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:04.000Z",
          "key" : 1659625204000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.177136031064121
          },
          "Voltage_T" : {
            "value" : 15.177045128562234
          },
          "Voltage_R" : {
            "value" : 15.177136031064121
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:05.000Z",
          "key" : 1659625205000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.200772935693914
          },
          "Voltage_T" : {
            "value" : 15.198863983154297
          },
          "Voltage_R" : {
            "value" : 15.20090931112116
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:06.000Z",
          "key" : 1659625206000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196227377111262
          },
          "Voltage_T" : {
            "value" : 15.196136474609375
          },
          "Voltage_R" : {
            "value" : 15.199000358581543
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:07.000Z",
          "key" : 1659625207000,
          "doc_count" : 21,
          "Voltage_S" : {
            "value" : 15.201000213623047
          },
          "Voltage_T" : {
            "value" : 15.199000358581543
          },
          "Voltage_R" : {
            "value" : 15.199000358581543
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:08.000Z",
          "key" : 1659625208000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.208999633789062
          },
          "Voltage_T" : {
            "value" : 15.206000328063965
          },
          "Voltage_R" : {
            "value" : 15.208999633789062
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:09.000Z",
          "key" : 1659625209000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196000099182129
          },
          "Voltage_T" : {
            "value" : 15.196000099182129
          },
          "Voltage_R" : {
            "value" : 15.196000099182129
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:10.000Z",
          "key" : 1659625210000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.199000358581543
          },
          "Voltage_T" : {
            "value" : 15.199000358581543
          },
          "Voltage_R" : {
            "value" : 15.201000213623047
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:11.000Z",
          "key" : 1659625211000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.187999725341797
          },
          "Voltage_T" : {
            "value" : 15.185999870300293
          },
          "Voltage_R" : {
            "value" : 15.187999725341797
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:12.000Z",
          "key" : 1659625212000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.192999839782715
          },
          "Voltage_T" : {
            "value" : 15.192999839782715
          },
          "Voltage_R" : {
            "value" : 15.196000099182129
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:13.000Z",
          "key" : 1659625213000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.201000213623047
          },
          "Voltage_T" : {
            "value" : 15.201000213623047
          },
          "Voltage_R" : {
            "value" : 15.204000473022461
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:14.000Z",
          "key" : 1659625214000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.192999839782715
          },
          "Voltage_T" : {
            "value" : 15.192999839782715
          },
          "Voltage_R" : {
            "value" : 15.196000099182129
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:15.000Z",
          "key" : 1659625215000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.190999984741211
          },
          "Voltage_T" : {
            "value" : 15.187999725341797
          },
          "Voltage_R" : {
            "value" : 15.190999984741211
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:16.000Z",
          "key" : 1659625216000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196000099182129
          },
          "Voltage_T" : {
            "value" : 15.196000099182129
          },
          "Voltage_R" : {
            "value" : 15.199000358581543
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:17.000Z",
          "key" : 1659625217000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.182999610900879
          },
          "Voltage_T" : {
            "value" : 15.182999610900879
          },
          "Voltage_R" : {
            "value" : 15.185999870300293
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:18.000Z",
          "key" : 1659625218000,
          "doc_count" : 21,
          "Voltage_S" : {
            "value" : 15.180999755859375
          },
          "Voltage_T" : {
            "value" : 15.180999755859375
          },
          "Voltage_R" : {
            "value" : 15.180999755859375
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:19.000Z",
          "key" : 1659625219000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.202954985878684
          },
          "Voltage_T" : {
            "value" : 15.202954985878684
          },
          "Voltage_R" : {
            "value" : 15.202954985878684
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:20.000Z",
          "key" : 1659625220000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196363752538508
          },
          "Voltage_T" : {
            "value" : 15.19349986856634
          },
          "Voltage_R" : {
            "value" : 15.196363752538508
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:21.000Z",
          "key" : 1659625221000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.18359054218639
          },
          "Voltage_T" : {
            "value" : 15.186318050731312
          },
          "Voltage_R" : {
            "value" : 15.186454426158559
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:22.000Z",
          "key" : 1659625222000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.198273051868785
          },
          "Voltage_T" : {
            "value" : 15.192681659351695
          },
          "Voltage_R" : {
            "value" : 15.195545543323863
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:23.000Z",
          "key" : 1659625223000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.213317957791416
          },
          "Voltage_T" : {
            "value" : 15.213045206936924
          },
          "Voltage_R" : {
            "value" : 15.216045466336338
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:24.000Z",
          "key" : 1659625224000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.204454985531894
          },
          "Voltage_T" : {
            "value" : 15.204454985531894
          },
          "Voltage_R" : {
            "value" : 15.206500313498758
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:25.000Z",
          "key" : 1659625225000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.210682262073863
          },
          "Voltage_T" : {
            "value" : 15.210682262073863
          },
          "Voltage_R" : {
            "value" : 15.213636138222434
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:26.000Z",
          "key" : 1659625226000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.201454769481312
          },
          "Voltage_T" : {
            "value" : 15.199545816941695
          },
          "Voltage_R" : {
            "value" : 15.201591101559726
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:27.000Z",
          "key" : 1659625227000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.201000213623047
          },
          "Voltage_T" : {
            "value" : 15.199000358581543
          },
          "Voltage_R" : {
            "value" : 15.201000213623047
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:28.000Z",
          "key" : 1659625228000,
          "doc_count" : 21,
          "Voltage_S" : {
            "value" : 15.221000126429967
          },
          "Voltage_T" : {
            "value" : 15.21804750533331
          },
          "Voltage_R" : {
            "value" : 15.221000126429967
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:29.000Z",
          "key" : 1659625229000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.212000413374467
          },
          "Voltage_T" : {
            "value" : 15.211727662519975
          },
          "Voltage_R" : {
            "value" : 15.212000413374467
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:30.000Z",
          "key" : 1659625230000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.211000442504883
          },
          "Voltage_T" : {
            "value" : 15.2091815254905
          },
          "Voltage_R" : {
            "value" : 15.211000442504883
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:31.000Z",
          "key" : 1659625231000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.216454592618076
          },
          "Voltage_T" : {
            "value" : 15.216272700916637
          },
          "Voltage_R" : {
            "value" : 15.218272642655807
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:32.000Z",
          "key" : 1659625232000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.221545566212047
          },
          "Voltage_T" : {
            "value" : 15.218818057667125
          },
          "Voltage_R" : {
            "value" : 15.218999862670898
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:33.000Z",
          "key" : 1659625233000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.21745456348766
          },
          "Voltage_T" : {
            "value" : 15.214454304088246
          },
          "Voltage_R" : {
            "value" : 15.217181812633168
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:34.000Z",
          "key" : 1659625234000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.2260911247947
          },
          "Voltage_T" : {
            "value" : 15.22763642397794
          },
          "Voltage_R" : {
            "value" : 15.227909174832432
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:35.000Z",
          "key" : 1659625235000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.228818286548961
          },
          "Voltage_T" : {
            "value" : 15.227182041515004
          },
          "Voltage_R" : {
            "value" : 15.229000091552734
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:36.000Z",
          "key" : 1659625236000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.215363415804775
          },
          "Voltage_T" : {
            "value" : 15.215181610801004
          },
          "Voltage_R" : {
            "value" : 15.218090924349697
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:37.000Z",
          "key" : 1659625237000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.216727256774902
          },
          "Voltage_T" : {
            "value" : 15.21399974822998
          },
          "Voltage_R" : {
            "value" : 15.217000007629395
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:38.000Z",
          "key" : 1659625238000,
          "doc_count" : 21,
          "Voltage_S" : {
            "value" : 15.211571829659599
          },
          "Voltage_T" : {
            "value" : 15.209475835164389
          },
          "Voltage_R" : {
            "value" : 15.211571829659599
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:39.000Z",
          "key" : 1659625239000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.21790903264826
          },
          "Voltage_T" : {
            "value" : 15.217636195096103
          },
          "Voltage_R" : {
            "value" : 15.21790903264826
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:40.000Z",
          "key" : 1659625240000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.217272715135055
          },
          "Voltage_T" : {
            "value" : 15.217272715135055
          },
          "Voltage_R" : {
            "value" : 15.217272715135055
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:41.000Z",
          "key" : 1659625241000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.225636568936435
          },
          "Voltage_T" : {
            "value" : 15.22304543581876
          },
          "Voltage_R" : {
            "value" : 15.225636568936435
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:42.000Z",
          "key" : 1659625242000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.22700023651123
          },
          "Voltage_T" : {
            "value" : 15.223999977111816
          },
          "Voltage_R" : {
            "value" : 15.22700023651123
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:43.000Z",
          "key" : 1659625243000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.195909109982578
          },
          "Voltage_T" : {
            "value" : 15.19549998370084
          },
          "Voltage_R" : {
            "value" : 15.195909109982578
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:44.000Z",
          "key" : 1659625244000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.206545136191629
          },
          "Voltage_T" : {
            "value" : 15.203954826701771
          },
          "Voltage_R" : {
            "value" : 15.206545136191629
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:45.000Z",
          "key" : 1659625245000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.202091043645686
          },
          "Voltage_T" : {
            "value" : 15.201682047410445
          },
          "Voltage_R" : {
            "value" : 15.204682176763361
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:46.000Z",
          "key" : 1659625246000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.199273066087203
          },
          "Voltage_T" : {
            "value" : 15.199273066087203
          },
          "Voltage_R" : {
            "value" : 15.201409339904785
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:47.000Z",
          "key" : 1659625247000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.200727506117387
          },
          "Voltage_T" : {
            "value" : 15.200727506117387
          },
          "Voltage_R" : {
            "value" : 15.201000213623047
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:48.000Z",
          "key" : 1659625248000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196681932969527
          },
          "Voltage_T" : {
            "value" : 15.194090799851852
          },
          "Voltage_R" : {
            "value" : 15.196681932969527
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:49.000Z",
          "key" : 1659625249000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196000099182129
          },
          "Voltage_T" : {
            "value" : 15.19559097290039
          },
          "Voltage_R" : {
            "value" : 15.196000099182129
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:50.000Z",
          "key" : 1659625250000,
          "doc_count" : 21,
          "Voltage_S" : {
            "value" : 15.198571750095912
          },
          "Voltage_T" : {
            "value" : 15.196000099182129
          },
          "Voltage_R" : {
            "value" : 15.198571750095912
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:51.000Z",
          "key" : 1659625251000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.199000358581543
          },
          "Voltage_T" : {
            "value" : 15.198454856872559
          },
          "Voltage_R" : {
            "value" : 15.199000358581543
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:52.000Z",
          "key" : 1659625252000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.192454598166726
          },
          "Voltage_T" : {
            "value" : 15.189999840476297
          },
          "Voltage_R" : {
            "value" : 15.192454598166726
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:53.000Z",
          "key" : 1659625253000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.192636229775168
          },
          "Voltage_T" : {
            "value" : 15.192090728066184
          },
          "Voltage_R" : {
            "value" : 15.192636229775168
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:54.000Z",
          "key" : 1659625254000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.192999839782715
          },
          "Voltage_T" : {
            "value" : 15.192999839782715
          },
          "Voltage_R" : {
            "value" : 15.192999839782715
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:55.000Z",
          "key" : 1659625255000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.192999839782715
          },
          "Voltage_T" : {
            "value" : 15.192999839782715
          },
          "Voltage_R" : {
            "value" : 15.195454597473145
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:56.000Z",
          "key" : 1659625256000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.195454597473145
          },
          "Voltage_T" : {
            "value" : 15.192999839782715
          },
          "Voltage_R" : {
            "value" : 15.196000099182129
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:57.000Z",
          "key" : 1659625257000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.196000099182129
          },
          "Voltage_T" : {
            "value" : 15.195454597473145
          },
          "Voltage_R" : {
            "value" : 15.198454856872559
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:58.000Z",
          "key" : 1659625258000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.204182104630904
          },
          "Voltage_T" : {
            "value" : 15.204182104630904
          },
          "Voltage_R" : {
            "value" : 15.204727606339889
          }
        },
        {
          "key_as_string" : "2022-08-04T15:00:59.000Z",
          "key" : 1659625259000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.206000328063965
          },
          "Voltage_T" : {
            "value" : 15.204364083030008
          },
          "Voltage_R" : {
            "value" : 15.204364083030008
          }
        },
        {
          "key_as_string" : "2022-08-04T15:01:00.000Z",
          "key" : 1659625260000,
          "doc_count" : 22,
          "Voltage_S" : {
            "value" : 15.204364083030008
          },
          "Voltage_T" : {
            "value" : 15.201545715332031
          },
          "Voltage_R" : {
            "value" : 15.204000473022461
          }
        }
      ]
    }
  }
}

 

aggregation의 bucket에 접근하여 평균값들을 자신이 원하는데로 뽑아내어 사용하면 문제 해결 !