計算列
実装済みExcel の数式で書いた列を追加できます。累計、マージン、直前の行からの変化、日付など。
その column: ディレクティブは、結果自身の列を使ったExcelの数式で計算した列を、結果に追加します。数式言語はExcelのものです。同じ演算子、同じ優先順位、同じ引数を持つ同じ関数、同じエラー値です。唯一の違いはセルの指し方です。結果には英字の列ではなく名前付きの列があるため、参照では列を次の名前で指定します !.
-- sqlly column: Line Total = !Qty * !Price
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
SELECT OrderId, Qty, Price, Revenue, Cost FROM order_lines ORDER BY OrderId;
データベースにこの式は渡されません。SQLly が行の到着のたびに列を計算し、 Excel エクスポート Excel のライブ数式として書き込みます。ディレクティブを自分で入力する必要はありません。列ヘッダーを右クリックして、次を選択します 計算列を追加…、SQLly がその行を書き込みます。次の 計算列ガイド 数式バーについて順を追って説明します。
構文
-- sqlly column: <Name> = <formula>[; after=<column>][; result=<n>]
| パート | 意味 |
|---|---|
<Name> | 新しい列の名前。スペースや句読点は使えますが、次の文字は使えません =. |
<formula> | Excel の数式。先頭の = は省略可能です。Excel は必要としますが、SQLly はどちらでも問題ありません。 |
after=<column> | 新しい列をこの列の直後に配置します。指定しない場合、列は末尾に追加されます。 |
result=<n> | バッチの n 番目の結果セットにのみ追加します(1 から数えます)。省略すると、この列は、読み取り元の列を含むすべての結果セットに結合されます。 |
A ; 引用符で囲まれた文字列の中とかっこの中にあるものは数式の一部と見なされるため、 TEXTJOIN("; ", TRUE, !A, !B) 書いたとおりに動作します。後の column: 同じ名前の行は、以前の行を置き換えます。結果にすでにある列が置き換えられることはありません。クエリが次を返す場合、 Total、計算列の Total はその結果ではスキップされます。
リファレンス
行はクエリが返した順に番号が付けられるため、次を追加してください ORDER BY 式が別の行を読み取るたびに。列名は大文字と小文字を区別せずに照合されます。
| 書き込み | 読み取り | Excel で言うと |
|---|---|---|
!Qty | この行の Qty。 | B7 7 行目 |
!Qty[1] | 前の行の Qty。 | B6 |
!Qty[3] | 3 行上の Qty。 | B4 |
!Qty[-1] | 次の行の Qty。 | B8 |
!Qty[$1] | どの行にいても、先頭行の Qty。 | B$2 |
!"Order Total" | 名前に空白や句読点を含む列(二重引用符で囲みます)。行オフセットでも使えます: !"Order Total"[1]. | |
!Qty[2]:!Qty | 範囲: この行と、その前の 2 行の Qty。 | B5:B7 |
!Jan:!Mar | 列をまたぐ範囲: この行の Jan から Mar までのすべての列を、結果の列順で指します。 | C7:E7 |
- 最初の行より前は空白です。
!Qty[1]が最初の行にあると空のセルを読み取るため、算術では 0 として数えられ、テキストでは""テキスト内で。同じことが次にも当てはまります!Qty[-1]が最後の行にある場合も同じです。 - 結果に存在しない列を参照する数式を持つ列 はその結果では省略されるため、結果セットの列が異なるバッチでも 1 つのディレクティブで対応できます。
- 範囲は関数の内側に入れます。
SUM(!Qty[2]:!Qty)は問題ありません。単独の!Qty[2]:!Qty全式が#VALUE!、Excel と同様です。
変数
@name クエリが宣言した値を読み取るため、1 つの数値で SQL と数式の両方を制御できます:
-- sqlly set rate = 0.0825
-- sqlly param: @region text = West
-- sqlly column: Tax = ROUND(!Amount * @rate, 2)
-- sqlly column: Label = @region & " - " & !Customer
SELECT Customer, Amount FROM invoices WHERE Region = @region;
-- sqlly set値 数値のように見える場合は数値として、そうでなければテキストとして読み取られます。-- sqlly param:値 宣言された型のままになります:intそしてdecimal数値として、boolTRUEまたはFALSEとして、dateそしてdatetime実際の日付として、それ以外はテキストとして扱われます。パラメーターストリップに入力した値は、宣言された既定値より優先され、null空白として読み取られます。- 宣言されていない変数 提供します
#NAME?.
オペレーター
Excel と同じ演算子です。結合の弱いものから強いものの順:
| オペレーター | 意味 |
|---|---|
= <> < > <= >= | 比較演算で、TRUE または FALSE を返します。テキストは大文字小文字を区別せずに比較されるため、 "a" = "A" は TRUE です。 |
& | テキストを連結します: !First & " " & !Last. |
+ - | 加算と減算。 |
* / | 乗算と除算。 |
^ | べき乗。 |
% | パーセント。数値の後ろに書きます: 15% 0.15 です。 |
- (先頭) | 否定。 |
: | 2 つの参照間の範囲。 |
Excel の癖は意図してそのまま再現されるため、数式はグリッドでもワークブックでも同じ答えになります。否定 (単項マイナス) は、続く演算子よりも強く結合します ^、そのため -2^2 4 です。同順位の演算子は左から右に評価されるため、 2^3^2 は 64 です。さらに、 1+2&3 テキストは "33"。迷ったときは括弧を使ってください。数値として読めるテキストは、算術演算では数値として扱われます("3"+4 は 7) で、TRUE は 1 として数えられます。
空白、NULL、エラー
- A
NULL空欄のセルになります。 算術演算では 0 として、テキストでは""テキストでは、Excel の空のセルと同様に空文字として扱われます。ISBLANK()空白とゼロを区別します。 - エラーは Excel の - そして、数式でも同じように流れます:
#DIV/0!(ゼロ除算)、#VALUE!(型の異なる値。例:"x"+1),#REF!(存在しない列)、#NAME?(未知の関数または変数)、#NUM!(範囲外の数値。例:SQRT(-1))、および#N/A(値なし。次からNA()またはIFS一致なし)。エラーはセルに表示されますが、クエリを停止することはありません。 - Excel と同じようにエラーを捉えます:
IFERROR(!Profit / !Units, 0).
関数
以下のすべての関数は Excel と同じ順序で同じ引数を受け取り、同じ結果を返します。SQLly が知らない名前を使うと、次のようになります #NAME?.
- 論理
IF,IFS,IFERROR,IFNA,AND,OR,NOT,XOR,SWITCH,CHOOSE,TRUE,FALSE、そしてCOALESCE(下記参照)。- 情報
ISBLANK,ISNUMBER,ISTEXT,ISLOGICAL,ISERROR,ISERR,ISNA,ISEVEN,ISODD,N,T,NA.- 数学と統計
ABS,SIGN,ROUND,ROUNDUP,ROUNDDOWN,MROUND,INT,TRUNC,CEILING,CEILING.MATH,FLOOR,FLOOR.MATH,EVEN,ODD,MOD,QUOTIENT,POWER,SQRT,EXP,LN,LOG,LOG10,PI,SIN,COS,TAN,DEGREES,RADIANS,SUM,PRODUCT,AVERAGE,MEDIAN,MIN,MAX,COUNT,COUNTA,COUNTBLANK.- テキスト
LEFT,RIGHT,MID,LEN,UPPER,LOWER,PROPER,TRIM,CLEAN,CONCAT,CONCATENATE,TEXTJOIN,SUBSTITUTE,REPLACE,REPT,FIND,SEARCH,EXACT,TEXT,FIXED,VALUE,CHAR,CODE,TEXTBEFORE,TEXTAFTER.- 日付と時刻
DATE,TIME,TODAY,NOW,YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,WEEKDAY,WEEKNUM,ISOWEEKNUM,EDATE,EOMONTH,DATEDIF,DAYS,DATEVALUE,TIMEVALUE,NETWORKDAYS,WORKDAY.
COALESCE(a, b, …)は SQLly が SQL から借りてきた唯一の追加機能で、空白でも""、なければ最後のもの。COALESCE(!Nickname, !FirstName, "Customer")。Excel のエクスポートでは、次の入れ子形式として書き込まれますIFs、Excel にはそのような関数がないためです。- 日付は Excel と同じように扱われます。 データベースの日付/時刻列は実際の日付として読み込まれるため、
!ShippedOn - !OrderedOnは日数で、!Due + 30は日付であり、EDATE,EOMONTHそしてDATEDIF期待どおりに動作します。 TODAY()そしてNOW()クエリが実行されると、コンピュータのクロックを読みます。TEXTExcel の書式コードを受け付けます:"#,##0.00","0.0%","000","$#,##0","yyyy-mm-dd","mmm d, yyyy","dddd","h:mm AM/PM","hh:mm:ss".
例
収益に対する割合としてのマージン。ゼロ除算を防ぎ、Cost 列の後ろに配置します。次の -- sqlly format: column=Margin %; number=1; style=percent パーセントで表示するには:
-- sqlly column: Margin % = IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue); after=Cost
累計。この列は、直前の行にある自分自身の値を読み、 N() 最初の行の上の空白を 0 に変えます:
-- sqlly column: Running Total = !Amount + N(!"Running Total"[1])
SELECT InvoiceDate, Amount FROM invoices ORDER BY InvoiceDate;
前の行からの変化を、変化量とパーセンテージで表します。最初の行にはその前の行がないため、どちらもそこでは 0 を表示し、最初の値全体や #DIV/0!:
-- sqlly column: Change = IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1])
-- sqlly column: Change % = IFERROR(!Sales / !Sales[1] - 1, 0)
初月に対する各月の割合:
-- sqlly column: vs First = !Sales / !Sales[$1]
直近 3 行の移動合計と移動平均:
-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)
2つの日付の間の日数、営業日数、翌月末の期日:
-- sqlly column: Days to Ship = !ShippedDate - !OrderDate
-- sqlly column: Working Days = NETWORKDAYS(!OrderDate, !ShippedDate)
-- sqlly column: Due = EOMONTH(!InvoiceDate, 1)
数値と日付から組み立てたテキスト: TEXT:
-- sqlly column: Summary = !Customer & ": " & TEXT(!Amount, "$#,##0.00") & " on " & TEXT(!OrderDate, "mmm d, yyyy")
列が空の場合のフォールバック、およびティアラベル:
-- sqlly column: Contact = COALESCE(!Email, !Phone, "none on file")
-- sqlly column: Tier = IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze")
列の計算方法
- 宣言された順。 式は、同じ行内で自分より前に宣言された計算列と、前の行の計算列(自分自身を含む)を読み取れます。これにより、累計(ランニングトータル)が可能になります。
- 行が到着した順。
[1]常に、グリッドをソートした後の上の行ではなく、データベースがこの行の前に送信した行を指します。グリッドをソートまたはフィルターすると、計算された値は行とともに移動し、再計算はされません。 - 各列の型は数式から決まります。 算術演算は数値を返し、日付関数と日付演算は日付を返します (そのため日付書式と日付フィルターが機能します)。比較、
AND,ORそしてIS関数は TRUE/FALSE を返します。テキスト関数および&テキストを返します。IFの分岐が異なる型を返すものはテキストになるため、IF(…, "", !A - !B)テキスト列を作ります。次を使用してください0代わりに""数値のままにするには。 - 列を追加する結果セット。 式が読み取るすべての列を持つすべての結果セット、または次を指定した結果 n のみ
result=n. - フォーマットは、他の列と同じように機能します。
format:,conditional:,grid:、ピボットもすべて、計算列をデータベースが返した列と同じように扱います。 - 非常に大きな結果。 メモリに保持できないほど大きな結果は、ディスクからページ単位で読み込まれます。このとき数式から見えるのは、各ページで読み込まれた行だけです(加えて、短い遡り参照用の
[n]参照)。そのため、累計はページごとに最初からやり直しになります。全体を通した累計が必要な場合は、次のようなウィンドウ関数でデータベースに計算させましょうSUM(Amount) OVER (ORDER BY InvoiceDate).