WINDOW関数には、集計関数、ランキング関数、分析関数の3つの種類があります。
集計関数: COUNT、AVERAGE、SUM、MAX、MINなど
ランキング関数: WRANK、ROWNUMBER、DENSERANK、WPERCENTRANK、CUMEDIST
分析関数: FIRSTVALUE、LASTVALUE、NTHVALUE、LEAD、LAG、NTILE
集約関数にORDERBYの設定は必須ではないが、window frameを定義することができます。
ROWNUMBER
構文
注意事項
パーティション内の現在の行番号を返します。行番号の範囲は1からパーティション行数までとなります。ORDERBYやwindow frameを設定する必要がありません。
W_RANK
構文
注意事項
パーティション内の各行の順位を返します。項目が同値の場合は並列で同順位になります。ORDERBYの設定が必要となり、window frameを定義することができません。
DENSERANK
構文
注意事項
パーティション内の各行の順位を間隔なしで返します。項目が同値の場合は並列で同順位になります。ORDERBYの設定が必要となり、window frameを定義することができません。
W_PERCENTRANK
構文
注意事項
パーティション内の順位をパーセント値（0～1の数値）で返します。行順位と対象行数を使用してパーセンテージを計算します：(順位 - 1) / (対象行数 - 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
(必須) バケットの個数
注意事項
ひとつのパーティションをN個のバケットに分割し、各行にバケット番号を割り当てます。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 charset="utf-8" />
<meta name="spreadjs culture" content="ja-jp" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/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$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/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$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.min.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;
}
