主页 / dba / 问题 / 262344
Accepted
425nesp
Asked: 2020-03-20 20:57:48 +0800 CST

Postgres 为 1 个表的不匹配行显示计数 0

  • 772

假设我有这个模式。

create table foo_access (
    id         serial primary key,
    user_id    integer not null,
    created_at timestamptz not null
);

-- This data set uses user_id=0 to mean null. _shrug_
insert into foo_access (user_id, created_at) values (0, '2020-03-20T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-19T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-18T00:00:00Z');
insert into foo_access (user_id, created_at) values (2, '2020-03-18T00:00:00Z');

我想看看有多少不同的用户在过去 5 天内每天至少访问一次 foo。

我有一个这样的查询。

select
  date_trunc('day', created_at) as period,
  count(distinct user_id) as n
from foo_access
where user_id > 0 and
  created_at >= now() - interval '5 day'
group by period

这给了我一张这样的桌子。

         period         | n
------------------------+---
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(2 rows)

几乎是我想要的，但不完全是。

有没有办法也显示没有人访问 foo 的日子？我希望桌子看起来更像这样。

         period         | n
------------------------+---
 2020-03-15T00:00:00+00 | 0
 2020-03-16T00:00:00+00 | 0
 2020-03-17T00:00:00+00 | 0
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(5 rows)

SQL小提琴：http ://sqlfiddle.com/#!17/ece05/2/0

这个问题有点类似于How to count matching values and print 0 for non-matching value in PostgreSQL? ，除了我没有加入两个表。我只有一张桌子。

postgresql

1 个回答

  1. Best Answer
    WITH RECURSIVE
cte AS ( SELECT date_trunc('day', now() - interval '5 day') AS period
         UNION ALL
         SELECT period + INTERVAL '1 day'
         FROM cte
         WHERE period < date_trunc('day', now()) )
select
  cte.period,
  count(distinct foo_access.user_id) as n
from cte
LEFT JOIN foo_access on cte.period = date_trunc('day', foo_access.created_at)
                    AND foo_access.user_id > 0
group by cte.period;

    小提琴

    我想看看有多少不同的用户在过去 5 天内每天至少访问一次 foo。

    在我的查询中，您将获得 6 天的统计数据（午夜查询时除外）。如果需要，您可以修复它。

    • 0

相关问题