I am working on a partitioning problem. I am looking to partition an entire table using three different ranges:
ColumnA > 0.0
ColumnA <= 0.0
ColumnB -20.0 <= AND <=20.0
Then, I need to apply mathematical expressions to each partition:
Mean(ColumnA)
Max(ColumnA)
Min(ColumnB)
The third part of the problem is to create a table populating Columns using the output from the 3 partitions:
Median
Max
Min
What I have tried:
This is the solution I came up with:
CREATE TABLE Table2 AS
(SELECT
MEAN(ColumnA) OVER p0,
MAX (ColumnA) OVER p0,
MIN(ColumnA) OVER p0,
MEAN(ColumnA) OVER p1,
MAX (ColumnA) OVER p1,
MIN(ColumnA OVER p1,
MEAN(ColumnB) OVER p2,
MAX (ColumnB) OVER p2,
MIN(ColumnB) OVER p2,
FROM Table1,
WINDOW p0 AS (PARTITION BY WHERE ColumnA > 0.0) AS MEAN,
WINDOW p1 AS (PARTITION BY WHERE ColumnA <=0.0) AS MAX,
WINDOW p2 AS (PARTITION BY WHERE ColumnB >=-20.0 AND <=20.0) Min;
I want to make sure this is correct. I am concerned I am confused as to how I am applying the mathematical functions to each partition and populating the new table with the data. The wording of the problem seems unclear to me. I am also wondering if there is a better way to write this? Maybe using a Partition By Range clause? Or, a Cross Apply? Open to suggestions.