аюсь идентифицировать тренды тренда (на основе максимальных хитов) по временным рядам с помощью функции MySQL JSON. Ниже мой стол

CREATE TABLE TAG_COUNTER (
    account       varchar(36) NOT NULL,
    time_id       INT NOT NULL,
    counters      JSON,
    PRIMARY KEY   (account, time_id)
)

В каждом веб-запросе API я получу несколько разных тегов для каждой учетной записи, и на основе количества тегов я подготовлюINSERT ON DUPLICATE KEY UPDATE запрос. Ниже приведен пример вставки с двумя тегами.

INSERT INTO `TAG_COUNTER`
  (`account`, `time_id`, `counters`)
VALUES
  ('google', '2018061023', '{"tag1": 1, "tag2": 1}')
ON DUPLICATE KEY UPDATE `counters` =
  JSON_SET(`counters`,
           '$."tag1"',
           IFNULL(JSON_EXTRACT(`counters`,
                        '$."tag1"'), 0) + 1,
           '$."tag2"',
           IFNULL(JSON_EXTRACT(`counters`,
                        '$."tag2"'), 0) + 1
  );

time_id - yyyyMMddhh, и это часовая агрегация для каждой строки.

Теперь моя проблема - получение меток. Приведенный ниже запрос даст мне агрегацию для tag1, но мы не будем знать теги перед выполнением этого запроса.

SELECT
SUBSTRING(time_id, 1, 6) AS month,
SUM(counters->>'$.tag1')
FROM TAG_COUNTER
WHERE counters->>'$.tag1' > 0
GROUP BY month;

Поэтому мне нужна общая группа по запросу вместе с заказом по, чтобы получить тренды для времени на час / день / месяц.

Образец ожидаемого выхода

Time(hour/day/month)  Tag_name  Tag_count_value(total hits)

Когда я искал в Интернете, все, где это упоминается, как показано ниже{"tag_name": "tag1", "tag_count": 1} вместо прямого{"tag1" : 1} и они использовали tag_name в группе по.

Q1) Так всегда ли нужно иметь общеизвестный ключ json для выполнения группы по ...?

Q2) Если мне нужно пойти по этому пути, то каковы изменения в моем запросе INSERT ON DUPLICATE KEY UPDATE для этой новой строки json label / value? Поскольку счетчик должен быть создан, когда он не существует, и должен увеличиваться на единицу, когда он существует.

Q3) Должен ли я поддерживать массив объектов

[
 {"tag_name": "tag1", "tag_count": 2},
 {"tag_name": "tag2", "tag_count": 3}
]

ИЛИ объект объектов, как показано ниже?

{
 {"tag_name": "tag1", "tag_count": 2},
 {"tag_name": "tag2", "tag_count": 3}
}

Так что же лучше, чем структура структуры JSON INSERT и RETRIEVAL количества трендов?

Q4) Могу ли я пойти с существующим{"key" : "value"} формат вместо{"key_label" : key, "value_lable" : "value"} а можно ли извлекать тренды ..? так как я думаю, что{"key" : "value"} очень прост и хорош в производительности.

Q5) При получении я используюSUBSTRING(time_id, 1, 6) AS month, Сможет ли он использовать индекс?

ИЛИ мне нужно создать несколько столбцов, таких какtime_hour(2018061023), time_day(20180610), time_month(201806) и использовать запрос по конкретным столбцам?

ИЛИ я могу использоватьMySQL функции даты и времени? это будет использовать индекс для более быстрого поиска?

Пожалуйста помоги.