Re: Real world issue: How to "split" queries in presence of record replication and replication sensitive aggregate functions ?

From: <pamelafluente_at_libero.it>
Date: 16 Sep 2006 08:36:39 -0700
Message-ID: <1158420999.000849.10290_at_d34g2000cwd.googlegroups.com>

Marshall ha scritto:
. I'm afraid
> you'll need to reread the wikipedia entry with this in mind.

Thanks, I will.
>
> I'm sorry, but I'm not willing to work through your overly
> complicated example to try to figure out how to answer
> your question. If you can produce a minimal example,
> I would look at it. Maybe something with one table
> and two column, and maybe two or three rows.

Ok I will produce a couple of simple examples using Northwind, which is quite common so that we do not have to do a scheme ex novo.

I will keep simple (Just 1 table is useless, because in such a case no value duplication can occur) .

For table relationships see: Northwind

Pathological examples (plain join would give wrong reports)

[report may make *no sense*: I am just focusing on causing value duplication within the join. Function can be substituted with any
other function that is influenced by value "duplication", ok say not IDEMPOTENT] the goal is to deal consistently with any report request, even if it may not meaningful as to its practical meaning.

Report 1

---

       Products.ProductID
       [Order Details].Quantity
       Orders.OrderID
       avg([Order Details].UnitPrice)
       count(Orders.Freight)

where
Function1 = avg([Order Details].UnitPrice) Function2 = count(Orders.Freight)

Report 2

---

Products.ProductName | Function1 | [Order Details].UnitPrice

where
Function1 = sum(Products.UnitsInStock)

queries which give possible "wrong" result (function computation may be wrong) :

SELECT

       Products.ProductID AS "ProductID",
       [Order Details].Quantity AS "Quantity",
       Orders.OrderID AS "OrderID",
       avg([Order Details].UnitPrice) AS "Function1",
       count(Orders.Freight) AS "Function2"
FROM
       [Order Details],
       Products,
       Orders
WHERE
       Products.ProductID = [Order Details].ProductID AND
       Orders.OrderID = [Order Details].OrderID
GROUP BY
       Products.ProductID,
       [Order Details].Quantity,
       Orders.OrderID


SELECT
       Products.ProductName AS "ProductName",
       sum(Products.UnitsInStock) AS "Function1",
       [Order Details].UnitPrice AS "UnitPrice"
FROM
       [Order Details]
        INNER JOIN
       Products
        ON Products.ProductID = [Order Details].ProductID
GROUP BY
       Products.ProductName,
       [Order Details].UnitPrice

>
> Marshall
Received on Sat Sep 16 2006 - 17:36:39 CEST