clickhouse with

with 定义变量

with pow(2, 3) as res
select pow(res, 2); -- 64

with 1 as num
select 2 + num; -- 3

with 调用函数

select uid,
       count(1) as cnt,
       sum(total_amount) as final_amount
from tb_orders_base
group by uid;


-- 将count函数、sum函数提到最前
with
    count(1) as cnt,
    sum(total_amount) as final_amount
select uid,
       cnt,
       final_amount
from tb_orders_base
group by uid;

with 子查询

with t1 as (
           select uid,
                  total_amount,
                  dt
           from tb_orders_base
           )
select uid,
       dt,
       sum(total_amount)
from t1
group by uid, dt;

with (
     select uid,
            ctime
     from tb_orders_base
     where ctime = '2024-03-13 21:29:24'
       and uid = '1'
     ) as a
select *
from tb_orders_base
where (uid, ctime) = a;