SQL over partition -avoid repeated statement in for every column

Question

I have the follwoing SQL statement. Is there a way to avoid writing the OVER(PARTIION... statement multiple times for each line as it is identical in every line?

            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  

Any suggestions would be appreciated to make the statement easier to read.

Answer 1

Yes, there is a way to avoid writing the clause multiple times, but the cure is worse than the problem (and therefore is not recommended). You would have to write dynamic SQL, which would be more difficult to read, require more code, be more fragile, etc. What you have is not difficult to read save that it violates the DRY principle. It is good to recognize this and as TheGameiswar[+1] pointed out, you may one day have a solution without a downside.

Answer 2

In standard SQL, you have a clause where you can name the Partitioning option,but it is currently not supported by any RDBMS .You can see the connect item raised by Itzik Ben-Gan and vote for it

If implemented the Clause would look like this..

     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 );