【clickhouse SQL语句03】

1、with

#语法

with  表达式  as name
select  x from xxx

with语法有三种形式：

• 定义变量
• 调用函数
• 查询

 形式1：定义变量  -->注意：这种是要给字段赋一个临时的值，所以select的时候必须查这个字段，如果查*输出的结果仍然是原数据的值

with 'hello' as sku_id
select sku_id,* from t_order_mt

 形式2：调用函数

with toYYYYMM(create_time) as dy 
select dy,* from t_order_mt limit 2;

-- 语句解析：用with调用函数toYYYYMM把create_time定义成年月

 实际工作中真实的sql

WITH 
    JSONExtract( data,  'event_raw_log', 'JSON') AS event_raw_log
    ,JSONExtract( event_raw_log,  'device_info', 'JSON') AS device_info
   ,JSONExtractString(device_info,'device_id') as device_id
SELECT device_id,* FROM target_event_attribution 
where device_id = 'b6d17958-06f5-4fc5-9097-d4b79a3dfb51' and appkey = '750bb7f034cd701b'
ORDER BY db_time desc 
limit 10 ;

形式3：执行查询  --->返回结果必须是一行，否则就报错

语法：with ()  as  xx

1）全局聚合  sum  count  avg

with (select count(1) from t_order_mt) as cnt 
select cnt,* from t_order_mt limit 2;

2、array join 函数获取每一行并将他们展开到多行（unfold）

• 获取数组的角标

SELECT 
    id,
    name,
    bobby,
    arrayEnumerate(bobby) as idx --获取数组的角标
from 
    tb_arr_join

 针对上面的结果，我们想要输出bobby里面的每一个数及对应得到index

• 升级语句

SELECT 
    id,
    name,
    bobby,
    arrayEnumerate(bobby) as idx, --获取数组的角标
    h,
    i
from 
    tb_arr_join
array join --拆分数组
    bobby as h,
    idx as i

 只想要数组的元素和它的角标

SELECT 
    id,
    name,
    h,
    idx
from 
    tb_arr_join
array join
    --拆分数组
    bobby as h,
    arrayEnumerate(bobby) as idx

 # 案例：

需求：

1. 将同一个店铺中的时间收集到数组中，按照日期排序
2. array join
3. 日期 - 编号
4. 分组 聚合

步骤：

1、创建一个shop.txt文件，放到clickhouse 的服务器下的/root/data路径下

a,2017-02-05,200
a,2017-02-06,300
a,2017-02-07,200
a,2017-02-08,200
b,2017-02-05,200
b,2017-02-06,300
b,2017-02-09,400
b,2017-02-08,200
c,2017-01-31,200
c,2017-02-01,300
c,2017-02-03,400
c,2017-02-10,600
a,2017-03-01,200
a,2017-03-02,300
a,2017-03-04,400
a,2017-03-05,600

2、创建shop表

CREATE table shop(
    name String,
    cdate Date,
    money Float64
)engine=MergeTree()
order by (name,cdate);

3、用clickhouse-client导入shop.txt文件数据

[root@localhost data]# clickhouse-client --host localhost --user default --port 9000 --password 123456 -q 'insert into shop FORMAT CSV' < '/root/data/shop.txt'

4、查看数据是否分组

select * from shop

截止到这里，我们需求的第一步的数据已经有了

1）将同一个店铺中的时间收集到数组中，按照日期排序

SELECT name,groupArray(cdate) as dates ,arrayEnumerate(dates) as idxs from shop group by name;

2）array join

SELECT name , dy,idx from (
        -- groupArray  合并成数组，arrayEnumerate 获取数组角标
        SELECT name,groupArray(cdate) as dates ,arrayEnumerate(dates) as idxs from shop group by name
)  
array join 
    dates as dy,
    idxs as idx
order by dy asc;

 3) 日期减编号

-- groupArray  合并成数组，arrayEnumerate 获取数组角标,subtractDays 求日期差值
SELECT name , dy,idx,subtractDays(dy,idx) sub    from (
        
        SELECT name,groupArray(cdate) as dates ,arrayEnumerate(dates) as idxs from shop group by name
)  
array join 
    dates as dy,
    idxs as idx
order by dy asc;

 4）分组 聚合

SELECT 
name,sub,COUNT(1) cnt
from 
(
    SELECT name , dy,idx,subtractDays(dy,idx) sub    from (
            SELECT name,groupArray(cdate) as dates ,arrayEnumerate(dates) as idxs from shop group by name
    )  
    array join 
        dates as dy,
        idxs as idx
    order by dy asc
)
group by (name,sub)
HAVING cnt >1
order by name,cnt DESC 
limit 1 by name; -- 只取一个name