查看未运行 - percentile_cont [英] View not running - percentile_cont
问题描述
我有以下代码:
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 语句运行，那么它运行并执行得非常好！
if I run the code as a select statement with the where clause then it runs and performs very well!
但是如果我创建一个查询:
but if I create a query:
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'
然后它运行，运行，运行，运行，运行，运行，运行，运行，运行，运行，运行，运行......它没有输出任何结果
Then it runs and runs and runs and runs and runs and runs and runs and runs and runs and runs and runs and runs... it outputs no result
我的同事告诉我这可能是因为 WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) 但他不知道如何解决
My colleague told me it could be because of WITHIN GROUP (ORDER BY SQ.SUM_SALES) OVER (PARTITION BY SQ.JT) but he doesn't know how to solve it
是否有另一种方法可以在不使用 PERCENTILE_CONT(x) 或不使用任何使用 OVER (PARTITION BY) 的函数的情况下获取 PERCENTILE
Is there another way to get the PERCENTILEs without using PERCENTILE_CONT(x) or without any function which uses OVER (PARTITION BY)
我只想获得诸如最小最大中位数和百分位数之类的统计数据
I just want to have the stats like min max median and percentiles
或者我可以让子查询先执行吗?我也试过使用 top(51619861) max number 但它不起作用.
Or can I for the subquery to execute first? I have also tried using top(51619861) max number but it doesn't work.
推荐答案
评论有点长.
SQL Server 可以替代 用户定义的函数.特别是，您可以拥有一个执行以下所有操作的 UDF:
SQL Server has an alternative to views which are user defined functions. In particular, you can have a UDF that does all the following:
- 它需要参数.
- 它返回一个表.
- 它像视图一样被内联(也就是说，代码"本质上是插入到查询中的，但比这稍微复杂一些).
我认为它们是参数化视图".
I think of them as "parameterized views".
我的建议是您创建一个用户定义的表值内联函数，该函数采用您将放在 WHERE 子句中的参数.
My suggestion is that you create a user defined table-valued inline function that takes the parameters that you would put in the WHERE clause.
您的情况的基本语法是:
The basic syntax in your case is:
CREATE FUNCTION ufn_sales_stats (
@compid int,
@target_date date
) RETURNS TABLE
AS
RETURN ( <your parameterized query here> );
这篇关于查看未运行 - percentile_cont的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
