With 子句

当子查询在WITH段中使用时，它的结果应该是一行标量
WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
EventDate = toDate(ts_upper_bound) AND
EventTime <= ts_upper_bound;
WITH ['hello'] AS hello
SELECT
hello,
*
FROM
(
WITH ['hello'] AS hello
SELECT hello
);

例：行列转换
create table t_row_column
(usrid String, productlst String,costlst String) engine = Log;
insert into t_row_column
(usrid, productlst, costlst)
values ('user_1', 'p_1,p_2,p_5', '234,651,12'),
('user_2', 'p_1,p_5', '234, 24');
select * from t_row_column;
-------------------- 一行转多行 ------------------------
--使用字符串拆分合并函数(splitByChar arrayStringConcat)
select usrid,
product,
cost
from
(select usrid,
splitByChar(',', productlst) as p_list,
splitByChar(',', costlst) as c_list
from t_row_column
)
array join p_list as product, c_list as cost;
-------------------- 多行转一行 --------------------
select userid,
arrayStringConcat(groupArray(cost), '|')
from
(select 'a' as userid,
toString(arrayJoin([1,2,4])) as cost
)
group by userid;

例：连续值出现的次数
create table test(uid UInt8,post_time DateTime,content String) engine =Log;
insert into test(uid, post_time, content)
values
(1,'2019-03-01 00:00:00','a'),
(2,'2019-03-01 00:00:01','b'),
(3,'2019-03-01 00:00:02','c'),
(3,'2019-03-01 00:00:03','d'),
(3,'2019-03-01 00:00:04','e'),
(2,'2019-03-01 00:00:05','f'),
(2,'2019-03-01 00:00:06','g'),
(1,'2019-03-01 00:00:07','h'),
(4,'2019-03-01 00:00:08','i'),
(4,'2019-03-01 00:00:09','j'),
(4,'2019-03-01 00:00:10','k'),
(5,'2019-03-01 00:00:11','l');
-- 使用 range(返回从0到N-1的数字数组) 和 arrayMap 来查找数组的位置
select aa.uid, aa.post_time, aa.contents, aa.rank,bb.rownum
from
(select uid, post_time, contents, rank
from
(select ttx.uid ,
count() as total,
arrayMap(x -> x +1, range(total)) as rnk_list,
arraySort(groupArray(ttx.post_time)) as post_time_list,
groupArray(ttx.content) as content_list
from test ttx
group by ttx.uid
) array join rnk_list as rank, post_time_list as post_time, content_list as contents
) as aa
all inner join
(select *, rowNumberInBlock() + 1 as rownum
from (select * from test ttx order by ttx.post_time asc)) bb on aa.uid = bb.uid and aa.post_time = bb.post_time

例：分组TopN
create table t_group_top_n
(
employ_id String,
dep_id String,
sal UInt64
) engine = Log;
insert into t_group_top_n
values ('e_1', 'd_1', 123),
('e_1', 'd_1', 223),
('e_3', 'd_1', 89),
('e_4', 'd_2', 123),
('e_5', 'd_2', 123);
select *
from t_group_top_n tgtn
order by tgtn.dep_id, tgtn.sal desc
limit 1 by tgtn.dep_id

例：分组排名
select
day_list,
three_category_code,
sku,
account,
country,
asin,
company_sku,
amount,
rnk
from
(
select day_list,three_category_code,sku,account,country,asin,company_sku,groupArray(tmp1.amount) as total_rt_cost_lst,arrayEnumerate(total_rt_cost_lst) as rank
from
(
select
toYYYYMM(purchase_date) as day_list,three_category_code,sku,account,country,asin,company_sku,amount
from
ptx_db.or_xc_order_item_new
where
sku = 'PXMPBH088AD-CAAA3'
and order_status != 'Canceled'
and toYYYYMM(purchase_date)= 201907
group by day_list,three_category_code,sku,account,country,asin,company_sku,amount
order by amount desc ) tmp1
group by day_list,three_category_code,sku,account,country,asin,company_sku ) tmp2
array join total_rt_cost_lst as amount,rank as rnk

例：求两个相邻元素之间的差值
SELECT
k,
arrayDifference(groupArray(v)),
groupArray(v)
FROM
(
SELECT
t.1 AS k,
t.2 AS v
FROM
(
SELECT arrayJoin([('a', 1), ('a', 2), ('b', 1), ('a', 3), ('b', 2), ('b', 100)]) AS t
)
)
GROUP BY k

例：日 周 月 销售额预估统计
with (select groupArray(local_at) from (select local_at from or_xc_asinsku_report where local_at>='2019-06-01' group by local_at order by local_at desc)) as arr,
arrayJoin(arr) as arr_days ,subtractDays(arr_days,1) as arr_days_yesterday,subtractDays(arr_days,7) as arr_days_week,subtractDays(arr_days,30) as arr_days_month
--select arr_days,arr_days_yesterday,arr_days_week,arr_days_month
SELECT
asin,
shop_sku,
country,
account,
arr_days,
arr_days_yesterday,
arr_days_week,
arr_days_month,
sum(re.sales) as sales_days,
sumIf(re.sales, re.local_at>=arr_days_week and re.local_at<=arr_days_yesterday) as sales_days_7, sumIf(re.sales, re.local_at>=arr_days_month and re.local_at<=arr_days_yesterday) as sales_days_30,
(sales_days0.3+sales_days_70.5+sales_days_30*0.2) as Estimate_sales
FROM (select asin,shop_sku,country,account,local_at,sales from or_xc_asinsku_report where sales!=0 order by local_at desc ) re
where account='TY' AND country='CA' and shop_sku='TYMPHP055AG-CAAS2' and asin='B075FGMQ35' and arr_days='2019-12-19'
group by
asin,
shop_sku,
country,
account,
arr_days