sql - 是否可以对 SQL 中的同一列应用多个窗口函数

Question

我想知道这样的事情是否可能

WITH t1(day_partition, entity_id, feature) AS (values
  ('2020-05-15', 'id_1', 1),
  ('2020-05-16', 'id_1', 2),
  ('2020-05-15', 'id_2', 3),
  ('2020-05-16', 'id_2', 4)

)

SELECT
  day_partition,
  entity_id,
  LAG(SUM(feature)) OVER (PARTITION BY entity_id order by day_partition) AS x
  -- or perhaps like this?
  --- LAG(SUM(feature) OVER (PARTITION BY entity_id order by day_partition)) OVER () AS x
FROM t1

下面的这个查询产生了预期的结果，但我想知道该tmp表是否可以避免

WITH t1(day_partition, entity_id, feature) AS (values
  ('2020-05-15', 'id_1', 1),
  ('2020-05-16', 'id_1', 2),
  ('2020-05-15', 'id_2', 3),
  ('2020-05-16', 'id_2', 4)

),

tmp AS (
  SELECT
    day_partition,
    entity_id,
    SUM(feature) OVER (PARTITION BY entity_id order by day_partition) AS x
  FROM
    t1
)

SELECT
  day_partition,
  entity_id,
  LAG(x) OVER (order by day_partition)
FROM
  tmp

标签： sqlwindow-functions

Answer 1

不，你不能。

一个窗口函数必须在另一个窗口函数之前预先计算。您可以做的唯一外观改进（如果您愿意）是在其位置使用表格表达式。如果您以这种方式重写查询，它将如下所示：

WITH t1 (day_partition, entity_id, feature) AS (values
  ('2020-05-15', 'id_1', 1),
  ('2020-05-16', 'id_1', 2),
  ('2020-05-15', 'id_2', 3),
  ('2020-05-16', 'id_2', 4)
    
)
SELECT
  day_partition,
  entity_id,
  LAG(x) OVER(ORDER BY day_partition)
FROM (
  SELECT
    day_partition,
    entity_id,
    SUM(feature) OVER (PARTITION BY entity_id order by day_partition) AS x
  FROM  t1
) y

但同样，此查询需要两个范围来预先计算LAG().