Changelog
New updates and improvements at Cloudflare.
You can now use the
HAVINGclause andLIKEpattern matching operators in Workers Analytics Engine ↗.Workers Analytics Engine allows you to ingest and store high-cardinality data at scale and query your data through a simple SQL API.
The
HAVINGclause complements theWHEREclause by enabling you to filter groups based on aggregate values. WhileWHEREfilters rows before aggregation,HAVINGfilters groups after aggregation is complete.You can use
HAVINGto filter groups where the average exceeds a threshold:SELECTblob1 AS probe_name,avg(double1) AS average_tempFROM temperature_readingsGROUP BY probe_nameHAVING average_temp > 10You can also filter groups based on aggregates such as the number of items in the group:
SELECTblob1 AS probe_name,count() AS num_readingsFROM temperature_readingsGROUP BY probe_nameHAVING num_readings > 100The new pattern matching operators enable you to search for strings that match specific patterns using wildcard characters:
LIKE- case-sensitive pattern matchingNOT LIKE- case-sensitive pattern exclusionILIKE- case-insensitive pattern matchingNOT ILIKE- case-insensitive pattern exclusion
Pattern matching supports two wildcard characters:
%(matches zero or more characters) and_(matches exactly one character).You can match strings starting with a prefix:
SELECT *FROM logsWHERE blob1 LIKE 'error%'You can also match file extensions (case-insensitive):
SELECT *FROM requestsWHERE blob2 ILIKE '%.jpg'Another example is excluding strings containing specific text:
SELECT *FROM eventsWHERE blob3 NOT ILIKE '%debug%'Learn more about the
HAVINGclause or pattern matching operators in the Workers Analytics Engine SQL reference documentation.
You can now perform more powerful queries directly in Workers Analytics Engine ↗ with a major expansion of our SQL function library.
Workers Analytics Engine allows you to ingest and store high-cardinality data at scale (such as custom analytics) and query your data through a simple SQL API.
Today, we've expanded Workers Analytics Engine's SQL capabilities with several new functions:
countIf()- count the number of rows which satisfy a provided conditionsumIf()- calculate a sum from rows which satisfy a provided conditionavgIf()- calculate an average from rows which satisfy a provided condition
New date and time functions: ↗
toYear()toMonth()toDayOfMonth()toDayOfWeek()toHour()toMinute()toSecond()toStartOfYear()toStartOfMonth()toStartOfWeek()toStartOfDay()toStartOfHour()toStartOfFifteenMinutes()toStartOfTenMinutes()toStartOfFiveMinutes()toStartOfMinute()today()toYYYYMM()
Whether you're building usage-based billing systems, customer analytics dashboards, or other custom analytics, these functions let you get the most out of your data. Get started with Workers Analytics Engine and explore all available functions in our SQL reference documentation.
You can now perform more powerful queries directly in Workers Analytics Engine ↗ with a major expansion of our SQL function library.
Workers Analytics Engine allows you to ingest and store high-cardinality data at scale (such as custom analytics) and query your data through a simple SQL API.
Today, we've expanded Workers Analytics Engine's SQL capabilities with several new functions:
argMin()- Returns the value associated with the minimum in a groupargMax()- Returns the value associated with the maximum in a grouptopK()- Returns an array of the most frequent values in a grouptopKWeighted()- Returns an array of the most frequent values in a group using weightsfirst_value()- Returns the first value in an ordered set of values within a partitionlast_value()- Returns the last value in an ordered set of values within a partition
bitAnd()- Returns the bitwise AND of two expressionsbitCount()- Returns the number of bits set to one in the binary representation of a numberbitHammingDistance()- Returns the number of bits that differ between two numbersbitNot()- Returns a number with all bits flippedbitOr()- Returns the inclusive bitwise OR of two expressionsbitRotateLeft()- Rotates all bits in a number left by specified positionsbitRotateRight()- Rotates all bits in a number right by specified positionsbitShiftLeft()- Shifts all bits in a number left by specified positionsbitShiftRight()- Shifts all bits in a number right by specified positionsbitTest()- Returns the value of a specific bit in a numberbitXor()- Returns the bitwise exclusive-or of two expressions
abs()- Returns the absolute value of a numberlog()- Computes the natural logarithm of a numberround()- Rounds a number to a specified number of decimal placesceil()- Rounds a number up to the nearest integerfloor()- Rounds a number down to the nearest integerpow()- Returns a number raised to the power of another number
lowerUTF8()- Converts a string to lowercase using UTF-8 encodingupperUTF8()- Converts a string to uppercase using UTF-8 encoding
hex()- Converts a number to its hexadecimal representationbin()- Converts a string to its binary representation
New type conversion functions: ↗
toUInt8()- Converts any numeric expression, or expression resulting in a string representation of a decimal, into an unsigned 8 bit integer
Whether you're building usage-based billing systems, customer analytics dashboards, or other custom analytics, these functions let you get the most out of your data. Get started with Workers Analytics Engine and explore all available functions in our SQL reference documentation.