계산된 열

정상 동작

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 수식이 다른 행을 읽을 때마다 그렇습니다. 열 이름은 대소문자를 구분하지 않고 일치시킵니다.

작성읽기엑셀에서는
!Qty이 행의 수량.B7 7행에서
!Qty[1]이전 행의 수량.B6
!Qty[3]세 행 위의 수량.B4
!Qty[-1]다음 행의 수량.B8
!Qty[$1]현재 어느 행에 있든 첫 번째 행의 수량.B$2
!"Order Total"이름에 공백이나 특수 문자가 있는 열은 큰따옴표로 감쌉니다. 행 오프셋과도 함께 쓸 수 있습니다: !"Order Total"[1].
!Qty[2]:!Qty범위: 이 행과 그 앞 두 행의 Qty.B5:B7
!Jan:!Mar열 전체 범위: 결과의 열 순서대로 이 행의 Jan부터 Mar까지 모든 열.C7:E7
  • 첫 번째 행 앞은 비어 있음. !Qty[1] 는 첫 번째 행에서 빈 셀을 읽는데, 산술에서는 0으로 계산되고 "" 텍스트로 표시됩니다. 다음도 마찬가지입니다 !Qty[-1] 가 마지막 행에 있을 때도 마찬가지입니다.
  • 수식이 결과에 없는 열을 읽는 열 해당 결과에서는 제외되므로, 하나의 지시문으로 결과 집합마다 열이 다른 배치도 처리할 수 있습니다.
  • 범위는 함수 안에 들어갑니다. SUM(!Qty[2]:!Qty) 은(는) 괜찮습니다. 반면 맨 !Qty[2]:!Qty 수식 전체가 #VALUE!, Excel에서처럼.

변수

@name 쿼리가 선언한 값을 읽어 오므로 숫자 하나로 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 숫자로, bool TRUE 또는 FALSE로, date 그리고 datetime 실제 날짜로 취급하고 그 외는 텍스트로 취급합니다. 파라미터 스트립에 입력한 값이 선언된 기본값보다 우선하며, null 공백으로 읽습니다.
  • 선언되지 않은 변수 줍니다 #NAME?.

연산자

Excel과 같은 연산자들, 가장 느슨한 결합부터 가장 강한 결합까지:

연산자의미
= <> < > <= >=비교, TRUE 또는 FALSE를 반환. 텍스트는 대소문자를 구분하지 않고 비교하므로 "a" = "A" TRUE입니다.
&텍스트 연결: !First & " " & !Last.
+ -덧셈 및 뺄셈.
* /곱셈 및 나눗셈.
^거듭제곱.
%숫자 뒤에 붙는 퍼센트: 15% 는 0.15입니다.
- (앞에)부정.
:두 참조 사이의 범위.

Excel의 특이한 규칙은 의도적으로 그대로 담겨 있어서, 같은 수식이 그리드에서나 워크북에서나 같은 답을 냅니다: 부정은 ^, 그래서 -2^2 4입니다; 같은 등급의 연산자는 왼쪽에서 오른쪽으로 평가하므로 2^3^2 는 64입니다; 그리고 1+2&3 은(는) 다음 텍스트입니다 "33". 애매할 때는 괄호를 쓰세요. 숫자처럼 읽히는 텍스트는 산술 연산에서 숫자입니다("3"+4 7입니다), 그리고 TRUE는 1로 셉니다.

빈 셀, NULL 및 오류

  • A NULL 는 빈 셀입니다. 산술에서는 0으로 계산되고 "" 텍스트로 나타나며, Excel의 빈 셀과 같은 모습입니다. ISBLANK() 공백과 0을 구분합니다.
  • 오류 값은 Excel을 따릅니다 - 그리고 수식에서도 똑같이 다뤄집니다: #DIV/0! (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, …) 는 SQL에서 빌려 온 SQLly만의 추가 기능입니다. 공백도 다음도 아닌 첫 번째 인수를 뜻합니다 "", 그래도 안 되면 마지막 것. COALESCE(!Nickname, !FirstName, "Customer"). Excel 내보내기에서는 다음을 중첩한 형태로 씁니다 IF로 겹쳐 씁니다. Excel에는 그런 함수가 없기 때문입니다.
  • 날짜는 Excel과 동일하게 작동합니다. 데이터베이스의 날짜나 시간 열은 실제 날짜로 읽히므로 !ShippedOn - !OrderedOn 는 일 수이며, !Due + 30 날짜이며, EDATE, EOMONTH 그리고 DATEDIF 기대하는 대로 동작합니다.
  • TODAY() 그리고 NOW() 쿼리가 실행될 때 컴퓨터의 시계를 읽습니다.
  • TEXT Excel의 형식 코드를 사용합니다.: "#,##0.00", "0.0%", "000", "$#,##0", "yyyy-mm-dd", "mmm d, yyyy", "dddd", "h:mm AM/PM", "hh:mm:ss".

예시

수익 대비 비율로 계산한 마진으로, 0으로 나누는 오류를 방지하며 비용 열 뒤에 배치합니다. 여기에 다음 이름을 지정하세요 -- 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]

마지막 세 행의 이동 합계와 이동 평균:

-- sqlly column: 3-Month Sales = SUM(!Sales[2]:!Sales)
-- sqlly column: 3-Month Avg = AVERAGE(!Sales[2]:!Sales)

두 날짜 사이의 일수, 근무일, 다음 달 말일의 마감일:

-- 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 분기마다 다른 종류를 돌려주는 CASE 식은 텍스트가 되고, 그래서 IF(…, "", !A - !B) 텍스트 열이 됩니다; 이럴 때는 0 다음 대신 "" 쓰면 열이 숫자로 유지됩니다.
  • 어떤 결과 집합이 해당 열을 가져오나요. 수식이 읽는 열을 모두 갖춘 모든 결과 집합, 아니면 다음이 붙은 n번째 결과만 result=n.
  • 서식 지정은 다른 열에서와 같이 작동합니다. format:, conditional:, grid: 그리고 피벗도 계산된 열을 데이터베이스가 반환한 열처럼 처리합니다.
  • 매우 큰 결과. 메모리에 담기에 너무 큰 결과는 디스크에서 페이지 단위로 읽어 옵니다. 그러면 수식은 각 페이지에서 함께 읽어 온 행만 보고(다음을 위한 짧은 뒤돌아보기 [n] 참조), 그래서 누적 합계는 페이지마다 처음부터 다시 시작됩니다. 긴 누적 합계는 창 함수로 데이터베이스에서 계산하게 두세요. 예를 들면 SUM(Amount) OVER (ORDER BY InvoiceDate).
잠금이 이를 지켜 줍니다. 다음을 클릭하면 lock 결과에서 계산된 열을 그리드의 나머지 설정과 함께 잠금 블록에 작성하므로 매 실행 시 다시 나타납니다.