-- ============================================================
-- 子查询+聚合测试
-- ============================================================

-- 1. 子查询中用聚合函数
select * from /test/aaa/test_subquery where s_value > (select avg(s_value) from /test/aaa/test_subquery)
/**
output()
**/
;

-- 2. 子查询中用 count
select * from /test/aaa/test_subquery where s_type in (select s_type from /test/aaa/test_subquery group by s_type having count(*) > 1)
/**
output()
match("subq_001")
match("subq_002")
match("subq_003")
match("subq_004")
**/
;

-- 3. 子查询 group by
select s_type, count(*) from /test/aaa/test_subquery where s_value > (select min(s_value) from /test/aaa/test_subquery where s_type='type_x') group by s_type
/**
output()
**/
;

-- 4. 子查询嵌套的聚合
select max(s_value) from /test/aaa/test_subquery where s_type in (select s_type from /test/aaa/test_subquery)
/**
output()
**/
;