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

gorkyyrv 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(340)

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

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 可提供table

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

sql window-functions

来源：https://stackoverflow.com/questions/62180027/is-it-possible-to-apply-multiple-window-functions-for-the-same-column-in-sql

1条答案

按热度按时间

xn1cxnb41#

不，你不能。
一个窗口函数必须在另一个窗口函数之前进行预计算。唯一可以做的表面改进（如果您愿意的话）是使用表表达式代替它。如果以这种方式重写查询，它将如下所示：

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() .

赞(0）回复(0）举报 2021-07-26

我来回答

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

1条答案

相关问题

热门标签

最新问答