How to get count of one day and count of last three days in single query?

Question

It's a similar question like: How to get sum of one day and sum of last three days in single query?

Suppose I have a statistical table like this:

date | stats
-------------
10/1 | 2
10/1 | 3
10/1 | 2
10/2 | 1
10/3 | 3
10/3 | 2
10/4 | 1
10/4 | 1

What I want is three columns:

1. Date
2. count(distinct stats) of Date
3. count(distinct stats) of last three days before Date

So the only row in my expected result should be:

date | today | last three day
-----+-------+---------------
10/4 |   1   |      3

The difference between this question and the similar question I mentioned before is that we can not count distinct stats of the last three day by using sum(count(distinct stats)) over (...) since the same kind of stats appearing on different days would be counted multiple times.

What should I do to archive this?

Thanks!

Answer 1

I would be inclined to do this using a correlated subquery:

select t.date, count(distinct stats),
       (select count(distinct t2.stats)
        from t t2
        where t2.date >= t.date - interval '3 day' and
              t2.date < t.date
       )
from t
group by date;

Answer 2

I think you need another query to resolve it using for example a left outer join of the same table to archive it.

With your data and some more.

date | stats
-------------
10/1 | 2
10/1 | 3
10/1 | 2
10/2 | 1
10/3 | 3
10/3 | 2
10/4 | 1
10/4 | 1
10/7 | 2
10/8 | 3
10/9 | 2
10/10 | 4
10/10 | 3
10/10 | 2
10/11 | 1
10/12 | 4

I do it this query to get the example data:

SELECT  unnest(array[   '2015/10/1','2015/10/1','2015/10/1','2015/10/2','2015/10/3','2015/10/3','2015/10/4','2015/10/4',
                    '2015/10/7', '2015/10/8', '2015/10/9', '2015/10/10', '2015/10/10', '2015/10/10', '2015/10/11', '2015/10/12'])::date as date, 
            unnest(array[   2, 3, 2, 1, 3, 2, 1, 1,
                    2, 3, 2, 4, 3, 2, 1, 4]) as stats
    ) AS F

And now I do the query to obtain the data that you need:

SELECT  f.date, count(distinct f.stats), count(distinct x.stats)
    FROM    (
        SELECT  unnest(array[   '2015/10/1','2015/10/1','2015/10/1','2015/10/2','2015/10/3','2015/10/3','2015/10/4','2015/10/4',
                    '2015/10/7', '2015/10/8', '2015/10/9', '2015/10/10', '2015/10/10', '2015/10/10', '2015/10/11', '2015/10/12'])::date as date, 
            unnest(array[   2, 3, 2, 1, 3, 2, 1, 1,
                    2, 3, 2, 4, 3, 2, 1, 4]) as stats
    ) AS F
    LEFT OUTER JOIN (SELECT  unnest(array[  '2015/10/1','2015/10/1','2015/10/1','2015/10/2','2015/10/3','2015/10/3','2015/10/4','2015/10/4',
                    '2015/10/7', '2015/10/8', '2015/10/9', '2015/10/10', '2015/10/10', '2015/10/10', '2015/10/11', '2015/10/12'])::date as date, 
            unnest(array[   2, 3, 2, 1, 3, 2, 1, 1,
                    2, 3, 2, 4, 3, 2, 1, 4]) as stats) AS x
    ON x.date BETWEEN f.date - INTERVAL '3 DAYS' AND  f.date 
    GROUP BY f.date

Results:

date;today;last three day
    "2015-10-01";2;2
    "2015-10-02";1;3
    "2015-10-03";2;3
    "2015-10-04";1;3
    "2015-10-07";1;2
    "2015-10-08";1;2
    "2015-10-09";1;2
    "2015-10-10";3;3
    "2015-10-11";1;4
    "2015-10-12";1;4

I hope this solution will help.