Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?
Date: Sat, 16 Sep 2006 03:21:05 GMT
Message-ID: <B4KOg.21172$9u.240155_at_ursa-nb00s0.nbnet.nb.ca>
Marshall wrote:
> pamelafluente_at_libero.it wrote:
>
>>This involves a group of record and requires a GROUP BY clause.
>
> It's the other way around, actually: a GROUP BY requires an
> aggregate function, but an aggregate function does not require
> a GROUP BY.
I doubt very many if any dbmses require an aggregate function for a group by. Without any aggregates, group by is synonymous with project.
>>2. >>For "Replication sensitive" I mean: >> >> - An aggregate function such that if some value (operand) is >>replicated the result comes out wrong. >> >>For instance: >> >>* Replication sensitive* : >>sum() >>count() >>avg() >>Any Quantile and so on >>... >> >>* Not Replication sensitive * : >>max() >>min() >>count distinct () >>anything not influenced by the replication of values
>
>
> The term is idempotent. If the binary form of the aggregate
> function is idempotent, the aggregate will return the same value
> even if values are repeated arbitrarily. Since + is not idempotent,
> sum() is "sensitive" to repeated values. Since binary min *is*
> idempotent, aggregate min() is not "sensitive" to repeated
> values.
>
>
>
>>Yes I will. But for ease of discussion and so that anyone here can >>follow and make observations. I propose to take a free standard well >>know database, easy to work with. I suggest to take Northwind for >>ACCESS.
If that choice indicates where the ignorant learned about database theory, that explains a lot.
> Actually that's not a good idea. When discussing this sort of question,
> (or most any sort of question, really) it is best to start with
> *minimal* examples. You want to have the fewest number
> of tables and the fewest number of attributes to demonstrate
> the issue. Since you're quite concerned about generality, there
> is also the issue of whether the minimal example will fully
> generalize. But if you've chosen your minimal example well,
> it often will. There's no need to haul the entirety of Northwind
> into the conversation. So far I've avoided jumping in on the
> conversation because I've been unable to disentagle your
> complicated posts.
Kinda like Dijkstra's polemic against diagrams as self-defeating crutches.
> Anyway, to the extent that I understand your question, the
> answer is either trivial or impossible as the case may be.
> If you have an idempotent aggregate, it's a non-issue.
> If you have a non-idempotent aggregate, and you apply a
> transformation to a relation but wish to know the value
> of the aggregate before the transformation, then either
> your transformation is reversible, in which case you reverse
> the transformation and apply the aggregate, or else
> the transformation is not reversible, in which case it's
> impossible.
>
> If I haven't understood your question correctly, it would
> be nice if you could construct a *minimal* example for
> your question, and we could start from there.
>
>
> Marshall
>
Received on Sat Sep 16 2006 - 05:21:05 CEST