sql - 计算上一时间范围内按特定列划分的行

我的数据集由来自不同行业的不同公司的每日（实际上是工作日）时间序列组成，我使用PostgreSQL。我的数据集中有一个指示符变量，它的值为1，-1，大多数时候为0。为了提高问题的可读性，我将指标变量不等于零的天数称为指标事件。
因此，对于前三个工作日内同一行业的其他指标事件之前的所有指标事件，应将指标变量更新为零。
我们可以考虑以下示例数据集：

day              company    industry       indicator
2012-01-12       A          financial      1
2012-01-12       B          consumer       0
2012-01-13       A          financial      1
2012-01-13       B          consumer       -1
2012-01-16       A          financial      0
2012-01-16       B          consumer       0
2012-01-17       A          financial      0
2012-01-17       B          consumer       0
2012-01-17       C          consumer       0
2012-01-18       A          financial      0
2012-01-18       B          consumer       0
2012-01-18       C          consumer       1

因此，应更新为零的指标值在2012年1月13日为A公司的分录，在2012年1月18日为C公司的分录，因为它们之前在3个工作日内有同一行业的另一个指标事件。
我试着用以下方法来完成它：

UPDATE test SET indicator = 0
WHERE (day, industry) IN (
SELECT day, industry
  FROM (
       SELECT industry, day,
       COUNT(CASE WHEN indicator <> 0 THEN 1 END)
          OVER (PARTITION BY industry ORDER BY day
                ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) As cnt
       FROM test
       ) alias
  WHERE cnt >= 2)

我的想法是计算当天和前3天按行业划分的指标事件。如果计数大于1，则将指示器值更新为零。
薄弱点是，迄今为止，它的计算超过了前三行（按行业划分），而不是前三个工作日。因此，在示例数据中，它无法在2012年1月18日更新C公司，因为它在过去三个工作日内计算industry=consumer的最后三行，而不是计算industry=consumer的所有行。
我尝试了不同的方法，比如在代码的第三个最后一行添加另一个子查询，或者在第三个最后一行之后添加WHERE EXISTS-子句，以确保代码在前面三个日期上计数。但什么都没用。我真的不知道该怎么做（我只是学着和PostgreSQL一起工作）。
你知道怎么修吗？
或者我的想法完全错了，你知道另一种方法来解决我的问题吗？

最佳答案

数据库设计
首先，你的桌子应该正常化。industry应该是引用integer表的industry_id的小外键列（通常industry）。也许你已经有了，只是为了这个问题而简化了。实际的表定义会有很大的帮助。
由于带有指示符的行很少但非常有趣，请创建（可能是“覆盖”）部分索引以使任何解决方案更快：

CREATE INDEX tbl_indicator_idx ON tbl (industry, day)
WHERE  indicator <> 0;

Equality first, range last.
假设indicator定义为NOT NULL。如果industry是一个integer，那么这个索引将非常有效。
查询
此查询标识要重置的行：

WITH x AS (               -- only with indicator
   SELECT DISTINCT industry, day
   FROM   tbl t
   WHERE  indicator <> 0
   )
SELECT industry, day
FROM  (
   SELECT i.industry, d.day, x.day IS NOT NULL AS incident
        , count(x.day) OVER (PARTITION BY industry ORDER BY day_nr
                             ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ct
   FROM  (
      SELECT *, row_number() OVER (ORDER BY d.day) AS day_nr
      FROM  (
         SELECT generate_series(min(day), max(day), interval '1d')::date AS day
         FROM   x
         ) d
      WHERE  extract('ISODOW' FROM d.day) < 6
      ) d
   CROSS  JOIN (SELECT DISTINCT industry FROM x) i
   LEFT   JOIN x USING (industry, day)
   ) sub
WHERE  incident
AND    ct > 1
ORDER  BY 1, 2;

SQL Fiddle.
ISODOW as extract() parameter可以方便地截断周末。
将其集成到您的UPDATE中：

WITH x AS (               -- only with indicator
   SELECT DISTINCT industry, day
   FROM   tbl t
   WHERE  indicator <> 0
   )
UPDATE tbl t
SET    indicator = 0
FROM  (
   SELECT i.industry, d.day, x.day IS NOT NULL AS incident
        , count(x.day) OVER (PARTITION BY industry ORDER BY day_nr
                             ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ct
   FROM  (
      SELECT *, row_number() OVER (ORDER BY d.day) AS day_nr
      FROM  (
         SELECT generate_series(min(day), max(day), interval '1d')::date AS day
         FROM   x
         ) d
      WHERE  extract('isodow' FROM d.day) < 6
      ) d
   CROSS  JOIN (SELECT DISTINCT industry FROM x) i
   LEFT   JOIN x USING (industry, day)
   ) u
WHERE  u.incident
AND    u.ct > 1
AND    t.industry = u.industry
AND    t.day = u.day;

这应该比使用相关子查询和对每一行的函数调用的解决方案快得多。即使这是基于我之前的回答，也不适合这个案子。