I have 2 tables.
loc_hour:
| id | geohash | log_date | hour |
|---|---|---|---|
| 664baa8cdfc87dde | sybewp | 20210615 | 12 |
| 6723eda90ac3fcb | sws101 | 20211219 | 19 |
| 750361de905a1507 | sxk9db | 20210108 | 4 |
| 806f9a6b9363bf38 | sxr4xt | 20210507 | 9 |
loc_grid:
| id | geohash | gridtype |
|---|---|---|
| db3da68153508522 | sxk9vb | Home |
| ffffa0804d88 | sy9c0k | Work |
| 7baa782dbfd93d11 | swtf4d | Other |
These tables contain thousands of different devices.
The loc_hour table contains current location information from devices every hour for a whole year.
In the loc_grid table, there are attributes of the locations where the devices are located. (Home: indicates that the device is at home. Work: indicates that the device is at home.)
What I'm trying to do is calculate how long the devices spend in their home, work or other place as a percentage of the long term (3 months) and short term (1 month).
Desired outputs:
| id | home_percent | work_percent | other_percent | total_day_count | total_hour_count | period |
|---|---|---|---|---|---|---|
| a | 40 | 40 | 20 | 5 | 5 | last 1 month |
| b | 50 | 30 | 20 | 10 | 8 | last 1 month |
| c | 70 | 20 | 10 | 9 | 7 | last 1 month |
| ... | ... | ... | ... | ... | ... | ... |
| id | home_percent | work_percent | other_percent | total_day_count | total_hour_count | period |
|---|---|---|---|---|---|---|
| d | 40 | 40 | 20 | 5 | 5 | last 3 month |
| k | 50 | 30 | 20 | 10 | 8 | last 3 month |
| s | 70 | 20 | 10 | 9 | 7 | last 3 month |
| ... | ... | ... | ... | ... | ... | ... |