如何在Cube.dev滚动窗口中使用当日日期参数并按周聚合数据

核心思路 #

你的需求是统计每个日期下可下单的用户数，再将这些每日数据按周聚合。原代码的核心问题在于：

• 错误使用固定的${TODAY}，但实际需要动态匹配每个统计日期
• 误用rollingWindow处理区间匹配场景，这应该用日期序列关联用户区间来实现

以下是修正后的Cube.dev模型代码及分步解释：

方案1：拆分日期维度Cube（推荐，复用性高） #

1. 创建日期维度Cube #

先生成覆盖所有可能下单日期的序列，用于后续关联用户表：

Cube(`Dates`, {
  sql: `
    SELECT generate_series(
      (SELECT MIN(can_order_from) FROM public.users),
      (SELECT MAX(can_order_to) FROM public.users),
      INTERVAL '1 day'
    ) AS date
  `,
  dimensions: {
    date: {
      sql: `date`,
      type: `time`,
      primaryKey: true
    },
    week: {
      sql: `DATE_TRUNC('week', date)`,
      type: `time`,
      title: '统计周'
    }
  }
});

2. 修改用户Cube实现统计与聚合 #

关联日期维度，匹配用户可下单区间，同时支持每日/每周统计：

Cube(`Users`, {
  sql: `SELECT * FROM public.users`,

  joins: {
    Dates: {
      relationship: `many_to_many`,
      sql: `${CUBE}.can_order_from <= ${Dates.date} AND ${CUBE}.can_order_to >= ${Dates.date}`
    }
  },

  measures: {
    // 每日可下单用户数（去重，避免同一用户重复统计）
    dailyOrderableUsers: {
      type: `countDistinct`,
      sql: `id`,
      title: '每日可下单用户数'
    },
    // 每周可下单用户总数（每日数据求和）
    weeklyTotalOrderableUsers: {
      type: `sum`,
      sql: `${dailyOrderableUsers}`,
      title: '每周可下单用户总数'
    },
    // 每周独立可下单用户数（该周内至少一天可下单的用户）
    weeklyUniqueOrderableUsers: {
      type: `countDistinct`,
      sql: `id`,
      title: '每周独立可下单用户数'
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },
    canOrderFrom: {
      sql: `can_order_from`,
      type: `time`
    },
    canOrderTo: {
      sql: `can_order_to`,
      type: `time`
    },
    // 关联日期维度
    statDate: {
      sql: `${Dates.date}`,
      type: `time`,
      title: '统计日期'
    },
    statWeek: {
      sql: `${Dates.week}`,
      type: `time`,
      title: '统计周'
    }
  },

  dataSource: `default`
});

方案2：单Cube内直接关联日期序列（简化版） #

如果不需要复用日期维度，可直接在用户Cube内生成日期序列并关联：

Cube(`Users`, {
  sql: `
    SELECT 
      u.id,
      u.can_order_from,
      u.can_order_to,
      d.date AS stat_date
    FROM public.users u
    JOIN (
      SELECT generate_series(
        (SELECT MIN(can_order_from) FROM public.users),
        (SELECT MAX(can_order_to) FROM public.users),
        INTERVAL '1 day'
      ) AS date
    ) d ON u.can_order_from <= d.date AND u.can_order_to >= d.date
  `,

  measures: {
    dailyOrderableUsers: {
      type: `countDistinct`,
      sql: `id`,
      title: '每日可下单用户数'
    },
    weeklyTotalOrderableUsers: {
      type: `sum`,
      sql: `${dailyOrderableUsers}`,
      title: '每周可下单用户总数'
    },
    weeklyUniqueOrderableUsers: {
      type: `countDistinct`,
      sql: `id`,
      title: '每周独立可下单用户数'
    }
  },

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primaryKey: true
    },
    canOrderFrom: {
      sql: `can_order_from`,
      type: `time`
    },
    canOrderTo: {
      sql: `can_order_to`,
      type: `time`
    },
    statDate: {
      sql: `stat_date`,
      type: `time`,
      title: '统计日期'
    },
    statWeek: {
      sql: `DATE_TRUNC('week', stat_date)`,
      type: `time`,
      title: '统计周'
    }
  },

  dataSource: `default`
});

关键问题解决说明 #

1. 动态日期匹配
   原代码的${TODAY}是固定值，无法满足每日统计需求。通过generate_series生成日期序列，关联用户的can_order_from和can_order_to区间，实现对每个日期的动态匹配，统计该日期下可下单的用户数。

2. 按周聚合
   通过DATE_TRUNC('week', date)将日期截断到周起始（PostgreSQL默认周一为周起始），生成周维度。查询时按该维度分组，可选择：

   • 对每日用户数求和，得到每周用户总数
   • 统计周内独立用户数，得到每周至少一天可下单的用户数

示例数据与预期结果 #

示例用户数据 #

每日统计结果 #

按周聚合结果 #

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