回答:SELECT dt_id , year_mon , city_show_nm , day_cust_cnt_600 , mon_avg_cust_cnt_600 , mon_stddev_600 --满足less_flag='y'且9天连续或者连续大于9天中的第九天设为y不满足则为N ,CASE WHEN less_flag='Y' AND row_number()OVER(PARTITION BY city_show_nm,year_mon,new_date ORDER BY dt_id )=5 THEN 'Y' ELSE 'N' END AS is_low_avg FROM( SELECT dt_id , year_mon , city_show_nm , day_cust_cnt_600 , mon_avg_cust_cnt_600 , mon_stddev_600 , less_flag --满足第二个需求,若为连续,则日期减去排序为固定常数 , dt_id - cast(row_number()OVER(PARTITION BY city_show_nm,year_mon,less_flag ORDER BY dt_id) AS INT) new_date FROM warn_flag