wenfang6 commented on issue #6790:
URL:
https://github.com/apache/incubator-gluten/issues/6790#issuecomment-2287713619
> > > @wenfang6 Would you like to share the whole query plan in text? Thanks.
> > > I use spark3.2.1~
> >
> >
> > ```
> > WITH v1 AS (
> > SELECT
> > i_category,
> > i_brand,
> > cc_name,
> > d_year,
> > d_moy,
> > sum(cs_sales_price) sum_sales,
> > avg(sum(cs_sales_price))
> > OVER
> > (PARTITION BY i_category, i_brand, cc_name, d_year)
> > avg_monthly_sales,
> > rank()
> > OVER
> > (PARTITION BY i_category, i_brand, cc_name
> > ORDER BY d_year, d_moy) rn
> > FROM item, catalog_sales, date_dim, call_center
> > WHERE cs_item_sk = i_item_sk AND
> > cs_sold_date_sk = d_date_sk AND
> > cc_call_center_sk = cs_call_center_sk AND
> > (
> > d_year = 1999 OR
> > (d_year = 1999 - 1 AND d_moy = 12) OR
> > (d_year = 1999 + 1 AND d_moy = 1)
> > )
> > GROUP BY i_category, i_brand,
> > cc_name, d_year, d_moy),
> > v2 AS (
> > SELECT
> > v1.i_category,
> > v1.i_brand,
> > v1.cc_name,
> > v1.d_year,
> > v1.d_moy,
> > v1.avg_monthly_sales,
> > v1.sum_sales,
> > v1_lag.sum_sales psum,
> > v1_lead.sum_sales nsum
> > FROM v1, v1 v1_lag, v1 v1_lead
> > WHERE v1.i_category = v1_lag.i_category AND
> > v1.i_category = v1_lead.i_category AND
> > v1.i_brand = v1_lag.i_brand AND
> > v1.i_brand = v1_lead.i_brand AND
> > v1.cc_name = v1_lag.cc_name AND
> > v1.cc_name = v1_lead.cc_name AND
> > v1.rn = v1_lag.rn + 1 AND
> > v1.rn = v1_lead.rn - 1)
> > SELECT *
> > FROM v2
> > WHERE d_year = 1999 AND
> > avg_monthly_sales > 0 AND
> > CASE WHEN avg_monthly_sales > 0
> > THEN abs(sum_sales - avg_monthly_sales) / avg_monthly_sales
> > ELSE NULL END > 0.1
> > ORDER BY sum_sales - avg_monthly_sales, 3
> > LIMIT 100
> > ```
>
> Thanks @wenfang6 . But I may not made it clearer that, would you like to
do an explain on the SQL then paste the output here? It's more helpful if we
can analyze on Spark query plan than on SQL itself.
gluten sql ui show this
```
== Fallback Summary ==
No fallback nodes
== Physical Plan ==
ColumnarBroadcastExchange (3)
+- BroadcastQueryStage (2)
+- ReusedExchange (1)
===== Adaptively Optimized Out Exchanges =====
Subplan:1
ColumnarBroadcastExchange (21)
+- ^ ProjectExecTransformer (19)
+- ^ WindowExecTransformer (18)
+- ^ SortExecTransformer (17)
+- ^ InputIteratorTransformer (16)
+- AQEShuffleRead (14)
+- ShuffleQueryStage (13)
+- ColumnarExchange (12)
+- VeloxAppendBatches (11)
+- ^ ProjectExecTransformer (9)
+- ^ RegularHashAggregateExecTransformer (8)
+- ^ InputIteratorTransformer (7)
+- ShuffleQueryStage (5)
+- ReusedExchange (4)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
