当前位置：文江博客话题详情

SQL Server 是否具有与 Excel 的 KURT 函数等效的功能？

发布于 2024-12-08 22:03:22 字数 57 浏览 0 评论 0原文

Excel 具有 KURT 函数，可返回数据集的峰度。 SQL Server 是否有等效的聚合函数？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烛影斜 2024-12-15 22:03:22

TSQL 具有计算峰度所需的所有函数，但我认为您必须自己将它们放在一个自定义函数中。

这是我发现的一个实现（参考：SolidQ)

SELECT 
   Kurt = SUM(SQUARE(SQUARE(((Age*1.0-m.mean)/m.[StDev])))) * MIN(m.corrfact2) - MIN(m.subfact)
FROM 
      vTargetMail v 
   CROSS JOIN
      (
       SELECT 
          mean = AVG(Age*1.0), [StDev] = STDEV(Age),
          corrfact2 = COUNT(*)*1.0 * (COUNT(*)+1) / (COUNT(*)-1) / (COUNT(*)-2) / (COUNT(*)-3),
          subfact = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3)
       FROM vTargetMail v
      ) AS m;

TSQL has all of the functions required to calculate kurtosis, but I think you'd have to put it all together yourself in a custom function.

Here's one implementation I found (ref: SolidQ)

SELECT 
   Kurt = SUM(SQUARE(SQUARE(((Age*1.0-m.mean)/m.[StDev])))) * MIN(m.corrfact2) - MIN(m.subfact)
FROM 
      vTargetMail v 
   CROSS JOIN
      (
       SELECT 
          mean = AVG(Age*1.0), [StDev] = STDEV(Age),
          corrfact2 = COUNT(*)*1.0 * (COUNT(*)+1) / (COUNT(*)-1) / (COUNT(*)-2) / (COUNT(*)-3),
          subfact = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3)
       FROM vTargetMail v
      ) AS m;

回复收藏 0 原文

油焖大侠 2024-12-15 22:03:22

我对此表示怀疑——这是一个晦涩的函数，而且 MySQL 倾向于只实现更接近数学基础核心的函数。

然而，峰度的计算方法相当简单。请参阅此处的公式。

回复收藏 0 原文

温柔戏命师 2024-12-15 22:03:22

通过结合中的方法http://blogs.solidq.com/en/sqlserver/skewness-and-kurtosis-part-1-t-sql-solution/#abh_posts 和 https://sqlwithpanks.wordpress .com/2016/06/22/kurtosis-a-measure-of-tailedness-of-the-distribution/，以下是经过测试的工作版本：

;with v as (select floor(rand(convert(varbinary,newid()))*365)+1 as X from dbo.TblModel07_High),
AGG as (SELECT m1 = AVG(X*1.0), sd1 = STDEV(X),corrfact1 = COUNT(*)*1.0 / (COUNT(*)-1) / (COUNT(*)-2),
[corrfact2] = COUNT(*)*1.0 * (COUNT(*)+1) / (COUNT(*)-1) / (COUNT(*)-2) / (COUNT(*)-3),
[subfact] = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3) from v)
SELECT N=count(*), Xmin=min(X), Xmax=max(X),M = MIN(m1),SD = MIN(sd1),CV = min(sd1)/min(m1),
Skew = SUM(((X*1.0 - m1)/sd1)*((X*1.0-m1)/sd1)*((X*1.0-m1)/sd1))* min(corrfact1),
Kurt = SUM( SQUARE( SQUARE( ( ( X * 1.0 - m1 )/sd1 ) ) ) ) * min(corrfact2) - min(subfact)+3 from v cross apply
(select m1,sd1,corrfact1,corrfact2,subfact from AGG) A

By combine the method from http://blogs.solidq.com/en/sqlserver/skewness-and-kurtosis-part-1-t-sql-solution/#abh_posts and https://sqlwithpanks.wordpress.com/2016/06/22/kurtosis-a-measure-of-tailedness-of-the-distribution/, the following is a tested work version:

;with v as (select floor(rand(convert(varbinary,newid()))*365)+1 as X from dbo.TblModel07_High),
AGG as (SELECT m1 = AVG(X*1.0), sd1 = STDEV(X),corrfact1 = COUNT(*)*1.0 / (COUNT(*)-1) / (COUNT(*)-2),
[corrfact2] = COUNT(*)*1.0 * (COUNT(*)+1) / (COUNT(*)-1) / (COUNT(*)-2) / (COUNT(*)-3),
[subfact] = 3.0 * SQUARE((COUNT(*)-1)) / (COUNT(*)-2) / (COUNT(*)-3) from v)
SELECT N=count(*), Xmin=min(X), Xmax=max(X),M = MIN(m1),SD = MIN(sd1),CV = min(sd1)/min(m1),
Skew = SUM(((X*1.0 - m1)/sd1)*((X*1.0-m1)/sd1)*((X*1.0-m1)/sd1))* min(corrfact1),
Kurt = SUM( SQUARE( SQUARE( ( ( X * 1.0 - m1 )/sd1 ) ) ) ) * min(corrfact2) - min(subfact)+3 from v cross apply
(select m1,sd1,corrfact1,corrfact2,subfact from AGG) A

回复收藏 0 原文

~没有更多了~