Implementação de tags de tendências baseadas em mysql json
Estou tentando identificar as marcas de tendência (com base no número máximo de hits) em séries temporais usando o recurso mysql json. Abaixo está minha mesa
CREATE TABLE TAG_COUNTER (
account varchar(36) NOT NULL,
time_id INT NOT NULL,
counters JSON,
PRIMARY KEY (account, time_id)
)
Em cada solicitação de API da Web, receberei várias tags diferentes por conta e, com base no número de tags, prepararei oINSERT ON DUPLICATE KEY UPDATE
inquerir. O exemplo abaixo mostra a inserção com duas tags.
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 é aaaamMMddhh e é agregação horária em cada linha.
Agora, meu problema é a recuperação de tags de exclusão. A consulta abaixo me agregará a tag1, mas não saberemos as tags antes de fazer essa consulta.
SELECT
SUBSTRING(time_id, 1, 6) AS month,
SUM(counters->>'$.tag1')
FROM TAG_COUNTER
WHERE counters->>'$.tag1' > 0
GROUP BY month;
Então, eu preciso de grupo genérico por consulta, juntamente com ordem para obter as marcas de tendências para o tempo de hora em hora / diariamente / mensalmente.
A amostra do resultado esperado é
Time(hour/day/month) Tag_name Tag_count_value(total hits)
Quando pesquisei na web, todos os locais onde é mencionado como abaixo{"tag_name": "tag1", "tag_count": 1}
em vez de direto{"tag1" : 1}
e eles estavam usando tag_name no grupo por.
Q1) Portanto, é sempre obrigatório ter uma chave json conhecida comum para executar o grupo por ..?
Q2) Se eu tiver que seguir esse caminho, qual é a alteração na minha consulta INSERT ON DUPLICATE KEY UPDATE para essa nova estrutura de rótulo / valor json? Uma vez que o contador deve ser criado quando não existe e deve aumentar em um quando existir.
Q3) eu tenho que manter a matriz de objetos
[
{"tag_name": "tag1", "tag_count": 2},
{"tag_name": "tag2", "tag_count": 3}
]
OU objeto de objetos como abaixo?
{
{"tag_name": "tag1", "tag_count": 2},
{"tag_name": "tag2", "tag_count": 3}
}
Então, o que é melhor acima da estrutura json dos termos INSERT e RETRIEVAL da contagem de tendências?
Q4) Posso ir com existente{"key" : "value"}
formato em vez de{"key_label" : key, "value_lable" : "value"}
e possível extrair tendências ..? desde que eu estou pensando que{"key" : "value"}
é muito direto e bom no desempenho.
Q5) Ao recuperar, estou usandoSUBSTRING(time_id, 1, 6) AS month
. Será capaz de usar o índice?
OU preciso criar várias colunas comotime_hour(2018061023)
, time_day(20180610)
, time_month(201806)
e usar consulta em colunas específicas?
OU posso usarfunções de data e hora do mysql? isso usará o índice para recuperação mais rápida?
Por favor ajude.