SQL over partition - 避免每列重複語句

我有以下 SQL 語句。有沒有辦法避免為每一行多次編寫 OVER(PARTIION… 語句，因為它在每一行中都是相同的？

           SELECT 
           DISTINCT
           LAST_VALUE (DSET_COB_DT)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_DSET_COB_DT,
           LAST_VALUE (SRC_HUB)              OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB,
           LAST_VALUE (TRD_SRC_SYS)          OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_SRC_SYS,
           LAST_VALUE (TRD_ID)               OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_ID,
           LAST_VALUE (NVL (TRD_VER,0))      OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_TRD_VER,
           LAST_VALUE (SRC_HUB_INGESTION_DT) OVER(PARTITION BY TRD_ID ORDER BY TRD_VER,DSET_COB_DT NULLS FIRST RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RS_SRC_HUB_INGESTION_DT,

           FROM DF_RISK_SIT2_OWNER.recon_ts_rs  

任何建議將不勝感激，以使聲明更易於閱讀。

是的，有一種方法可以避免多次編寫該子句，但治標不治本（因此不推薦）。您將不得不編寫動態 SQL，這將更難閱讀，需要更多程式碼，更脆弱等。您所擁有的內容並不難閱讀，但它違反了DRY原則。很高興認識到這一點並作為 TheGameiswar

$$ +1 $$指出，你有一天可能會找到一個沒有缺點的解決方案。

在標準 SQL 中，您有一個可以命名 Partitioning 選項的子句，但目前任何 RDBMS 都不支持它。您可以查看Itzik Ben-Gan 提出的連接項並投票支持它

如果實施該條款將如下所示..

    SELECT empid, qty,
    SUM(qty) OVER W1 AS run_sum_qty,
    AVG(qty) OVER W1 AS run_avg_qty,
    FROM Sales.EmpOrders
   WINDOW W1 AS ( PARTITION BY empid
    ORDER BY ordermonth
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW );

引用自：https://dba.stackexchange.com/questions/145753