Oracle技术问询：如何从多行同类型数据中为每个ffi_customer_id每年提取唯一行

Got it, let's tackle this problem step by step. Your core goal is to get one unique row per ffi_customer_id for each year, using the reported_on and created_on dates to determine the year. Below are practical SQL solutions for common database dialects, along with explanations of the logic.

Key Approach #

The main steps to achieve this are:

• Extract the year: Use either reported_on or created_on (we’ll use COALESCE to prioritize one date if the other is null—adjust this based on your business rules).
• Group the data: Group by ffi_customer_id and the extracted year to ensure one row per customer per year.
• Aggregate other fields: Choose how to combine non-unique fields (like earliest/latest dates, statuses, etc.) based on your final output needs.

SQL Examples by Dialect #

PostgreSQL / BigQuery #

SELECT
  ffi_customer_id,
  EXTRACT(YEAR FROM COALESCE(reported_on, created_on)) AS customer_year,
  -- Example aggregations: tweak these based on your required fields
  MIN(created_on) AS earliest_created_date,
  MAX(reported_on) AS latest_reported_date,
  STRING_AGG(DISTINCT status, ', ') AS combined_statuses -- Merge status values if needed
FROM your_table_name
GROUP BY ffi_customer_id, EXTRACT(YEAR FROM COALESCE(reported_on, created_on))
ORDER BY ffi_customer_id, customer_year;

MySQL #

MySQL uses YEAR() for date extraction and GROUP_CONCAT() for string aggregation:

SELECT
  ffi_customer_id,
  YEAR(COALESCE(reported_on, created_on)) AS customer_year,
  MIN(created_on) AS earliest_created_date,
  MAX(reported_on) AS latest_reported_date,
  GROUP_CONCAT(DISTINCT status SEPARATOR ', ') AS combined_statuses
FROM your_table_name
GROUP BY ffi_customer_id, YEAR(COALESCE(reported_on, created_on))
ORDER BY ffi_customer_id, customer_year;

Customization Tips #

• Date Priority: If you always want to use created_on's year regardless of reported_on, replace COALESCE(reported_on, created_on) with created_on.
• Aggregation Rules: For numeric fields, use SUM(), AVG(), MIN(), or MAX() as needed. For text fields, use concatenation functions or pick first/last values with FIRST_VALUE()/LAST_VALUE() (window functions if required).
• Null Date Handling: If both dates might be null, add a WHERE clause like WHERE reported_on IS NOT NULL OR created_on IS NOT NULL to exclude those rows, or set a default year (e.g., 2000) for null cases.

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