I have this table in PostgreSQL: with this data: I need to know how many users are going to visit my shop every day. A user store visited twice a day, it should be double-counted For example it should be up to
CREATE TABLE visit NULL not BIGSERIAL (ID not NULL pRIMARY KEY, slot TSRANGE not NULL, user_id integer reference users (ID), the barrier to exclude use overlapping_timeslots GIST (; & amp; =, with & amp user_id) with slot);
| ID | Time Lit | User_id | | 1 | 10.02.2014 10:00 - 10.02.2014 17:00 | 2 | | 2 | 10.02.2014 18:00 - 10.02.2014 19:00 | 2 | | 3 | 11.02.2014 01:00 - 11.02.2014 02:00 | 2 | | 4 | 10.02.2014 12:00 - 11.02.2014 17:00 | 2 | | 5 | 11.02.2014 12:00 - 11.02.2014 12:30 pm 2 |
10.02 user = 3 (ID: .. 1, 2,4) user 11:02 = 3 (ID: 3,4,5)
"as an arbitrary period of time for the lack of definition for every day" to:
.
Use LEFT JOIN
to include the day with 0 visits.
Comments
Post a Comment