sql - 计算列组合的出现次数

Question

我有不同公司的每日时间序列(实际上是工作日)，并且我使用 PostgreSQL。还有一个指标变量(称为标志)在大多数情况下取值 0，在一些罕见的事件日取 1。如果某个公司的指标变量取值1，我想进一步调查相应公司从事件发生前两天到事件发生后一天的条目。我将其称为 [-2,1] 窗口，事件日为第 0 天。

我正在使用以下查询

CREATE TABLE test AS
WITH cte AS (
   SELECT *
        , MAX(flag) OVER(PARTITION BY company ORDER BY day
                         ROWS BETWEEN 1 preceding AND 2 following) Lead1
   FROM mytable)
SELECT *
FROM cte
WHERE Lead1 = 1 
ORDER BY day,company

对于经历该事件的公司，该查询采用从事件发生前 2 天到事件发生后 1 天不等的条目。查询对所有事件都这样做。

这是结果表的一小部分。

day              company    flag     
2012-01-23       A          0        
2012-01-24       A          0         
2012-01-25       A          1         
2012-01-25       B          0         
2012-01-26       A          0         
2012-01-26       B          0        
2012-01-27       B          1        
2012-01-30       B          0        
2013-01-10       A          0        
2013-01-11       A          0              
2013-01-14       A          1              

现在我想分别为每个 [-2,1] 窗口做进一步的计算。所以我需要一个变量来识别每个 [-2,1] 窗口。我的想法是，我用变量“occur”计算每个公司的窗口数，以便在进一步的计算中我可以使用子句

    GROUP BY company, occur

因此我想要的输出看起来像这样:

day              company    flag     occur
2012-01-23       A          0        1
2012-01-24       A          0        1 
2012-01-25       A          1        1 
2012-01-25       B          0        1 
2012-01-26       A          0        1 
2012-01-26       B          0        1
2012-01-27       B          1        1
2012-01-30       B          0        1
2013-01-10       A          0        2
2013-01-11       A          0        2
2013-01-14       A          1        2 

在示例中，公司 B 只出现一次 (occur = 1)。但是A公司出现了两次。第一次从 2012-01-23 到 2012-01-26。第二次是从 2013-01-10 到 2013-01-14。公司 A 的第二个时间范围不包括事件日 (-2,-1,0,1) 周围的所有四天，因为该公司在该时间范围结束之前离开了数据集。

正如我所说，我在工作日工作。我不关心假期，我有周一到周五的数据。之前我写了以下函数:

CREATE OR REPLACE FUNCTION addbusinessdays(date, integer)
  RETURNS date AS
$BODY$ 
WITH alldates AS (
    SELECT i,
    $1 + (i * CASE WHEN $2 < 0 THEN -1 ELSE 1 END) AS date
    FROM generate_series(0,(ABS($2) + 5)*2) i
),
days AS (
    SELECT i, date, EXTRACT('dow' FROM date) AS dow
    FROM alldates
),
businessdays AS (
    SELECT i, date, d.dow FROM days d
    WHERE d.dow BETWEEN 1 AND 5
    ORDER BY i
)

-- adding business days to a date --
SELECT date FROM businessdays WHERE
        CASE WHEN $2 > 0 THEN date >=$1 WHEN $2 < 0
             THEN date <=$1 ELSE date =$1 END
    LIMIT 1
    offset ABS($2)
$BODY$
  LANGUAGE 'sql' VOLATILE;

它可以从给定日期添加/减去工作日，并且工作方式如下:

    select * from addbusinessdays('2013-01-14',-2)

交付结果 2013-01-10。所以在 Jakub 的方法中，我们可以将倒数第二行和倒数第三行更改为

      w.day BETWEEN addbusinessdays(t1.day, -2) AND addbusinessdays(t1.day, 1)

并且可以处理工作日。

Answer 1

函数

在使用函数 addbusinessdays() 时，考虑一下:

CREATE OR REPLACE FUNCTION addbusinessdays(date, integer)
  RETURNS date AS
$func$ 
SELECT day
FROM  (
    SELECT i, $1 + i * sign($2)::int AS day
    FROM   generate_series(0, ((abs($2) * 7) / 5) + 3) i
    ) sub
WHERE  EXTRACT(ISODOW FROM day) < 6  -- truncate weekend
ORDER  BY i
OFFSET abs($2)
LIMIT  1
$func$  LANGUAGE sql IMMUTABLE;

要点

• 永远不要引用语言名称sql。它是一个标识符，而不是一个字符串。

• 为什么函数 VOLATILE？使其成为 IMMUTABLE 以获得更好的重复使用性能和更多选项(例如在功能索引中使用它)。

• (ABS($2) + 5)*2) 填充过多。替换为 ((abs($2) * 7)/5) + 3)。

• 多个级别的 CTE 毫无用处。

上一个 CTE 中的

• ORDER BY 也没有用。

• 如我之前的回答所述，extract(ISODOW FROM ...)截断周末更方便。

查询

也就是说，我根本不会将上述函数用于此查询。一次性构建一个完整的相关日期网格，而不是计算每一行的日期范围。

基于评论中的这个断言(真的应该在问题中!):

two subsequent windows of the same firm can never overlap.

WITH range AS (              -- only with flag
   SELECT company
        , min(day) - 2 AS r_start
        , max(day) + 1 AS r_stop
   FROM   tbl t 
   WHERE  flag <> 0
   GROUP  BY 1
   )
, grid AS (
   SELECT company, day::date
   FROM   range r
         ,generate_series(r.r_start, r.r_stop, interval '1d') d(day)
   WHERE  extract('ISODOW' FROM d.day) < 6
   )
SELECT *, sum(flag) OVER(PARTITION BY company ORDER BY day
                         ROWS BETWEEN UNBOUNDED PRECEDING
                         AND 2 following) AS window_nr
FROM  (
   SELECT t.*, max(t.flag) OVER(PARTITION BY g.company ORDER BY g.day
                           ROWS BETWEEN 1 preceding
                           AND 2 following) in_window
   FROM   grid     g
   LEFT   JOIN tbl t USING (company, day)
   ) sub
WHERE  in_window > 0      -- only rows in [-2,1] window
AND    day IS NOT NULL    -- exclude missing days in [-2,1] window
ORDER  BY company, day;

如何？

• 构建所有工作日的网格:CTE grid。

• 为使网格尽可能小，提取每个公司的最小和最大(加上缓冲区)天数:CTE range。

• LEFT JOIN 实际行到它。现在，后续窗口函数的框架可以使用静态数字。

• 要获得每个旗帜和公司的不同数字 (window_nr)，只需从网格开始计算旗帜(考虑缓冲区)。

• 只在 [-2,1] 窗口内保留天数 (in_window > 0)。

• 只保留表中实际行的日期。

瞧。

SQL Fiddle.