2

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
);
Dmitry Sokolov
  • 1,303
  • 1
  • 17
  • 29
  • Can you please explain in more details how did you get to have this numbers 2, 2 and 3 next to this dates. Ok, I understand this: give me dates that are not bigger than next date than 1 day but this numbers I do not understand. Cheers! – VBoka Nov 25 '19 at 18:11
  • Did you mean 1 year, not 1 day? That would make the results make more sense... though the counts are off there. Yeah, more explanation would really help. – Shawn Nov 25 '19 at 18:18
  • I believe I get it now: first and second row have the difference of one day and then third one does not. So from first three rows OP wants to show first row with number 2 because there are two dates inside of one day difference... Like last three rows are the reason for number 3... – VBoka Nov 25 '19 at 18:22
  • I tried this solution https://stackoverflow.com/a/23200488/5558021 but get wrong numbers (0, 3, 2 instead 2, 2, 3) – Dmitry Sokolov Nov 25 '19 at 18:29
  • @DmitrySokolov please edit your question and add that query that you tried. Cheers! – VBoka Nov 25 '19 at 18:31
  • editing has been done – Dmitry Sokolov Nov 25 '19 at 18:39

1 Answers1

2

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

VBoka
  • 8,995
  • 3
  • 16
  • 24