Postgresql: Create a date sequence, use it in date range query, PostgreSQL

Postgresql: Create a date sequence, use it in date range query

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

create table y_unit_status(
  unit          int  not null,
  date_occurred date not null,
  start_date    date not null,
  end_date      date,
  status        text
);

insert into y_unit_status(unit, date_occurred, start_date, end_date, status)
values (1, '2017-01-01', '2017-01-01', '2017-01-05', 'Occupied No Notice'),
       (1, '2017-01-06', '2017-01-06', '2017-01-31', 'Occupied Notice'),
       (1, '2017-02-01', '2017-02-01', NULL,         'Vacant'),
       (2, '2017-01-01', '2017-01-01', NULL,         'Occupied No Notice');

with params as (
  select date '2017-01-01' date_from,
         date '2017-02-02' date_to
)
select     (date_from + d)::text, status, count(unit)
from       params
cross join generate_series(0, date_to - date_from) d
left join  y_unit_status on daterange(start_date, end_date, '[]') @> date_from + d
group by   1, 2;

with params as (
  select date '2017-01-01' date_from,
         date '2017-02-02' date_to
)
select     (date_from + d)::text,
           count(unit) filter (where status = 'Occupied No Notice') occupied_no_notice,
           count(unit) filter (where status = 'Occupied Notice') occupied_notice,
           count(unit) filter (where status = 'Vacant') vacant
from       params
cross join generate_series(0, date_to - date_from) d
left join  y_unit_status on daterange(start_date, end_date, '[]') @> date_from + d
group by   1;


absolute service time: 0,47 sec

	text	status	count
1	2017-01-01	Occupied No Notice	2
2	2017-01-02	Occupied No Notice	2
3	2017-01-03	Occupied No Notice	2
4	2017-01-04	Occupied No Notice	2
5	2017-01-05	Occupied No Notice	2
6	2017-01-06	Occupied No Notice	1
7	2017-01-06	Occupied Notice	1
8	2017-01-07	Occupied No Notice	1
9	2017-01-07	Occupied Notice	1
10	2017-01-08	Occupied No Notice	1
11	2017-01-08	Occupied Notice	1
12	2017-01-09	Occupied No Notice	1
13	2017-01-09	Occupied Notice	1
14	2017-01-10	Occupied No Notice	1
15	2017-01-10	Occupied Notice	1
16	2017-01-11	Occupied No Notice	1
17	2017-01-11	Occupied Notice	1
18	2017-01-12	Occupied No Notice	1
19	2017-01-12	Occupied Notice	1
20	2017-01-13	Occupied No Notice	1
21	2017-01-13	Occupied Notice	1
22	2017-01-14	Occupied No Notice	1
23	2017-01-14	Occupied Notice	1
24	2017-01-15	Occupied No Notice	1
25	2017-01-15	Occupied Notice	1
26	2017-01-16	Occupied No Notice	1
27	2017-01-16	Occupied Notice	1
28	2017-01-17	Occupied No Notice	1
29	2017-01-17	Occupied Notice	1
30	2017-01-18	Occupied No Notice	1
31	2017-01-18	Occupied Notice	1
32	2017-01-19	Occupied No Notice	1
33	2017-01-19	Occupied Notice	1
34	2017-01-20	Occupied No Notice	1
35	2017-01-20	Occupied Notice	1
36	2017-01-21	Occupied No Notice	1
37	2017-01-21	Occupied Notice	1
38	2017-01-22	Occupied No Notice	1
39	2017-01-22	Occupied Notice	1
40	2017-01-23	Occupied No Notice	1
41	2017-01-23	Occupied Notice	1
42	2017-01-24	Occupied No Notice	1
43	2017-01-24	Occupied Notice	1
44	2017-01-25	Occupied No Notice	1
45	2017-01-25	Occupied Notice	1
46	2017-01-26	Occupied No Notice	1
47	2017-01-26	Occupied Notice	1
48	2017-01-27	Occupied No Notice	1
49	2017-01-27	Occupied Notice	1
50	2017-01-28	Occupied No Notice	1
51	2017-01-28	Occupied Notice	1
52	2017-01-29	Occupied No Notice	1
53	2017-01-29	Occupied Notice	1
54	2017-01-30	Occupied No Notice	1
55	2017-01-30	Occupied Notice	1
56	2017-01-31	Occupied No Notice	1
57	2017-01-31	Occupied Notice	1
58	2017-02-01	Occupied No Notice	1
59	2017-02-01	Vacant	1
60	2017-02-02	Occupied No Notice	1
61	2017-02-02	Vacant	1

	text	occupied_no_notice	occupied_notice	vacant
1	2017-01-01	2	0	0
2	2017-01-02	2	0	0
3	2017-01-03	2	0	0
4	2017-01-04	2	0	0
5	2017-01-05	2	0	0
6	2017-01-06	1	1	0
7	2017-01-07	1	1	0
8	2017-01-08	1	1	0
9	2017-01-09	1	1	0
10	2017-01-10	1	1	0
11	2017-01-11	1	1	0
12	2017-01-12	1	1	0
13	2017-01-13	1	1	0
14	2017-01-14	1	1	0
15	2017-01-15	1	1	0
16	2017-01-16	1	1	0
17	2017-01-17	1	1	0
18	2017-01-18	1	1	0
19	2017-01-19	1	1	0
20	2017-01-20	1	1	0
21	2017-01-21	1	1	0
22	2017-01-22	1	1	0
23	2017-01-23	1	1	0
24	2017-01-24	1	1	0
25	2017-01-25	1	1	0
26	2017-01-26	1	1	0
27	2017-01-27	1	1	0
28	2017-01-28	1	1	0
29	2017-01-29	1	1	0
30	2017-01-30	1	1	0
31	2017-01-31	1	1	0
32	2017-02-01	1	0	1
33	2017-02-02	1	0	1