获取上周的创建和删除条目

获取上周的创建和删除条目

由于两个时间戳在时间范围内可以存在0-n次并且彼此独立，因此您必须做更多的事情：

需要Postgres 9.3+ ：

WITH var(ts_min) AS (SELECt date_trunc('day', now()) - interval '6 days')SELECT day     , COALESCE(c.created, 0) AS created     , COALESCE(d.deleted, 0) AS deletedFROM   var vCROSS  JOIN LATERAL (   SELECt d::date AS day   FROM   generate_series (v.ts_min   , v.ts_min + interval '6 days'   , interval '1 day') d   ) tLEFT   JOIN (   SELECt created_at::date AS day, count(*) AS created   FROM   entries   WHERe  created_at >= (SELECt ts_min FROM var)   GROUP  BY 1   ) c USING (day)LEFT   JOIN (   SELECt canceled_at::date AS day, count(*) AS deleted   FROM   entries   WHERe  canceled_at >= (SELECt ts_min FROM var)   GROUP  BY 1   ) d USING (day)ORDER  BY 1;

CTE

var

仅是为了方便地提供一次启动时间戳。