window 函数有三种类型：聚合函数、排名函数和分析函数。
聚合函数：COUNT（计数）、AVERAGE（平均）、SUM（求和）、MAX（最大值）、MIN（最小值）等。
排名函数：WRANK（窗口排名）、ROWNUMBER（行号）、DENSERANK（密集排名）、WPERCENTRANK（百分比排名）、CUMEDIST（累积分布）等。
分析函数：FIRSTVALUE（首个值）、LASTVALUE（最后一个值）、NTHVALUE（第N个值）、LEAD（向前查找）、LAG（向后查找）、NTILE（等分）等。
聚合函数不需要使用ORDERBY，但它们接受window frame定义。
ROWNUMBER
语法
用例语法
返回当前行在其分区中的编号。行号从1到分区行数之间。它既不需要ORDERBY，不接受window frame定义。
W_RANK
语法
用例语法
返回结果集中每行在分区中的排名。同级行被视为并列并获得相同的排名。需要使用ORDERBY，不接受window frame定义。
DENSERANK
语法
用例语法
返回当前行在其分区中的排名，没有间隔。同级行被视为并列并获得相同的排名。需要使用ORDERBY，不接受window frame定义。
W_PERCENTRANK
语法
用例语法
计算行在行分区中相对排名的百分比。使用行的排名和总行数来计算百分比：排名-1 / 总行数-1。百分比的范围是[0,1]。需要使用ORDERBY，不接受window frame定义。
CUMEDIST
语法
用例语法
累积分布值。将小于或等于当前行值的行数除以分区内的总行数。范围为(0,1]。需要使用ORDERBY，不接受window frame定义。
LEAD
语法
参数
描述
value_function
(必须) 字段名称或公式。
[offset_value]
(可选) 当前行之后的行偏移量，偏移量默认为1。
[default_value]
(可选) 默认值，默认为null。
用例语法
提供对位于当前行之后给定物理偏移量处的行的值的访问。如果不存在这样的行，则返回默认值。不接受window frame定义。
LAG
语法
参数
描述
value_function
(必须) 字段名称或公式。
[offset_value]
(可选) 当前行之前的行偏移量，偏移量默认为1。
[default_value]
(可选) 默认值，默认为null。
用例语法
提供对位于当前行之前给定物理偏移量处的行的值的访问。如果不存在这样的行，则返回默认值。不接受window frame定义。
NTILE
语法
参数
描述
n
(必须) buckets的个数
用例语法
将一个分区划分为N个buckets，并为分区中的每一行分配一个buckets编号。不接受window frame定义。
FIRSTVALUE
语法
参数
描述
value_function
(必须) 字段名称或公式。
用例语法
window frame内第一行的值。不需要使用ORDERBY。
LASTVALUE
语法
参数
描述
value_function
(必须) 字段名称或公式。
用例语法
window frame内最后一行的值。不需要使用ORDERBY。
NTHVALUE
语法
参数
描述
value_function
(必须) 字段名称或公式。
n
(必须) window frame内的第n行。
用例语法
window frame 内第n行的值。如果没有这样的行，则返回值为null。不需要使用ORDERBY。
/*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;
ratioMedals(spread);
countyMedals(spread);
athleteDenseRankMedals(spread);
employeeDepartmentPayRank(spread);
saleDivideGroups(spread);
timeToNextStation(spread);
revenueTrends(spread);
trainTravelTime(spread);
spread.resumePaint();
}
function ratioMedals(spread) {
//init a data manager
var dataManager = spread.dataManager();
var athleteMedalTable = dataManager.addTable("athleteMedalTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/olympic-athlete-medals.csv"
}
},
schema: {
type: "csv",
columns: {
Medals: { dataType: "number" },
TotalMedals: { dataType: "formula", value: '=WINDOW(SUM([Medals]), "CountryMedals")' },
},
window: {
CountryMedals: "=WINDOWDEF(PARTITIONBY([Country]), ORDERBY([Country], ORDERDESC([Medals])), FRAMEROWS(-1, -1))"
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "Ratio Of Medals", 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
athleteMedalTable.fetch().then(function () {
var myView = athleteMedalTable.addView("myView", [
{ value: "Country", width: 100 },
{ value: "Athlete", width: 80 },
{ value: "Medals", width: 80 },
{ value: "TotalMedals", caption: 'Total Medals', width: 150, style: { backColor: "#E2EFDA" } },
{ value: "=[@Medals] / [@TotalMedals]", caption: 'Ratio Of Medals', width: 150, style: { backColor: "#E2EFDA", formatter: '0.00%' } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function countyMedals(spread) {
//init a data manager
var dataManager = spread.dataManager();
var countryMedalTable = dataManager.addTable("countryMedalTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/olympic-country-medals.csv"
}
},
schema: {
type: "csv",
columns: {
Medals: { dataType: "number" },
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(1, "Country Medals Rank", 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
countryMedalTable.fetch().then(function () {
var myView = countryMedalTable.addView("myView", [
{ value: "Country", width: 100 },
{ value: "Medals", width: 80 },
{ value: "=WINDOW(W_RANK(), ORDERBY(ORDERDESC([Medals])))", caption: 'Rank', width: 150, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function athleteDenseRankMedals(spread) {
//init a data manager
var dataManager = spread.dataManager();
var athleteMedalTable = dataManager.addTable("athleteMedalRankTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/olympic-athlete-medals.csv"
}
},
schema: {
type: "csv",
columns: {
Medals: { dataType: "number" },
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(2, "Athlete Medals Rank", 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
athleteMedalTable.fetch().then(function () {
var myView = athleteMedalTable.addView("myView", [
{ value: "=WINDOW(ROWNUMBER(), PARTITIONBY([Country]))", caption: 'No.', width: 70, style: { backColor: "#E2EFDA" } },
{ value: "Country", width: 100 },
{ value: "Athlete", width: 80 },
{ value: "Medals", width: 80 },
{ value: "=WINDOW(DENSERANK(), PARTITIONBY([Country]), ORDERBY(ORDERDESC([Medals])))", caption: 'Rank', width: 150, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function employeeDepartmentPayRank(spread) {
//init a data manager
var dataManager = spread.dataManager();
var employeeDepartmentPayTable = dataManager.addTable("employeeDepartmentPayTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/employee-department-pay.csv"
}
},
schema: {
type: "csv",
window: {
DepartmentRate: "=WINDOWDEF(PARTITIONBY([Department]), ORDERBY([Rate]))"
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(3, "Employee Pay Rank", 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
employeeDepartmentPayTable.fetch().then(function () {
var myView = employeeDepartmentPayTable.addView("myView", [
{ value: "Department", width: 120 },
{ value: "LastName", width: 120 },
{ value: "Rate", width: 80 },
// The CUMEDIST returns a value that represents the percent of employees with a salary less than or equal to the current employee in the same department.
{ value: "=WINDOW(CUMEDIST(), \"DepartmentRate\")", caption: 'Salary % ( <= current)', width: 180, style: { backColor: "#E2EFDA", formatter: '0.00' } },
// The W_PERCENTRANK function calculates the percent rank of the employee's salary within a department.
{ value: "=WINDOW(W_PERCENTRANK(), \"DepartmentRate\")", caption: 'Salary %', width: 140, style: { backColor: "#E2EFDA", formatter: '0.00' } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function timeToNextStation(spread) {
//init a data manager
var dataManager = spread.dataManager();
var timeToNextStationTable = dataManager.addTable("timeToNextStationTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/train-details.csv"
}
},
schema: {
type: "csv",
columns: {
TrainNo: { dataName: 'Train No', dataType: 'number' },
TrainName: { dataName: 'Train Name' },
ArrivalTime: { dataName: 'Arrival time' },
DepartureTime: { dataName: 'Departure Time', },
LeadArrivalTime: { dataType: 'formula', value: '=WINDOW(LEAD([@ArrivalTime]), PARTITIONBY([TrainNo]))' },
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(4, "Time To Next Station", 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
timeToNextStationTable.fetch().then(function () {
var myView = timeToNextStationTable.addView("myView", [
{ value: "TrainNo", caption: "Train No", width: 120 },
{ value: "TrainName", caption: "Train Name", width: 120 },
{ value: "ArrivalTime", caption: "Arrival Time", width: 120 },
{ value: "DepartureTime", caption: "Departure Time", width: 150 },
{ value: "=IF(ISBLANK([@LeadArrivalTime]), 0 ,TIMEVALUE([@LeadArrivalTime]) - TIMEVALUE([@DepartureTime]))", caption: "Time To Next Station", width: 180, style: { backColor: "#E2EFDA", formatter: 'h:mm:ss;@' } },
{ value: '=TIMEVALUE([@ArrivalTime]) - WINDOW(MIN(MAP([ArrivalTime], LAMBDA(time,TIMEVALUE(time)))), PARTITIONBY([TrainNo]))', caption: "Elapsed Travel Time", width: 180, style: { backColor: "#E2EFDA", formatter: 'h:mm:ss;@' } },
]);
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" },
PreviousRevenue: { dataType: "formula", value: '=WINDOW(LAG([@Amount]), "ProductYear")' }
},
window: {
ProductYear: "=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Year]))"
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(5, "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" } },
{ value: "PreviousRevenue", caption: 'Previous Revenue', width: 180, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
{ value: "=[@Amount] - IF(ISBLANK([@PreviousRevenue]), 0, [@PreviousRevenue])", caption: 'Revenue Trends', width: 150, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function saleDivideGroups(spread) {
//init a data manager
var dataManager = spread.dataManager();
var saleYearToDateDivideGroupTable = dataManager.addTable("saleYearToDateDivideGroupTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/sale-ytd.csv"
}
},
schema: {
type: "csv",
columns: {
SalesYTD: { dataType: "number" },
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(6, "Sale Groups", 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
saleYearToDateDivideGroupTable.fetch().then(function () {
var myView = saleYearToDateDivideGroupTable.addView("myView", [
{ value: "City", width: 100 },
{ value: "FirstName", width: 100 },
{ value: "LastName", width: 100 },
// It divides rows into four groups of employees based on their year-to-date sales
{ value: "=WINDOW(NTILE(4), PARTITIONBY([City]), ORDERBY(ORDERDESC([SalesYTD])))", caption: 'Quartile', width: 100, style: { backColor: "#E2EFDA" } },
{ value: "SalesYTD", caption: 'Sales', width: 100, style: { formatter: "$#,##0.00" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function trainTravelTime(spread) {
//init a data manager
var dataManager = spread.dataManager();
var trainTravelTimeTable = dataManager.addTable("trainTravelTimeTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/train-details.csv"
}
},
schema: {
type: "csv",
columns: {
TrainNo: { dataName: 'Train No', dataType: 'number' },
TrainName: { dataName: 'Train Name' },
ArrivalTime: { dataName: 'Arrival time' },
DepartureTime: { dataName: 'Departure Time', },
LastArrivalTime: { dataType: 'formula', value: '=WINDOW(LASTVALUE(TIMEVALUE([@ArrivalTime])), PARTITIONBY([TrainNo]))' },
FirstDepartureTime: { dataType: 'formula', value: '=WINDOW(FIRSTVALUE(TIMEVALUE([@DepartureTime])), PARTITIONBY([TrainNo]))' },
ForthArrivalTime: { dataType: 'formula', value: '=WINDOW(NTHVALUE(TIMEVALUE([@ArrivalTime]), 4), PARTITIONBY([TrainNo]))' },
SecondArrivalTime: { dataType: 'formula', value: '=WINDOW(NTHVALUE(TIMEVALUE([@DepartureTime]), 2), PARTITIONBY([TrainNo]))' },
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(7, "Train Travel Time", 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
trainTravelTimeTable.fetch().then(function () {
var myView = trainTravelTimeTable.addView("myView", [
{ value: "TrainNo", caption: "Train No", width: 120 },
{ value: "TrainName", caption: "Train Name", width: 120 },
{ value: "ArrivalTime", caption: "Arrival Time", width: 120 },
{ value: "DepartureTime", caption: "Departure Time", width: 150 },
{ value: '=[@LastArrivalTime] - [@FirstDepartureTime]', caption: "Max Travel Time", width: 150, style: { backColor: "#E2EFDA", formatter: 'h:mm:ss;@' } },
{ value: "=[@ForthArrivalTime] - [@SecondArrivalTime]", caption: "2nd - 4th Station Travel Time", width: 220, style: { backColor: "#E2EFDA", formatter: 'h:mm:ss;@' } },
]);
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;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
margin: 0;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.container {
width: 300px;
height: 100%;
float: left;
border: 1px solid lightgrey;
}
