Select with subquery to pick up previous record

Question

Select with subquery to pick up previous record

Navigation

#1 by (0 votes)

2

I have a structure of records of user readings by reference. According to data below.

SELECT L.LeituraID, L.UsuarioID, L.Referencia, L.Leitura
FROM Leituras AS L

|-----------|-----------|------------|---------|
| LeituraID | UsuarioID | Referencia | Leitura |
|-----------|-----------|------------|---------|
| 1         | 1         | 01/01/2018 | 52      |
| 2         | 2         | 01/01/2018 | 157     |
| 3         | 3         | 01/01/2018 | 73      |
| 4         | 1         | 01/02/2018 | 63      |
| 5         | 2         | 01/02/2018 | 171     |
| 6         | 3         | 01/02/2018 | 89      |
|-----------|-----------|------------|---------|

I need to get the user's read data by a reference date and make a consumption calculation.

To calculate the consumption, it is necessary to have the information of the previous reading.

I set up the query by bringing the current reading data and a subquery to get the previous reading.

The problem is that this search with the subquery is taking too long.

Is there any way to get this earlier reading faster?

WITH cteLeituras AS (
    SELECT 
        L.LeituraID
        , L.UsuarioID
        , L.Referencia
        , L.Leitura
        (
            SELECT TOP 1 LA.Leitura 
            From Leituras AS LA 
            WHERE LA.UsuarioID = L.UsuarioID AND LA.Referencia < L.Referencia
            ORDER BY LA.Referencia DESC
        ) AS LeituraAnterior
    FROM Leituras AS L
    WHERE L.Referencia = '2018-02-01'
), cteLeiturasConsumo AS (
    SELECT 
        L.LeituraID
        , L.UsuarioID
        , L.Referencia
        , L.Leitura
        , L.LeituraAnterior
        , (L.Leitura - L.LeituraAnterior) AS Consumo
    FROM cteLeituras AS L 
)

|-----------|-----------|------------|---------|-----------------|---------|
| LeituraID | UsuarioID | Referencia | Leitura | LeituraAnterior | Consumo |
|-----------|-----------|------------|---------|-----------------|---------|
| 4         | 1         | 01/02/2018 | 63      | 52              | 11      |
| 5         | 2         | 01/02/2018 | 171     | 157             | 14      |
| 6         | 3         | 01/02/2018 | 89      | 73              | 16      |
|-----------|-----------|------------|---------|-----------------|---------|

sql desempenho sql-server query

asked by anonymous 21.11.2018 / 18:28

1 answer

Difficulty with a loop "for" in C [closed] Why use getElementById if the id is in the window?

score 0 · Accepted Answer

You can use the ROW_NUMBER() partitioned by usuarioid to get the order of the readings and perform the calculation:

WITH ordenado AS (
  SELECT l.leituraid,
      l.usuarioid,
      l.referencia,
      l.leitura,
      ROW_NUMBER() OVER(PARTITION BY l.usuarioid ORDER BY l.referencia) AS ordem
  FROM leituras l
)
SELECT atual.*,
      (atual.leitura - ISNULL(anterior.leitura, 0)) AS consumo
  FROM ordenado atual
      LEFT JOIN ordernado anterior ON anterior.usuarioid = atual.usuarioid
                                  AND anterior.ordem = atual.ordem - 1
WHERE atual.referencia = '2018-02-01'

ROW_NUMBER()

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.