Suppose I have a DB table with three colums: transaction id, customer id, and date (the actual implementation has 30+ columns, but the further details aren't relevant).
I'm displaying this data in a group ordering. A groups contains any transactions with the same customer id, ordered by date.
But I'm also sorting the groups. For the date sort, I take the min/max of the date column. This is easily accomplished with SQL GROUP BY clause and aggregates. So far so good, I thought.
However, a SELECT MAX(date),transaction_id,(...) FROM table GROUP BY customer_id collapses all the rows within a group unto one. Multiple transactions per customer are no longer displayed. Actually the one that is returned is picked at random from within the group. Of course this won't do!
My first stab was to essentially run the same query twice in one SELECT statement, once as a subquery with a GROUP BY clause, and again as the outer query without GROUP BY. Then I LEFT JOIN the query with itself ... which sounds insane, until you realize that this step again duplicates all the rows that collapse due to the GROUP BY clause in the inner query.
As a first "optimization" I hoisted some of the per-group constants (anything in the GROUP BY clause basically) out of the outer query, into the subquery, because the subquery is the one of the two joining tables that changes at lower frequency.
Something like this:
(code may be syntactially unsound, because I've tried to simplify/change identifiers to protect the identity of the database holder)
You see that the inner query SELECTs cust_id and type_code, which are also SELECTed by the outer query, both times from the same database table, only to be able to JOIN inner and outer using these two fields. Again, the only thing this subquery and join accomplish is smearing the single aggregate MAX across all rows that the outer query would already produce by itself.
I find this approach messy, and inefficient.
To restate the goal again, I'm looking for a way to insert, without reducing the number of rows, aggregate functions into each row of a database result.
The database runs on MySQL 5.1.somethingsomething. MySQL-specific extensions are fair game.
Anyone?
I'm displaying this data in a group ordering. A groups contains any transactions with the same customer id, ordered by date.
But I'm also sorting the groups. For the date sort, I take the min/max of the date column. This is easily accomplished with SQL GROUP BY clause and aggregates. So far so good, I thought.
However, a SELECT MAX(date),transaction_id,(...) FROM table GROUP BY customer_id collapses all the rows within a group unto one. Multiple transactions per customer are no longer displayed. Actually the one that is returned is picked at random from within the group. Of course this won't do!
My first stab was to essentially run the same query twice in one SELECT statement, once as a subquery with a GROUP BY clause, and again as the outer query without GROUP BY. Then I LEFT JOIN the query with itself ... which sounds insane, until you realize that this step again duplicates all the rows that collapse due to the GROUP BY clause in the inner query.
As a first "optimization" I hoisted some of the per-group constants (anything in the GROUP BY clause basically) out of the outer query, into the subquery, because the subquery is the one of the two joining tables that changes at lower frequency.
Something like this:
PHP:
$qc="SELECT ang.cust_id,ang.id,ang.datum,pd.name,pd.vorname,ang.type_code,sq.gtstamp".
" FROM".
" ((angebote AS ang LEFT JOIN personendaten AS pd ON pd.ID=ang.cust_id)".
" LEFT JOIN".
" (SELECT UNIX_TIMESTAMP(MAX(datum)) AS gtstamp,cust_id,type_code".
" FROM angebote".
" GROUP BY cust_id,type_code AS sq".
" ON sq.cust_id=ang.cust_id AND sq.type_code=ang.type_code".
" WHERE ".implode(" AND ",array_prepend($filter_crit,'ang.')).
" ORDER BY ".implode(",",$order_crit);You see that the inner query SELECTs cust_id and type_code, which are also SELECTed by the outer query, both times from the same database table, only to be able to JOIN inner and outer using these two fields. Again, the only thing this subquery and join accomplish is smearing the single aggregate MAX across all rows that the outer query would already produce by itself.
I find this approach messy, and inefficient.
To restate the goal again, I'm looking for a way to insert, without reducing the number of rows, aggregate functions into each row of a database result.
The database runs on MySQL 5.1.somethingsomething. MySQL-specific extensions are fair game.
Anyone?
