zhztheplayer commented on issue #6790: URL: https://github.com/apache/incubator-gluten/issues/6790#issuecomment-2287295449
> > @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 be make 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. -- 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]
