将OVER函数与generate_series一起使用

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": { "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');

和以下查询：

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  100
2018-01-11  0
2018-01-12  100

但问题是，当有一天没有结果时，它应该得到与前一天相同的数据，因此看起来应该这样：

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

我正在考虑在这里使用OVER功能，但我无法使其正常工作。有什么办法做到吗？

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

Answer 1

使用累积count(*)作为窗口函数来指定分区(具有领先的非零值和连续的空值的组(。接下来，在这些分区中使用窗口函数first_value()添加一个外部选择：

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。

Answer 2

您可以使用累积功能，例如 max()：

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

如果您的数据正在增加。