创建一个包含两行值的报告,每行值都来自一个单独的SELECT语句

最简单的方法是对另一组查询进行UNION,这些查询具有更严格的where子句:

SELECT
    '*' as Kind,

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()
    ) as "5 Minutes",
    
    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW()
    ) as "1 Hour",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW()
    ) as "1 Day",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    ) as "Total"

UNION ALL

SELECT
    'Active', 

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE AND "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()
    ) as "5 Minutes",
    
    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE AND "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW()
    ) as "1 Hour",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE AND "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW()
    ) as "1 Day",

    (SELECT COUNT(*)
    FROM public.jobs AS "Jobs"
    WHERE "Jobs"."IsActive" IS TRUE 
    ) as "Total"

如果我是你,我更喜欢用这种方式来解决你的疑问:

select
  "Jobs"."active" as Status,
  sum(case when "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '5 minutes' AND NOW() then 1 else 0 end) as "5 Minutes",
  sum(case when "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Hours' AND NOW() then 1 else 0 end) as "1 Hour",
  sum(case when "Jobs"."updated_at" "Jobs"."updated_at" BETWEEN NOW() - INTERVAL '1 Day' AND NOW() then 1 else 0 end) as "1 Day",
  count(*) as "Total"
from public.jobs AS "Jobs"
group by "Jobs"."active"

这样你就能读懂你的表 public.jobs 一次,而不是几次(每 count ). 使用此选项,按分组 status 是一个简单的 group by 活动

基本上,您需要条件聚合。在Postgres中,通常使用 filter :

SELECT COUNT(*) FILTER (WHERE j."updated_at" BETWEEN NOW() - INTERVAL '5 minute' AND NOW()) as cnt_5_minutes,
       COUNT(*) FILTER (WHERE j."updated_at" BETWEEN NOW() - INTERVAL '1 hour' AND NOW()) as cnt_1_hour,
       COUNT(*) FILTER (WHERE j."updated_at" BETWEEN NOW() - INTERVAL '1 day' AND NOW()) as cnt_1_day,
       COUNT(*) as Total
FROM public.jobs j;

你可能没有未来的更新日期,所以这更简单地写为:

SELECT COUNT(*) FILTER (WHERE j."updated_at" >= NOW() - INTERVAL '5 minute') as cnt_5_minutes,
       COUNT(*) FILTER (WHERE j."updated_at" >= NOW() - INTERVAL '1 hour') as cnt_1_hour,
       COUNT(*) FILTER (WHERE j."updated_at" >= NOW() - INTERVAL '1 day') as cnt_1_day,
       COUNT(*) as Total
FROM public.jobs j;

此外,我建议您删除的双引号 updated_at 。在标识符周围使用双引号只是一个坏习惯。

唯一不明显的是如何创建新行。

基本上,添加第二行 UNION ALL .

首先摆脱所有的分离 SELECT 不过,每个度量的查询。这是不必要的昂贵(如果桌子不是很小的话,这一点很重要)。单个 选择 使用条件聚合可以替换所有原始聚合(就像Gordon建议的那样)。在Postgres 9.4或更高版本中 总数的 FILTER 条款 是要走的路。看见

• Aggregate columns with additional (distinct) filters

若要获取另一行,只需运行另一个查询并添加筛选器 "active" IS TRUE 每个表达式(归结为 active ,作为 boolean 列不需要进一步评估)。

但这将使成本再次翻倍,我们可以避免这种情况。跑 单个 选择 在一个 CTE ,拆分结果为 联合所有 在外部查询中:

WITH cte AS (
   SELECT count(*) FILTER (WHERE updated_at > now() - interval '5 min')             AS ct_5min
        , count(*) FILTER (WHERE updated_at > now() - interval '5 min' AND active)  AS ct_5min_a
        , count(*) FILTER (WHERE updated_at > now() - interval '1 hour')            AS ct_1h
        , count(*) FILTER (WHERE updated_at > now() - interval '1 hour' AND active) AS ct_1h_a
        , count(*) FILTER (WHERE updated_at > now() - interval '1 day')             AS ct_1d
        , count(*) FILTER (WHERE updated_at > now() - interval '1 day' AND active)  AS ct_1d_a
        , count(*)                       AS ct_all
        , count(*) FILTER (WHERE active) AS ct_all_a
   FROM   public.jobs
   )
SELECT '*' AS status, ct_5min, ct_1h, ct_1d, ct_all
FROM   cte
UNION ALL
SELECT 'Active', ct_5min_a, ct_1h_a, ct_1d_a, ct_all_a
FROM   cte