Column Expressions

Attention

Hashquery is currently in public preview. Here are some caveats to keep in mind for early adopters:

Backwards compatibility may not be preserved between version upgrades, and you may need to update your package regularly.
There may be differences in the SQL logic generated by hashquery compared to the Hashboard app.
You may encounter dialect-specific SQL syntax errors or other unexpected errors. If you come across one, let us know by reporting an issue.

column( name: str | None = None, *, sql: str | None = None, value: Any | None = None, ) → ColumnExpression

Constructs a ColumnExpression. This function has three variants:

column(str) will construct a column expression using the column name. This name is escaped according to the dialect.
column(sql=str) will construct an expression which uses the literal SQL, unescaped, as its contents. The syntax {{ some_expr }} can be used within this SQL string to reference attributes and measure definitions on the given Model. Note that when this is done, the referenced expressions can also become unescaped.
column(value=Any) will construct an expression which represents the provided Python value. For example, None is translated to NULL.

class ColumnExpression

named( name: str | KeyPath, ) → ColumnExpression

Forms a copy of this column expression with a new name. The name of a column expression will be the identifier when attached to a Model with with_attribute or with_measure. When queried, this will be analogous to an AS <name> clause.

All names are allowed except for names of the form __my_name__, which Hashquery reserves for internal use.

abstract disambiguated(namespace: ModelNamespace | str) → ColumnExpression

Some column expressions require scoping to a namespace, to avoid conflicting names. For example, a column(“id”) expression refers to different values when qualified with one table sales.id vs. another customers.id, despite being otherwise the same definition.

.disambiguated can be used to identify what namespace a given column expression needs to be qualified with.

If a column expression has not had disambiguated applied, it may still appear fully qualified in the final query, typically scoped to the namespace of the model being invoked (ie. the contents of the FROM clause).

property by_second: ColumnExpression: Truncate the target time column to the containing second.

property by_minute: ColumnExpression: Truncate the target time column to the containing minute.

property by_hour: ColumnExpression: Truncate the target time column to the containing hour.

property by_day: ColumnExpression: Truncate the target time column to the containing day.

property by_week: ColumnExpression: Truncate the target time column to the containing week. Sunday is considered the start of the week by default, though this can be changed in the Hashboard project’s settings.

property by_month: ColumnExpression: Truncate the target time column to the containing month.

property by_quarter: ColumnExpression: Truncate the target time column to the containing quarter.

property by_year: ColumnExpression: Truncate the target time column to the containing year.

property is_today: ColumnExpression: Filters the target time column to the current day.

property is_yesterday: ColumnExpression: Filters the target time column to yesterday.

property is_this_week: ColumnExpression: Filters the target time column to the current week. Note that weeks start on Sunday.

property is_last_week: ColumnExpression: Filters the target time column to the previous week. Note that weeks start on Sunday.

property is_this_month: ColumnExpression: Filters the target time column to the current month.

property is_last_month: ColumnExpression: Filters the target time column to the previous month.

property is_this_quarter: ColumnExpression: Filters the target time column to the current quarter.

property is_last_quarter: ColumnExpression: Filters the target time column to the previous quarter.

property is_this_year: ColumnExpression: Filters the target time column to the previous quarter.

property is_last_year: ColumnExpression: Filters the target time column to the previous quarter.

format_timestamp(format: Literal['iso'] | str = 'iso')

Formats a timestamp value as a string, according to a standard Python datetime.strftime format string. By default, uses an ISO 8601 representation.

This supports the following specifier tokens: - All tokens supported by datetime.strftime, except “%c”, “%x”, and “%X”. - %Q: The quarter number.

Notes: - Timezone tokens (%Z, %z and %:z) will produce empty strings

for timezone unaware values.

NULL values are not coerced or formatted. They will remain NULL in the output.

strftime(format: Literal['iso'] | str = 'iso')

Formats a timestamp value as a string, according to a standard Python datetime.strftime format string. By default, uses an ISO 8601 representation.

This supports the following specifier tokens: - All tokens supported by datetime.strftime, except “%c”, “%x”, and “%X”. - %Q: The quarter number.

Notes: - Timezone tokens (%Z, %z and %:z) will produce empty strings

for timezone unaware values.

NULL values are not coerced or formatted. They will remain NULL in the output.

bucket_other( *buckets: Model | Any, other: Any = 'Other', ) → ColumnExpression: Coerces any values for the target column not in buckets into the other value.

in_( other: str | Iterable[Any] | Model | ColumnExpression, /, *, case_sensitive: bool | None = True, ) → ColumnExpression

Returns a new ColumnExpression which is True for records where this column’s value is contained within the given other value, else False.

This method can accept strings for substring checking, iterables for checking if a value is in a given set, or Models for checking if a column is inside of a dynamically collected list of values.

contains( value: Any | ColumnExpression, /, *, case_sensitive: bool | None = True, ) → ColumnExpression

Returns a new ColumnExpression which is True for records where this column contains the following value.

For strings, this is a substring match, with the optional case_sensitive setting.

For arrays, this checks if the array contains the given value. case_sensitive is not currently supported for arrays.

contains_any( *values: Any | ColumnExpression, ) → ColumnExpression: Returns a new ColumnExpression which is True when this column contains any of the given values.

contains_all( *values: Any | ColumnExpression, ) → ColumnExpression: Returns a new ColumnExpression which is True when this column contains all of the given values.