如何为每个ani_id按观测日期生成累计观测序号（obs_nb）

Got it, the issue here is that your current window function is calculating the total number of observations for each ani_id instead of a sequential count for each entry in that group.

To get the cumulative observation number you want (starting at 1 for each ani_id and incrementing with each entry), you need to use the ROW_NUMBER() window function instead of COUNT(), and specify an ordering within each partition to determine the sequence.

Here's the corrected query:

SELECT 
  ani_id, 
  obs_date, 
  ROW_NUMBER() OVER (PARTITION BY ani_id ORDER BY obs_date) AS obs_nb 
FROM v_metadata_all 
ORDER BY ani_id, obs_date;

What changed: #

• Replaced COUNT(ani_id) OVER (PARTITION BY ani_id) with ROW_NUMBER() OVER (PARTITION BY ani_id ORDER BY obs_date):
  • ROW_NUMBER() assigns a unique sequential integer to each row within the partition of ani_id.
  • The ORDER BY obs_date ensures the numbering follows the chronological order of observations (adjust this if you need a different sorting logic for your sequence).
• Removed the GROUP BY ani_id, cap_date clause—it’s unnecessary here because we’re using a window function to number individual rows, and grouping would collapse entries which defeats the purpose of sequential numbering.

Expected Output: #

Notes: #

• If you might have multiple entries with the same obs_date for a single ani_id and want to handle ties, you could use RANK() or DENSE_RANK() instead of ROW_NUMBER(). RANK() leaves gaps for tied values, while DENSE_RANK() doesn’t. But based on your example, ROW_NUMBER() should work perfectly.

内容的提问来源于stack exchange，提问作者yannick