CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7872

Using OVER function with generate_series

I have the following schema in my database: 

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": { "social": { "total": "high" } } }', '2018-01-10', '2018-01-11');

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

and the following query:

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;

which returns following results:

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

but the problem is that when on some day there was no results it should get the same data as on the previous day, so it should look like this:

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

I was thinking about using OVER function here but I was not able to make it work. Is there any way of doing that?

http://sqlfiddle.com/#!17/0cd2c/1

Upvotes: 1

Views: 718

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270603

You can use a cumulative function, such as max():

select, date::date, coalesce(positive, 0.00),
        max(positive) over (order by date::date)

This works if your data is increasing.

Upvotes: 0

klin
klin

Reputation: 121784

Use a cumulative count(*) as a window function to designate partitions (groups with leading non-null value and consecutive null values). Next, add one more outer select with the window function first_value() in these partitions:

SELECT *, first_value(positive) OVER (PARTITION BY part ORDER BY date)
FROM (
    SELECT date::date, positive, count(positive) OVER (ORDER BY date) as part
    FROM generate_series('2018-01-09'::date, '2018-01-12', '1d') s(date)
    LEFT JOIN (
        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-09'
            AND survey_results.created_at::date <= '2018-01-12'
          GROUP BY date, scores
        ) q USING(date)
    ) q
ORDER BY date ASC;

SqlFiddle.

Upvotes: 1

Related Questions