 | |
#1
 
| |||
| |||
|
Ranking by group -
04-13-2009
, 10:36 AM
#2
| |||
| |||
|
|
I adopted a ranking approach which I found here (http:// allenbrowne.com/ranking.html) However, I am doing rankings by product and by working week. The code is: A) Core query (xx_test2) SELECT weeks, product_name, website, Min(price) AS minprice FROM product_history GROUP BY weeks, product_name, website; B) Sub query SELECT weeks, product_name, website, minprice, (SELECT count(website) AS howmany FROM xx_test2 AS dupe WHERE ((dupe.minprice<xx_test2.minprice) AND (dupe.weeks=xx_test2.weeks) AND (dupe.product_name = xx_test2.product_name)))+1 AS rank FROM xx_test2; The trouble is that the core table has currently 80k records and growing, and the report is already very very slow (half an hour and then i decided to quit it). Does anyone have any suggestions to improve performance? Would it make any difference looping the query to do an append and changing the product and week parameters? I can't imagine it would. The other alternative is that I keep a permanent rank table and append the weekly results each week. Or is this query completely wrong! Ta ALJ |
WHERE ((dupe.minprice<xx_test2.minprice)
Would it ever be less than minprice of test2?
Are you printing a report? 80K records is a lot to print.
In the core query you group on the website (as well as product and
week). Do you really need to group on the website as well?
Instead of ranking, couldn't you sort on
weeks, product_name, price, website
and achieve the same thing? When printing a report, use a running sum
over the group to show the ranking?
#3
| |||
| |||
|
|
Astley Le Jasper wrote: I adopted a ranking approach which I found here (http:// allenbrowne.com/ranking.html) However, I am doing rankings by product and by working week. The code is: A) Core query (xx_test2) SELECT weeks, product_name, website, Min(price) AS minprice FROM product_history GROUP BY weeks, product_name, website; B) Sub query SELECT weeks, product_name, website, minprice, * (SELECT count(website) AS howmany * FROM xx_test2 AS dupe * WHERE ((dupe.minprice<xx_test2.minprice) * AND (dupe.weeks=xx_test2.weeks) * AND (dupe.product_name = xx_test2.product_name)))+1 AS rank FROM xx_test2; The trouble is that the core table has currently 80k records and growing, and the report is already very very slow (half an hour and then i decided to quit it). Does anyone have any suggestions to improve performance? Would it make any difference looping the query to do an append and changing the product and week parameters? I can't imagine it would. The other alternative is that I keep a permanent rank table and append the weekly results each week. Or is this query completely wrong! Ta ALJ You have * * * * WHERE ((dupe.minprice<xx_test2.minprice) Would it ever be less than minprice of test2? Are you printing a report? *80K records is a lot to print. In the core query you group on the website (as well as product and week). *Do you really need to group on the website as well? Instead of ranking, couldn't you sort on * * * * weeks, product_name, price, website and achieve the same thing? *When printing a report, use a running sum over the group to show the ranking? |
- The 'where' element is part of the ranking, counting how many
records are less than the current record.
- No, I'm not printing a report. I'm going to use it as the basis of
further analysis, charting, trends etc.
- No, unfortunately just ordering isn't going to cut it for the sort
of analysis we want to do. We're trying to check which are the current
and historic cheapest websites.
ALJ
#4
| ||||
| ||||
|
|
On 13 Apr, 19:14, Salad <o... (AT) vinegar (DOT) com> wrote: Astley Le Jasper wrote: I adopted a ranking approach which I found here (http:// allenbrowne.com/ranking.html) However, I am doing rankings by product and by working week. The code is: A) Core query (xx_test2) SELECT weeks, product_name, website, Min(price) AS minprice FROM product_history GROUP BY weeks, product_name, website; B) Sub query SELECT weeks, product_name, website, minprice, (SELECT count(website) AS howmany FROM xx_test2 AS dupe WHERE ((dupe.minprice<xx_test2.minprice) AND (dupe.weeks=xx_test2.weeks) AND (dupe.product_name = xx_test2.product_name)))+1 AS rank FROM xx_test2; The trouble is that the core table has currently 80k records and growing, and the report is already very very slow (half an hour and then i decided to quit it). Does anyone have any suggestions to improve performance? Would it make any difference looping the query to do an append and changing the product and week parameters? I can't imagine it would. The other alternative is that I keep a permanent rank table and append the weekly results each week. Or is this query completely wrong! Ta ALJ You have WHERE ((dupe.minprice<xx_test2.minprice) Would it ever be less than minprice of test2? Are you printing a report? 80K records is a lot to print. In the core query you group on the website (as well as product and week). Do you really need to group on the website as well? Instead of ranking, couldn't you sort on weeks, product_name, price, website and achieve the same thing? When printing a report, use a running sum over the group to show the ranking? Hi Salad, - The 'where' element is part of the ranking, counting how many records are less than the current record. |
reason.
I see absolutely no reason to group by website in your core query.
Quote:
|
- No, I'm not printing a report. I'm going to use it as the basis of further analysis, charting, trends etc. |
Do you have to recompute the ranking for the entire database each time
you want to do some analysis?
Quote:
|
- No, unfortunately just ordering isn't going to cut it for the sort of analysis we want to do. We're trying to check which are the current and historic cheapest websites. |
Well, I've seen in the past something like
dcount("*","table","ID <= " & [ID]) + 1
in the past.
What happens if you have a query1 that gets the weeks, product, and min
price.
The create query2. It links query1 to the table on weeks and product
and counts the number of price-min records less than the records price
-min. I wouldn't know how you'd rank ties as you seem to have no id in
your table.
Quote:
|
ALJ |
#5
| ||||||
| ||||||
|
|
Having dupe minimum < the test minimum doesn't seem right for some odd reason. |
Quote:
|
I see absolutely no reason to group by website in your core query. True but I need the website and the complains if it isn't summarised |
Quote:
|
Are you stuffing the ranking to a field? Yes |
Quote:
|
Do you have to recompute the ranking for the entire database each time you want to do some analysis? I was going to use this to create a temp table when the database |
However, I'm thinking that if I can't do this faster I'm giong to have
to do incremental appends when new data comes in. Still, even getting
the base ranked table is taking ages.
Quote:
|
What happens if you have a query1 that gets the weeks, product, and min price. That's what's it's doing at the moment with the core query |
Quote:
|
The create query2. It links query1 to the table on weeks and product and counts the number of price-min records less than the records price-min. I wouldn't know how you'd rank ties as you seem to have no id in your table. The expression that generates 'rank' bundles those records that are |
Gaaaahh! You would have thought this was something that would be built-
in!
#6
| |||
| |||
|
|
Are you stuffing the ranking to a field? Yes |
the record number. Don't know if AbsolutePosition is available in a
query but I'd think scanning 80K records would be quick.
Set rst = ...
with rst
Do while not eof
.Edit
...
.Update
Loop
end with
Quote:
|
Do you have to recompute the ranking for the entire database each time you want to do some analysis? I was going to use this to create a temp table when the database opens. It would pick up any changes to the history if they were made. However, I'm thinking that if I can't do this faster I'm giong to have to do incremental appends when new data comes in. Still, even getting the base ranked table is taking ages. What happens if you have a query1 that gets the weeks, product, and min price. That's what's it's doing at the moment with the core query |
Quote:
|
The create query2. It links query1 to the table on weeks and product and counts the number of price-min records less than the records price-min. I wouldn't know how you'd rank ties as you seem to have no id in your table. The expression that generates 'rank' bundles those records that are related (by week and product) together so only those are counted. But the ranking is on websites. That's not required in the first query. Gaaaahh! You would have thought this was something that would be built- in! |
#7
| |||
| |||
|
|
Astley Le Jasper wrote: Are you stuffing the ranking to a field? Yes Maybe sort the file in its order and then use AbsolutePosition to get the record number. *Don't know if AbsolutePosition is available in a query but I'd think scanning 80K records would be quick. * * * * Set rst = ... * * * * with rst * * * * * * * * Do while not eof * * * * * * * * .Edit * * * * * * * * ... * * * * * * * * .Update * * * * * * * * Loop * * * * end with Do you have to recompute the ranking for the entire database each time you want to do some analysis? I was going to use this to create a temp table when the database opens. It would pick up any changes to the history if they were made. However, I'm thinking that if I can't do this faster I'm giong to have to do incremental appends when new data comes in. Still, even getting the base ranked table is taking ages. What happens if you have a query1 that gets the weeks, product, andmin price. That's what's it's doing at the moment with the core query Notice I didn't have website. The create query2. *It links query1 to the table on weeks and product and counts the number of price-min records less than the records price-min. *I wouldn't know how you'd rank ties as you seem to have no id in your table. The expression that generates 'rank' bundles those records that are related (by week and product) together so only those are counted. But the ranking is on websites. *That's not required in the first query.. Gaaaahh! You would have thought this was something that would be built- in! Wish it was. |
ALJ
#8
| |||
| |||
|
|
On Apr 14, 3:41 pm, Salad <o... (AT) vinegar (DOT) com> wrote: Astley Le Jasper wrote: Are you stuffing the ranking to a field? Yes Maybe sort the file in its order and then use AbsolutePosition to get the record number. Don't know if AbsolutePosition is available in a query but I'd think scanning 80K records would be quick. Set rst = ... with rst Do while not eof .Edit ... .Update Loop end with Do you have to recompute the ranking for the entire database each time you want to do some analysis? I was going to use this to create a temp table when the database opens. It would pick up any changes to the history if they were made. However, I'm thinking that if I can't do this faster I'm giong to have to do incremental appends when new data comes in. Still, even getting the base ranked table is taking ages. What happens if you have a query1 that gets the weeks, product, and min price. That's what's it's doing at the moment with the core query Notice I didn't have website. The create query2. It links query1 to the table on weeks and product and counts the number of price-min records less than the records price-min. I wouldn't know how you'd rank ties as you seem to have no id in your table. The expression that generates 'rank' bundles those records that are related (by week and product) together so only those are counted. But the ranking is on websites. That's not required in the first query. Gaaaahh! You would have thought this was something that would be built- in! Wish it was. Thanks salad. I'll have a look at that loop. ALJ |
calc...no matter what you do. It might be fastest to order the table
first then scan thru the records. Your break would be on week/product
and rank the website based on the price.
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.