PostgreSQL window function: row_number () over (partition order on col2)

Question

The following result set is derived from an SQL query with multiple joins and a join. The SQL query already groups the rows by date and game. I need a column to describe the number of attempts in the game, divided by the date column.

Username Game ID Date johndoe1 Game_1 100 7/22/14 1:52 AM johndoe1 Game_1 100 7/22/14 1:52 AM johndoe1 Game_1 100 7/22/14 1:52 AM johndoe1 Game_1 100 7/22/14 1:52 AM johndoe1 Game_1 121 7/22/14 1:56 AM johndoe1 Game_1 121 7/22/14 1:56 AM johndoe1 Game_1 121 7/22/14 1:56 AM johndoe1 Game_1 121 7/22/14 1:56 AM johndoe1 Game_1 121 7/22/14 1:56 AM johndoe1 Game_1 130 7/22/14 1:59 AM johndoe1 Game_1 130 7/22/14 1:59 AM johndoe1 Game_1 130 7/22/14 1:59 AM johndoe1 Game_1 130 7/22/14 1:59 AM johndoe1 Game_1 130 7/22/14 1:59 AM johndoe1 Game_1 200 7/22/14 2:54 AM johndoe1 Game_1 200 7/22/14 2:54 AM johndoe1 Game_1 200 7/22/14 2:54 AM johndoe1 Game_1 200 7/22/14 2:54 AM johndoe1 Game_1 210 7/22/14 3:54 AM johndoe1 Game_1 210 7/22/14 3:54 AM johndoe1 Game_1 210 7/22/14 3:54 AM johndoe1 Game_1 210 7/22/14 3:54 AM 

I have the following sql query that lists the lines in a section, but not entirely correct, since I want to count the instances of this game based on the date and the game. In this case, johndoe1 tried to break into timestamps five times in Game_1.

This query returns the result set below

 select * , row_number() over (partition by ct."date" order by ct."date") as "Attempts" from csv_temp as ct Username Game ID Date Attempts (Desired Attempts col.) johndoe1 Game_1 100 7/22/14 1:52 AM 1 1 johndoe1 Game_1 100 7/22/14 1:52 AM 2 1 johndoe1 Game_1 100 7/22/14 1:52 AM 3 1 johndoe1 Game_1 100 7/22/14 1:52 AM 4 1 johndoe1 Game_1 121 7/22/14 1:56 AM 1 2 johndoe1 Game_1 121 7/22/14 1:56 AM 2 2 johndoe1 Game_1 121 7/22/14 1:56 AM 3 2 johndoe1 Game_1 121 7/22/14 1:56 AM 4 2 johndoe1 Game_1 121 7/22/14 1:56 AM 5 2 johndoe1 Game_1 130 7/22/14 1:59 AM 1 3 johndoe1 Game_1 130 7/22/14 1:59 AM 2 3 johndoe1 Game_1 130 7/22/14 1:59 AM 3 3 johndoe1 Game_1 130 7/22/14 1:59 AM 4 3 johndoe1 Game_1 130 7/22/14 1:59 AM 5 3 johndoe1 Game_1 200 7/22/14 2:54 AM 1 4 johndoe1 Game_1 200 7/22/14 2:54 AM 2 4 johndoe1 Game_1 200 7/22/14 2:54 AM 3 4 johndoe1 Game_1 200 7/22/14 2:54 AM 4 4 johndoe1 Game_1 210 7/22/14 3:54 AM 1 5 johndoe1 Game_1 210 7/22/14 3:54 AM 2 5 johndoe1 Game_1 210 7/22/14 3:54 AM 3 5 johndoe1 Game_1 210 7/22/14 3:54 AM 4 5 

Any pointers would be very helpful.

Answer 1

Consider partition by similar to the fields that you would specify group by , and then when the partition values ​​change, the window start function restarts in step 1

EDIT as indicated by a_horse_with_no_name, for this need we need dense_rank() as opposed to row_number() rank() or dense_rank() repeat the assigned numbers. row_number() must be a different value for each row in the section. The difference between rank() and dense_rank() is that the latter does not miss numbers.

For your request, try:

 dense_rank() over (partition by Username, Game order by ct."date") as "Attempts" 

You do not share or order the same field; just order would be sufficient if it were necessary. This is not here.