作者:卫通达邱小洁GPS | 来源:互联网 | 2022-12-17 17:08
我正在尝试使用mysql json功能识别时间序列中的趋势标签(基于最大命中).下面是我的表
CREATE TABLE TAG_COUNTER (
account varchar(36) NOT NULL,
time_id INT NOT NULL,
counters JSON,
PRIMARY KEY (account, time_id)
)
在每个web 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,它是每行的每小时聚合.
现在我的问题是treding标签的回溯.下面的查询将为我提供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)如果我必须采用这种方式,那么对于这个新的json标签/值struture,我的INSERT ON DUPLICATE KEY UPDATE查询的变化是什么?由于计数器必须在不存在时创建,并且在存在时应增加1.
Q3)我是否必须维护对象数组
[
{"tag_name": "tag1", "tag_count": 2},
{"tag_name": "tag2", "tag_count": 3}
]
OR对象如下所示?
{
{"tag_name": "tag1", "tag_count": 2},
{"tag_name": "tag2", "tag_count": 3}
}
那么在趋势计数的INSERT和RETRIEVAL的json结构中间哪个更好?
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日期时间函数吗?那会使用索引来加快检索速度吗?
请帮忙.