TableSheet 窗口函数

概述本 Demo 展示了 TableSheet 窗口函数的核心功能，包括窗口定义、分区排序和帧控制。Demo 通过四个工作表分别演示了如何使用 WINDOW、PARTITIONBY、ORDERBY、FRAMEROWS、FRAMERANGE、FRAMEGROUPS 以及 WINDOWDEF 实现累计计算、移动平均、范围分析等高级数据分析功能。实现思路为每个数据分析场景创建独立的 TableSheet 和数据表在数据表的 schema 中使用 window 属性定义可重用的窗口（WINDOWDEF）在视图列中使用 WINDOW 函数引用窗口定义，应用聚合函数通过 PARTITIONBY 对数据进行分区，ORDERBY 进行排序使用 FRAMEROWS、FRAMERANGE、FRAMEGROUPS 定义不同的窗口帧范围结合迷你图（VARISPARKLINE、HBARSPARKLINE）可视化分析结果代码解析定义可重用的窗口（WINDOWDEF）这段代码在表的 schema 中定义了两个可重用的窗口。BeginCurrent 使用 FRAMEROWS(-1, [@]) 定义从分区开始到当前行的窗口，用于计算累计值；BeginEnd 定义整个分区的窗口，用于计算总计值。-1 表示分区的开始，[@] 表示当前行。使用窗口函数计算累计和总计 WINDOW 函数的第一个参数是聚合函数（如 SUM、AVERAGE），第二个参数引用 WINDOWDEF 定义的窗口名称。这避免了重复编写 PARTITIONBY 和 ORDERBY 表达式。使用 FRAMEROWS 计算移动平均 FRAMEROWS([@-2], [@]) 定义了一个包含当前行及前两行的窗口，用于计算 3 年移动平均。[@-2] 表示当前行之前的第 2 行。使用 FRAMERANGE 基于值范围定义窗口 FRAMERANGE([@-200], [@+200]) 基于排序字段的值范围定义窗口，而不是行数。这里筛选出金额在当前值 ±200 范围内的行，适用于分析相近价格的销售趋势。使用 FRAMEGROUPS 基于分组定义窗口 FRAMEGROUPS 将具有相同值的行视为一组。[@-2] 和 [@+2] 表示当前组前后各 2 个组，第三个参数 2 表示排除当前行和同行。运行效果 Discount Amount 工作表：显示每个产品的销售量累计值和年度总计当销售数量超过 30 时，金额自动打 8 折并以紫色显示 "Running Total Quantity" 列显示累计销售量，"Total Quantity" 列显示年度总销售量 "Running Discount Total Amount" 和 "Discount Total Amount" 显示折扣后的累计金额和总金额 Revenue Trends 工作表：显示每个产品按年份排列的收入数据 "Moving Average Revenue" 列显示 3 年移动平均值（当前年及前两年） "Revenue Trends %" 列使用迷你图直观显示收入与移动平均的对比 Quantity Of Revenue Trends 工作表：显示相近价格订单的平均收入和订单数量使用 FRAMERANGE 筛选金额在当前值 ±200 范围内的订单 "Average Revenue Trends" 和 "Quantity Of Revenue Trends" 使用条形图显示趋势颜色渐变显示订单数量的分布情况（红色=低，绿色=高） Monthly Revenue Trends 工作表：显示每个产品按月份的收入数据 "Monthly Average Revenue" 显示当月平均收入 "Nearly Average Revenue" 显示近 5 个月的移动平均（当前月±2个月，排除当前月本身） "Revenue Trends %" 迷你图对比月度平均与近 5 月平均 API 参考 WINDOW 函数 window_function：必填，窗口函数（如 SUM、AVERAGE、COUNT 等） partitionby_function：可选，将行划分为分区 orderby_function：可选，定义分区内行的排序顺序 frame_function：可选，指定窗口帧的起点和终点 PARTITIONBY 函数将数据行分割为多个分区，窗口函数在每个分区内独立计算。 ORDERBY 函数定义分区内行的逻辑顺序，默认为升序。 FRAMEROWS 函数 beginning_function：窗口起始位置，接受 -1（分区开始）、[@-n]（当前行前 n 行）、[@]（当前行） ending_function：窗口结束位置，接受 -1（分区结束）、[@+n]（当前行后 n 行）、[@]（当前行） exclude_mode：排除模式（0=不排除，1=排除当前行，2=排除当前行和同行，3=保留当前行排除其他同行） FRAMERANGE 函数基于 ORDERBY 字段的值范围定义窗口，参数含义与 FRAMEROWS 相同，但基于值距离而非行数。 FRAMEGROUPS 函数基于相同值的行组定义窗口，将所有值相同的行视为一组。 WINDOWDEF 函数在表的 schema.window 中定义可重用的窗口，可在多个 WINDOW 函数中引用，避免重复代码。

/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
spread.clearSheets();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.options.allowDynamicArray = true;
spread.options.highlightInvalidData = true;
spread.options.calcOnDemand = true;
discountAmount(spread);
revenueTrends(spread);
quantityRevenueTrends(spread);
monthlyRevenueTrends(spread);
spread.resumePaint();
}
function discountAmount(spread) {
//init a data manager
var dataManager = spread.dataManager();
var discountAmountTable = dataManager.addTable("discountAmountTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderPriceQuantityData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Quantity: { dataType: "number" },
Price: { dataType: "number" },
Amount: { dataType: 'formula', value: "=[@Price] * [@Quantity]" },
DiscountAmount: { dataType: 'formula', value: "=IF([@Quantity] > 30, [@Amount] * 0.8, [@Amount])" },
},
window: {
BeginCurrent: '=WINDOWDEF(PARTITIONBY([Category], [Product], YEAR([@OrderDate])), FRAMEROWS(-1,[@]))',
BeginEnd: '=WINDOWDEF(PARTITIONBY([Category], [Product], YEAR([@OrderDate])))',
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "Discount Amount", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
discountAmountTable.fetch().then(function () {
var discountHighlightRule = {
ruleType: "formulaRule",
formula: "[@Quantity]>30",
style: {
foreColor: "purple"
}
};
var myView = discountAmountTable.addView("myView", [
{ value: "Category", width: 90 },
{ value: "Product", width: 90 },
{ value: "=YEAR([@OrderDate])", caption: "Year", width: 80 },
{ value: "Amount", caption: "Amount", width: 100, conditionalFormats: [discountHighlightRule] },
// If the quantity exceeds 30, 20% off sales amount
{ value: "DiscountAmount", caption: "Discount Amount", conditionalFormats: [discountHighlightRule], width: 160 },
{ value: "Price", width: 70 },
{ value: "Quantity", width: 90, conditionalFormats: [discountHighlightRule] },
// The cumulative annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), \"BeginCurrent\")", caption: 'Running Total Quantity', width: 190, style: { backColor: "#D9E1F2" } },
// The total annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), \"BeginEnd\")", caption: 'Total Quantity', width: 140, style: { backColor: "#D9E1F2" } },
// The cumulative annual sales amount of each category and product, if the quantity exceeds 30, 20% off sales amount
{ value: "=WINDOW(SUM([DiscountAmount]), \"BeginCurrent\")", caption: 'Running Discount Total Amount', width: 240, style: { backColor: "#E2EFDA" } },
// The total annual sales amount of each category and product, if the quantity exceeds 30, 20% off sales amount
{ value: "=WINDOW(SUM([DiscountAmount]), \"BeginEnd\")", caption: 'Discount Total Amount', width: 190, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function revenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var revenueTrendsTable = dataManager.addTable("revenueTrendsTable", {
data: orderYearProductDataSource,
schema: {
type: "csv",
columns: {
Quantity: { dataType: "number" },
Amount: { dataType: "number" },
MovingAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductYear")' },
},
window: {
ProductYear: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Year]), FRAMEROWS([@-2], [@]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(1, "Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
revenueTrendsTable.fetch().then(function () {
var myView = revenueTrendsTable.addView("myView", [
{ value: "Year", width: 150 },
{ value: "Product", width: 105 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// Calculate the 3-year moving average of earned per product
// FRAMEROWS([@-2], [@]): the preceding two years and current
{ value: 'MovingAverageRevenue', caption: 'Moving Average Revenue', width: 200, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// To show a ratio of increase and decrease between the current and moving average amount
{ value: '=VARISPARKLINE(ROUND(([@Amount] - [@MovingAverageRevenue]) / [@Amount], 2),0,,,,0.2,TRUE)', caption: 'Revenue Trends %', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function quantityRevenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var quantityRevenueTrendsTable = dataManager.addTable("quantityRevenueTrendsTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderAmountData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Amount: { dataType: "number" },
AverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductRangeAmount")' },
QuantityOfRevenue: { dataType: 'formula', value: '=WINDOW(COUNT([Amount]), "ProductRangeAmount")' },
},
window: {
ProductRangeAmount: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(2, "Quantity Of Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);    
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
quantityRevenueTrendsTable.fetch().then(function () {
var myView = quantityRevenueTrendsTable.addView("myView", [
{ value: "Product", width: 150 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// The trend comparison of average selling prices and the quantity of the proximate orders
// When the price is within a certain range, there are more orders
// When the price is higher or lower, the order quantity decreases
// This situation of the quantity of the orders are close to the normal distribution
// The analytic function AVERAGE can obtain the average of proximate selling prices within each product
// FRAMERANGE([@-200], [@+200]): the amount range which minus or plus 200 against current amount will help to retrieve the orders as a window
{ value: "AverageRevenue", caption: 'Average Revenue', width: 150, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// Calculating the ratio of the average amount of the proximate orders against max amount in each product to show the bars
{ value: "=HBARSPARKLINE([@AverageRevenue] / WINDOW(MAX([Amount]), PARTITIONBY([Product])), \"#347B98\")", caption: 'Average Revenue Trends', width: 200, style: { backColor: "#E2EFDA" } },
// The analytic function COUNT can obtain the quantity of proximate selling prices within each product
// FRAMERANGE([@-200], [@+200]): the amount range which minus or plus 200 against current amount will help to retrieve the orders as a window
{ value: "QuantityOfRevenue", caption: 'Quantity Of Revenue', width: 180, style: { backColor: "#E2EFDA" } },
// Using LET to cached the ratio of the number of the proximate orders against the total number of the orders in each product to show the bars which indicates the trends through the ratio and colors
{ value: "=LET(ratio, [@QuantityOfRevenue] / WINDOW(COUNT([Amount]), PARTITIONBY([Product])),color,IF(ratio >= 0.32,\"green\", IF(ratio >= 0.2, \"#66B032\", IF(ratio >= 0.1, \"#B2D732\", \"red\"))), HBARSPARKLINE(ratio, color))", caption: 'Quantity Of Revenue Trends', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function monthlyRevenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var monthlyRevenueTrendsTable = dataManager.addTable("monthlyRevenueTrendsTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderAmountData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Amount: { dataType: "number" },
MonthlyAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductMonthly")' },
NearlyAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductMonthly", FRAMEGROUPS([@-2], [@+2], 2))' },
},
window: {
ProductMonthly: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY(VALUE(DATEPART([@OrderDate], "M"))), FRAMEGROUPS([@], [@]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(3, "Monthly Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
monthlyRevenueTrendsTable.fetch().then(function () {
var myView = monthlyRevenueTrendsTable.addView("myView", [
{ value: "Product", width: 150 },
{ value: '=VALUE(DATEPART([@OrderDate], "M"))', caption: 'Month', width: 150 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
{ value: 'MonthlyAverageRevenue', caption: 'Monthly Average Revenue', width: 220, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
{ value: 'NearlyAverageRevenue', caption: 'Nearly Average Revenue', width: 200, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
{ value: '=VARISPARKLINE(ROUND(([@MonthlyAverageRevenue] - [@NearlyAverageRevenue]) / [@MonthlyAverageRevenue], 2),0,,,,0.2,TRUE)', caption: 'Revenue Trends %', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/orderDataSource.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/orderYearProductDataSource.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</html>

.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 210px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 3px;
margin-top: 3px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width: 100%;
text-align: center;
}
input[type=text] {
width: 230px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
margin: 0;
}

SpreadJS 在线示例