sql - 在 PostgreSQL 中呈现来自 3 个类似查询的数据

Question

在我的 PostgreSQL 数据库中，我有以下架构:

CREATE TABLE survey_results (
    id integer NOT NULL,
    scores jsonb DEFAULT '{}'::jsonb,
    raw jsonb,
    created_at timestamp without time zone,
    updated_at timestamp without time zone  
);

在这个表中我有以下数据:

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (1, '{"medic": { "social": { "total": "high" } } }', null, now(), now());

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (2, '{"medic": { "social": { "total": "medium" } } }', null, now(), now());

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (3, '{"medic": { "social": { "total": "low" } } }', null, now(), now());

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (4, '{}', '{ "survey": { "denied": true } }', now(), now());

我想按照以下格式从此表中获取数据:

{
  "positive": {
    "2018-01-15": 2,
  },
  "negative": {
    "2018-01-5": 1,
  }
  "declined": {
    "2018-01-15": 1,
  }
}

我可以像这样在 3 个单独的查询中获取此数据:

WITH positive_count AS (
  SELECT
    COUNT(*) AS count_all,
    date(survey_results.created_at)
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('high','medium'))
    GROUP BY date(survey_results.created_at)
), negative_count AS (
  SELECT
    COUNT(*) AS count_all,
    date(survey_results.created_at)
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('low'))
    GROUP BY date(survey_results.created_at)
), declined_count AS (
  SELECT
    COUNT(*) AS count_all,
    date(survey_results.created_at)
    FROM "survey_results"
    WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')
    GROUP BY date(survey_results.created_at)
)

SELECT * from positive_count;

我如何将这些结合起来，以我描述的格式通过一个查询获取这些数据？或者用以下方式格式化会更容易:

  date      |positive_count|negative_count| declined_count|
------------+--------------+--------------+----------------
 2018-01-15 |       1      |       1      |        1      |

这是sql fiddle :

http://sqlfiddle.com/#!17/a9705/2

提前致谢。

Answer 1

为了得到你的结果，它是一个表格:

SELECT
  date(survey_results.created_at),
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium'))) AS positive,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low'))) AS negative,
  COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')) AS declined
  FROM survey_results
  GROUP BY date(survey_results.created_at)

获取json格式

select 
jsonb_build_object('positive', 
  json_agg(jsonb_build_object(date, positive)) FILTER (WHERE positive <> 0)
) ||
jsonb_build_object('negative',
  json_agg( jsonb_build_object(date, negative)) FILTER (WHERE negative <> 0)
) ||
jsonb_build_object('declined', 
  json_agg(jsonb_build_object(date, declined)) FILTER (WHERE declined <> 0)
)
from (
SELECT
  date(survey_results.created_at) as date,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium'))) AS positive,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low'))) AS negative,
  COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')) AS declined
  FROM survey_results
  GROUP BY date(survey_results.created_at)
) as t1;

或者基于你原始查询的json

WITH positive_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*)
    ) as j
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('high','medium'))
    GROUP BY date(survey_results.created_at)
), negative_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*)
    ) as j
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('low'))
    GROUP BY date(survey_results.created_at)
), declined_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*)
    ) as j
    FROM "survey_results"
    WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')
    GROUP BY date(survey_results.created_at)
)
SELECT
jsonb_build_object('positive', (SELECT json_agg(j) from positive_count)) ||
jsonb_build_object('negative', (SELECT json_agg(j) from negative_count)) ||
jsonb_build_object('declined', (SELECT json_agg(j) from declined_count))
;

编辑:在评论之后，保留条目为 0

WITH positive_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium')))
    ) as j
    FROM "survey_results"
    GROUP BY date(survey_results.created_at)
), negative_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low')))
    ) as j
    FROM "survey_results"
    GROUP BY date(survey_results.created_at)
), declined_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true'))
    ) as j
    FROM "survey_results"
    GROUP BY date(survey_results.created_at)
)
SELECT
jsonb_build_object('positive', (SELECT json_agg(j) from positive_count)) ||
jsonb_build_object('negative', (SELECT json_agg(j) from negative_count)) ||
jsonb_build_object('declined', (SELECT json_agg(j) from declined_count))

---

编辑日期优先的结构(见评论)

基于我的查询(这样效率更高，因为它只查询一次表)

select 
json_agg(
jsonb_build_object(date, 
  jsonb_build_object('positive', positive)  ||
  jsonb_build_object('negative', negative)  ||
  jsonb_build_object('declined', declined)
))
from (
SELECT
  date(survey_results.created_at) as date,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium'))) AS positive,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low'))) AS negative,
  COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')) AS declined
  FROM survey_results
  GROUP BY date(survey_results.created_at)
) as t1;

此外，WITH 查询会更复杂，因为 3 个查询中的每一个都返回日期，但我们只需要一个来源。当然，在条件进入 FILTER 之后，所有 3 个 WITH 查询都返回相同的行(只是差异计数)。所以我们可以从他们中的任何一个那里获取日期。但是为什么要运行 3 个查询，一个就够了？

我们确实需要 2 个查询(1 个子查询，就像我一样)，因为有 2 个单独的聚合:1) 按日期聚合 2) 将此聚合的结果聚合到 1 行中。