我的数据如
report_id report_number event_id
"149317" 2 "3403"
"149315" 1 "3403"
"149314" 0 "3403"
"149320" 1 "3405"
"149319" 0 "3405"
"149327" 0 "3409"
预期:
在event_id的基础上,在结果集中添加一个新列,以显示该event_id
的最大report_number
report_id report_number event_id count
"149317" 2 "3403" 2
"149315" 1 "3403" 2
"149314" 0 "3403" 2
"149320" 1 "3405" 1
"149319" 0 "3405" 1
"149327" 0 "3409" 0
使用MAX
作为分析函数:
SELECT *, MAX(report_number) OVER (PARTITION BY event_id) count
FROM yourTable;