为访问查询创建自定义聚合函数(access aggregate function)

最常见 / 最频繁 / 最密集值

最常见 / 最频繁 / 最密集值

我想找到(或创建一个自定义)聚合函数在 MS Access 查询中使用,这将返回最常见的值(即“最常出现的项目”)按不同的字段分组。

示例数据:

Scenario:

我有每小时的天气预报数据。预报是updated不规则的,一天一次或多次,包括description,如“晴天”或“多云”(每小时变化)。

“每天,每个预测中最常见的Description是什么?

(您也可以从here.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
…
…
Grouped:

使用“总计”查询对数据进行分组,如下所示:

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最常出现:

screenshot grouped

...但我只需要列出“Top 1”每个DatePST|Updated|Description组合,像这样的“期望输出”:

screenshot desired

关系”需要被忽略(null 或零长度),或者理想情况下连接到一个值,如cloudy, clear,每个值在该日期有 12 条记录 | 预测)。

“最终目标”是交叉表样式的输出,如下所示:

crosstab example

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

在研究解决方案时,我发现 SQL Server 使添加自定义聚合函数(如thisthis)变得非常简单,T-SQL 甚至具有内置函数可能会完成这项工作,例如:

DENSE_RANK,或者可能。

TOP 1 WITH TIES,

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

任何想法都赞赏。

2

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

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

要确定最常见的值,建议使用子查询。

解决这个问题将需要多个步骤,而解决所有这些在我看来太广泛了。

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

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]
)

本站系公益性非盈利分享网址,本文来自用户投稿,不代表码文网立场,如若转载,请注明出处

(909)
Vue包版本不匹配:vue@2.6.14 vue-server-renderer@ 2.7.10
上一篇
SalesforceLWC尝试从闪电数据行获取recordId 使用docs& Apex方法数据中的示例返回空白对象
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(62条)