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`
    }
  }
})