如何在Postgresql SELECT查询中基于多个列添加计数器id列

Question

我想创建一个基于question_id和matrix_question_id计数器数的列，这是我的查询:

WITH cte_survey AS 
(
SELECT 
user_input_id, 
question_id,
type,
matrix_subtype,
question,
matrix_question_id,
matrix_questions,
COALESCE(value_text, value_free_text, value_date::text, value_number::text, value) AS all_value
FROM
(SELECT 
a.id, a.user_input_id,
d.id as question_id, d.type,
d.matrix_subtype,
d.question, 
a.value_suggested_row,
c.id as matrix_question_id,
c.value as matrix_questions, 
a.value_suggested,
b.value,
a.value_text, 
a.value_free_text,
a.value_date,
a.value_number
FROM 
survey_user_input_line a 
LEFT JOIN 
survey_label b on b.id = a.value_suggested
LEFT JOIN 
survey_label c on c.id = a.value_suggested_row
LEFT JOIN 
survey_question d on d.id = a.question_id
LEFT JOIN 
survey_user_input e on e.id = a.user_input_id
WHERE 
a.survey_id = 6
ORDER BY 
question_id, user_input_id,  matrix_question_id, value_suggested_row) AS TempTable
)
SELECT 
ROW_NUMBER() OVER (ORDER BY question_id) AS id, * 
FROM
(SELECT *
FROM cte_survey 
WHERE type != 'multiple_choice'
UNION

SELECT 
user_input_id, question_id, type,
matrix_subtype, question, 
matrix_question_id, matrix_questions,
STRING_AGG(all_value, ',')
FROM 
cte_survey
WHERE 
type = 'multiple_choice'
GROUP BY 
user_input_id, question_id, type, question, matrix_subtype,
question, matrix_question_id, matrix_questions
ORDER BY 
question_id, matrix_question_id, user_input_id) AS result_answer

这是结果

我想创建一个新的列，id_counter代表基于question_id和matrix_question_id的id计数器，我希望结果像这样

Answer 1

这里你需要的是DENSE_RANK，你可以把它放在row_number旁边:

DENSE_RANK() over (ORDER BY question_id) as id_counter

进一步分:

1. 如果查询的两个部分没有重叠的机会，UNION ALL将比UNION更有效
2. row_number是不确定的，每次可能返回不同的结果。您应该添加一个额外的排序子句，也许是question_id, matrix_question_id, user_input_id或其他一些独特的组合。
3. ORDER BY对派生表、视图或CTE的结果不能保证工作(除非您将其与LIMIT一起使用，或者在排名或窗口函数中使用)。你应该只放在查询的最外面。
4. 第一个TempTable似乎毫无意义，你可以直接将它合并到你的CTE中。