如何在AWS QuickSight中基于指定月购用户实现动态月度销量分析

我完全理解你的困扰——在QuickSight里实现这种分两步的用户分群+时间范围统计，确实不像MySQL那样直接写子查询来得顺手。不过通过参数、计算字段和窗口函数的组合，完全可以实现你的需求，下面是一步步的实操方案：

1. 创建动态起始月份参数 #

首先我们需要一个能让用户切换起始月份的参数：

• 打开你的user_orders数据集编辑页面，点击顶部的参数选项卡
• 点击「创建参数」，配置如下：
  • 参数名称：StartMonth
  • 数据类型：选择「日期时间」，并指定格式为年月（YYYY-MM）
  • 允许的值：可以选「基于字段」，关联order_date的年月值，这样用户能直接从已有月份里选择；或者选「任意值」支持手动输入
  • 默认值：可设置为示例中的2020-04

2. 创建计算字段标记目标用户 #

我们需要先识别出在选定起始月份有购买行为的用户，这里用窗口函数实现类似MySQL子查询的效果：

• 在数据集编辑页面，点击计算字段→「创建计算字段」
• 字段名称：IsTargetUser
• 计算表达式：

IF(
  MAX(
    IF(
      DATE_TRUNC('MM', order_date) = ${StartMonth},
      1,
      0
    )
  ) OVER(PARTITION BY user_id) = 1,
  'Yes',
  'No'
)

说明：这个字段会为每个用户标记是否属于目标群体——对每个用户，检查其所有订单是否有匹配起始月份的记录，只要有一条就标记为Yes。

3. 创建计算字段筛选统计时间范围 #

接下来要限定统计的时间范围是起始月份及之后的11个月（共12个月）：

• 再创建一个计算字段，名称：InTimeRange
• 计算表达式：

IF(
  order_date >= ${StartMonth}
  AND order_date < ADD_MONTHS(${StartMonth}, 12),
  'Yes',
  'No'
)

说明：ADD_MONTHS函数会自动计算起始月份往后推12个月的日期，确保我们只统计12个月内的订单。

4. 构建可视化分析 #

现在把这些组件组合起来生成你要的分析表：

1. 回到QuickSight的分析页面，添加一个「表格」可视化组件
2. 把order_date拖到「行」区域，然后设置其聚合方式为年月，并自定义格式为MM-YYYY（比如04-2020）
3. 把quantity拖到「值」区域，设置聚合方式为SUM
4. 添加两个筛选器：
   • 筛选IsTargetUser等于Yes
   • 筛选InTimeRange等于Yes
5. 最后添加参数控件：点击顶部的控件→「添加控件」，选择我们创建的StartMonth参数，这样用户就能动态切换起始月份了

可选优化：合并筛选条件 #

如果你觉得两个筛选器太繁琐，可以把两个条件合并到一个计算字段里：

• 创建计算字段ValidOrder：

IF(
  MAX(IF(DATE_TRUNC('MM', order_date) = ${StartMonth}, 1, 0)) OVER(PARTITION BY user_id) = 1
  AND order_date >= ${StartMonth}
  AND order_date < ADD_MONTHS(${StartMonth}, 12),
  'Yes',
  'No'
)

然后只需要筛选ValidOrder等于Yes即可，效果是一样的。

用你的示例数据测试的话，当StartMonth设为2020-04时，会得到和你预期完全一致的结果：4月销量8，5月1，6月3。

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