WITH -- 1. 统计今日订单量 today_orders AS ( SELECT shop_id, COUNT(*) AS today_cnt FROM orders WHERE order_date = CURRENT_DATE GROUP BY shop_id ), -- 2. 统计过去7天（不含今天）日均订单量 last7_orders AS ( SELECT shop_id, COUNT(*) / 7.0 AS avg_7_cnt FROM orders WHERE order_date >= date_sub(CURRENT_DATE, 7) AND order_date < CURRENT_DATE GROUP BY shop_id ) -- 3. 筛选今日订单量为过去7天均值2倍的商家 SELECT t.shop_id, t.today_cnt, l.avg_7_cnt FROM today_orders t JOIN last7_orders l ON t.shop_id = l.shop_id WHERE t.today_cnt >= 2 * l.avg_7_cnt ;