Redshift/PostgreSQL如何新增列实现用户分布统计？

现有一张聚合表，结构及数据如下：

*-------------------*
| territory | users |
*-------------------*
| worldwide |   20  |
| usa       |   6   |
| germany   |   3   |
| australia |   2   |
| india     |   5   |
| japan     |   4   | 
*-------------------*

需要实现以下需求：

• 新增ww_user列，所有行均填充worldwide对应的users值（即20）
• 新增% users distribution列，其中worldwide行的该值为0.0，其他地区为当前行users与ww_user的比值

期望结果如下：

*-----------------------------------------------------*
| territory | users | ww_user |  % users distribution |
*-----------------------------------------------------*
| worldwide |   20  |   20    |    0.0                |
| usa       |   6   |   20    |    0.30               |
| germany   |   3   |   20    |    0.15               | 
| australia |   2   |   20    |    0.10               | 
| india     |   5   |   20    |    0.25               | 
| japan     |   4   |   20    |    0.20               | 
*-----------------------------------------------------*

核心是先获取worldwide对应的users值，再关联到每一行进行计算，以下是几种通用实现方式：

方法1：子查询获取全局值 #

直接在SELECT语句中用子查询取出worldwide的用户数，通过条件判断计算分布占比：

SELECT
  territory,
  users,
  (SELECT users FROM your_table WHERE territory = 'worldwide') AS ww_user,
  CASE
    WHEN territory = 'worldwide' THEN 0.0
    ELSE ROUND(users / (SELECT users FROM your_table WHERE territory = 'worldwide'), 2)
  END AS `% users distribution`
FROM your_table;

方法2：窗口函数（适用于MySQL 8+、PostgreSQL等支持窗口函数的数据库） #

用窗口函数MAX()结合条件筛选，一次性获取全局的worldwide用户值，避免重复子查询：

SELECT
  territory,
  users,
  MAX(CASE WHEN territory = 'worldwide' THEN users END) OVER () AS ww_user,
  CASE
    WHEN territory = 'worldwide' THEN 0.0
    ELSE ROUND(users / MAX(CASE WHEN territory = 'worldwide' THEN users END) OVER (), 2)
  END AS `% users distribution`
FROM your_table;

方法3：交叉连接（CROSS JOIN） #

先单独查询出worldwide的用户数据，再和原表做交叉连接，确保每一行都能拿到该值：

SELECT
  t.territory,
  t.users,
  ww.users AS ww_user,
  CASE
    WHEN t.territory = 'worldwide' THEN 0.0
    ELSE ROUND(t.users / ww.users, 2)
  END AS `% users distribution`
FROM your_table t
CROSS JOIN (SELECT users FROM your_table WHERE territory = 'worldwide') ww;

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