sql - 如何在 Teradata SQL 中合并 Sum 和 Rank 函数？

Question

我试图从前 8 个月总结前 6 个月的石油价值。关于如何将 Sum 和 Rank 函数合并到 SQL 代码中，我对此不太了解。

这是我的 SQL 代码：-

SELECT WELL_NAME,
                Number_of_Months_on_Prod,
                OIL,
                ROW_NUMBER() OVER(PARTITION BY WELL_NAME ORDER BY OIL DESC) AS RANK_OIL,
                SUM(CASE WHEN (RANK_OIL >= 1) AND (RANK_OIL) <= 6 THEN  Coalesce(BOE6, 0) ELSE 0 END) Over (PARTITION BY WELL_NAME ) as  BEST_6_OF_8_MONTH_OIL

FROM TABLE_WELL
ORDER BY WELL_NAME ;

我得到有序解析函数不能嵌套错误。任何帮助将不胜感激。这是我的样本数据集。

标签： sqlteradata

Answer 1

我看到了这个多层次的窗口函数：

SELECT WELL_NAME, Number_of_Months_on_Prod, OIL,
        SUM(CASE WHEN RANK_OIL <= 6 AND SEQNUM <= 8 THEN BOE6 END) OVER (PARTITION BY WELL_NAME) AS BEST_6_OF_8_MONTH_OIL
FROM (SELECT TW.*,
             ROW_NUMBER() OVER (PARTITION BY WELL_NAME, (CASE WHEN SEQNUM <= 8 THEN 1 ELSE 2 END)
                                ORDER BY OIL DESC
                               ) AS RANK_OIL
      FROM (SELECT TW.*,
                   ROW_NUMBER() OVER (PARTITION BY WELL_NAME ORDER BY OIL DESC) AS SEQNUM
            FROM TABLE_WELL TW
           ) TW
     ) TW
ORDER BY WELL_NAME ;