好的,所以我是SQL的新手,这就是我问这个问题的原因。
我有一张名为:kpi_notification_metrics_per_month的表,此表有 2 列:
- 日期
- 通知计数
我想创建一个全新的表格,该表将显示
- 意味 着
- 中位数
- 模式
对于"通知计数"列。
示例表:
Date NotificationCount
01/04/2018 00:00 0
31/03/2018 00:00 0
25/03/2018 00:00 0
24/03/2018 00:00 0
22/03/2018 00:00 0
18/03/2018 00:00 0
17/03/2018 00:00 0
14/03/2018 00:00 0
11/03/2018 00:00 0
07/04/2018 00:00 1
26/03/2018 00:00 1
21/03/2018 00:00 1
15/03/2018 00:00 1
13/03/2018 00:00 1
12/03/2018 00:00 1
10/03/2018 00:00 1
08/04/2018 00:00 2
30/03/2018 00:00 2
09/03/2018 00:00 2
08/03/2018 00:00 2
20/03/2018 00:00 3
19/03/2018 00:00 4
02/04/2018 00:00 9
23/03/2018 00:00 11
27/03/2018 00:00 22
03/04/2018 00:00 28
28/03/2018 00:00 34
04/04/2018 00:00 39
05/04/2018 00:00 43
29/03/2018 00:00 47
06/04/2018 00:00 50
16/03/2018 00:00 140
预期成果:
Mean Median Mode
13.90625 1 0
以下是在 Oracle 中执行此操作的方法:
select
avg(notificationcount) as statistic_mean,
median(notificationcount) as statistic_median,
stats_mode(notificationcount) as statistic_mode
from mytable;
不需要另一张桌子。您可以(并且应该(始终临时查询数据。为方便起见,您可以按照 jarlh 在请求注释中的建议创建视图。
意思是:使用Avg()
Select Avg(NotificationCount)
From kpi_notification_metrics_per_month
中位数:按 ASC 和 DESC 排序 对于TOP 50 Percent
的数据,找到中间的。
Select ((
Select Top 1 NotificationCount
From (
Select Top 50 Percent NotificationCount
From kpi_notification_metrics_per_month
Where NotificationCount Is NOT NULL
Order By NotificationCount
) As A
Order By NotificationCountDESC) +
(
Select Top 1 NotificationCount
From (
Select Top 50 Percent NotificationCount
From kpi_notification_metrics_per_month
Where NotificationCount Is NOT NULL
Order By NotificationCount DESC
) As A
Order By NotificationCount Asc)) / 2
模式:获取每个值集的计数,并按 DESC 顺序获取前 1 行。
SELECT TOP 1 with ties NotificationCount
FROM kpi_notification_metrics_per_month
WHERE NotificationCount IS Not NULL
GROUP BY NotificationCount
ORDER BY COUNT(*) DESC
所有这些都在Sql Server 2014中工作。
参考: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/calculating-mean-median-and-mode-with-sq/