Get multiple GROUP BY results per group, or use separate concatenated table
I am working on an auction web application. Now i have a table with bids, and from this table i want to select the last 10 bids per auction.
Now I know I can get the last bid by using something like:
SELECT bids.id FROM bids WHERE * GROUP BY bids.id ORDER BY bids.created
Now I have read that setting an amount for the GROUP BY results is not an easy thing to do, actually I have found no easy solution, if there is i would like to hear that.
But i have come up with some solutions to tackle this problem, but I am not sure if i am doing this well.
Alternative
The first thing is creating a new table, calling this bids_history. In this table i store a string of the last items.
example:
bids_history
================================================================
auction_id bid_id bidders times
1 20,25,40 user1,user2,user1 time1,time2,time3
I have to store the names and the times too, because I have found no easy way of taking the string used in bid_id(20,25,40), and just using this in a join. This way i can just just join on auction id, and i have the latest result.
Now when there is placed a new bid, these are the steps:
- insert bid into bids get the lastinserteid
- get the bids_history string for this
auction product
- explode the string
- insert new values
- check if there are more than 3
- implode the array, and insert the string again
This all seems to me not a very well solution. I really don't know which way to go. Please keep in mind this is a website with a lot of bidding's, they can g up to 15.000 bidding's per auction item. Maybe because of this amount is GROUPING and ORDERING not a good way to go. Please correct me if I am wrong.
After the auction is over i do clean up the bids table, removing all the bids, and store them in a separate table.
Can someone please help me tackle this problem!
And if you have been, thanks for reading..
EDIT
The tables i use are:
bids
======================
id (prim_key)
aid (auction id)
uid (user id)
cbid (current bid)
created (time created)
======================
auction_products
====================
id (prim_key)
pid (product id)
closetime (time the auction closses)
What i want as the result of the query:
result
===============================================
auction_products.id bids.uid bids.created
2 开发者_JAVA百科 6 time1
2 8 time2
2 10 time3
5 3 time1
5 4 time2
5 9 time3
7 3 time1
7 2 time2
7 1 time3
So that is per auction the latest bids, to choose by number, 3 or 10
Using user variable, and control flow, i end up with that (just replace the <=3
with <=10
if you want the ten auctions) :
SELECT a.*
FROM
(SELECT aid, uid, created FROM bids ORDER BY aid, created DESC) a,
(SELECT @prev:=-1, @count:=1) b
WHERE
CASE WHEN @prev<>a.aid THEN
CASE WHEN @prev:=a.aid THEN
@count:=1
END
ELSE
@count:=@count+1
END <= 3
Why do this in one query?
$sql = "SELECT id FROM auctions ORDER BY created DESC LIMIT 10";
$auctions = array();
while($row = mysql_fetch_assoc(mysql_query($sql)))
$auctions[] = $row['id'];
$auctions = implode(', ', $auctions);
$sql = "SELECT id FROM bids WHERE auction_id IN ($auctions) ORDER BY created LIMIT 10";
// ...
You should obviously handle the case where, e.g. $auctions
is empty, but I think this should work.
EDIT: This is wrong :-)
You will need to use a subquery:
SELECT bids1.id
FROM ( SELECT *
FROM bids AS bids1 LEFT JOIN
bids AS bids2 ON bids1.created < bids2.created
AND bids1.AuctionId = bids2.AuctionId
WHERE bid2.id IS NULL)
ORDER BY bids.created DESC
LIMIT 10
So the subquery performs a left join from bids to itself, pairing each record with all records that have the same auctionId and and a created date that is after its own created date. For the most recent record, there will be no other record with a greater created date, and so that record would not be included in the join, but since we use a Left join, it will be included, with all the bids2 fields being null, hence the WHERE bid2.id IS NULL
statement.
So the sub query has one row per auction, contianing the data from the most recent bid. Then simply select off the top ten using orderby and limit.
If your database engine doesn't support subqueries, you can use a view just as well.
Ok, this one should work:
SELECT bids1.id
FROM bids AS bids1 LEFT JOIN
bids AS bids2 ON bids1.created < bids2.created
AND bids1.AuctionId = bids2.AuctionId
GROUP BY bids1.auctionId, bids1.created
HAVING COUNT(bids2.created) < 9
So, like before, left join bids with itself so we can compare each bid with all the others. Then, group it first by auction (we want the last ten bids per auction) and then by created. Because the left join pairs each bid with all previous bids, we can then count the number of bids2.created per group, which will give us the number of bids occurring before that bid. If this count is < 9 (because the first will have count == 0, it is zero indexed) it is one of the ten most recent bids, and we want to select it.
To select last 10
bids for a given auction, just create a normalized bids
table (1 record per bid) and issue this query:
SELECT bids.id
FROM bids
WHERE auction = ?
ORDER BY
bids.created DESC
LIMIT 10
To select last 10
bids per multiple auctions, use this:
SELECT bo.*
FROM (
SELECT a.id,
COALESCE(
(
SELECT bi.created
FROM bids bi
WHERE bi.auction = a.id
ORDER BY
bi.auction DESC, bi.created DESC, bi.id DESC
LIMIT 1 OFFSET 9
), '01.01.1900'
) AS mcreated
COALESCE(
(
SELECT bi.id
FROM bids bi
WHERE bi.auction = a.id
ORDER BY
bi.auction DESC, bi.created DESC, bi.id DESC
LIMIT 1 OFFSET 9
), 0)
AS mid
FROM auctions a
) q
JOIN bids bo
ON bo.auction >= q.auction
AND bo.auction <= q.auction
AND (bo.created, bo.id) >= (q.mcreated, q.mid)
Create a composite index on bids (auction, created, id)
for this to work fast.
精彩评论