AWS QuickSight活跃事件随时间计数的可视化实现咨询

我来给你分享两种亲测可行的方案，你可以根据自己的数据源规模和操作习惯选择：

方案一：自定义SQL预处理数据（推荐大数据量场景） #

这种方法提前在数据源层面计算出每个时间点的活跃事件数，后续在QuickSight里直接可视化即可，性能更优。

步骤如下：

1. 在QuickSight中创建数据集时，选择自定义SQL模式（如果已有数据集，可以编辑数据集切换到自定义SQL）
2. 根据你的数据源类型，编写生成日期序列并关联事件表的SQL：
   • 如果你用的是Athena/PostgreSQL：

     WITH date_range AS (
         SELECT date AS t_date
         FROM generate_date_array(
             (SELECT MIN(startDate) FROM your_event_table), -- 数据集最早的事件开始日期
             (SELECT MAX(endDate) FROM your_event_table), -- 数据集最晚的事件结束日期
             INTERVAL '1' DAY -- 时间粒度，可改为HOUR/WEEK等
         ) AS dates
     )
     SELECT 
         dr.t_date,
         COUNT(e.event_id) AS active_events -- event_id是你的事件唯一标识字段，替换成实际字段名
     FROM date_range dr
     LEFT JOIN your_event_table e
     ON dr.t_date BETWEEN e.startDate AND e.endDate -- 这里的边界逻辑可根据需求调整，比如改为dr.t_date >= e.startDate AND dr.t_date < e.endDate
     GROUP BY dr.t_date
     ORDER BY dr.t_date
     

   • 如果你用的是Redshift：

     WITH date_range AS (
         SELECT (SELECT MIN(startDate) FROM your_event_table) + (n || ' days')::interval AS t_date
         FROM generate_series(0, (SELECT DATE_PART('day', MAX(endDate) - MIN(startDate)) FROM your_event_table)) n
     )
     SELECT 
         dr.t_date::date,
         COUNT(e.event_id) AS active_events
     FROM date_range dr
     LEFT JOIN your_event_table e
     ON dr.t_date BETWEEN e.startDate AND e.endDate
     GROUP BY dr.t_date::date
     ORDER BY dr.t_date::date
     

3. 执行SQL后，将生成的数据集导入QuickSight
4. 构建折线图：
   • 把t_date拖到X轴，设置为连续日期（选择你需要的粒度，比如天/周）
   • 把active_events拖到Y轴，聚合方式选择求和（因为SQL里已经聚合过，这里直接用原值即可）
   • 调整图表样式，比如添加数据标签、趋势线等

方案二：QuickSight内置计算字段+表计算（无需修改数据源） #

如果不想修改数据源，可以直接在QuickSight内通过计算字段和表计算实现：

步骤如下：

1. 打开你的事件数据集，添加一个日期参数：
   • 参数名称设为p_TargetDate，类型选日期，范围设置为从数据集的startDate最小值到endDate最大值，允许选择所有日期值
2. 创建第一个计算字段IsActiveOnTargetDate：
   • 公式：

     ifelse(
         ${startDate} <= ${p_TargetDate} AND ${endDate} >= ${p_TargetDate},
         1,
         0
     )
     

     这个字段用来标记当前事件在目标日期是否活跃
3. 创建第二个表计算ActiveEventsCount：
   • 类型选择总和，计算范围设为整个数据集，公式：

     sum(${IsActiveOnTargetDate})
     

     这个计算会统计目标日期的所有活跃事件数
4. 构建折线图：
   • 把参数p_TargetDate拖到X轴，设置为连续日期粒度
   • 把表计算ActiveEventsCount拖到Y轴
   • 开启参数的时间轴控件（在参数设置里勾选“显示控件”），这样拖动时间轴就能看到活跃事件数随时间的变化曲线
   • 注意：这种方法在数据集较大时可能会有性能问题，建议小数据集使用

额外提示 #

• 时间粒度可以根据需求调整：比如要按小时统计，只需把SQL里的INTERVAL '1' DAY改成INTERVAL '1' HOUR，或者在QuickSight的日期轴上切换粒度
• 如果需要展示特定时间范围的活跃数，可以在QuickSight中添加日期过滤器，限制X轴的时间范围

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