Amit Amit - 6 months ago 14
SQL Question

SQL - get last row in date range

I have a table containing:
Balance, Client_ID, Date

This table has ~25 Million rows - Most days, a service executes and creates a new row for each client, with today's date, and balance of the client.

Inside a date range, lets say 01/01/2016 to 12/05/2016, I need to get the first and last row.

*the service does not run every day, so doing Date = 12/05/2016 will not work. If today's balance is equal to yesterday's balance, there is no row inserted (saves me about 90% of the data, which if I calculate correctly, should be 300 Million rows)

To do such, I run these two queries:
Get the first date:

6.9433851242065 seconds


SELECT * FROM (SELECT * FROM daily
WHERE TIME >= '01/01/2016' AND TIME < '13/05/2016') dates
GROUP BY Client_ID


Get the last date:
32.034277915955 seconds


SELECT * FROM (SELECT * FROM daily
WHERE TIME >= '01/01/2016' AND TIME < '13/05/2016'
ORDER BY Date DESC) dates
GROUP BY Client_ID


The first query has no order, because rows are inserted always in the right order, by the service mentioned above - and such is much faster. (7/32)

How can I make both queries faster, or at least the second one?

Query description:


  • Get the row where the date is the first date after 01/01/2016

  • Get the row where the date is the last date before 13/05/2016



EDIT: The checked answer gives me the following:

ASC and DESC are mine, 'combined' is the suggested answer





  • dates_ASC: 33.300458192825

  • dates_DESC: 8.9232740402222

  • dates_combined: 8.4357199668884







  • dates_ASC: 5.4825110435486

  • dates_DESC: 10.173403978348

  • dates_combined: 2.7024359703064







  • dates_ASC: 15.090759038925

  • dates_DESC: 29.375104904175

  • dates_combined: 3.2885720729828


Answer

Pick each client's min and max time in a derived table. Join with that table:

select *
from daily d1
  join (select Client_ID, max(TIME) as maxtime, min(TIME) as mintime
        from daily
        WHERE TIME >= '01/01/2016' AND TIME < '13/05/2016'
        group by Client_ID) d2
 on d1.Client_ID = d2.Client_ID and d1.TIME in (d2.mintime, d2.maxtime)