1

I'm using Oracle 11g.
Assuming multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit.

With this table:
datetime_id | user_id | place_id

and I want to get
user_id | first_place_visited_id | second_place_visited_id | third_place_visited_id.

I tried this:

select * row_number() over ( partition by user_id,place_id 
                                   order by datetime_id desc ) rn
          from data_table  

Before I pivot the result to get one raw for each user, I face a problem.
Let's assume that some user has this kind of data

datetime_id    | user_id | place_id  
   1           | 1       | 1      
   2           | 1       | 1   
   3           | 1       | 2   
   4           | 1       | 2  
   5           | 1       | 1   
   6           | 1       | 2  
   7           | 1       | 2  
   8           | 1       | 3  

The analytical function here will partition the data into windows as following:

   date_time_id| user_id | place_id   rn

1st window:

   1           | 1       | 1          1
   2           | 1       | 1          2
   5           | 1       | 1          3

2nd window:

   3           | 1       | 2          1
   4           | 1       | 2          2
   6           | 1       | 2          3
   7           | 1       | 2          4

3rd window:

   8           | 1       | 3          1  

How can I apply a condition such as Having in group by clause, such that I can force datetime_id to have consecutive values inside each window, i.e I want something like this:

   date_time_id| user_id | place_id   rn  

1st window:

   1           | 1       | 1          1
   2           | 1       | 1          2

2nd window:

   3           | 1       | 2          1
   4           | 1       | 2          2  

3rd window:

   5           | 1       | 1          1  

4th window:

   6           | 1       | 2          1
   7           | 1       | 2          2

5th window:

   8           | 1       | 3          1  
  • 1
    It's not entirely clear what your desired output is. Do multiple consecutive entries (sorted by datetime) with the same place_id represent a single visit? And, do you want three unique places? Assuming datetime_id is generated in chronological order, by default, I would assume that the 3 most recently visited places are (in order): 3, 2, 2. If you want unique places, or if consecutive entries with the same date are a single visit: 3, 2, 1. In any case, I don't think you want to partition by place_id - that's what you need row numbers for. – RDFozz Jul 17 '18 at 19:34
  • I edited the question,"user_id | first_place_visited_id | second_place_visited_id | third_place_visited_id.", I want the first three visited places, and for each date there is only one visited place. thus in my example I want to get those places: 1,2,1 – Fadi Bakoura Jul 18 '18 at 3:43
  • 1
    The problem of detecting groups of consecutive rows with common properties belongs to a class of problem called gaps and islands, which has solutions applicable to more than one platform, including those applicable to Oracle. Feel free to explore the questions in the link provided and maybe you'll be able to come up with a solution to your specific problem on your own, in which case feel free to post your own answer. – Andriy M Jul 18 '18 at 6:03
  • 1
    On a different note, it's a little confusing that you are using the term "count" and yet there's no use of the COUNT function in your case, nor is there any mentioning of row counting. Given that your example is using the ROW_NUMBER function, I'm guessing you are using the word "count" in the sense "enumerate" or "assign row numbers", is that correct? – Andriy M Jul 18 '18 at 6:06
  • Thank you @AndriyM, I was able to solve the problem using gaps and islands approach. As for the term count, you are right, it means I want to enumerate them. – Fadi Bakoura Jul 18 '18 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.