Tableau技术需求：基于日均值计算维度的全局平均排名

Got it, let's work through this together! You've already nailed the first part—calculating daily average values and their corresponding ranks per category. Now we just need to roll those daily ranks up to get each category's average rank across the full period, with one row per category.

Step 1: Use Your Precomputed Daily Rank Data #

First, let's assume you have a dataset (could be a temporary table, CTE, or existing persisted table) that holds your daily aggregated ranks. Let's call this daily_category_ranks with these columns:

• date: The specific day of the aggregation
• category: Your target dimension (e.g., "Category A", "Category B")
• daily_avg_value: The average value for the category on that day
• daily_rank: The rank of the category (based on daily_avg_value) for that day

To get the average rank per category across the entire period, you just need to group by category and compute the mean of the daily_rank values. Here's the straightforward SQL query:

SELECT
  category,
  ROUND(AVG(daily_rank), 2) AS average_rank
FROM
  daily_category_ranks
GROUP BY
  category
ORDER BY
  average_rank;

Quick Breakdown: #

• ROUND(AVG(daily_rank), 2): Computes the average of all daily ranks for each category, rounded to 2 decimal places (adjust the number if you need more/less precision)
• GROUP BY category: Aggregates all daily records into one row per category
• ORDER BY average_rank: Optional but helpful to sort results from the best (lowest average rank) to worst (highest)

For example, if Category A has daily ranks [1, 3, 1, 1] over 4 days, this query would calculate (1+3+1+1)/4 = 1.5 as its average rank (matching the 1.6 example you mentioned if there are more days included).

Step 2: Do It All in One Query (If You Don't Have Precomputed Ranks) #

If you haven't stored the daily ranks yet, you can combine the daily average calculation, ranking, and final average rank into a single query using nested CTEs:

WITH daily_avg_calculation AS (
  -- First, compute daily average values per category
  SELECT
    date,
    category,
    AVG(value) AS daily_avg_value
  FROM
    your_raw_data_table
  GROUP BY
    date,
    category
),
daily_rank_calculation AS (
  -- Then, calculate the daily rank for each category
  SELECT
    date,
    category,
    daily_avg_value,
    RANK() OVER (PARTITION BY date ORDER BY daily_avg_value DESC) AS daily_rank
    -- Swap RANK() with ROW_NUMBER() if you need strict ranking without ties
  FROM
    daily_avg_calculation
)
-- Finally, compute the average rank per category across all days
SELECT
  category,
  ROUND(AVG(daily_rank), 2) AS average_rank
FROM
  daily_rank_calculation
GROUP BY
  category
ORDER BY
  average_rank;

This query flows from raw data all the way to your final single-row-per-category result, no intermediate tables needed.

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