The window functions have three types: aggregate functions, ranking functions and analytic functions.
Aggregate functions: COUNT, AVERAGE, SUM, MAX, MIN, etc.
Ranking functions: WRANK, ROWNUMBER, DENSERANK, WPERCENTRANK, CUMEDIST.
Analytic functions: FIRSTVALUE, LASTVALUE, NTHVALUE, LEAD, LAG, NTILE.
The aggregate functions don’t require ORDERBY, but they accept the window frame definition.
ROWNUMBER
Syntax
Usage notes
Returns the number of the current row within its partition. Row numbers range from 1 to the number of partition rows. It neither requires the ORDERBY nor accepts window frame definition.
W_RANK
Syntax
Usage notes
Returns the rank of each row within the partition of a result set. Peers are considered ties and receive the same rank. Requires ORDERBY, does not accept window frame definition.
DENSERANK
Syntax
Usage notes
Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. Requires ORDERBY, does not accept window frame definition.
W_PERCENTRANK
Syntax
Usage notes
Calculates the percentage of the relative rank of a row within a partition of rows. Using the rank of the row and the number of the total rows to compute the percentage: rank-1 / rows -1. And the range is [0,1]. Requires ORDERBY, does not accept window frame definition.
CUMEDIST
Syntax
Usage notes
Cumulative distribution value. The number of rows with values be less than or equal to the current row’s value is divided by the total number of rows within the partition. And the range is (0,1]. Requires ORDERBY, does not accept window frame definition.
LEAD
Syntax
Argument
Description
value_function
(Required) The field name or formula.
[offset_value]
(Optional) Row offset after the current, offset = 1.
[default_value]
(Optional) default value, default = null
Usage notes
Provides access to the value from a row at a given physical offset that follows the current row. If these is no such row, the return value is the default value. Does not accept window frame definition.
LAG
Syntax
Argument
Description
value_function
(Required) The field name or formula.
[offset_value]
(Optional) Row offset before the current, offset = 1.
[default_value]
(Optional) default value, default = null
Usage notes
Provides access to the value from a row at a given physical offset that before the current row. If these is no such row, the return value is the default value. Does not accept window frame definition.
NTILE
Syntax
Argument
Description
n
(Required) The count of buckets.
Usage notes
Divides a partition into N buckets, assigns each row in the partition its bucket number. Does not accept window frame definition.
FIRSTVALUE
Syntax
Argument
Description
value_function
(Required) The field name or formula.
Usage notes
The value for the first row within the window frame. It does not require ORDERBY.
LASTVALUE
Syntax
Argument
Description
value_function
(Required) The field name or formula.
Usage notes
The value for the last row within the window frame. It does not require ORDERBY.
NTHVALUE
Syntax
Argument
Description
value_function
(Required) The field name or formula.
n
(Required) The n-th row within the window frame.
Usage notes
The value for the n-th row within the window frame. If there is no such row, the return value is null. It does not require 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 charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/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$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.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;
}
