Window関数一覧

Window関数は、スライディングウィンドウフレーム(現在の行に何らかの関連がある行の集合)に基づいて結果を計算します。Window関数を使用すると、集約値および非集約値を同時に計算することができます。

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; }