Tableau技术求助：按ENR值取各县Top10 ID的PRICE中位数

用SQL实现 #

假设你筛选后的数据集存在表county_top10_enr，包含county、id、price字段：

• 支持PERCENTILE函数的数据库（PostgreSQL、BigQuery、SQL Server等）：
  直接分组调用中位数函数即可，两种类型的中位数可选：

  SELECT 
      county,
      -- 连续型中位数（返回插值结果）
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS price_median_cont,
      -- 离散型中位数（返回数据中实际存在的值）
      PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS price_median_disc
  FROM county_top10_enr
  GROUP BY county;
  

• MySQL 8.0+版本：
  MySQL无原生中位数函数，可通过排序取中间值计算：

  WITH ranked_prices AS (
      SELECT 
          county,
          price,
          ROW_NUMBER() OVER (PARTITION BY county ORDER BY price) AS row_num,
          COUNT(*) OVER (PARTITION BY county) AS total_count
      FROM county_top10_enr
  )
  SELECT 
      county,
      AVG(price) AS price_median
  FROM ranked_prices
  WHERE row_num IN (FLOOR((total_count + 1)/2), CEIL((total_count + 1)/2))
  GROUP BY county;
  

用Python Pandas实现 #

假设你已经得到筛选后的DataFrametop10_df，包含county、id、price列：

• 最简实现：
  Pandas内置的median()方法直接处理分组计算：

  median_result = top10_df.groupby('county')['price'].median().reset_index()
  median_result.rename(columns={'price': 'price_median'}, inplace=True)
  

• 自定义中位数逻辑（可选）：
  如果需要手动控制奇偶个数的中位数计算逻辑：

  def calculate_median(series):
      sorted_vals = series.sort_values().reset_index(drop=True)
      n = len(sorted_vals)
      mid = n // 2
      if n % 2 == 1:
          return sorted_vals[mid]
      else:
          return (sorted_vals[mid - 1] + sorted_vals[mid]) / 2
  
  median_result = top10_df.groupby('county')['price'].apply(calculate_median).reset_index(name='price_median')
  

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