雪花中变焦场的统计测度
在雪花表中有一个名为MER_DATA的字段，其值如下所示：
43,44.25,44.5,42.75,44,44.25,42.75,43,42.5,42.5,36.75,42.25,42.75,43.25,43.25,43.25,42.75,43.5,42,43,43.75,43.75,43.25,41.75,43.25,42.5,43.25,42.75,43.25,43.5,43.25,43.25,43.75，...
每行中都有大约4k(这随行而异)数字，字段的数据类型为varchar(30000)。数据大约是700 k行。
现在，我想使用上面所示的列表中的数字来计算每一行的标准偏差。
我尝试使用以下查询在MySQL中执行此操作：
select mac, `timestamp`, std(res), min(res), max(res)
from
(select mac, `timestamp`, r.res from table cmr ,
json_table(mer_data, '$[*]' columns (res float path '$'))r)T
group by mac, `timestamp`;这给了我正确的结果，但700 K行需要很长时间。
我也想在雪花上做同样的事。有什么最佳的方法吗？
此外，查询需要在10分钟内在雪花中运行。mysql查询最多需要1小时。
回答 1
Stack Overflow用户
发布于 2022-01-25 11:33:11
如果没有表定义和示例源数据，就很难为您的问题提供一个完整的解决方案，但是下面是一个使用STRTOK_SPLIT_TO_TABLE表函数来实现这个问题的示例，该函数首先将您的varchar数字拆分成行，这样我们就可以重新聚合值，以获得每行的标准偏差。
首先，在适当的比例上生成一些测试数据：
Create or Replace Table cmr (mer_data varchar) as
With gen as (
select
uniform(1,700000, random()) row_num,
normal(50, 1, random(0))::decimal(4,2) num
from table(generator(rowcount => 2800000000)) v
)
Select listagg(num, ',') listNums from gen group by row_num
;检查一下，我们有700 K行，每行的数字都是不同的。
Select
count(*) row_count,
min(REGEXP_COUNT( mer_data , '[,]' ))+1 min_num_count,
max(REGEXP_COUNT( mer_data , '[,]' ))+1 max_num_count
from cmr limit 10;将varchar列表拆分为带有STRTOK_SPLIT_TO_TABLE的行，并按照生成的SEQ列进行分组，以计算值的stddev。
Select
seq row_num,
stddev(value) stdListNums,
min(value) minNum, max(value) maxNum,
count(value) countListNums
from cmr, table(STRTOK_SPLIT_TO_TABLE(mer_data ,','))
Group By 1
;对于我的数据，查询只需要3分钟和XSMALL虚拟仓库，在大型虚拟仓库上则需要30秒多一点。
您可以阅读有关STRTOK_SPLIT_TO_TABLE函数这里的内容。
https://stackoverflow.com/questions/70845074
复制相似问题
腾讯云开发者
