SELECT

CITY_ID 城市 ,

count( DISTINCT ( USER_ID ) ) CITY_ID 最近7天内下过单的用户数

FROM

ORDER

WHERE

DATE_SUB( CURDATE( ), INTERVAL 6 DAY ) <= DATE( CREATE TIME )

GROUP BY

CITY_ID A

LEFT JOIN (

SELECT

COUNT( CASE WHEN DATE_SUB( CURDATE( ), INTERVAL 6 DAY ) <= DATE( G.IT ) THEN USER_ID END ) 首次下单时间是最近七天内的用户数,

G.IT,

G.CITY_ID

FROM

( SELECT USER_ID, CITY_ID, min( CREATE time ) IT FROM ORDER GROUP BY USER_ID ) G

GROUP BY

CITY_ID

) B ON A.CITY_ID = B.CITY_ID