efficient join needed against a virtual table used multiple times in a subquery

I am trying to run a query similar to this one:

SELECT s.custno, 
       s.prodno, 
       IF(daycode = 1, (SELECT avg(sell) 
                        FROM   sales ss 
                               JOIN (SELECT DISTINCT `prodno` 
                                     FROM   `familycode` 
                                     WHERE  prodfamily = 101) f2 
                                 ON f2.prodno = ss.prodno 
                        WHERE  ss.CUSTNO = 800 
                               AND ss.weekno = s.weekno - 1), (SELECT avg(sell) 
                                                               FROM   sales ss 
                                                                      JOIN (SELECT DISTINCT `prodno`
                                                                            FROM   `familycode`
                                                                            WHERE  prodfam = 101) f3
                                                                        ON f3.prodno = ss.prodno
                                                               WHERE  ss.CUSTNO = 800 
                                                                      AND ss.weekno = s.weekno)) AS weekavg
FROM   sales s 
       JOIN product p 
         ON p.prodno = s.prodno 
       JOIN (SELECT DISTINCT `prodno` 
             FROM   `familycode` 
             WHERE  prodfamily = 101) f 
         ON f.prodno = s.prodno 
WHERE  s.CUSTNO = 800 
ORDER  BY ardate8n ASC, 
          s.CUSTNO, 
          s.prodno 

In this query I would like the get the products week average based on the daycode param for a product family.

A product belongs to a product family.

• If daycode = 1 I want to get for the same product family the average of the previous week.
• else I want to get average on the current week for the same product family.

As you see f,f2,f3 are similar virtual ta开发者_Go百科bles, that helps us to link against existing products based on product family.

How could this query be rewritten so to not compute f2, f3 tables, which is a very slow process.

I don't have a mysql environment available to me atm, this is from memory and not run against some mocked up tables.

first, i would try to stick to just joins, as the only filtering done against familycode is a 'distinct' marker. I do not know the data in familycode, so compare a working slow query to the new one and watch for a 'sales spike' from duplicates!

FROM   sales ss 
JOIN (SELECT DISTINCT `prodno` 
FROM   `familycode` 
 WHERE  prodfamily = 101) f2 
ON f2.prodno = ss.prodno 
WHERE  ss.CUSTNO = 800 

becomes

 FROM   sales ss 
JOIN familycode f2 
ON f2.prodno = ss.prodno 
WHERE  ss.CUSTNO = 800 and f2.prodfamily = 101

My second approach, and if you get duplicates sales spike issue, would be to try an 'exists' clause instead of a 'distinct' clause. that solution would look like this:

FROM   sales ss 
    JOIN familycode f2 
    ON f2.prodno = ss.prodno 
    WHERE  ss.CUSTNO = 800 and exists (select 1 from familycode f2 where f2.prodno = ss.prodno and f2.prodfamily= 101)

I guess I'm a bit confused, but here's my go at it. It would have been helpful if you showed some sample output with your question. I also don't see any correlation with ardate8n nor do I completely understand how ss.weekno = s.weekno - 1 works for you. I hope this helps somehow. If it isn't working towards your goals please expand you answer to include expected output, table definitions, and what exactly you are trying to accomplish.

Basically it sounds like you have a sales, product, and productfamily table and need to report on the average sales of all products in a product family during a given week (whether it is the current week or the previous one). If I understand this correctly, the following SELECT should work out and be pretty fast, however it will get you all products and every week.

SELECT p.prodno, f.familycode, s.custno, s.weekno, AVG( s.sell ) as sales_avg
    FROM sale s
    JOIN product p USING(prodno)
    JOIN productfamily f USING(prodno)
GROUP BY s.weekno, f.familycode, p.prodno
ORDER BY s.weekno

Now, turn this into a view.

CREATE VIEW "main"."view_weekly_product_sales_avg" AS 
SELECT p.prodno, f.familycode, s.custno, s.weekno, AVG( s.sell ) as sales_avg
    FROM sale s
    JOIN prod p USING(prodno)
    JOIN prodfamily f USING(prodno)
GROUP BY s.weekno, f.familycode, p.prodno
ORDER BY s.weekno;

Then query the view/report how you need. From your example, I think you would query it like this:

SELECT prodno, custno, sales_avg
    FROM view_weekly_product_sales_avg
WHERE familycode = 1 AND custno = 1 AND weekno = 1

For me this reports out the following table:

prodno  custno  sales_avg
1       1       37.3333333333333
2       1       12.0
3       1       78.0
8       1       12.0