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

Question

最常见/频繁/密集值

我想查找(或创建自定义)聚合函数用于 MS Access 查询，将返回 最常见的值 (即，“最常出现的项目”)按不同字段分组时。下面是我将如何使用它的一个例子——但我可以想到其他的。

样本数据:

Scenario:

I have hourly weather forecast data. Forecasts are updated irregularly, one or more times a day, including a description like "sunny" or "cloudy" (which varies hour-by-hour).

"What is the most common Description, per day, per forecast?

_{(You can also View or Download the sample data as an .xlsx from here).}

“原始”数据(选定字段):

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是最常见的:



...但我只需要列出“前 1 名” 每个DatePST | Updated | Description组合 ，就像这个“期望的输出”:



“ Ties ”需要被忽略(空或零长度)，或者理想情况下，连接成一个单一的值，如 cloudy, clear ，在那个日期|预测中每个都有 12 条记录)。

“最终目标”是这样的交叉表样式输出:



这将用于相当大的数据集，因此手动计算是不可能的。我可以用 VBA 完全完成它，但这需要大量的工作，所以我希望有一种方法来创建 自定义聚合函数 可用于 Access 交叉表的“值”部分。

在研究解决方案时，我发现 SQL Server 可以非常简单地添加自定义聚合函数(如 this 或 this )，并且 T-SQL 甚至具有可能完成这项工作的内置函数，例如:

DENSE_RANK ， 或者可能。

TOP 1 WITH TIES ,

...所以我希望有一种方法可以在 VBA 中做类似的事情，而不必重新发明轮子。

任何想法表示赞赏。

Answer 1

Access 不支持自定义聚合。不过，您可以使用自定义域聚合。

Allen Browne有一个示例自定义域聚合。但是，这些将对性能产生重大影响。

要识别最频繁的值，推荐的方法是使用子查询。

解决这个问题需要多个步骤，在我看来，解决所有这些问题对 SO 来说太宽泛了。

选择每个类别的最高记录的后续步骤是:

SELECT DatePST, Updated, Description 
FROM YourGroupByQuery q 
WHERE [Count] = (   
    SELECT Max(s.[Count]) 
    FROM YourGroupByQuery s 
    WHERE s.[DatePST] = q.[DatePST] And s.[Updated] = q.[Updated]
)