Missing dates when using GROUP BY date

debugcn 投稿 Dev

Mateusz Urbański

In my PostgreSQL database I have survey_results table:

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

I have the following records in this table:

INSERT INTO survey_results (id, scores, created_at, updated_at)
    VALUES (1, '{"medic": { "social": { "total": "high" } } }', '2018-01-11', '2018-01-10');

INSERT INTO survey_results (id, scores, created_at, updated_at)
    VALUES (2, '{"medic": { "social": { "total": "high" } } }', '2018-01-12', '2018-01-12');

and following query:

SELECT
  distinct(date(survey_results.created_at)),

  ROUND(
    COUNT(*) FILTER (WHERE (
      scores#>>'{medic,social,total}' in('high'))) OVER(order by date(survey_results.created_at)
    ) * 1.0 /

    (
      GREATEST(
        COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium','low')
    )
  ) OVER(order by date(survey_results.created_at)), 1.0))* 100, 2
)
 AS positive

  FROM survey_results
  WHERE  
    survey_results.created_at::date >= '2018-01-10'
    AND survey_results.created_at::date <= '2018-01-12'
  GROUP BY date, scores
  ORDER BY date ASC;

which returns:

date        positive
2018-01-11  100
2018-01-12  100

The problem is that query omitted 2018-01-10 because there was not records, that's because of group by. Is there any way to update this query to also return days that do not have records:

date        positive
2018-01-10  0
2018-01-11  100
2018-01-12  100

Here is sqlfiddle:

http://sqlfiddle.com/#!17/5e007/1

klin

Use the function generate_series():

SELECT date::date, coalesce(positive, 0.00) as positive
FROM generate_series('2018-01-10'::date, '2018-01-12', '1d') s(date)
LEFT JOIN (
    -- your query
    SELECT
      distinct(date(survey_results.created_at)),
      ROUND(
        COUNT(*) FILTER (WHERE (
          scores#>>'{medic,social,total}' in('high'))) OVER(order by date(survey_results.created_at)
        ) * 1.0 /
        (
          GREATEST(
            COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium','low')
        )
      ) OVER(order by date(survey_results.created_at)), 1.0))* 100, 2
    )
     AS positive
      FROM survey_results
      WHERE  
        survey_results.created_at::date >= '2018-01-10'
        AND survey_results.created_at::date <= '2018-01-12'
      GROUP BY date, scores
    -- your query
    ) q USING(date)
ORDER BY date ASC;

    date    | positive 
------------+----------
 2018-01-10 |     0.00
 2018-01-11 |   100.00
 2018-01-12 |   100.00
(3 rows)

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]