select user_id,
       min(login_date) start_date,
       max(login_date) end_date
from (
         select user_id,
                login_date,
                date_sub(login_date, rn) flag
         from (
                  select user_id,
                         login_date,
                         row_number() over (partition by user_id order by login_date) rn
                  from (
                           select user_id,
                                  date_format(login_ts, 'yyyy-MM-dd') login_date
                           from user_login_detail
                           group by user_id, date_format(login_ts, 'yyyy-MM-dd')
                       ) t1
              ) t2
     ) t3
group by user_id, flag
having count(*) >= 2;

select create_date,
       sum(if(gender = '男', total_amount, 0)) total_amount_male,
       sum(if(gender = '女', total_amount, 0)) total_amount_female
from order_info oi
         left join
     user_info ui
     on oi.user_id = ui.user_id
group by create_date;

select
create_date,
      round( sum(total_amount_by_day) over (order by create_date rows between 2 preceding
           and current row ),2) total_3d,
       round(avg(total_amount_by_day) over(order by create_date rows between 2 preceding
           and current row ),2) avg_3d

       from
(select create_date,
       sum(total_amount) total_amount_by_day
from order_info2
group by create_date)t1

--由起点到当前行的开窗函数
sum(score) over(partition by subject order by score rows between unbounded preceding and current row)
--当前行和前面一行的窗口聚合
sum(score) over (partition by subject order by score rows between 1 preceding and 1 following)
--当前和后面所有行
sum(score) over (partition by subject order by score rows between current and unbounding following)
join列合并（行），union行合并（上下合并）