DATE | ADRESS |
---|---|
2010-09-02 | ENGLAND |
2010-09-03 | ENGLAND |
2010-09-04 | ENGLAND |
2010-09-05 | ENGLAND |
2010-09-06 | GERMANY |
2010-09-07 | GERMANY |
2010-09-08 | GERMANY |
2011-09-03 | ENGLAND |
2012-09-02 | ENGLAND |
2013-09-03 | ENGLAND |
I have a table like this and what I want to do, I want the result I want to get, how can I do it?
DATE | ADRESS |
---|---|
2010-09-02 - 2010-09-05 | ENGLAND |
2010-09-06 - 2010-09-08 | GERMANY |
2011-09-03 - 2013-09-03 | ENGLAND |
3条答案
按热度按时间mbjcgjjk1#
You have a gaps and islands problem here, you could use the difference between two row_numbers approach to solving it, try the following:
see demo
jaxagkaj2#
you can try this
roejwanj3#
As already mentioned before this is a gaps and islands problem.
This is an other way to solve it :
Lag() is a window function that allows you to look back a number of rows and access data of that row from the current row.
Then we check by a
CASE WHEN
clause where the records have been broken.Demo here