为Access查询创建自定义聚合函数

最常见/频繁/密集的值

我希望在MS Access查询中找到（或创建自定义的）聚合函数，该函数将返回按不同字段分组时的最常见值（即“最常出现的项”）。以下是我如何使用它的一个示例-但我想到了其他用途。

样本数据：

情境：

我有每小时的天气预报数据。预测不规则地更新，一天或多次，包括像“晴朗”或“多云”这样的描述（每小时不同）。

“每天每次预测最常见的描述是什么？”

（您也可以从此处查看或下载样本数据为 .xlsx ）。

“原始”数据（选择字段）：

DateTimePST updated description temperature windSpeed

2018-02-11 02:00 2018-02-06 17:53 cloudy -0.47 2.93

2018-02-11 02:00 2018-02-07 18:22 cloudy 2.09 8.92

2018-02-11 02:00 2018-02-08 22:48 rain 1.54 9.7

2018-02-11 03:00 2018-02-06 03:00 cloudy 0.13 4.31

2018-02-11 03:00 2018-02-06 17:53 cloudy -0.29 2.43

2018-02-11 03:00 2018-02-07 18:22 cloudy 1.97 5.79

2018-02-11 03:00 2018-02-08 22:48 snow 1.4 10.2

2018-02-11 04:00 2018-02-06 03:00 cloudy 0.16 4.07

2018-02-11 04:00 2018-02-06 17:53 cloudy 0.02 2.78

2018-02-11 04:00 2018-02-07 18:22 cloudy 1.89 5.54

2018-02-11 04:00 2018-02-08 22:48 snow 1.44 10.75

2018-02-11 05:00 2018-02-06 03:00 cloudy 0.14 3.83

…

…

分组：

使用类似于以下'合计'查询的方式将数据进行分组：

SELECT Int([DateTimePST]) AS DatePST, a.updated, i.description, Count(a.id) AS cnt

FROM tblWeatherData AS a

LEFT JOIN tblIconsToDesc AS i

ON a.icon = i.icon

GROUP BY Int([DateTimePST]), a.updated, i.description

ORDER BY Int([DateTimePST]), a.updated, Count(a.id) DESC;

...生成一个有序列表，显示哪些 Description 最常出现：

...但我需要仅列出每个DatePST|Updated|Description组合的最高级别的“Top 1”，类似于这个“期望输出”：

如果有"并列名次"，则将其忽略（null 或零长度），或者最好将它们拼接成一个值，例如 cloudy, clear，它们在日期|预报上都有12条记录)。

最终目标是生成如下交叉表式的输出:

这将用于庞大的数据集，因此排除手动计算。我可以使用 VBA 完全完成它，但需要大量工作，因此希望有一种方法可以创建一个自定义聚合函数，可在 Access 交叉表的“值”部分中使用。

在研究解决方案时，我发现 SQL Server 很容易添加自定义聚合函数（例如 这个 或 这个），而 T-SQL 甚至有内置的函数可以完成此任务，例如：

DENSE_RANK，或者也许是。

TOP 1 WITH TIES，

...因此，我希望在 VBA 中有一种类似的方法，而无需重新发明轮子。

欢迎提出任何想法。