sql - PostgreSQL 高级记录计数

Question

我有以下架构:

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

INSERT INTO survey_results (id, scores, created_at, updated_at)
    VALUES (1, '{"medic": { "categories": { "motivation": "high" } } }', '2017-10-01', '2017-10-01');

INSERT INTO survey_results (id, scores, created_at, updated_at)
    VALUES (2, '{"medic": { "categories": { "motivation": "medium" } } }', '2017-10-02', '2017-10-02');

INSERT INTO survey_results (id, scores, created_at, updated_at)
    VALUES (3, '{"medic": { "categories": { "motivation": "low" } } }', '2017-10-03', '2017-10-03');

我使用以下查询从此表中获取数据:

SELECT
  date(survey_results.created_at),
  json_build_object(
    'high', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('high'))),
    'medium', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('medium'))),
    'low', COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('low')))
  ) as motivation                               
  FROM survey_results
  GROUP BY date(survey_results.created_at)
  ORDER BY date asc;

返回以下格式的数据:

   date    |              motivation
------------------------------------------------------
2017-10-01 |    {"high" : 1, "medium" : 0, "low" : 0}
2017-10-02 |    {"high" : 0, "medium" : 1, "low" : 0}
2017-10-03 |    {"high" : 0, "medium" : 0, "low" : 1}

例如，如果日期是 2017-10-01，则应包括从时间开始到 2017-10-01 结束的所有调查结果。第二个日期将包括第一个日期所做的所有调查结果加上 2017-10-02 收集的调查结果，依此类推。

所以结果应该是:

   date    |              motivation
------------------------------------------------------
2017-10-01 |    {"high" : 1, "medium" : 0, "low" : 0}
2017-10-02 |    {"high" : 1, "medium" : 1, "low" : 0}
2017-10-03 |    {"high" : 1, "medium" : 1, "low" : 1}

这是带有模式和基本查询的 sql fiddle: http://sqlfiddle.com/#!17/61d7da/1

有没有办法在 PostgreSQL 中做类似的事情？

Answer 1

我想你想要:

SELECT date(survey_results.created_at),
      json_build_object(
    'high', SUM(COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('high')))) OVER (ORDER BY date(survey_results.created_at)),
    'medium', SUM(COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('medium')))) OVER (ORDER BY date(survey_results.created_at)),
    'low', SUM(COUNT(*) FILTER (WHERE (scores#>>'{medic,categories,motivation}' in('low')))) OVER (ORDER BY date(survey_results.created_at))
  ) as motivation                               
FROM survey_results
GROUP BY date(survey_results.created_at)
ORDER BY date asc;

也就是使用累计和。

作为忠告，如果没有所有 JSON 对象内容，您的查询将更易于使用和理解。您可能想让查询正常工作，然后在一切正常后添加 JSON 格式。