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
