id | registered |
1 | 2021-08-01 01:00:00 |
2 | 2021-08-02 01:00:00 |
3 | 2021-08-03 01:00:00 |
4 | 2021-08-04 01:00:00 |
- DeviceTable
id | deviceId | timestamp |
1 | 1 | 2021-08-01 01:00:00 |
2 | 1 | 2021-08-02 01:00:00 |
3 | 1 | 2021-08-04 01:00:00 |
4 | 2 | 2021-08-03 01:00:00 |
5 | 2 | 2021-08-03 04:00:00 |
6 | 4 | 2021-08-06 04:00:00 |
7 | 4 | 2021-08-07 04:00:00 |
8 | 3 | 2021-08-04 10:00:00 |
- AHistory
id | deviceId | timestamp |
1 | 2 | 2021-08-02 02:00:00 |
2 | 2 | 2021-08-08 04:00:00 |
3 | 4 | 2021-08-06 04:00:00 |
4 | 4 | 2021-08-07 04:00:00 |
- BHistory
id | deviceId | timestamp |
1 | 1 | 2021-08-06 04:00:00 |
2 | 2 | 2021-08-06 04:00:00 |
3 | 3 | 2021-08-08 04:00:00 |
4 | 4 | 2021-08-07 04:00:00 |
- CHistory
현재 테이블이 Device에 관한 테이블이 하나 있고 이력에 관한 테이블이 3개가 존재합니다.
기간으로 검색된 Device들의 처음 이력과 마지막 이력을 찾고 싶어서 아래처럼 쿼리를 만들어 보았습니다.
SELECT id, registered, MIN(timestamp) first, MAX(timestamp) last FROM ( SELECT id, registered, b.timestamp FROM DeviceTable a LEFT OUTER JOIN ( SELECT deviceId, timestamp FROM AHistory UNION ALL SELECT deviceId, timestamp FROM BHistory UNION ALL SELECT deviceId, timestamp FROM CHistory ) b ON a.id = b.deviceId WHERE registered BETWEEN '2021-08-02 00:00:00' AND '2021-08-03 23:59:59' ) subQuery GROUP BY id;
하지만 이력테이블을 union 부터 하는것이 좋은 방법은 아닌것 같은데 어떻게 튜닝을 해야할지 모르겠습니다.
좀 더 좋은 방법이 없을지 조언 부탁드립니다.