🟰Fórmulas Admitidas

Arcwise le permite utilizar casi todas las fórmulas en datos arbitrariamente grandes. Las fórmulas se pueden utilizar directamente en datos conectados a Arcwise: Agregar Columnas. Las fórmulas también se pueden utilizar en Google Sheet.

Para hacer referencia a otras columnas en fórmulas, puede utilizar el nombre de la columna (por ejemplo, fecha_envío) o la notación A:A, B:B estilo hoja de cálculo. Al hacer clic en las columnas mientras el editor de fórmulas está abierto, se insertará automáticamente una referencia a la columna en el editor.

Examples formulas:

• MONTH(created_date) extraerá el mes de una columna de fecha

• MID(name, FIND(" ", name), LEN(name)) extraería el apellido de una columna de cadena que contiene nombres completos (por ejemplo, "Nombre Apellido")

Column Formulas and Operators

Los campos de fórmula admiten un subconjunto limitado de funciones de hoja de cálculo. Estos se traducirán automáticamente a su equivalente SQL. También se admiten operadores aritméticos estándar (+, -, /, *, ^).

Funciones lógicas

• AND(x, y)

• IF(logical_expression, value_if_true, value_if_false)

• IFNA(value, value_if_na)

  • NOTA: esto en realidad compara los valores NULL en la base de datos

  • Advertencia: IFERROR no es compatible actualmente. Para el caso de uso común de "detectar errores de división por cero", puedes usar: DIV0(a, b) en Snowflake y SAFE_DIVIDE(a, b) en BigQuery, lo que dará como resultado un valor NULL (vacío) en lugar de un error. .

• NOT(x)

• OR(x, y)

Funciones de cadena

• DATEVALUE(string)

• FIND(needle, haystack, [starting_position = 1])

• LEFT(string, number_of_characters)

• LEN(string)

• LOWER(string)

• INT(string)

  • Converts a string to an integer (rounding to the nearest integer)

• MID(string, starting_at, extract_length)

• REGEXEXTRACT(text, regular_expression)

  • NOTA: La función REGEXEXTRACT de Google Sheets completará varias columnas con múltiples grupos de coincidencias. Nuestra implementación siempre devolverá la parte coincidente completa del tema. Los diferentes tipos de almacenes (Snowflake, Bigquery, PostgreSQL) tienen cada uno sus propias implementaciones de REGEX. Al escribir REGEX, espere el comportamiento específico del almacén al que están conectados sus datos, ya que ejecutamos su REGEX sin ningún procesamiento en ese almacén de datos.

• RIGHT(string, number_of_characters)

• TRIM(string)

• UPPER(string)

• Concatenación de cadenas: ambas & y CONCATENATE(string1, [string2, ...])

Funciones numéricas

• INT(value)

• MAX(value1, [value2, ...])

• MIN(value1, [value2, ...])

• RAND()

• RANDBETWEEN(low, high)

• ROUND(value, [places])

• ROUNDDOWN(value, [places])

• ROUNDUP(value, [places])

• VALUE(val)

  • Convierte un valor (normalmente una cadena) en un valor numérico.

• Funciones matemáticas estándar: ABS, ACOS, ASIN, ATAN, COS, COT, DEGREES, EXP, FLOOR, LN, LOG, LOG10, MOD, PI, POWER, RADIANS, SIGN, SIN, SQRT, TAN

Funciones de fecha

• DATEDIF(date1, date2)

• DATEVALUE(string)

• DAY(date)

• EOMONTH(date)

• EDATE(date, number_of_months)

• MONTH(date)

• WEEKDAY(date)

• WEEKNUM(date)

• YEAR(date)

Uso avanzado: funciones de desplazamiento/ventana

Puede resultar útil pasar directamente a los ejemplos (ver más abajo).

• ROLLINGSUM(value)

  • Calcula la suma móvil de un valor o columna (también requiere que se agregue un orden de clasificación en la pestaña).

• ROLLINGSUMBY(value, group_column1, [group_column2, ...])

  • Calcula la suma continua de una columna en todas las filas con el mismo grupo_columna1, grupo_columna2, etc.

• ROLLINGMIN(value) & ROLLINGMINBY(value, group...)

  • Calcula el mínimo rodante de una columna (misma sintaxis que la anterior)

• ROLLINGMAX(value) & ROLLINGMAXBY(value, group, ...)

  • Calcula el máximo rodante de una columna (misma sintaxis que la anterior)

• FILLDOWN(value) & FILLDOWNBY(value, group, ...)

  • "Rellena" valores no vacíos/no NULL en una columna hacia abajo. En otras palabras, si el argumento del valor es actualmente NULL o está vacío, entonces tomaremos el valor anterior no vacío de la misma agrupación (si existe).

También tenemos versiones "no rodantes" de min/max/sum (MINBY, MAXBY, SUMBY) que calculan el mínimo/máximo/suma general en todas las filas dentro del mismo grupo.

Ejemplos

Esta tabla de ejemplo muestra el resultado de varios "cálculos sucesivos" diferentes utilizando columnas de valores y grupos hipotéticos. (suponiendo que la tabla ya esté ordenada).

Proporcionar un grupo básicamente significa que todas las filas dentro de un determinado grupo (por ejemplo, el grupo "a" o el grupo "b") tendrán sus valores móviles calculados de forma independiente. Puede proporcionar varias columnas de grupo para refinar aún más la agrupación.

value	group	ROLLINGSUM(value)	ROLLINGMINBY(value, group)	FILLDOWNBY(value, group)	SUMBY(value, group)
1	a	1	1	1	3
(empty)	a	1	1	1	3
2	a	3	1	2	3
(empty)	b	3	(empty)	(empty)	4
4	b	7	4	4	4
(empty)	b	7	4	4	4

Si el valor está en la columna A y el grupo está en la columna B, estas son las fórmulas equivalentes de Google Sheets:

(Suponiendo que C2, D2, E2, F2 comiencen en $A2)

• ROLLINGSUM(value)

  • C3 = $C2 + $A3 (arrastre hacia abajo)

• SUMBY(value, group)

  • D3 = SUMIF($B2:$B, $B3, $A2:$A)

• ROLLINGMINBY(value, group)

  • E3 = IF($B3=$B2, MIN($E3, $E2), $E3) (arrastre hacia abajo)

• FILLDOWNBY(value, group)

  • F3 = IF($B3=$B2, IF($F3<>"", $F3, $F2), $F3) (arrastre hacia abajo)

Uso avanzado: funciones SQL

Aunque no están documentadas oficialmente, la mayoría de las funciones SQL funcionarán dentro de los campos de fórmula de Arcwise. Consulta la documentación oficial de tu almacén.

Funciones útiles que generalmente funcionan en todas las bases de datos (ADVERTENCIA: a menudo son DIFERENTES de las funciones de Google Sheets del mismo nombre).

• REPLACE(source_string, search_string, replace_string)

  • Ejemplo: REPLACE("the quick fox", "quick", "slow")

• REGEXP_REPLACE(subject, pattern, replacement)

  • https://docs.snowflake.com/en/sql-reference/functions/regexp_replace

• REGEXP_SUBSTR(subject, pattern[, position[, occurrence]])

  • https://docs.snowflake.com/en/sql-reference/functions/regexp_substr

Enlaces a referencias:

• Snowflake functions: https://docs.snowflake.com/en/sql-reference/intro-summary-operators-functions

• BigQuery functions: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators

Fórmulas de agregación

Las fórmulas de agregación no se pueden utilizar para crear nuevas columnas en las pestañas de datos conectados de Arcwise: Agregar Columnas. Ellos se usan exclusivamente dentro de Google Sheets: Usar Fórmulas en Hojas.

• COUNT / COUNTIF / COUNTIFS

• COUNTUNIQUE / COUNTUNIQUEIFS

• SUM / SUMIF / SUMIFS

• AVERAGE / AVERAGEIF / AVERAGEIFS

• MIN / MINA / MINIFS

• MAX / MAXA / MAXIFS

• MEDIAN / MODE

• COUNTA / ROWS

  • NOTA: Independientemente de la columna pasada, siempre devolverán el número completo de filas en el conjunto de datos conectado.

• LOOKUP / XLOOKUP

  • Tanto BUSCAR como BUSCAR XL son iguales con Arcwise (y no requieren "datos ordenados"). Sin embargo, si hay varias coincidencias, una de ellas se seleccionará al azar.

  • Nota: por el momento, solo se admiten los primeros tres argumentos (clave_búsqueda, rango_búsqueda, rango_resultados).

• ARCFILTERIFS

  • Un cruce entre el FILTRO de hojas y una función IFS (devolverá todas las filas coincidentes en la tabla, pero toma pares de criterios como CONTAR.SI/SUMAR.)

• ARCUNIQUE

  • Devuelve todos los valores únicos en una columna.

• ARCQUERY

  • Ejecuta SQL en línea. Consulte la sección siguiente sobre ARCQUERY.

NOTA: Las agregaciones numéricas como SUMA, PROMEDIO, MIN, MAX, etc. no funcionarán con columnas de cadena por el momento.

Notas sobre funciones de criterios (*IF / *IFS)

• Para las columnas que no son cadenas en la base de datos, interpretamos el criterio de cadena vacía "" para contar el número de valores NULL. Por el contrario, "<>" contará el número de valores no nulos.

  • Ejemplo: =COUNTIF(ARCTable!A:A, "") contará el número de valores NULL en la columna A.

• Las columnas de fecha se pueden comparar con YYYY/MM/DD, MM/DD/YYYY, YYYY-MM-DD, MM-DD-YYYY criterios (o fechas de hojas de cálculo numéricas como 44322). Es posible que otros formatos no funcionen como se esperaba.

ARCQUERY

Exponemos una fórmula especial, ARCQUERY, para permitir consultas SQL en línea en una celda de una hoja de cálculo. Sintácticamente, los primeros argumentos deben ser columnas de tablas Arcwise y el segundo argumento debe ser una consulta SQL. En la consulta SQL, utilice el nombre de la hoja de origen en minúsculas para hacer referencia a la tabla de origen de Arcwise. Ejemplo:

=ARCQUERY(ArcTable!$A:$A, "SELECT * FROM arctable LIMIT 10")

Puede pasar columnas de varias hojas de Arcwise y consultarlas todas a la vez:

=ARCQUERY(ArcTable!$A:$A, Sheet2!$B:$B, "SELECT * FROM arctable JOIN sheet2
 ON arctable.id = sheet2.id")

Nota: Es posible que sea necesario citar las hojas con espacios u otros caracteres no alfanuméricos. Normalmente esto significa que el nombre de la tabla será, por ejemplo. "hoja 1" (si estás en BigQuery, usa comillas invertidas: "hoja 1"). Tenga en cuenta que las comillas dobles deben ir acompañadas de dos comillas dobles:

=ARCQUERY('Sheet 1'!$A:$A, "SELECT * FROM ""sheet 1""")

Limitaciones conocidas

Cuando se utilizan columnas Arcwise, las siguientes funciones no son compatibles actualmente (a partir de julio de 2023). ¡Contáctenos si tiene un caso de uso que los requiera!

• ARRAYFORMULA (y usando literales de matriz como {1,2,3} en general)

• ROW / COLUMN / INDIRECT

• LET / LAMBDA