View not running - percentile_cont

Question

我有以下代码：

SELECT  DISTINCT
        SQ.COMP_ID,
        SQ.JT,
        CAST(MIN(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT)  AS DECIMAL (10,2)) AS MINIMUM,
        CAST(PERCENTILE_CONT(0.15) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P15,
        CAST(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P25,
        CAST(AVG(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS AVERAGE,
        CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS MEDIAN,  
        CAST(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P75,
        CAST(PERCENTILE_CONT(0.85) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P85,
        CAST(MAX(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS MAXIMUM,
        COUNT(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS AMOUNT,
        SQ.TARGET_DATE
FROM
(
SELECT  DISTINCT
        E.COMP_ID, 
        E.STAFF_ID, 
        E.CONTRACT_ID,
        CT.JOB_TITLE AS JT, 
        CAST(SUM(E.REVENUE) AS DECIMAL (10,2)) AS SUM_SALES,
        DT.CAL_DATE AS TARGET_DATE

FROM SALES E
LEFT JOIN DATES_TABLE DT ON 1=1
LEFT JOIN CONTRACT_TABLE CT ON CT.COMP_ID = E.COMP_ID and CT.STAFF_ID = E.STAFF_ID AND CT.CONTRACT_ID = E.CONTRACT_ID
LEFT JOIN J_CONDI C3 ON E.COMP_ID = C3.COMP_ID AND C3.COND_DATE = '20200101'

WHERE 
    E.SALES_DATE = DATEADD(MM,DATEDIFF(MM,0,DT.CAL_DATE),0) 
AND E.SALES_ID in ('566165', '864651')

GROUP BY
        E.COMP_ID, 
        E.STAFF_ID, 
        E.CONTRACT_ID,
        CT.JOB_TITLE,   
        DT.CAL_DATE
) SQ
WHERE
SQ.COMP_ID = '1561656' AND
SQ.TARGET_DATE = '20201215'

如果我将代码作为带有where子句的select语句运行，那么它将运行并执行得非常好！

但是如果我创建一个查询：

CREATE VIEW SALES_STATS AS
(
SELECT  DISTINCT
        SQ.COMP_ID,
        SQ.JT,
        CAST(MIN(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT)  AS DECIMAL (10,2)) AS MINIMUM,
        CAST(PERCENTILE_CONT(0.15) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P15,
        CAST(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P25,
        CAST(AVG(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS AVERAGE,
        CAST(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS MEDIAN,  
        CAST(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P75,
        CAST(PERCENTILE_CONT(0.85) WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS P85,
        CAST(MAX(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS DECIMAL (10,2)) AS MAXIMUM,
        COUNT(SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) AS AMOUNT,
        SQ.TARGET_DATE
FROM
(
SELECT  DISTINCT
        E.COMP_ID, 
        E.STAFF_ID, 
        E.CONTRACT_ID,
        CT.JOB_TITLE AS JT, 
        CAST(SUM(E.REVENUE) AS DECIMAL (10,2)) AS SUM_SALES,
        DT.CAL_DATE AS TARGET_DATE

FROM SALES E
LEFT JOIN DATES_TABLE DT ON 1=1
LEFT JOIN CONTRACT_TABLE CT ON CT.COMP_ID = E.COMP_ID and CT.STAFF_ID = E.STAFF_ID AND CT.CONTRACT_ID = E.CONTRACT_ID
LEFT JOIN J_CONDI C3 ON E.COMP_ID = C3.COMP_ID AND C3.COND_DATE = '20200101'

WHERE 
    E.SALES_DATE = DATEADD(MM,DATEDIFF(MM,0,DT.CAL_DATE),0) 
AND E.SALES_ID in ('566165', '864651')

GROUP BY
        E.COMP_ID, 
        E.STAFF_ID, 
        E.CONTRACT_ID,
        CT.JOB_TITLE,   
        DT.CAL_DATE
) SQ
)

我按如下方式调用该视图

SELECT *
FROM SALES_STATS SST
WHERE
SST.COMP_ID = '1561656' AND
SST.TARGET_DATE = '20201215'

然后它就跑啊跑啊。它不输出任何结果

我的同事告诉我这可能是因为WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT)，但他不知道如何解决它

是否有其他方法可以在不使用PERCENTILE_CONT(x)或不使用OVER (PARTITION BY)的函数的情况下获得百分位数

我只想要像最小、最大、中位数和百分位数这样的统计数据。

或者我可以让子查询先执行吗？我也尝试过使用top(51619861) max number，但它不起作用。

Answer 1

这对于评论来说有点长了。

SQL Server有一种user defined functions视图的替代方法。特别是，您可以使用UDF来执行以下所有操作：

• It接受参数。
• it返回一个表。
• 它的内联方式类似于视图(也就是说，“代码”本质上是插入到查询中的，但它要比这个复杂一点)。

我认为它们是“参数化视图”。

我的建议是创建一个用户定义的表值内联函数，该函数接受放在WHERE子句中的参数。

在您的案例中，基本语法是：

CREATE FUNCTION ufn_sales_stats (
    @compid int,
    @target_date date
)  RETURNS TABLE  
AS  
RETURN (  <your parameterized query here> );