Min and Max from same subquery-CodePudding

If a query having a subselect for the highest money payed and the lowest money payed in example:

SELECT p.firstname, 
       p.lastname, 
       (SELECT MAX(pmt.amount) 
          FROM Payment pmt 
         WHERE pmt.person.id = p.id)  maxAmount,
       (SELECT MIN(pmt.amount) 
          FROM Payment pmt 
         WHERE pmt.person.id = p.id)  minAmount,
  FROM Person p

We need to write two subqueries. This have a bad performance on huge databases.

Any solutions in plain JPQL?

CodePudding user response：

If you have a huge database this problem probably should be addressed by tweaking the schema design. Most RDBMS would have to do a full table scan on Payment table to find both minimum and maximum amount value. You can check this by looking at EXPLAIN output for your current query.

Since you are concerned with the query speed you can denormalize the schema and store the maxAmount and minAmount columns in Person table or create a new PersonMinMaxPayment table. You can then update these when new row is inserted into Payment table e.g. by declaring @PrePersist or creating a trigger.

There are also few more possibilities if your system can be eventually consistent. You can recalculate these values with a delay e.g. as a once a day batch job.

CodePudding user response：

I used this:

   SELECT p.firstname,
          p.lastname, 
          MAX(pmt.amount),
          MIN(pmt.amount)
     FROM Person p
LEFT JOIN p.payments pmt
    GROUP BY p.firstname, p.lastname