Partitioned SUM() fails with expression
The following query works:
SELECT r1_1."store__id" AS "store__id", r1_1."region__id" AS "region__id",
r1_1."fact_sales_sales_amt" AS "amount",
SUM(r1_1."fact_sales_sales_amt") OVER (PARTITION BY r1_1."region__id") AS "gross_sales_sum"
FROM
(
-- leaf_node(out, fact_sales)
SELECT fact_sales."store_id" AS "store__id", dim_store."region_id" AS "region__id",
SUM(fact_sales."sales_amt") AS "fact_sales_sales_amt", SUM(fact_sales."tax_amt") AS "fact_sales_tax_amt"
FROM fact_sales
JOIN dim_store
ON dim_store.store_id = fact_sales.store_id
JOIN max_dim_date
ON max_dim_date.the_date = fact_sales.the_date
AND (max_dim_date.the_month ILIKE 'january')
GROUP BY fact_sales."store_id", dim_store."region_id"
) AS r1_1
But a partitioned sum()
with an expression fails with a generic server error.
TIOStreamTransport.java:read:130:org.apache.thrift.transport.TTransportException: Socket is closed by peer.
SELECT r1_1."store__id" AS "store__id", r1_1."region__id" AS "region__id",
r1_1."fact_sales_sales_amt" AS "amount",
-- this causes the problem
SUM(r1_1."fact_sales_sales_amt" - ABS(r1_1."fact_sales_tax_amt")) OVER (PARTITION BY r1_1."region__id") AS "net_sales_sum"
FROM
(
-- leaf_node(out, fact_sales)
SELECT fact_sales."store_id" AS "store__id", dim_store."region_id" AS "region__id",
SUM(fact_sales."sales_amt") AS "fact_sales_sales_amt", SUM(fact_sales."tax_amt") AS "fact_sales_tax_amt"
FROM fact_sales
JOIN dim_store
ON dim_store.store_id = fact_sales.store_id
JOIN max_dim_date
ON max_dim_date.the_date = fact_sales.the_date
AND (max_dim_date.the_month ILIKE 'january')
GROUP BY fact_sales."store_id", dim_store."region_id"
) AS r1_1
-Larry
-
Same thing with partitioned
AVG()
and an expression, so I'm guessing this is a problem with window functions in general.--- Works AVG(r1_1."fact_sales_sales_amt") OVER (PARTITION BY r1_1."region__id") AS "gross_sales_avg" -- Does not work AVG(r1_1."fact_sales_sales_amt" - ABS(r1_1."fact_sales_tax_amt")) OVER (PARTITION BY r1_1."region__id") AS "net_sales_avg"
Please sign in to leave a comment.
Comments
1 comment