We are trying to read from HDFS parquet file and do some advanced windowing operation and write it back to HDFS parquet once done. We are using spark SQL for building the pipeline.
Our cluster configuration is : 6 node cluster, 48 cores & 256 GB Memory in each node.
We are reading about 1.5 TB of source data and process it in spark.
My spark-shell configuration : --driver-cores 2 --driver-memory 10G --num-executors 50 --executor-memory 18G --executor-cores 5
I've checked the partition size of each stage and it doesn't appear to be skewed. Each partition is getting about 200 MB of data.
I ran the Spark SQL WINDOW operation on the data and tried to perform some action like .show & .count -- it gave back the result in 40 mins.
While I'm trying to write the output of window operation, we get the below error.
18/04/20 20:31:03 ERROR storage.ShuffleBlockFetcherIterator: Failed to get block(s) from ncdledhdnr0006.woolworths.com.au:7337
java.lang.RuntimeException: java.util.concurrent.TimeoutException: Timeout waiting for task.
at org.spark_project.guava.base.Throwables.propagate(Throwables.java:160)
at org.apache.spark.network.client.TransportClient.sendRpcSync(TransportClient.java:275)
at org.apache.spark.network.sasl.SaslClientBootstrap.doBootstrap(SaslClientBootstrap.java:70)
at org.apache.spark.network.crypto.AuthClientBootstrap.doSaslAuth(AuthClientBootstrap.java:117)
at org.apache.spark.network.crypto.AuthClientBootstrap.doBootstrap(AuthClientBootstrap.java:76)
at org.apache.spark.network.client.TransportClientFactory.createClient(TransportClientFactory.java:244)
at org.apache.spark.network.client.TransportClientFactory.createClient(TransportClientFactory.java:182)
at org.apache.spark.network.shuffle.ExternalShuffleClient.lambda$fetchBlocks$0(ExternalShuffleClient.java:97)
at org.apache.spark.network.shuffle.RetryingBlockFetcher.fetchAllOutstanding(RetryingBlockFetcher.java:141)
at org.apache.spark.network.shuffle.RetryingBlockFetcher.start(RetryingBlockFetcher.java:121)
at org.apache.spark.network.shuffle.ExternalShuffleClient.fetchBlocks(ExternalShuffleClient.java:106)
at org.apache.spark.storage.ShuffleBlockFetcherIterator.sendRequest(ShuffleBlockFetcherIterator.scala:228)
at org.apache.spark.storage.ShuffleBlockFetcherIterator.fetchUpToMaxBytes(ShuffleBlockFetcherIterator.scala:435)
at org.apache.spark.storage.ShuffleBlockFetcherIterator.initialize(ShuffleBlockFetcherIterator.scala:323)
at org.apache.spark.storage.ShuffleBlockFetcherIterator.<init>(ShuffleBlockFetcherIterator.scala:140)
at org.apache.spark.shuffle.BlockStoreShuffleReader.read(BlockStoreShuffleReader.scala:45)
at org.apache.spark.sql.execution.ShuffledRowRDD.compute(ShuffledRowRDD.scala:165)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:96)
at org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:53)
at org.apache.spark.scheduler.Task.run(Task.scala:108)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:338)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.util.concurrent.TimeoutException: Timeout waiting for task.
at org.spark_project.guava.util.concurrent.AbstractFuture$Sync.get(AbstractFuture.java:276)
at org.spark_project.guava.util.concurrent.AbstractFuture.get(AbstractFuture.java:96)
at org.apache.spark.network.client.TransportClient.sendRpcSync(TransportClient.java:271)
... 30 more
</i>
Can someone please advise what can be done to get around this?
Below is the Window functions
SELECT
| ASD.FW_END_DATE
| ,ASD.ARTICLE_NBR
| ,ASD.UOM
| ,ASD.SITE
| ,ASD.SALES_ORG
| ,ASD.COOP
| ,ASD.MANUAL_CLAIMS
| ,ASD.TRADING_GP_CO
| ,ASD.FIRST_COST_VALUE
| ,ASD.WET
| ,ASD.FREIGHT_INC_ON_COST
| ,ASD.Miscellaneous
| ,ASD.VOLUME_SPEND
| ,ASD.ALLOWANCES
| ,ASD.PURCHASE_DEALS
| ,ASD.VARIANCE_TO_MAP
| ,ASD.NATIONAL_LANDED_COST
| ,ASD.WHS_ALLOWANCE
| ,ASD.SETTLEMENT_DISC
| ,ASD.UNFUNDED_MARKDOWN
,ASD.Shrink as shrink_TWEEK
,SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS shrink_LWEEK
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS shrink_4WEEKS
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS shrink_13WEEKS
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS shrink_26WEEKS
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS shrink_52WEEKS
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS shrink_LY
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS shrink_LWEEK_LY
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS shrink_4WEEKS_LY
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS shrink_13WEEKS_LY
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS shrink_26WEEKS_LY
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS shrink_52WEEKS_LY
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS shrink_FIN_PER
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS shrink_FIN_QTR
| , SUM(ASD.shrink) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS shrink_FIN_YR
,ASD.PROMO_SALES as PROMO_SALES_TWEEK
,SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PROMO_SALES_LWEEK
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS PROMO_SALES_4WEEKS
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS PROMO_SALES_13WEEKS
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS PROMO_SALES_26WEEKS
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS PROMO_SALES_52WEEKS
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_LY
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS PROMO_SALES_LWEEK_LY
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_4WEEKS_LY
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_13WEEKS_LY
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_26WEEKS_LY
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_52WEEKS_LY
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS PROMO_SALES_FIN_PER
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS PROMO_SALES_FIN_QTR
| , SUM(ASD.PROMO_SALES) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS PROMO_SALES_FIN_YR
,ASD.PROMO_SALES_UNITS as PROMO_SALES_UNITS_TWEEK
,SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PROMO_SALES_UNITS_LWEEK
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS PROMO_SALES_UNITS_4WEEKS
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS PROMO_SALES_UNITS_13WEEKS
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS PROMO_SALES_UNITS_26WEEKS
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS PROMO_SALES_UNITS_52WEEKS
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_UNITS_LY
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS PROMO_SALES_UNITS_LWEEK_LY
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_UNITS_4WEEKS_LY
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_UNITS_13WEEKS_LY
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_UNITS_26WEEKS_LY
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS PROMO_SALES_UNITS_52WEEKS_LY
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS PROMO_SALES_UNITS_FIN_PER
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS PROMO_SALES_UNITS_FIN_QTR
| , SUM(ASD.PROMO_SALES_UNITS) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS PROMO_SALES_UNITS_FIN_YR
,ASD.ONLINE_SALES_UOM AS ONLINE_SALES_UOM_TWEEK
,SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS ONLINE_SALES_UOM_LWEEK
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UOM_4WEEKS
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UOM_13WEEKS
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UOM_26WEEKS
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UOM_52WEEKS
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UOM_LY
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS ONLINE_SALES_UOM_LWEEK_LY
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UOM_4WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UOM_13WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UOM_26WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UOM_52WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS ONLINE_SALES_UOM_FIN_PER
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS ONLINE_SALES_UOM_FIN_QTR
| , SUM(ASD.ONLINE_SALES_UOM) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS ONLINE_SALES_UOM_FIN_YR
,ASD.ONLINE_SALES_UNITS AS ONLINE_SALES_UNITS_TWEEK
,SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS ONLINE_SALES_UNITS_LWEEK
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UNITS_4WEEKS
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UNITS_13WEEKS
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UNITS_26WEEKS
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS ONLINE_SALES_UNITS_52WEEKS
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UNITS_LY
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS ONLINE_SALES_UNITS_LWEEK_LY
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UNITS_4WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UNITS_13WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UNITS_26WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS ONLINE_SALES_UNITS_52WEEKS_LY
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS ONLINE_SALES_UNITS_FIN_PER
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS ONLINE_SALES_UNITS_FIN_QTR
| , SUM(ASD.ONLINE_SALES_UNITS) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS ONLINE_SALES_UNITS_FIN_YR
| ,ASD.NET_SALES AS NET_SALES_TWEEK
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS NET_SALES_LWEEK
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS NET_SALES_4WEEKS
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS NET_SALES_13WEEKS
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS NET_SALES_26WEEKS
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS NET_SALES_52WEEKS
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS NET_SALES_LY
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS NET_SALES_LWEEK_LY
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS NET_SALES_4WEEKS_LY
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS NET_SALES_13WEEKS_LY
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS NET_SALES_26WEEKS_LY
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS NET_SALES_52WEEKS_LY
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS NET_SALES_FIN_PER
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS NET_SALES_FIN_QTR
| , SUM(ASD.NET_SALES) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS NET_SALES_FIN_YR
| ,ASD.SALES_QUANTITY_SUOM AS SALES_QUANTITY_SUOM_TWEEK
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS SALES_QUANTITY_SUOM_LWEEK
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS SALES_QUANTITY_SUOM_4WEEKS
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS SALES_QUANTITY_SUOM_13WEEKS
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS SALES_QUANTITY_SUOM_26WEEKS
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE,ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS SALES_QUANTITY_SUOM_52WEEKS
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS SALES_QUANTITY_SUOM_LY
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS SALES_QUANTITY_SUOM_LWEEK_LY
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS SALES_QUANTITY_SUOM_4WEEKS_LY
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS SALES_QUANTITY_SUOM_13WEEKS_LY
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS SALES_QUANTITY_SUOM_26WEEKS_LY
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS SALES_QUANTITY_SUOM_52WEEKS_LY
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS SALES_QUANTITY_SUOM_FIN_PER
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS SALES_QUANTITY_SUOM_FIN_QTR
| , SUM(ASD.SALES_QUANTITY_SUOM) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS SALES_QUANTITY_SUOM_FIN_YR
| ,ASD.INTRIMGP AS INTRIMGP_TWEEK
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS INTRIMGP_LWEEK
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS INTRIMGP_4WEEKS
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS INTRIMGP_13WEEKS
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS INTRIMGP_26WEEKS
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS INTRIMGP_52WEEKS
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS INTRIMGP_LY
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS INTRIMGP_LWEEK_LY
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS INTRIMGP_4WEEKS_LY
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS INTRIMGP_13WEEKS_LY
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS INTRIMGP_26WEEKS_LY
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS INTRIMGP_52WEEKS_LY
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS INTRIMGP_FIN_PER
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS INTRIMGP_FIN_QTR
| , SUM(ASD.INTRIMGP) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS INTRIMGP_FIN_YR
| ,ASD.TRADING_GP AS TRADING_GP_TWEEK
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS TRADING_GP_LWEEK
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS TRADING_GP_4WEEKS
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS TRADING_GP_13WEEKS
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 25 PRECEDING AND CURRENT ROW) AS TRADING_GP_26WEEKS
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 51 PRECEDING AND CURRENT ROW) AS TRADING_GP_52WEEKS
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 52 PRECEDING AND 52 PRECEDING) AS TRADING_GP_LY
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 53 PRECEDING AND 53 PRECEDING) AS TRADING_GP_LWEEK_LY
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 55 PRECEDING AND 52 PRECEDING) AS TRADING_GP_4WEEKS_LY
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 64 PRECEDING AND 52 PRECEDING) AS TRADING_GP_13WEEKS_LY
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 77 PRECEDING AND 52 PRECEDING) AS TRADING_GP_26WEEKS_LY
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS BETWEEN 103 PRECEDING AND 52 PRECEDING) AS TRADING_GP_52WEEKS_LY
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.FP_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS TRADING_GP_FIN_PER
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.FQ_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS TRADING_GP_FIN_QTR
| , SUM(ASD.TRADING_GP) OVER (PARTITION BY ASD.FY_END_DATE, ASD.SITE, ASD.ARTICLE_NBR,ASD.UOM ORDER BY ASD.FW_END_DATE ROWS UNBOUNDED PRECEDING) AS TRADING_GP_FIN_YR
| FROM ALL_FWD_FPQY_SKU_SITE_SORG_MSRS ASD
