sql-server - Msg 1949 和 Msg 8668，当我创建索引视图时

Question

我想创建以下视图：

CREATE VIEW vPRODUCT
WITH SCHEMABINDING
AS
    SELECT 
        PROD_ID AS PRODUCT_ID,
        YEAR(TIME_ID) AS YEAR,
        MONTH(TIME_ID) AS MONTH,
        DATEPART(WEEK, TIME_ID) AS WEEK,
        SUM(AMOUNT_SOLD) AS TOTAL_AMOUNT_SOLD,
        SUM(QUANTITY_SOLD) AS TOTAL_QUANTITY_SOLD,
        ROUND(SUM(AMOUNT_SOLD)/SUM(QUANTITY_SOLD),2) AS AVERAGE_SALE_PRICE,
        COUNT_BIG(*) AS COUNT
    FROM 
        dbo.SALES
    GROUP BY 
        PROD_ID, YEAR(TIME_ID), MONTH(TIME_ID), DATEPART(WEEK, TIME_ID);

然后创建索引视图：

CREATE UNIQUE CLUSTERED INDEX IDX_V
    ON vPRODUCT (PRODUCT_ID, YEAR, MONTH);

这将返回消息：

消息 1949，级别 16，状态 1，第 15 行
无法在视图“dbo.vPRODUCT”上创建索引。函数“datepart”产生不确定的结果。使用确定性系统函数，或修改用户定义函数以返回确定性结果。

因此，我尝试创建以下视图（没有 WEEK）：

CREATE VIEW vPRODUCT
WITH SCHEMABINDING
AS
    SELECT      
        PROD_ID AS PRODUCT_ID,
        YEAR(TIME_ID) AS YEAR,
        MONTH(TIME_ID) AS MONTH,
        SUM(AMOUNT_SOLD) AS TOTAL_AMOUNT_SOLD,
        SUM(QUANTITY_SOLD) AS TOTAL_QUANTITY_SOLD,
        ROUND(SUM(AMOUNT_SOLD)/SUM(QUANTITY_SOLD),2) AS AVERAGE_SALE_PRICE,
        COUNT_BIG(*) AS COUNT
    FROM 
        dbo.SALES
    GROUP BY 
        PROD_ID, YEAR(TIME_ID), MONTH(TIME_ID);

然后创建索引视图：

CREATE UNIQUE CLUSTERED INDEX IDX_V
    ON vPRODUCT (PRODUCT_ID, YEAR, MONTH);

然后我收到这条消息：

消息 8668，级别 16，状态 0，第 14 行
无法在视图“dbo.vPRODUCT”上创建聚集索引“IDX_V”，因为视图的选择列表包含聚合函数或分组列的结果表达式。考虑从选择列表中删除聚合函数或分组列的结果表达式。

有没有人可以指出查询有什么问题？

标签： sql-server