Cannot create an indexed view because I reference a derived table

Question

I perform a nested SELECT to create a derived table, hourly, that gets me hourly averages of the availability of a feature. Using hourly, I average all those values to obtain the daily average, and I want to create an indexed view of the daily averages. My query is as follows:

CREATE VIEW DailyView WITH SCHEMABINDING AS
SELECT hourly.Feature,
       AVG(hourly.AvgAvailability) AS AvgAvailability,
       CAST(hourly.DateTime AS date) AS Date FROM
       (SELECT DISTINCT 
                        SC.Feature,  
                        AVG(SA.Availability) AS AvgAvailability, 
                        SA.DateAndHour AS DateTime
       FROM   dbo.ServiceAvailability AS SA LEFT OUTER JOIN
              dbo.ServiceCatalog AS SC ON SA.ServiceID = SC.ServiceID
GROUP BY SC.Feature, SA.DateAndHour) hourly
GROUP BY hourly.Feature, CAST(hourly.DateTime AS date)
GO
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON DailyView(Date)
GO

However, I cannot create an indexed view this way as I get the following error:

Cannot create index on view "Reporting.dbo.DailyView" because it references derived table "hourly" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

It is important for me to index this view as we will be dealing with large amounts of data, but I am not sure how to get the nested average without the derived table in there.

Answer 1

The problem is not the subquery alone. You can't use AVG in an Indexed view. Use SUM and COUNT_Big instead so when you query this view you can calculate the average. LEFT JOINs are not allowed too. Only INNER JOINs

You can check if you can create an indexed view for the derived table after making changes in combination with the right indexes.