PostgreSQL实现同名不同格式球员数据分组求和及Knex.js方案

Hey there! Let's fix this name normalization issue so you can properly group those player stats. The key here is to standardize all those messy name variations into a consistent format before grouping—PostgreSQL won't automatically recognize "Kane, Patrick" and "P.Kane, Patrick*" as the same player, so we need to clean them up first.

First, let's cover the Knex.js approach since that's your priority, then follow up with raw PostgreSQL SQL for reference.

Knex.js Solution #

We'll use PostgreSQL's regex functions within Knex raw queries to normalize the player names, then group by the cleaned-up name:

// Define the name normalization logic as a Knex raw expression
const normalizedPlayerName = knex.raw(`
  TRIM(
    REGEXP_REPLACE(
      // Remove leading initials (like "P." at the start of the name)
      REGEXP_REPLACE(player_name, '^[A-Za-z]\\.', '', 'g'),
      // Remove trailing special characters (like "*" at the end)
      '[^A-Za-z\\s,]$', '', 'g'
    )
  ) AS normalized_player_name
`);

// Build the aggregation query
const aggregatedStats = await knex('player_stats')
  .select(
    knex.raw('SUM(games_played) AS games_played'),
    knex.raw('SUM(goals) AS goals'),
    knex.raw('SUM(points) AS points'),
    normalizedPlayerName
  )
  .groupBy('normalized_player_name');

Raw PostgreSQL SQL Solution #

If you prefer writing raw SQL directly (maybe for testing or more complex adjustments), here's the equivalent query:

SELECT
  SUM(games_played) AS games_played,
  SUM(goals) AS goals,
  SUM(points) AS points,
  TRIM(
    REGEXP_REPLACE(
      REGEXP_REPLACE(player_name, '^[A-Za-z]\.', '', 'g'),
      '[^A-Za-z\s,]$', '', 'g'
    )
  ) AS normalized_player_name
FROM player_stats
GROUP BY normalized_player_name;

Testing & Adjusting the Normalization Logic #

Before running the full aggregation, I recommend testing the name cleaning to make sure it handles all your variations correctly. For example:

Knex Test Query

const testNameCleanup = await knex('player_stats')
  .select(
    'player_name',
    knex.raw(`
      TRIM(
        REGEXP_REPLACE(
          REGEXP_REPLACE(player_name, '^[A-Za-z]\\.', '', 'g'),
          '[^A-Za-z\\s,]$', '', 'g'
        )
      ) AS normalized_name
    `)
  )
  .where('player_name', 'LIKE', '%Kane%') // Test with a specific player's variations
  .limit(10);

Raw SQL Test Query

SELECT
  player_name,
  TRIM(
    REGEXP_REPLACE(
      REGEXP_REPLACE(player_name, '^[A-Za-z]\.', '', 'g'),
      '[^A-Za-z\s,]$', '', 'g'
    )
  ) AS normalized_name
FROM player_stats
WHERE player_name LIKE '%Kane%'
LIMIT 10;

Notes for Edge Cases #

• If you have other variations (like names with middle initials in the middle, or parentheses like "Kane, Patrick (II)"), you can extend the regex. For example, add another REGEXP_REPLACE to remove parenthetical content: REGEXP_REPLACE(..., '\(.*\)', '', 'g')
• If case differences matter (e.g., "kane, patrick" vs "Kane, Patrick"), wrap the normalized name in LOWER() to group case-insensitively: LOWER(TRIM(...)) AS normalized_player_name

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