数据准备

    1. a,2017-02-05,200
    2. a,2017-02-06,300
    3. a,2017-02-07,200
    4. a,2017-02-08,400
    5. a,2017-02-10,600
    6. b,2017-02-05,200
    7. b,2017-02-06,300
    8. b,2017-02-08,200
    9. b,2017-02-09,400
    10. b,2017-02-10,600
    11. c,2017-01-31,200
    12. c,2017-02-01,300
    13. c,2017-02-02,200
    14. c,2017-02-03,400
    15. c,2017-02-10,600
    16. a,2017-03-01,200
    17. a,2017-03-02,300
    18. a,2017-03-03,200
    19. a,2017-03-04,400
    20. a,2017-03-05,600

    求连续N天的销售记录
    一 先建一个 ClickHouse 表

    1. create table  
    2. tb_shop(name String , ctime Date ,money Float64) 
    3. engine=MergeTree   --->  引擎
    4. primary key (name,ctime)  ----> 主键 
    5. order by (name,ctime) ;  ---->  分组

    二 导入数据

    1. cat  shop.csv | clickhouse-client -q "insert into  tb_shop  FORMAT  CSV"
    2. 把shop.csv 下的数据 导入到 tb_shop 表中 --> CSV 数据格式    
    4. select  * from tb_shop； 查看表数据

    三 把数据转化成数组

    1. select
    2. name,
    3. groupArray(ctime)  --->  时间进行排序 转化成数组
    4. from
    5. tb_shop
    6. group by name ;

    四 把数组进行索引编号排序

    1. select
    2. name ,
    3. groupArray(ctime) arr ,    ---->    时间进行排序
    4. arrayEnumerate(arr) arr_index  ---->  求出数组的索引进行编号排序
    5. from
    6. tb_shop 
    7. group by name ；

    五 把数组和后面的索引值进行拼接 使用 array join 方法

    弄了一个子查询

    1. select
    2. name ,
    3. ct ,
    4. idx
    5. from
    6. (
    7.   select
    8.   name ,
    9.   groupArray(ctime) arr ,
    10.   arrayEnumerate(arr) arr_index
    11.   from
    12.   tb_shop 
    13.   group by name)
    14. array join    ---->  把数组和索引进行拼接
    15. arr as ct ,
    16. arr_index as idx 
    17. order by name   ----> 按名字进行排序
    18. ;

    六 查看相同天数的连续的登录天数

    1. select
    2. name ,
    3. ct ,
    4. idx ,
    5. subtractDays(ct , idx) as diff  ---> 时间减去索引的个数
    6. from
    7. (
    8.   select
    9.   name ,
    10.   groupArray(ctime) arr ,
    11.   arrayEnumerate(arr) arr_index
    12.   from
    13.   tb_shop 
    14.   group by name)
    15. array join 
    16. arr as ct ,
    17. arr_index as idx 
    18. order by name 
    19. ;

    七 求出最终结果 连续N天的销售记录

    1. select
    2. name , 
    3. count(1)  days   ---> 登录天数的总和
    4. from
    5. (select
    6.  name ,
    7.  ct ,
    8.  idx ,
    9.  subtractDays(ct , idx) as diff
    10.  from
    11.  (
    12.    select
    13.    name ,
    14.    groupArray(ctime) arr ,
    15.    arrayEnumerate(arr) arr_index
    16.    from
    17.    tb_shop 
    18.    group by name)t1
    19.  array join 
    20.  arr as ct ,
    21.  arr_index as idx 
    22.  order by name)t2
    23. group  by  name , diff   ---->  name 和 diff  进行分组
    24. having  days >3      ---->   天数大于3天
    25. order by  name , days desc    ----->  name 和days  进行降序排序
    26. limit 1  by name ;   ----->  名字进行分组后取第一个数据