Search code examples
sqlsqlitewindow-functions

SQlite select only rows with defined difference between them


for example I have following rows

'1982-01-10T00:00:00Z'
'1982-01-11T00:00:00Z'
'1982-01-14T00:00:00Z'
'1985-01-16T00:00:00Z'
'1985-01-17T00:00:00Z'
'1985-02-12T00:00:00Z'
'1987-01-11T00:00:00Z'
'1987-01-12T00:00:00Z'
'1987-01-13T00:00:00Z'

I need only first row with difference between first and second rows not greeter than 1 day ,also I want getting count of rows with such difference, for this sample I want to get follow:

'1982-01-10T00:00:00Z', 2
'1985-01-16T00:00:00Z', 2
'1987-01-11T00:00:00Z', 3

Any idea?

I have tried query, but with wrong result:

SELECT utc_timestamp, utc_timestamp - LAG (utc_timestamp, 1, utc_timestamp) OVER (
        ORDER BY utc_timestamp
    ) difference
FROM (
    SELECT utc_timestamp, AVG(GB_temperature) as avgt
    FROM weather_data
    GROUP BY strftime('%Y-%m-%d', utc_timestamp)
    HAVING avgt < -4
);

Solution

  • Well, this looks ok but I believe it can be done in less code lines...

    select min(date_before), count(date_c)+1, month, year from
    (select strftime('%d',date_c) - lag(strftime('%d',date_c)) over (order by date_c) diff
           , strftime('%d',date_c) day
           , lag(strftime('%d', date_c)) over (order by date_c) day_before
           , strftime('%m', date_c) month
           ,  lag(strftime('%m', date_c)) over (order by date_c) m_before              
           , strftime('%Y', date_c) year
           , lag(strftime('%Y', date_c)) over (order by date_c) y_before
           , date_c
           , lag(date_c) over (order by date_c) date_before
    from testTable
    order by date_c)
    where diff = 1
    and month = m_before
    and year = y_before
    group by  month, year;
    

    Here is the DEMO