Re: Measure Theoretic Data Types in Postgresql

On 11.10.2012 07:37, Aaron Sheldon wrote:
> This would allow for a succinct syntax to do calculations such as
> finding the daily unique patient count given the intervals of their
> attendance in particular programs; a computation I encounter
> routinely as a statistician for a health services provider.

Hmm. It's easy to get the count of unique patients on a particular date
with something like:

select count(distinct patient) from attendance where interval &&
'2012-10-12'::date

I guess what you're after is to get that count for a range of days, in
one query, so that the result looks something like this:

date | patients
-----------+------------
2012-10-05 | 20
2012-10-06 | 24
2012-10-07 | 30
2012-10-08 | 29

The way I think of that problem is that you need to join the dates
you're interested in with the attendance table.

select date, count (distinct patientid)
from attendance
inner join (
select '2012-10-04'::date + a AS date from generate_series(1,20) a
) dates on interval @> date
group by date;
date | count
------------+-------
2012-10-05 | 11
2012-10-06 | 27
2012-10-07 | 47
2012-10-08 | 63
2012-10-09 | 83
2012-10-10 | 95
2012-10-11 | 80
2012-10-12 | 60
2012-10-13 | 35
2012-10-14 | 13
(10 rows)

I created the test table for that with:

create table attendance (patientid int4 , interval daterange)
insert into attendance select id, daterange('2012-10-05'::date +
(random()*5)::int4, '2012-10-10'::date + (random()*5)::int4) from
generate_series(1,100) id;

So, I think the current range types already cover that use case pretty
well. I can't imagine how the proposed measure theoretic concepts would
make that simpler. Can you give some more complicated problem, perhaps,
that the proposed measure theoretic concepts would make simpler than the
current tools?

- Heikki