使用“Teradata中的1以下行和后续行”来选择下一行的值

Question

我正在尝试将下一行的值选择到另一列中。但是，我得到了当前值和下一个值。

正在使用的SQL查询如下：

SELECT DISTINCT
  "Title",
  "US Release Date Title",
  MAX("Title") OVER (
    PARTITION BY "Brand"
    ORDER BY "US Release Date Title" DESC, "Title" DESC
    ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
) AS "Predecessor"

该查询的输出片段如下：

Call of Duty: Black Ops IIII    |  2018-10-12  |  Call of Duty: Black Ops IIII    |  2018-10-12
Call of Duty: Black Ops IIII    |  2018-10-12  |  Call of Duty: WWII              |  2017-11-03
Call of Duty: WWII              |  2017-11-03  |  Call of Duty: Infinite Warfare  |  2016-11-04
Call of Duty: WWII              |  2017-11-03  |  Call of Duty: WWII              |  2017-11-03
Call of Duty: Infinite Warfare  |  2016-11-04  |  Call of Duty: Advanced Warfare  |  2014-11-03
Call of Duty: Infinite Warfare  |  2016-11-04  |  Call of Duty: Infinite Warfare  |  2016-11-04
Call of Duty: Advanced Warfare  |  2014-11-03  |  null                            |  null
Call of Duty: Advanced Warfare  |  2014-11-03  |  Call of Duty: Advanced Warfare  |  2014-11-03

我想要的输出是:一个片段--来自该查询的输出如下：

Call of Duty: Black Ops IIII    |  2018-10-12  |  Call of Duty: WWII              |  2017-11-03
Call of Duty: WWII              |  2017-11-03  |  Call of Duty: Infinite Warfare  |  2016-11-04
Call of Duty: Infinite Warfare  |  2016-11-04  |  Call of Duty: Advanced Warfare  |  2014-11-03
Call of Duty: Advanced Warfare  |  2014-11-03  |  null                            |  null

Answer 1

我认为您需要在窗口函数之前使用select distinct。或者使用聚合：

SELECT "Title", "US Release Date Title",
       MAX("Title") OVER (PARTITION BY MAX("Brand")
                          ORDER BY "US Release Date Title" DESC,
                                   "Title" DESC
                          ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
                          ) AS "Predecessor"
FROM t
GROUP BY "Title", "US Release Date Title";

您确实需要一个围绕Brand的聚合函数才能工作。