Question 114546 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL update values by comparing it to other entries

so I got Questionnaire Data where the same Questions got asked multiple times, sadly they didnt think of giving them different question_ids, but at least i have the timestamps of when they were answered. Similar to this

person_id question_id answer timestamp
12 q1l1 0.4 12:38
12 q1l1 0.7 12:54

Is there a easy way to change the question id based on timestamps?

So to grab dublicates of the same question_id for the same person_id and compare the timestamps and set the question_ids to q1l1_1 and q1l1_2

person_id question_id answer timestamp
12 q1l1_1 0.4 12:38
12 q1l1_2 0.7 12:54
Tags:

2

Answers


  1. 0

    Window function is your approach:

    select 
    person_id, 
    question_id || '_' || row_number() over (partition by person_id, question_id order by timestamp),
    answer numeric, 
    timestamp
from questionnaire;

    sql online editor

    Login or Signup to reply.
  2. 0

    You can use ROW_NUMBER() function as the following:

    If you want only select the new question_id:

    select person_id,
       question_id || '_' || ROW_NUMBER() OVER 
       (PARTITION BY person_id ORDER BY timestamp) question_id,
       answer,
      timestamp
from table_name

    If you want to update question_id to the new value:

    update table_name set question_id = D.new_question_id
from  
(
  select person_id,question_id,
         question_id || '_' || ROW_NUMBER() 
         OVER (PARTITION BY person_id ORDER BY timestamp) new_question_id,
         answer, timestamp
  from table_name
) D
where table_name.person_id=D.person_id and
      table_name.question_id=D.question_id and
      table_name.timestamp=D.timestamp

    See a demo.

    I think you don’t need the condition table_name.question_id=D.question_id since all question_ids are the same for the same user.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search