如何在单个查询中获取一天的计数和最近三天的计数？ [英] How to get count of one day and count of last three days in single query?

问题描述

这是一个类似的问题，例如：如何在单个查询中获得一天的总和和最近三天的总和？

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

我想要的是三列：

1. 日期


2. 日期的计数（不同统计）


3. 日期的最近三天的计数（不同统计）

所以我预期结果中的唯一行应该是：

So the only row in my expected result should be:

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

这个问题和我之前提到的类似问题之间的区别是我们无法计算出不同的统计数据通过使用sum（count（distinct stats））超过（...）来计算最后三天的费用，因为在不同日期出现的同一类型的统计数据将被计算多次。

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.

该如何存档？

谢谢！

推荐答案

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

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

结果：

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.

这篇关于如何在单个查询中获取一天的计数和最近三天的计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！