如何在一次查询中得到一天的计数和最后三天的计数？

Question

这是一个类似的问题：How to get sum of one day and sum of last three days in single query?

假设我有这样一个统计表：

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

我想要的是三栏：

1. 日期
2. 日期计数(不同的统计数据)
3. 统计日期前三天的(不同的数据)

因此，我所期望的结果中唯一的一行应该是：

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

这个问题和我前面提到的类似问题的不同之处在于，我们不能用sum(计数(不同统计))在(.)上计算过去三天的不同统计量。由于同一类型的统计数据出现在不同的日子将被计数多次。

我该怎么做才能把这个存档？

谢谢!

Answer 1

我认为您需要另一个查询来解析它，例如，使用同一个表的左外部联接来存档它。

用你的数据和更多。

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

我这样做是为了获得示例数据：

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

现在，我执行查询以获得所需的数据：

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

结果：

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

我希望这个解决办法会有所帮助。

Answer 2

我倾向于使用相关子查询来完成这一任务：

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;