10

I have a table (in PostgreSQL 9.4) that looks like this:

CREATE TABLE dates_ranges (kind int, start_date date, end_date date);
INSERT INTO dates_ranges VALUES 
    (1, '2018-01-01', '2018-01-31'),
    (1, '2018-01-01', '2018-01-05'),
    (1, '2018-01-03', '2018-01-06'),
    (2, '2018-01-01', '2018-01-01'),
    (2, '2018-01-01', '2018-01-02'),
    (3, '2018-01-02', '2018-01-08'),
    (3, '2018-01-05', '2018-01-10');

Now I want to calculate for the given dates and for every kind, into how many rows from dates_ranges each date falls. Zeros could be possibly omitted.

Desired result:

+-------+------------+----+
|  kind | as_of_date |  n |
+-------+------------+----+
|     1 | 2018-01-01 |  2 |
|     1 | 2018-01-02 |  2 |
|     1 | 2018-01-03 |  3 |
|     2 | 2018-01-01 |  2 |
|     2 | 2018-01-02 |  1 |
|     3 | 2018-01-02 |  1 |
|     3 | 2018-01-03 |  1 |
+-------+------------+----+

I've come up with two solutions, one with LEFT JOIN and GROUP BY

SELECT
kind, as_of_date, COUNT(*) n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates
LEFT JOIN
    dates_ranges ON dates.as_of_date BETWEEN start_date AND end_date
GROUP BY 1,2 ORDER BY 1,2

and one with LATERAL, which is slightly faster:

SELECT
    kind, as_of_date, n
FROM
    (SELECT d::date AS as_of_date FROM generate_series('2018-01-01'::timestamp, '2018-01-03'::timestamp, '1 day') d) dates,
LATERAL
    (SELECT kind, COUNT(*) AS n FROM dates_ranges WHERE dates.as_of_date BETWEEN start_date AND end_date GROUP BY kind) ss
ORDER BY kind, as_of_date

I'm wondering is it any better way to write this query? And how to include pairs date-kind with 0 count?

In reality there is a few distinct kinds, period of up to five years (1800 dates), and ~30k rows in dates_ranges table (but it could grow significantly).

There are no indexes. To be precise in my case it's a result of subquery, but I've wanted to limit question to one issue, so it's more general.

  • What do you do if you the ranges in the table are non-overlapping or touching. For instance if you have a range where (kind,start,end) = (1,2018-01-01,2018-01-15) and (1,2018-01-20,2018-01-25) do you want to take that into account when determining how many overlapping dates you have? – Evan Carroll May 30 '18 at 17:32
  • I'm also confused why your table is small? Why isn't 2018-01-31 or 2018-01-30 or 2018-01-29 in it when the first range has all of them? – Evan Carroll May 30 '18 at 17:33
  • @EvanCarroll dates in generate_series are external parameters - they don't necessarily cover all ranges in dates_ranges table. As for the first question I suppose I don't understand it - rows in dates_ranges are independent, I don't want to determine overlaping. – BartekCh Jun 1 '18 at 7:47
2

The following query also works if "missing zeros" are OK:

select *
from (
  select
    kind,
    generate_series(start_date, end_date, interval '1 day')::date as d,
    count(*)
  from dates_ranges
  group by 1, 2
) x
where d between date '2018-01-01' and date '2018-01-03'
order by 1, 2;

but it's not any faster than the lateral version with the small dataset. It might scale better though, as no join is required, but the above version aggregates over all the rows, so it may lose out there again.

The following query tries to avoid unnecessary work by removing any series that don't overlap anyway:

select
  kind,
  generate_series(greatest(start_date, date '2018-01-01'), least(end_date, date '2018-01-03'), interval '1 day')::date as d,
  count(*)
from dates_ranges
where (start_date, end_date + interval '1 day') overlaps (date '2018-01-01', date '2018-01-03' + interval '1 day')
group by 1, 2
order by 1, 2;

-- and I got to use the overlaps operator! Note that you have to add interval '1 day' to the right as the overlaps operator considers time periods to be open on the right (which is fairly logical because a date is often considered to be a timestamp with time component of midnight).

  • Nice, I didn't know generate_series can be used like that. After a few tests I have following observations. Your query indeed scales really well with the selected range length - theret is practically no difference between 3 years and 10 years period. However for shorter periods (1 year) my solutions are faster - I'm guessing that the reason is that there are some really long ranges in dates_ranges (like 2010-2100), that are slowing down Your query. Limiting start_date and end_date inside the inner query should help though. I need to make a few more tests. – BartekCh May 30 '18 at 15:24
5

And how to include pairs date-kind with 0 count?

Build a grid of all combinations then LATERAL join to your table, like this:

SELECT k.kind, d.as_of_date, c.n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS  JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
CROSS  JOIN LATERAL (
   SELECT count(*)::int AS n
   FROM   dates_ranges
   WHERE  kind = k.kind
   AND    d.as_of_date BETWEEN start_date AND end_date
   ) c
ORDER  BY k.kind, d.as_of_date;

Should also be as fast as possible.

I had LEFT JOIN LATERAL ... on true at first, but there is an aggregate in the subquery c, so we always get a row and can use CROSS JOIN as well. No difference in performance.

If you have a table holding all relevant kinds, use that instead of generating the list with subquery k.

The cast to integer is optional. Else you get bigint.

Indexes would help, especially a multicolumn index on (kind, start_date, end_date). Since you are building on a subquery, this may or may not be possible to achieve.

Using set-returning functions like generate_series() in the SELECT list is generally not advisable in Postgres versions before 10 (unless you know exactly what you are doing). See:

If you have lots of combinations with few or no rows, this equivalent form may be faster:

SELECT k.kind, d.as_of_date, count(dr.kind)::int AS n
FROM  (SELECT DISTINCT kind FROM dates_ranges) k
CROSS JOIN (
   SELECT d::date AS as_of_date
   FROM   generate_series(timestamp '2018-01-01', timestamp '2018-01-03', interval '1 day') d
   ) d
LEFT   JOIN dates_ranges dr ON dr.kind = k.kind
                           AND d.as_of_date BETWEEN dr.start_date AND dr.end_date
GROUP  BY 1, 2
ORDER  BY 1, 2;
  • As for set-returning functions in the SELECT list - I've read that it is not advisable, however it looks like it works just fine, if there is only one such function. If I am sure that there will be only one, could something go wrong? – BartekCh Jun 1 '18 at 8:00
  • @BartekCh: A single SRF in the SELECT list works as expected. Maybe add a comment to warn against adding another one. Or move it to the FROM list to begin with in older versions of Postgres. Why risk complications? (That's also standard SQL and won't confuse people coming from other RDBMS.) – Erwin Brandstetter Jun 4 '18 at 15:26
1

Using the daterange type

PostgreSQL has a daterange. Using it is pretty simple. Starting with your sample data we move to use the type on the table.

BEGIN;
  ALTER TABLE dates_ranges ADD COLUMN myrange daterange;
  UPDATE dates_ranges
    SET myrange = daterange(start_date, end_date, '[]');
  ALTER TABLE dates_ranges
    DROP COLUMN start_date,
    DROP COLUMN end_date;
COMMIT;

-- Now you can create GIST index on it...
CREATE INDEX ON dates_ranges USING gist (myrange);

TABLE dates_ranges;
 kind |         myrange         
------+-------------------------
    1 | [2018-01-01,2018-02-01)
    1 | [2018-01-01,2018-01-06)
    1 | [2018-01-03,2018-01-07)
    2 | [2018-01-01,2018-01-02)
    2 | [2018-01-01,2018-01-03)
    3 | [2018-01-02,2018-01-09)
    3 | [2018-01-05,2018-01-11)
(7 rows)

I want to calculate for the given dates and for every kind, into how many rows from dates_ranges each date falls.

Now to query it we reverse the procedure, and generate a date series but here's the catch the query itself can use the containment (@>) operator to check that the dates are in range, using an index.

Note we use timestamp without time zone (to stop DST hazards)

SELECT d1.kind, day::date, count(d2.kind)
FROM dates_ranges AS d1
CROSS JOIN LATERAL generate_series(
  lower(myrange)::timestamp without time zone,
  upper(myrange)::timestamp without time zone,
  '1 day'
) AS gs(day)
INNER JOIN dates_ranges AS d2
  ON d2.myrange @> day::date
GROUP BY d1.kind, day;

Which is the itemized day-overlaps on the index.

As a side bonus, with the daterange type you can stop insertions of ranges that overlap with others using an EXCLUDE CONSTRAINT

  • Something is wrong with Your query, it looks like it is counting rows multiple times, one JOIN too much I guess. – BartekCh Jun 1 '18 at 16:44
  • @BartekCh no you have overlapping rows, you can get around this by removing the overlapping ranges (suggested) or using count(DISTINCT kind) – Evan Carroll Jun 1 '18 at 17:33
  • but I want overlapping rows. For example for kind 1 date 2018-01-01 is within first two rows from dates_ranges, but Your query gives 8. – BartekCh Jun 1 '18 at 18:59
  • or using count(DISTINCT kind) did you add the DISTINCT keyword there? – Evan Carroll Jun 1 '18 at 19:10
  • Unfortunately with DISTINCT keyword it still doesn't work as expected. It counts distinct kinds for every date, but I want to count all rows of each kind for every date. – BartekCh Jun 1 '18 at 20:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.