Alibaba PostgresSQL角色间胜率对比矩阵表创建及Tableau可视化实现技术问询

PostgreSQL Implementation #

Let's adjust your SQL to compute the win rates between character pairs and pivot them into the matrix format you need. We’ll cover two approaches: one for fixed character lists and another for dynamic sets.

1. Conditional Aggregation (Fixed Character List) #

This method works best if you know exactly which superstars you want to include:

WITH win_rate_calculations AS (
    -- Calculate win percentage for every fighter vs opponent pair
    SELECT
        character_used_by_user AS fighter,
        opponent_character AS opponent,
        ROUND(
            (SUM(CASE WHEN status_of_match = 'Win' THEN 1 ELSE 0 END)::FLOAT / COUNT(*)) * 100,
            1
        ) AS win_percent
    FROM master_table
    WHERE event = 'End_match'
    GROUP BY character_used_by_user, opponent_character
),
all_superstars AS (
    -- Get all unique characters from both user and opponent columns
    SELECT DISTINCT character_used_by_user AS superstar FROM master_table
    UNION
    SELECT DISTINCT opponent_character AS superstar FROM master_table
)
SELECT
    superstar AS "Character Name",
    -- Show N/A when a character fights themselves, else the win rate
    CASE WHEN superstar = 'AJ Styles' THEN 'N/A' ELSE COALESCE(wr_win.win_percent::TEXT, 'N/A') END AS "AJ Styles",
    CASE WHEN superstar = 'John Cena' THEN 'N/A' ELSE COALESCE(wr_cena.win_percent::TEXT, 'N/A') END AS "John Cena",
    CASE WHEN superstar = 'Kane' THEN 'N/A' ELSE COALESCE(wr_kane.win_percent::TEXT, 'N/A') END AS "Kane"
FROM all_superstars
LEFT JOIN win_rate_calculations wr_win ON superstar = wr_win.fighter AND wr_win.opponent = 'AJ Styles'
LEFT JOIN win_rate_calculations wr_cena ON superstar = wr_cena.fighter AND wr_cena.opponent = 'John Cena'
LEFT JOIN win_rate_calculations wr_kane ON superstar = wr_kane.fighter AND wr_kane.opponent = 'Kane'
WHERE superstar IN ('AJ Styles', 'John Cena', 'Kane')
ORDER BY superstar;

2. Using crosstab (Dynamic Character Sets) #

If your list of superstars might grow, use PostgreSQL's tablefunc extension to pivot dynamically:

First, enable the extension (run once):

CREATE EXTENSION IF NOT EXISTS tablefunc;

Then run the pivot query:

SELECT
    "Character Name",
    COALESCE("AJ Styles", 'N/A') AS "AJ Styles",
    COALESCE("John Cena", 'N/A') AS "John Cena",
    COALESCE("Kane", 'N/A') AS "Kane"
FROM crosstab(
    -- Source query: gets fighter, opponent, and their win rate as text
    'SELECT 
        character_used_by_user, 
        opponent_character, 
        ROUND((SUM(CASE WHEN status_of_match = ''Win'' THEN 1 ELSE 0 END)::FLOAT / COUNT(*)) * 100, 1)::TEXT AS win_rate
     FROM master_table
     WHERE event = ''End_match''
     GROUP BY character_used_by_user, opponent_character
     ORDER BY 1,2',
    -- Column list query: gets all unique opponents to use as columns
    'SELECT DISTINCT opponent_character FROM master_table ORDER BY 1'
) AS ct(
    "Character Name" TEXT,
    "AJ Styles" TEXT,
    "John Cena" TEXT,
    "Kane" TEXT
);

Note: Update the column list in the ct definition if new superstars are added.

Tableau Quick Setup for Win Rate Matrix #

Creating this matrix in Tableau is fast and interactive. Follow these steps:

1. Connect to Data: Link Tableau to your PostgreSQL database and load the master_table.
2. Create Win Rate Calculation:
   • Go to Analysis > Create Calculated Field.
   • Name it Win Rate % and use this formula:

     SUM(IF [Status of match] = 'Win' THEN 1 ELSE 0 END) / COUNT([unique_id])
     

   • Format as percentage: Right-click the field > Default Properties > Number Format > Percentage.
3. Build the Matrix:
   • Drag Character used by user to the Rows shelf.
   • Drag Opponent character to the Columns shelf.
   • Drag Win Rate % to the Text shelf.
4. Replace Diagonal with N/A:
   • Create another calculated field named Display Value:

     IF [Character used by user] = [Opponent character] THEN 'N/A' ELSE STR([Win Rate %]) END
     

   • Replace Win Rate % on the Text shelf with this new field.
5. Refine the Look:
   • Sort rows/columns alphabetically (right-click the header > Sort).
   • Adjust borders, text size, and alignment to match your desired table style.

This setup gives you an interactive matrix where you can filter, drill down, or export the exact table you need.

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