odbtools/odbctest/mql/basic/24bucket_promdb/1094.subquery.aggr.mql

SELECT id,prom.time('2025-01-01 09:09:09.090', '2025-01-03 19:19:19.191').find(name='test1').unit('sum','1d') FROM /test/bucketpromdb where id='bucketpromdb:wecisen1'
-- 测试数据保留 7 天，在类定义中 bucket.ttl=7
/**
output()
matchcount(id, "bucketpromdb:wecisen1", "prom.1.2", 2880, 1)
**/
;




select count(id),prom.count() from (
SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00').find(name='test1').unit('sum','1d') FROM /test/bucketpromdb
) group by prom
/**
timeout(60m)
output()
match(count, 3, "prom.0.2", 0)
match(count, 1, "prom.0.2", 8)
**/
;


select count(id),prom from (
SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00').find(name='test1').unit('sum','1d') FROM /test/bucketpromdb
) group by prom
/**
timeout(60m)
output()
todomatch(count, 3, prom, null)
match(count, 3)
match(count, 1, "prom.0.2", 2880, "prom.7.2", 2880)
**/
;

select x.id,x.prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00').find(name='test1').unit('sum','1d') from (
SELECT id,prom FROM /test/bucketpromdb
where id='bucketpromdb:wecisen1'
) x
/**
timeout(60m)
output()
match("x.prom.0.2", 2880, "x.prom.7.2", 2880)
**/
;



select count(id),prom.avg().max().sum().count().min() from (
SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00').find(name='test1').unit('sum','1d') FROM /test/bucketpromdb
) group by prom
/**
timeout(60m)
output()
match(count, 3, prom, null)
match(count, 1, "prom.0.2", 2880, "prom.0.8", 23040, "prom.0.11", 8)
**/
;



select count(*),count(prom) as promcount,prom.time('2025-01-03 00:00:00', '2025-01-08 00:00:00').find(name='test1').unit('sum','1d') 
FROM (
SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00') FROM /test/bucketpromdb
)
group by prom
/**
timeout(60m)
output()
match(count,1,promcount,1,"prom.len",5)
match(count,3,promcount,3,"prom.len",0)
**/
;


select a.id, a.prom
from
(
    select * from /test/bucketpromdb
) a
where a.id='bucketpromdb:wecisen1'
/**
timeout(60m)
output()
count(1)
match("a.id","bucketpromdb:wecisen1","a.prom.len",1,"a.prom.0.2",1)
**/
;




SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00').avg().groupby('name')
FROM /test/bucketpromdb
WHERE  prom.time('2025-01-03 00:00:00', '2025-01-08 00:00:00').avg().groupby('name').assert(avg=1)
/**
timeout(60m)
output()
match("prom.0.0","test1","prom.0.3",1)
**/
;

SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00').avg().groupby('name')
FROM /test/bucketpromdb
WHERE  prom.time('2025-01-03 00:00:00', '2025-01-08 00:00:00').avg().groupby('name').assert($1=1)
/**
timeout(60m)
output()
match("prom.0.0","test1","prom.0.3",1)
**/
;



select id,prom.avg().groupby(name) from(
    SELECT id,prom.time('2025-01-02 00:00:00', '2025-01-10 00:00:00')
    FROM /test/bucketpromdb
    WHERE  prom.time('2025-01-03 00:00:00', '2025-01-08 00:00:00').avg().groupby(name).assert($1=1)
)
/**
timeout(60m)
output()
match("prom.0.0","test1","prom.0.3",1)
**/
;