118 lines
2.3 KiB
JavaScript
118 lines
2.3 KiB
JavaScript
// Cube.js 数据模型 - 订单示例
|
||
// 请根据实际数据库表结构修改
|
||
|
||
cube(`Orders`, {
|
||
sql: `SELECT * FROM orders WHERE \${this.enabled}`,
|
||
|
||
// measures（指标）
|
||
measures: {
|
||
count: {
|
||
sql: `id`,
|
||
type: `count`,
|
||
},
|
||
|
||
totalAmount: {
|
||
sql: `amount`,
|
||
type: `sum`,
|
||
format: `currency`,
|
||
},
|
||
|
||
avgAmount: {
|
||
sql: `amount`,
|
||
type: `avg`,
|
||
format: `currency`,
|
||
},
|
||
|
||
cancelRate: {
|
||
sql: `CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*) ELSE 0 END`,
|
||
type: `number`,
|
||
format: `percent`,
|
||
},
|
||
},
|
||
|
||
// dimensions（维度）
|
||
dimensions: {
|
||
id: {
|
||
sql: `id`,
|
||
type: `number`,
|
||
primaryKey: true,
|
||
},
|
||
|
||
orderDate: {
|
||
sql: `order_date`,
|
||
type: `time`,
|
||
},
|
||
|
||
status: {
|
||
sql: `status`,
|
||
type: `string`,
|
||
},
|
||
|
||
category: {
|
||
sql: `category`,
|
||
type: `string`,
|
||
},
|
||
|
||
region: {
|
||
sql: `region`,
|
||
type: `string`,
|
||
},
|
||
|
||
channel: {
|
||
sql: `channel`,
|
||
type: `string`,
|
||
},
|
||
|
||
customerId: {
|
||
sql: `customer_id`,
|
||
type: `number`,
|
||
},
|
||
|
||
amount: {
|
||
sql: `amount`,
|
||
type: `number`,
|
||
format: `currency`,
|
||
},
|
||
},
|
||
|
||
// 预聚合配置
|
||
preAggregations: {
|
||
// 按月聚合（每小时刷新）
|
||
ordersByMonth: {
|
||
measures: [Orders.count, Orders.totalAmount, Orders.avgAmount],
|
||
dimensions: [Orders.category, Orders.region],
|
||
timeDimension: Orders.orderDate,
|
||
granularity: `month`,
|
||
refreshKey: {
|
||
every: `1 hour`,
|
||
timezone: 'Asia/Shanghai',
|
||
},
|
||
},
|
||
|
||
// 按日聚合（高频查询，5分钟刷新）
|
||
ordersByDay: {
|
||
measures: [Orders.count, Orders.totalAmount, Orders.cancelRate],
|
||
dimensions: [Orders.status, Orders.channel],
|
||
timeDimension: Orders.orderDate,
|
||
granularity: `day`,
|
||
refreshKey: {
|
||
every: `5 minutes`,
|
||
},
|
||
},
|
||
|
||
// 按地区聚合（每小时刷新）
|
||
ordersByRegion: {
|
||
measures: [Orders.count, Orders.totalAmount],
|
||
dimensions: [Orders.region, Orders.category],
|
||
timeDimension: Orders.orderDate,
|
||
granularity: `hour`,
|
||
refreshKey: {
|
||
every: `1 hour`,
|
||
},
|
||
},
|
||
},
|
||
|
||
// 数据安全：默认禁用
|
||
enabled: true,
|
||
});
|