获取上周的创建和删除条目 [英] Get created as well as deleted entries of last week
问题描述
通过此查询,我可以获取上周创建的所有条目:
With this query I can get all entries created during the last week:
SELECT day, COALESCE(ct, 0) AS ct
FROM (SELECT now::date - d AS day FROM generate_series (0, 6) d) d -- 6, not 7
LEFT JOIN (
SELECT created_at::date AS day, count(*) AS ct
FROM entries
WHERE created_at >= date_trunc('day', now()) - interval '6d'
GROUP BY 1
) e USING (day);
它返回这样的结果:
count | date
2 | 15.01.2014
0 | 14.01.2014
1 | 13.01.2014
0 | 12.01.2014
0 | 11.01.2014
0 | 10.01.2014
9 | 09.01.2014
现在我还想显示上周所有已删除的条目!我可以通过字段 deleted_at
来获取它们:我尝试过的是:
Now I also want to display all the deleted Entries of last Week! I can get them through the field deleted_at
: What I tried was:
SELECT day, COALESCE(ct, 0) AS created, COALESCE(dl,0) AS deleted
FROM (SELECT current_date - d AS day FROM generate_series (0, 6) d) d
LEFT JOIN (
SELECT created_at::date AS day,
count(
CASE WHEN (created_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END
) AS ct,
count(
CASE WHEN (canceled_at >= date_trunc('day', now()) - interval '6d') THEN 1 ELSE 0 END
) AS dl
FROM entries
GROUP BY 1
) e USING (day);
但这没用!现在,我得到两个相同的行:
But that didn't work! Now I get two rows that are the same:
deleted | created | date
2 | 2 | 15.01.2014
0 | 0 | 14.01.2014
1 | 1 | 13.01.2014
0 | 0 | 12.01.2014
0 | 0 | 11.01.2014
0 | 0 | 10.01.2014
9 | 9 | 09.01.2014
我怎么了?我该如何显示已创建和已删除的条目?
What do I wrong? How can I display created as well as deleted entries?
推荐答案
由于两个时间戳在时间范围内都可以存在0-n次,因此彼此独立,您需要做的更多:
Since both timestamps can exist 0 - n times in the time frame and independent from each other, you have to do more:
需要Postgres 9.3 + :
Requires 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 deleted
FROM var v
CROSS JOIN LATERAL (
SELECT d::date AS day
FROM generate_series (v.ts_min
, v.ts_min + interval '6 days'
, interval '1 day') d
) t
LEFT 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
仅是为了方便一次提供开始时间戳。
The CTE var
is only for the convenience to provide the starting timestamp once.
这篇关于获取上周的创建和删除条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!