Tableau创建含Min、Max、Median、Average等自定义文本值的新表

1. 先计算基础聚合统计值 #

先通过CTE一次性算出所有需要的聚合值，解决你之前只能获取最大值的问题：

WITH AggStats AS (
  SELECT
    MIN(target_column) AS min_val,         -- 替换为你要统计的列名
    MAX(target_column) AS max_val,
    AVG(target_column) AS avg_val,
    -- 中位数计算：兼容MySQL 8.0+/PostgreSQL/SQL Server，老版本需调整
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY target_column) OVER () AS median_val
  FROM table1
)

如果是老版本MySQL（无PERCENTILE_CONT），用以下方式计算中位数：

WITH SortedData AS (
  SELECT target_column,
         ROW_NUMBER() OVER (ORDER BY target_column) AS row_num,
         COUNT(*) OVER () AS total_rows
  FROM table1
)
SELECT
  MIN(target_column) AS min_val,
  MAX(target_column) AS max_val,
  AVG(target_column) AS avg_val,
  AVG(CASE WHEN row_num IN ((total_rows+1)//2, (total_rows+2)//2) THEN target_column END) AS median_val
FROM SortedData

2. 创建目标表（表2）并整合统计值 #

如果需要将统计项作为行展示，用UNION ALL生成表2：

CREATE TABLE table2 AS
SELECT 'Min' AS stat_type, min_val AS stat_value FROM AggStats
UNION ALL
SELECT 'Max' AS stat_type, max_val AS stat_value FROM AggStats
UNION ALL
SELECT 'Average' AS stat_type, avg_val AS stat_value FROM AggStats
UNION ALL
SELECT 'Median' AS stat_type, median_val AS stat_value FROM AggStats;

如果需要用嵌套条件逻辑自定义字段，参考以下写法：

CREATE TABLE table2 AS
SELECT
  -- 替换your_condition为实际业务判断逻辑
  CASE
    WHEN your_condition = 'show_min' THEN min_val
    WHEN your_condition = 'show_max' THEN max_val
    WHEN your_condition = 'show_avg' THEN avg_val
    WHEN your_condition = 'show_median' THEN median_val
    ELSE NULL
  END AS custom_stat,
  -- 可选：保留所有统计值作为列
  min_val,
  max_val,
  avg_val,
  median_val
FROM AggStats
-- 若需关联原表数据，可在此处JOIN table1
;

3. BI工具中的自定义字段处理（可视化场景） #

如果是在Tableau/Power BI等工具中操作：

• 先分别创建计算字段：
  • Min 值：MIN([目标列])
  • Max 值：MAX([目标列])
  • Average 值：AVG([目标列])
  • Median 值：MEDIAN([目标列])
• 再创建嵌套判断的自定义字段：

  IF [统计类型] = 'Min' THEN [Min 值]
  ELSEIF [统计类型] = 'Max' THEN [Max 值]
  ELSEIF [统计类型] = 'Average' THEN [Average 值]
  ELSEIF [统计类型] = 'Median' THEN [Median 值]
  ELSE NULL
  END
  

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