是否可以聚合聚合查询？ [英] Is it possible to Aggregate an Aggregate Query?

问题描述

好伙伴Byte Mates，


这可能是一个复杂的或一个简单的，但在这一点上，它超出了我目前的技能水平。解释可能有点冗长，但我会把问题放在前面。


我有一个在一个级别汇总值的查询。我有另一个查询聚合来自另一个级别的第一个查询的某些值。有没有办法将这两个查询合并为一个查询，以便我得到相同的结果，但只有一个查询？


我认为答案是否，但是我将在下面提出问题并提供这些查询的详细信息。我也提前为这个问题的长度道歉，但我认为我有适当的观众来分析问题。

背景

我在空军的军官促销世界工作，我处理有资格晋升的豆类计数。我们将某些百分比应用于人数，以确定一个机翼指挥官（由代码，由下面称为SRID代表）提供多少配额必须提供最强烈的建议。如果机翼指挥官没有获得配额，他们的合格人员数量将汇总到下一个更高级别（编号空军 - NAF）。在NAF级别汇总的符合条件总数具有相同的百分比，并且可以根据总人口获得配额。如果NAF没有获得配额，他们的所有资格都会在下一个更高级别（主要命令 - MAJCOM）汇总。


这在查询中如何看待？第一个查询 qryBPZRollupBySR ，如下所示：

OK Fellow Byte Mates,

Here might be a complicated one or an easy one, but at this point it is beyond my current skill level. The explanation might be a bit lengthy, but I will place the question up front.

I have a query that aggregates values at one level. I have another query that aggregates certain values from that first query at another level. Is there a way to combine those two queries into one query, so that I get the same results, but with only one query?

I think the answer is "No", but I will ask the question and provide details of these queries below. I also apologize in advance for the length of this question, but I think I have the right audience to analyze the problem.

Background
I work in the officer promotions world in the Air Force and I deal with counting beans that are eligible for promotion. We apply certain percentages to the number of people to determine how many quotas a wing commander (represented by a code, called a SRID, below) has to offer their strongest recommendations. If the wing commander does not earn a quota, their number of eligible officers is aggregated to the next higher level (the Numbered Air Force--NAF). The total number of eligibles aggregated at the NAF Level have the same percentage applied and they may earn quotas, based on the total population. If a NAF does not earn a quota, all their eligibles are aggregated at the next higher level (the Major Command--MAJCOM).

How does this look in Queries? The first Query, qryBPZRollupBySR, looks like this:

展开 | 选择 | Wrap | 行号

推荐答案

忙碌的时间即将到来，但在快速阅读之后，在我看来应该可以处理这些聚合并将它们组合成一个查询。


我可能会离开一两天： - （

Busy time coming up, but after a quick read it seems to me that it should be possible to handle these aggregations and bring them together into a single Query.

I may be away a day or two though :-(

刚抓了5分钟。


SRDP是什么（ ）为你做什么？


当你要求单个查询时，你在谈论[ qr yBPZRollupBySR ]作为SQL包含在[ qryBPZRollupByNAF ]中？


另外，您是在谈论作为单级达到的整体效果吗？查询，直接来自[ tblBoards ]，[ tblBoardIDs ]和[ tblSeniorRaters ]表？我可以想象这是不可能的，虽然在这个阶段我无法肯定。

Just grabbed 5 mins.

What does SRDP() do for you?

When you ask for a single query are you talking about [qryBPZRollupBySR] being included within [qryBPZRollupByNAF] as SQL?

Alternatively, are you talking about the overall effect being reached as a single-level query, directly from the [tblBoards], [tblBoardIDs] and [tblSeniorRaters] tables? That I can imagine being impossible, though I can''t say for sure at this stage.

SRDP（）计算配额。这是一个复杂的过程 不仅仅是 （数字x百分比）的计算。我发现使用用户定义的函数更容易。更清洁。


关于你的第二个问题，我认为我的意图是编写一个查询，没有子查询，只是导出最终结果。


再次，因为后续查询[ qryBPZRollupByNAF ]只聚合 某些 的值，基于某些标准，我不确定是否可行。但是，如果可以创建这样的怪物，我将学习一些关于查询的大量协议。

SRDP() calculates the quotas. It is a complex process not simply being a calculation of (number x percentage). I found using a user-defined function was "easier" and cleaner.

Concerning your second question, I think my "intent" is to write one query, without sub-sub-queries, that just derives the end result.

Again, because the subsequent query [qryBPZRollupByNAF] only aggregates some of the values, based on certain criteria, I''m not sure that is possible. However, methinks that if such a monster could be created, I would learn a termendous deal about queries.

这篇关于是否可以聚合聚合查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！