計算列
実装済み結果自身の列を使った Excel の数式で求める列を追加します。Excel の数式バー、色分け、関数が使えます。
Excel の数式が書ければ、SQLly では任意のクエリ結果に列を追加できます。利益率、累計、前月からの変化、2 つの日付の間の営業日数。Excel で入力するのと同じ数式を入力すると、新しい列がデータの隣に表示されます。SQL での書き方を知っている必要はなく、クエリを再実行する必要もありません。
Excel テーブルの計算列のように動作します。列全体に 1 つの数式を書けば、すべての行で計算されます。新しく覚えることは、セルの指定方法だけです。結果には文字の代わりに列名があるためです。
最初の計算列を追加します。
- クエリを実行します。 どの結果でも使えます。たとえば次のような結果:
QtyそしてPrice列。 - ヘッダーを右クリック 新しい列の直前に来る列の名前を入力し、次を選択します 計算列を追加….
- 数式バーは結果の上に開きます: fx、「Name」フィールド、
=、そして数式フィールド。SQLly は名前(Computed)、クリックした列への参照から数式を始めます。たとえば=!Price. - 列に名前を付け、数式を入力します - たとえば 行の合計 そして
=!Qty * !Price. - 押す Enter またはクリック 列を追加. Esc または キャンセル 何も変更することなくバーを閉じます。


A1 から !Name へ
Excel では次のように表示されます B7、SQLly は次のように表示します !Qty: 感嘆符と列名。式のそれ以外の部分は通常の Excel です。
| Excel (7 行目) | SQLly で | 意味 |
|---|---|---|
=B7*C7 | =!Qty * !Price | この行の Qty × この行の Price。 |
=B7-B6 | =!Qty - !Qty[1] | 前の行からの変化。 [2] は 2 行上です。 |
=B8 | =!Qty[-1] | 直後の行。 |
=B7/B$2 | =!Qty / !Qty[$1] | すべての行で固定されたままの最初の行と比較します。 |
=SUM(B5:B7) | =SUM(!Qty[2]:!Qty) | この行と、その前の 2 行。 |
=SUM(C7:E7) | =SUM(!Jan:!Mar) | Jan から Mar までの列にまたがる合計。 |
=B7*TaxRate | =!Qty * @rate | 名前付き値。 @rate 次を読み取ります -- sqlly set または -- sqlly param: クエリからの値。 |
スペースを含む列名は二重引用符で囲みます: !"Order Total"、または !"Order Total"[1] 前の行用。名前は大文字小文字を区別しないため、 !qty こちらでも動作します。
関数は Excel と同じもので、名前、引数、計算結果も同じです: IF, IFERROR, SUM, ROUND, TEXT, LEFT, DATE, EOMONTH, NETWORKDAYS ほかにも約100個。演算子も同様で、 & テキストの結合用、および %。その 参照ページ すべての関数を一覧表示します。SQL からの追加が 1 件あります: COALESCE(a, b, …) 空でない最初の値を返します。
数式が参照している内容を確認する
Excel は数式の入力中に各参照に色を付け、対応するセルの周りに同じ色の枠を描画します。SQLly も同じことを行います:
- 各参照に独自の色が付きます - Excel と同じ順序で、青、赤、紫、緑、オレンジと続きます。2 回使う参照には同じ色が付きます。色付きの数式は、入力中にフィールドの下に表示されます。
- グリッドは、読み取るセルを枠で囲みます - 同じ色で、それらの列に色味を付けます。枠線は選択している行(なければ先頭行)に対して描画されるため、
!Qty[1]上の行のセルを目に見える枠で囲みます。 - ミスはフィールドの下に具体的に表示されます - 「対応する ( のない ) があります。」のようなメッセージや、列を次のように書くことを促すヒント
!Name、そして 列を追加 式を正しく解釈できるようになるまで待機します。
適用すると何が起こるか
列は、すでに画面上にある行から即座に計算されます。データベースには何も送信されず、再実行もされないため、クエリが遅かった場合でも一瞬で表示されます。
SQLly は、実行されたステートメントの先頭に、その数式を 1 行のコメントとしてクエリに書き込みます。
-- sqlly column: Line Total = !Qty * !Price; after=Price
SELECT OrderId, Product, Qty, Price FROM order_lines ORDER BY OrderId;
その行こそが、列が戻ってくる仕組みです。明日クエリを実行し、 .sql ファイルに保存して同僚に送れば、その列は必ずそこにあります。データベースはコメントを無視するので、クエリは他の環境でもそのまま実行できます。編集は1回だけで済むため、 ⌘Z (CtrlZ Windows と Linux では) クエリから取り出されます。


いったん追加してしまえば、計算列も他の列と同じです。並べ替えも、フィルターも、数値の表示形式も自由で、色分けは次で行えます 条件付き書式、または次で使用 ピボット.
数式の読み取りと編集
- 計算列の任意のセルをクリックします そして数式バーには、同じ色と枠線で、その数式が読み取り専用で表示されます。数値がどのように算出されたかを確認する最も手早い方法です。
- 「数式を編集」をクリック そのバーで、または列のヘッダーを右クリックして次を選択します 数式を編集…、変更します。ボタンの表示は次のようになります 列を更新.
- 編集を適用すると、その列の行が置き換えられます クエリ内に 2 つ目を追加するのではなく、既存のものを、ロックブロック内を含めどこにあっても更新します。名前フィールドで列名を変更すると、行も一緒に名前が変更されます。


そのまま使える数式
| 列 | 数式 |
|---|---|
| マージン % | =IF(!Revenue = 0, 0, (!Revenue - !Cost) / !Revenue) |
| 累計 | =!Amount + N(!"Running Total"[1]) (列名を 累計) |
| 前の行からの変化 | =IF(ISBLANK(!Sales[1]), 0, !Sales - !Sales[1]) |
| 成長率% | =IFERROR(!Sales / !Sales[1] - 1, 0) |
| 初月を基準とした指数化 | =!Sales / !Sales[$1] |
| 直近3か月の移動合計 | =SUM(!Sales[2]:!Sales) |
| 発送までの日数 | =!ShippedDate - !OrderDate |
| 月末が期日 | =EOMONTH(!InvoiceDate, 1) |
| 期限超過日数 | =MAX(0, TODAY() - !DueDate) |
| 読みやすいラベル | =!Customer & ": " & TEXT(!Amount, "$#,##0.00") |
| 最適な連絡先 | =COALESCE(!Email, !Phone, "none on file") |
| ティア | =IFS(!Amount >= 10000, "Gold", !Amount >= 1000, "Silver", TRUE, "Bronze") |
累計が成り立つのは、計算列が直前の行にある自分自身の値を読めるからです。数式は、同じ行で、それより前に追加された任意の計算列を読むこともできます。
スプレッドシートと異なる点
- 行の順序はクエリによって決まります。
[1]データベースがこの行の直前に送信した行を意味します。累計や前月からの変化を求めるには、クエリの末尾に次を付けます:ORDER BYそのため、行は意図した順序で届きます。後からグリッドを並べ替えると、値は行とともに移動します。値が再計算されることはありません。 - 列ごとに 1 つの数式。 Excel テーブルの計算列と同様に、すべての行が同じ数式を使います。個々のセルだけを上書きすることはできません。
- 空の値は空のセルです。 A
NULLデータベースから取得した値は、算術演算では 0、テキストとしては空白として扱われます (Excel の空のセルと同じです)。最初の行より上も空です。 - Excel のエラーと同じ形式で表示されます。 0 で割ると
#DIV/0!そのセルでは、誤ったスペルの関数#NAME?、など。数式を次で囲みますIFERRORExcelと同じように。 - 型は式に従います。 日付の演算結果は日付列、比較の結果は TRUE/FALSE、テキスト関数の結果はテキストになります。もし
IF数値またはのどちらかを返せます""、列はテキストになります。次を返します0代わりにそちらを使えば、数値のまま保てます。 - 非常に大きな結果。 メモリに保持できないほど大きな結果は、ディスクから 1 ページずつ読み込まれ、数式から見えるのは各ページの行だけです。そのため累計は、ページごとに最初からやり直されます。
クエリと一緒に保存
数式はすでにクエリ内に -- sqlly column: 行。さらに次をクリックした場合 ロック 結果名の前に付けると、SQLly はグリッドのその他の設定(列の順序、幅、書式、色)とともに、計算列もロックブロックに書き込みます。
Excel に持っていく
結果を Excel にエクスポートするか、次を使用します スプレッドシートで開く、各計算列は実際の Excel の数式として出力されます。 =!Qty * !Price シートの 7 行目に =B7*C7, !Qty[1] は直前の行になります、 !Qty[$1] になります B$2、そして @rate はワークブック内の名前付き値になります。Excelで数値を変更すると列が再計算されます。詳細は Excel エクスポート.