Geek asks and answers

SELECTING THE NEWEST RECORDS FOR EACH ITEM IN A BIG DATASET

So, I have a table with about 1.5 million rows in it, looking something like this:

name   | time       | data1 | data2  
--------------------------------------
 93-15 | 1337348782 |   11  | 60.791 
 92-02 | 1337348783 |   11  | 62.584 
 92-02 | 1337348056 |   11  | 63.281
 93-15 | 1337348068 |    8  | 65.849
 92-02 | 1337348117 |   11  | 63.271 
 93-15 | 1337348129 |    8  | 65.849 
 92-02 | 1337348176 |   10  | 63.258 
 93-15 | 1337348188 |    8  | 65.849 
 92-02 | 1337348238 |   10  | 63.245 
 93-15 | 1337348248 |    8  | 65.849

... they correspond to updates of historical status from what needs to be controlled. Now, what I would like to do is find the current status if each block.

It was not difficult to find similar questions here in stackoverflow and extrapolate from the results, I came up with this query:

SELECT * FROM vehicles v
  JOIN ( SELECT  MAX(time) as max, name
    FROM vehicles
    GROUP BY name)
  m_v
ON (v.time = m_v.max AND v.name = m_v.name);

but, seeing that I have approximately 1.5 million rows (and counting), is there another approach that allows a faster query to be performed?

+3
source share
1 answer
WITH
  sequenced_data
AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC) AS sequence_id,
    *
  FROM
    vehicles
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

A coverage index on (name, time).


EDIT: , ..

PostgreSQL , . some_function() OVER (PARTITION BY some_fields ORDER BY some_fields).

ROW_NUMBER() OVER (PARTITION BY name ORDER BY time DESC).

ROW_NUMBER() . 1 to n n.

PARTITION BY name , . name //, ROW_NUMBER() 1 //.

ORDER BY time DESC // time , ROW_NUMBER().

...

 name  | time       | data1 | data2  | row_number
--------------------------------------------------

 92-02 | 1337348783 |   11  | 62.584 | 1
 92-02 | 1337348238 |   10  | 63.245 | 2
 92-02 | 1337348176 |   10  | 63.258 | 3
 92-02 | 1337348117 |   11  | 63.271 | 4
 92-02 | 1337348056 |   11  | 63.281 | 5

 93-15 | 1337348782 |   11  | 60.791 | 1
 93-15 | 1337348248 |    8  | 65.849 | 2
 93-15 | 1337348188 |    8  | 65.849 | 3
 93-15 | 1337348129 |    8  | 65.849 | 4
 93-15 | 1337348068 |    8  | 65.849 | 5

time DESC, time, name/window/partition row_number 1.

(name, time) , , . , ROW_NUMBER() ; time ROW_NUMBER() = 1, , name.

+6

More articles:


All Articles