How to pipe window function output directly into a new window function in SQL?-CodePudding

I am new to SQL and I have the following query:

-- Ref 1
SELECT *,
Max(Watts) OVER (PARTITION BY Freq) as MyMax,
log10(SUM(MyMax) OVER SumFunct * 1000) * 10 AS MySum
FROM MyTable
WINDOW SumFunct as (PARTITION BY temp, Freq, channel);

This doesn't work due to no such column: MyMax. Even though from my understanding the column for MyMax is being created on the fly, I'm guessing SQL still isn't able to use its values immediately as an input into the next window function

I already tried creating the column for MaxPower and populating it prior to the query above, via

-- Ref 2
ALTER TABLE MyTable ADD COLUMN MyMax real;
UPDATE MyTable SET MyMax = sub.MyMax 
FROM (
   SELECT *, Max(Watts) OVER (PARTITION BY Freq) AS MyMax
) sub;

But I don't know how to set MyMax with a window function within an UPDATE statement. My attempt at this was to create a sub-query but I feel like I was overcomplicating the problem and I also didn't get this working due to some syntax at the SELECT I couldn't isolate.

All I want to do is query MyMax using the window function above, and essentially pipe it into a new window function. The result: two extra columns with the data I need. My understanding of window functions is that they will create the column you want on the fly, but even when I break apart the top query (Ref 1) into two different calls:

-- Ref3
SELECT *,
Max(Watts) OVER (PARTITION BY Freq) as MyMax,
FROM MyTable;
SELECT *,
log10(SUM(MyMax) OVER SumFunct * 1000) * 10 AS MySum
FROM MyTable
WINDOW SumFunct as (PARTITION BY temp, Freq, channel);

It still doesn't recognize what MyMax is. Does anyone have any guidance on how to approach this problem?

CodePudding user response：

Nesting of window functions is not allowed.

You need a subquery that returns MyMax and the main query to return MySum:

SELECT *, log10(SUM(MyMax) OVER SumFunct * 1000) * 10 AS MySum
FROM (
  SELECT *, Max(Watts) OVER (PARTITION BY Freq) AS MyMax
  FROM MyTable
)
WINDOW SumFunct as (PARTITION BY temp, Freq, channel);

Or, a CTE:

WITH cte AS (
  SELECT *, Max(Watts) OVER (PARTITION BY Freq) as MyMax
  FROM MyTable
)
SELECT *, log10(SUM(MyMax) OVER SumFunct * 1000) * 10 AS MySum
FROM cte
WINDOW SumFunct as (PARTITION BY temp, Freq, channel);