65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 SQL 计算中位数

essentialSQL
emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0投票)

2019 年 12 月 29 日

MIT

4分钟阅读

viewsIcon

4032

如何使用 SQL 计算中位数

在这个谜题中，我们将学习如何找出某个人 whose birthday, among others, is in the middle。了解如何计算中位数是一项有用的技能。随着您开始探索商业智能，您会遇到类似的问题需要解决。

此外，请务必查看奖励问题。我们将处理来自殖民时代的数据。日历不同，您会发现标准的 DATETIME 数据类型不足以应对我们的挑战。

解决谜题是学习 SQL 的好方法。没有什么比练习所学到的更好了。一旦您解决了这个谜题，请在评论中发布您的答案，以便我们都能互相学习。我们还在 Facebook 上的 Essential SQL Learning Group 中讨论谜题和更多内容。请务必在那里找到我们！

计算日期列表中的中位数日期

今天的谜题由 Chris Huntley 撰写。

您有一份包含出生日期的名单，想找出那个有中间出生日期的人？

换句话说，谁拥有中位数出生日期？

Calculate The Median Example

您能接受挑战吗？您能否编写一个单一的 SQL 语句来找到这个人？

要开始，请务必下载代码示例。您可以在这里找到此谜题和奖励问题的示例数据。

注意日期转换问题

第一个想法可能是将其转换为整数，然后用于排序或计算中位数。但是，取决于您如何转换，您最终可能会得到不想要的结果。例如，将其转换为看起来像数字的 字符串，然后转换为整数值

这里有一种将日期转换为 INT 的方法，但不是正确的方法！

SELECT personid,
       personname,
       birthdate,
       CAST(CONVERT(NVARCHAR(10), birthdate,112)as INT) as BirthINT,
       CAST(CONVERT(NVARCHAR(10), birthdate,112)as INT)+1 as BirthINTIncremented,
       CAST(CONVERT(NVARCHAR(10), birthdate,112)as INT)+30 as BirthINTIncrementedMore
FROM   @datetable

如下所示，这种转换有一个缺点。您可以像其他任何整数一样对其进行递增，但它不再是日期了，因此会得到不存在的日期。日期的递增应使用适当的 数据类型和可能的适当函数来完成。

Calculate The Median - Incorrect Result

继续这个想法，我们只计算这个整数的中位数并得到以下结果。

-- calculating the median using the converted String
-- using a CTE to reference the conversion
;WITH ctedateconversion AS (
   SELECT personid,
          personname,
          birthdate,
          CONVERT( INT, Birthdate) as BirthINT
   FROM   @datetable
)
SELECT CONVERT(DATETIME,
               SUM(birthint)/(SELECT COUNT(*) FROM cteDateconversion)) As meanbirthdate
FROM ctedateconversion

我们确实得到了中位数日期，但它不是我们列表中的日期，这意味着它不代表我们表中的某个人。
如果我们去掉外部转换回日期的部分

SUM(birthint)/(SELECT COUNT(*) FROM cteDateconversion) As meanbirthdate

我们将得到值 29438

这是自 1900 年 1 月 1 日以来的天数。

如果您想确认这一点，请使用以下代码

SELECT DATEDIFF(dd, '1900-01-01 00:00:00','1980-08-07 00:00:00.000')

计算中位数日期的答案

为了获得中位数出生日期，我决定使用 (2) PERCENTILE_DISC。PERCENTILE_DISC 计算分布的特定百分位数，在我们的示例中，这是一个日期列表。指定 PERCENTILE_DISC (.5) 计算第 50 个百分位数，这恰好是中位数。

;with ctedateconversion as(
   SELECT personid,
          PersonName,
          Birthdate,
          Percentile_Disc(0.5) within group (order by Birthdate) OVER() as MedianBirthDate
   FROM   @datetable
)
SELECT personname,
       birthdate
FROM   ctedateconversion
WHERE  Birthdate = MedianBirthDate

这就得到了答案。

Calculate the Median - Incorrect Result

有不止一种方法可以得到您想要的答案。您是如何完成这项任务的？

奖励问题

找出那些在其总统任期开始或结束时年龄是所有总统平均年龄的总统。具体来说，要么是他们开始总统任期时的平均年龄，要么是他们结束总统任期时的平均年龄。这要求您仔细考虑所使用的数据类型。收集这些数据的分析师以非规范化的格式提供了数据，所有内容都以 字符串数据类型给出，因为他们无法让 DATETIME 工作。

要开始，请务必下载代码示例。您可以在这里找到奖励问题的示例数据。

示例数据

第一个想法可能是将其转换为 DATETIME，然后进行计算。如果您尝试这样做，您会发现一个错误。

SELECT President,
       Convert(DATETIME, Birthdate) as Birthdate,
       Convert(DATETIME, TermStart) TermStart,
       Convert(DATETIME, TermEnd) as TermEnd
FROM   Presidents

为什么？

DATETIME 有其历史，它可以存储的日期仅限于 1753 年 1 月 1 日之后的日期。历史是，以日历命名的格里高利教皇在 1582 年颁布改革，从儒略历改为格里高利历。

英国和殖民地花了好长一段时间才动手，他们最终决定 1752 年将是改革的过渡年份。为了完成改革，他们不得不从 1752 年 9 月份删掉 11 天。

SQL Server 在设计时，选择忽略了这段时期，并使用格里高利历支持 1753 年以后的日期。

一种选择可能是转换为 DATETIME2，它是格里高利历，支持从 00/00/0001 开始的所有日期，这使其与其他数据库管理系统兼容。

SELECT President,
       Convert(DATETIME2, Birthdate) as Birthdate,
       Convert(DATETIME2, TermStart) TermStart,
       Convert(DATETIME2, TermEnd) as TermEnd
FROM   Presidents

我们想如何获取这些数据？我们可以使用链式 CTE (公用表表达式)，这样我们就可以进行转换然后执行如下计算

; with ctepresidents as(
   SELECT President,
          Convert(DATETIME2, Birthdate) as Birthdate,
          Convert(DATETIME2, TermStart) TermStart,
          Convert(DATETIME2, TermEnd) as TermEnd
   FROM Presidents
)
,
CTESTEP2 as(
   SELECT President,
          CAST(DATEDIFF(dd, birthdate, termstart)/365.0 as INT) as startage,
          CAST(DATEDIFF(dd, birthdate, termend)/365.0 as INT) as endage
   FROM ctepresidents
)
,<span style="color: #339966;">ctefinal as(
   SELECT   CAST(SUM(startage)/(select COUNT(*) FROM Ctestep2)as INT) as meanStartAge,
            CAST(SUM(endage)/(select COUNT(*) FROM Ctestep2)as INT )as meanendAge
   FROM CTESTEP2)
,cteconnectingitall as(
   SELECT President
   FROM   CTESTEP2 c
          INNER JOIN <span style="color: #339966;">CTEFINAL f
          on c.startage = f.meanStartAge
   UNION ALL
   SELECT President
   FROM   <span style="color: #339966;">ctefinal ff
          INNER JOIN CTESTEP2 cc
          ON cc.endage = ff.meanendAge
)
SELECT * FROM cteconnectingitall

给我们

  • 总统任期开始时的平均年龄是： 54
  • 总统任期结束时的平均年龄是： 59

为了使确认更容易，这是代码

;with ctepresidents as(
   SELECT President,
          Convert(DATETIME2, Birthdate) as Birthdate,
          Convert(DATETIME2, TermStart) TermStart,
          Convert(DATETIME2, TermEnd) as TermEnd
   FROM   Presidents
)
,
CTESTEP2 as(
   SELECT President,
          CAST(DATEDIFF(dd, birthdate, termstart)/365.0 as INT) as startage,
          CAST(DATEDIFF(dd, birthdate, termend)/365.0 as INT) as endage
   FROM ctepresidents
)
SELECT * FROM CTESTEP2
WHERE  startage = 54
       OR endage = 59

Calculate the Median Value Result

您是如何得出答案的？您是否有更简单明了的方法？

简介

这篇博客由 Chris Huntley 撰写。他是一位住在达拉斯-沃斯堡地区的商业智能开发人员。他热爱数据、微软 BI 技术栈和墨西哥卷饼，但顺序不一定如此。

Chris 经常帮助其他 SQL 编码人员。

© . All rights reserved.