計算列

実装済み

結果自身の列を使った Excel の数式で求める列を追加します。Excel の数式バー、色分け、関数が使えます。

Excel の数式が書ければ、SQLly では任意のクエリ結果に列を追加できます。利益率、累計、前月からの変化、2 つの日付の間の営業日数。Excel で入力するのと同じ数式を入力すると、新しい列がデータの隣に表示されます。SQL での書き方を知っている必要はなく、クエリを再実行する必要もありません。

Excel テーブルの計算列のように動作します。列全体に 1 つの数式を書けば、すべての行で計算されます。新しく覚えることは、セルの指定方法だけです。結果には文字の代わりに列名があるためです。

最初の計算列を追加します。

  1. クエリを実行します。 どの結果でも使えます。たとえば次のような結果: Qty そして Price 列。
  2. ヘッダーを右クリック 新しい列の直前に来る列の名前を入力し、次を選択します 計算列を追加….
  3. 数式バーは結果の上に開きます: fx、「Name」フィールド、 =、そして数式フィールド。SQLly は名前(Computed)、クリックした列への参照から数式を始めます。たとえば =!Price.
  4. 列に名前を付け、数式を入力します - たとえば 行の合計 そして =!Qty * !Price.
  5. 押す Enter またはクリック 列を追加. Esc または キャンセル 何も変更することなくバーを閉じます。
SQLly
結果の上の数式バー: 数式内の各参照が固有の色で表示され、それが読み取るセルも、グリッド上で同じ色の枠で示されます。結果の上の数式バー: 数式内の各参照が固有の色で表示され、それが読み取るセルも、グリッド上で同じ色の枠で示されます。
結果の上の数式バー: 数式内の各参照が固有の色で表示され、それが読み取るセルも、グリッド上で同じ色の枠で示されます。

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 では) クエリから取り出されます。

SQLly
計算列を 2 つ(Line Total と Running Total)持つ結果。参照元の列の横に並び、クエリの先頭には sqlly の列指定行があります。計算列を 2 つ(Line Total と Running Total)持つ結果。参照元の列の横に並び、クエリの先頭には sqlly の列指定行があります。
計算列を 2 つ(Line Total と Running Total)持つ結果。参照元の列の横に並び、クエリの先頭には sqlly の列指定行があります。

いったん追加してしまえば、計算列も他の列と同じです。並べ替えも、フィルターも、数値の表示形式も自由で、色分けは次で行えます 条件付き書式、または次で使用 ピボット.

数式の読み取りと編集

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

そのまま使える数式

列数式
マージン %=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?、など。数式を次で囲みます IFERROR Excelと同じように。
  • 型は式に従います。 日付の演算結果は日付列、比較の結果は 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 エクスポート.