1求总户数
1 一个房间可能被不同天数登记多次,但我们只需要只有房间人大于1我们就算1次
2 这时候如果按天分组会导致这个房间会被重复算
3 博主想到一个办法:我们对这个房间注册的进行排序然后只拿时间较前的第一个这样就可以房间就只能算1了
SELECT a.court_id,
sum(if(a.id_count >= 1, 1, 0)) house_count,
a.day_time
from (
select
w.court_id,
w.house_no,
count(1) id_count,
w.day_time
from
(
select
n.court_id,
n.house_no,
row_number() over ( PARTITION BY n.court_id,n.house_no ORDER BY n.day_time ) rn,
n.day_time
from
(
SELECT r.court_id,
h.house_no,
r.id,
concat(substr(r.create_time, 1, 4), substr(r.create_time, 6, 2),
substr(r.create_time, 9, 2)) day_time
FROM dwd_smart_community.dwd_house_info h
JOIN dwd_smart_community.dwd_person_resident_info r
ON h.court_id = r.court_id
AND h.building_no = r.building_no
AND h.unit_no = r.unit_no
AND h.house_no = r.house_no
) n
where n.id is not null
)w
where w.rn = 1
GROUP BY w.court_id,
w.house_no,
w.day_time
) a
GROUP BY a.court_id,
a.day_time