Cube.js时间范围最佳实践

Question

我有一个日期范围的项目价格表。在cube.js中对此建模的最佳方式是什么，以便允许时间维度查询，如随时间变化的价格或商品的平均价格？

谢谢!

该表如下所示：

CREATE pricing test_timestamp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    itemId VARCHAR(255) NOT NULL,
    price INT,
    from TIMESTAMP,
    to TIMESTAMP
);

Answer 1

将周期视为非重叠：

cube(`Pricing`, {
  sql: `select itemId, price, from as timestamp from pricing_test_timestamp
  UNION ALL
  select itemId, -1 * price as price, to as timestamp from pricing_test_timestamp
  `,

  measures: {
    price: {
      sql: `price`,
      type: `sum`,
      rollingWindow: {
        trailing: `unbounded`
      }
    }
  },

  dimensions: {
    timestamp: {
      sql: `timestamp`,
      type: `number`
    }
  }
})