###问题01  按照ID分类,按照日期排序号
SELECT   c_id, ROW_NUMBER() OVER ( PARTITION BY k.c_id ORDER BY k.c_id, k.order_date ) sn ,
                        k.order_date
               FROM     order_info k
               WHERE    order_date BETWEEN '20170101' AND '20171231'
               GROUP BY k.c_id ,
                        order_date;
 ####练习                       
Select c_id ,row_number() over(partition by t.c_id order by t.order_date) 行序号,
       t.order_date
From   order_info t
WHERE      order_date BETWEEN "20170101" AND "20171231"
GROUP BY   t.c_id,t.order_date; 
############################
###问题02  连续7天及以上无订单的用户ID,对应起止时间,持续天数                       
with ks 
        AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY k.c_id ORDER BY k.c_id, k.order_date ) sn ,
                        k.c_id ,
                        k.order_date
               FROM     order_info k
               WHERE    order_date BETWEEN '20170101' AND '20171231'
               GROUP BY k.c_id ,
                        order_date
             )
         #两条序号相邻订单日期差值大于7
    SELECT  a.c_id ,
            a.order_date + 1 ksrq ,
            b.order_date - 1 jsrq ,
            timestampdiff(day, a.order_date, b.order_date)-1  ts
    FROM    ks a ,
            ks b
    WHERE   a.c_id = b.c_id
            AND b.sn =a.sn + 1
            AND timestampdiff(day, a.order_date, b.order_date) > 7;
            
            
##问题03,连续7天及以上有订单的用户ID,对应起止时间,持续天数
WITH  ks
         #按人分组查询订单日期和序号
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY k.c_id ORDER BY k.c_id, k.order_date ) sn ,
                        k.c_id ,
                        k.order_date
               FROM     order_info k
               WHERE    order_date BETWEEN '20170101' AND '20171231'
               GROUP BY k.c_id,
                        order_date
             ),
	  tj
          #计算订单日期和其序号差值，差值相同的订单日期连续
          AS ( SELECT   c_id ,
                        order_date,
                        order_date - sn diff ,
                        sn
               FROM     ks
             )
    SELECT  c_id  ,
            diff ,
            MIN(order_date) ksrq ,
            MAX(order_date) jsrq ,
            COUNT(1) ts   #每出现一次1,连续1天
    FROM    tj
    GROUP BY c_id ,
             diff
    HAVING  COUNT(1) > 7
    ORDER BY c_id ,
            diff;