Cheatography
Login Register

Devo LINQ, query language syntax Cheat Sheet

A Programming › Programming cheat sheet in English by elpluto

List of Devo platform LINQ query language flavor

Log Linq Devo
58,358 views 5,477 downloads 2 Jan 2023 History
PRs
Download

Structure keywords

from
Point to a table for the query. Specify a table name using tags.
from my.app.we­b.auth
where
Where clause to filter results.

where [filter1 expres­sion], [filter2 expression]

String values in expres­sions have to be surrounded by double quotes, single quotes are not allowed.
select
Add column to result set:
select source­_column or column operation as destination_column
select uriHos­t(uri) as host
group every / by
Group clause with an optional server and client aggreg­ation period filter:
group [every server­_pe­riod] [by column]
[every client_period]
group by statusCode
group every 10m
group every 10m by statusCode every 1m
every
Client aggreg­ation filter:
every period

Period syntax: an integer number follow by a symbol indicating the time period:[s:sec­onds, m:minutes, h:hours, d:days, no suffix­:mi­lli­sec­onds]

every 0 means no client period.

every 5m by serverIp
ifthenelse
if/the­n/else equivalent clause to set condit­ionally column values:

ifthenelse(condi­tion, errorV­alue, succes­sValue )

select ifthenelse(statu­sCode != 200,"Er­ror­"­,"Su­cce­ss") as status­Cod­eDesc
decode
switch­/case equivalent clause to set condit­ionally column values:

decode(column, checkV­alue, value,­[ch­eck­Value2, value2])

Each pair of arguments [check­Value, value] is equivalent to a case sentence of a switch statement.

decode(statu­sCode, 200, "­Suc­ces­s", 400, "Not Found", 406, "­Err­or", 404, "­Err­or") as status­Cod­eDesc
nvl
Null-C­oal­escing operator. Allow to set an alternate value when input value is null.

nvl(column, altern­ate­_va­lue­_wh­en_­null)`

Aggreg­ation functions and operators

avg
Returns the average of a range of values [avg] or only over not null values [nnavg] of the results on each group:

avg(column); nnavg(column)
count
Returns the count of results on each group:

count([column])

With argument, include only not null entries in the count.
first / nnfirst
Returns the first or the not null first entry of the results on each group:

first(column); nnfirst(column)
last / nnlast
Returns the last or the not null last entry of the results on each group:

last(column); nnlast(column)
max / min
Returns the maximum or the minimum value for the columns provided, on each group:

max/min(col1, [col2], [col3]…)
median
Returns the statis­tical median for a column on each group:

median(column)

Restricted to columns of integer type
sum
Returns the sum of the results on each group:

sum(column)
sum2
Returns the sum of the squares of the results on each group:

sum2(column)
percen­tile5
percen­tile10
percen­tile25
percen­tile75
percen­tile90
percen­tile95
Returns the specific statistic percen­tileN, using linear interp­ola­tion, of the results on each group:

percen­tile[N](column)
stddev / nnstddev
Returns the biased standard deviation [stddev] of the values or not null values [nnstddev] of the results on each group:

stddev­/nn­stddev(column)

Biased
ustddev / nnustddev
Returns the unbiased standard deviation [ustddev] of the values or not null values [unnst­ddev] of the results on each group:

ustdde­v/u­nns­tddev(column)

Unbiased
var / nnvar
Returns the biased variance [var] of the values or not null values [nnvar] of the results on each group:

var/nnvar(column)

Biased
uvar / nnuvar
Returns the unbiased variance [uvar] of the values or not null values [unnvar] of the results on each group:

uvar/u­nnvar(column)

Unbiased
hllpp
Returns the estimated count of distinct values of the results on each group using the HyperL­ogLog++ algorithm:

hllpp(column)

Applies on DC (distinct count) data types.
hllppcount
Returns the estimated count of distinct values of the results on each group using the HyperL­ogLog++ algorithm:

hllppcount(column)

Applies on float or integer data types.

String operators and functions

has, [->]
Case sensitive contains compar­ison. Using the operator -> only allows check one value:

has(column, value1, [value2],…)

column -> value1
weakhas
Case insens­itive contains comparison:
weakha­s(c­olumn, value)
in, [<-]
Case sensitive is contained compar­ison. Using the operator '­<-' allows only one value:
in(value1, [value­2],­[...], column)
value1 <- column
weakin
Case insens­itive is contained comparison:
weakin­(value, column)
startswith
Returns strings that start with specific value:
starts­wit­h(c­olumn, value)
endswith
Returns strings that end with a specific value:
endswi­th(­column, value)
toktains
Specia­lized contains function for ASCII delimited tokens:
toktai­ns(­column, value, [bool_­left], [bool_­right])
length
Returns the length of a string value:
length­(co­lumn)
locate
Returns the position of a substring, indexOf function:
locate­(co­lumn, substr­ing­_to­Locate)
lower
Returns the transf­orm­ation to lower case:
lower(­column)
upper
Returns the transf­orm­ation to upper case:
upper(­column)
replace
Replaces only first occurrence of a string with a substitute string:
replac­e(c­olumn, string­ToS­earch, string­ToR­eplace)
replaceall
Replaces all occurr­ences of a search string with a substitute string:
replac­eal­l(c­olumn, string­ToS­earch, string­ToR­eplace)
split
Returns a specific piece of splitting operation by a separator:
pieceN­umber begin at 0.
split(­column, separa­tor­String, pieceN­umber)
splitre
Returns a specific piece of splitting operation by a regular expression:
pieceN­umber begin at 0.
splitr­e(c­olumn, re(string) or regexp, pieceN­umber)
substring
Returns a substring beginning at specific index with the provided length:
substr­ing­(co­lumn, index, length)
subs
Returns a string replacing first substring occurrence based on a regular expression using a template string as substi­tution value:
FailValue is returned when is provided and no occurr­ences found
subs(c­olumn, regexp, template, [failValue])
subs(c­olumn, re(str­ing), templa­te(­str­ing), [failV­alue])
subsall
Returns a string replacing all substring occurr­ences based on a regular expression using a template string as substi­tution value:
FailValue is returned when is provided and no occurr­ences found
subs(c­olumn, regexp, template, [failValue])
subs(c­olumn, re(str­ing), templa­te(­str­ing), [failV­alue])
trim
Returns the result of trimming both sides:
trim(c­olumn)
ltrim
Returns the result of trimming left side:
ltrim(­column)
rtrim
Returns the result of trimming right side:
rtrim(­column)
matches, [~]
Matches function that finds occurr­ences in a column using a regular expression:
matche­s(c­olumn, re(string) or regexp value)
column ~ re(string) or regexp value
peek
Returns the part of a string based on a regular expres­sion, optionally indicating a specific part occurrence:
If no partNumber is provided then returns first part occurrence.
peek(column, re(string) or regexp, [partN­umber])
format­number
Format a number with a specific mask and locale:
format­num­ber­(nu­mbe­rCo­lumn, mask, locale)
format­num­ber­(to­tal­Amount, "­###.##­", "­en-­GB")
damerau
Returns Damerau distance:
damera­u(c­olumn, value)
hamming
Returns Hamming distance:
hammin­g(c­olumn, value)
levens­htein
Returns Levenstein distance:
levens­hte­in(­column, value)
osa
Returns osa distance:
osa(co­lumn, value)
public­suffix
Returns the main public suffix of a hostname:
publicsuffix(hostnameColumn))
'­www.my.si­te.c­o.u­k' = '­co.u­k'
rootdomain
Returns the root domain of a hostname part of an url:
rootdomain(hostnameUrlColumn)
'­www.my.si­te.c­om­' = '­sit­e'
rootprefix
Returns the root prefix of a hostname part of an url:
rootpredix(hostnameUrlColumn))
'­www.my.si­te.c­om­' = '­www.my.si­te'
rootsuffix
Returns the root suffix of a hostname part of an url:
rootsuffix(hostnameUrlColumn))
'­www.my.si­te.c­om­' = '­my.s­it­e.c­om'
subdomain
Returns the subdomain of a hostname part of an url:
subdomain(hostnameUrlColumn)
'­www.my.si­te.c­om­' = '­www­'
toplev­eld­omain
Returns the top level domain of a hostname part of an url::
topleveldomain(hostnameUrlColumn)
'­www.my.si­te.c­o.u­k' = '­uk'
shanno­nen­tropy

Crypto­graphic functions

md5
sha1
sha256
sha512

Web functions

urischeme
urihost
uriport
uripath
urifra­gment
uriquery
uriuser
urissp
uriaut­hority
absolu­teuri
opaqueuri
urldecode
uaurl
uaname
uatype
uaversion
uaicon
uarobot
uainfourl
uafamily
uacompany
uacomp­anyurl
uadevi­ceicon
uadevi­cei­nfourl
uadevi­cetype
uaosurl
uaosname
uaosicon
uaosfamily
uaosco­mpany
uaosco­mpa­nyurl
uaosve­rsion
Possible missing function to filter by OS version.

Meta functions

pragma­value
tablename

Data Types

str                 
String
int
Integer number: 1,58,12598
float
Floating point number:24.256
boolean
Boolean: true, false
timestamp
Timestamp date in format: yyyy-MM-dd HH:mm:­ss.SSS
boxar(int)
Byte array in hexade­cimal string format
duration
Amount of time: an integer following by a letter
[d]ays, [h]ours, [m]inutes, [s]econds, [No suffix­]:m­ill­ise­conds
geocord
Geographic coordi­nates set:
Latitu­de/­lon­gitude sexage­simal values: 40º24'N 3º41'W
Hash repres­ent­ation of coordi­nates (geohash)
ip
IPv4 address format: 192.16­8.5.56
ip6
IPv6 address format: 2001:0­db8­:85­a3:­000­0:0­000­:8a­2e:­037­0:7334
net4
IPv4 address in format: {x.x.x.x/0
net6
IPv6 address in format: x.x.x.x.x.x/s
regexp
Regular expres­sion: [^\w]
template
Represents a substi­tution string mask.
dc
Represents a estimated count of distinct elements in a data stream.
image
Image as Base64 encoding image.
mac
MAC address in format: 00:0a:­95:­9d:­68:16
namepa­ttern
Represents a part of a table name: my.app, demo, ...
set(name)
Represents a set of table names: {my.ap­p.test, my.app.test2}
json
String in json format: {"id­"­:345, "­nam­e":"J­ohn­"}
jq
Represents a jq filter, jq is a command line json processor.
.email

Common comparison functions and operators

eq, [=]
Equals to function and operator:
eq(column, value or column)
column1 = value or column
eqic
Case insens­itive Equals to function:
eqic(c­olumn, value or column)
ge, [>=]
Greater or equal function and operator:
ge(column, value or column)
column >= value or column
gt, [>]
Greater than function and operator:
gt(column, value or column)
column > value or column
le, [<=]
Less or equal function and operator:
le(column, value or column)
column <= value or column
lt, [<]
Less than function and operator:
lt(column, value or column)
column < value or column
ne, [/=]
Not equal function and operator:
ne(column, value or column)
column /= value or column
isnull
Check if is null function:
isnull­(co­lumn)
isnotnull
Check if is not null function:
isnotn­ull­(co­lumn)

Logig Functions

and
or
not

JSON related functions

jqeval
label
jsonparse

Math functions and operators

abs
add / [+]
sub / [-]
mul / [*]
div / [\]
rdiv / [/]
Real division function and operator:
mod / [%%]
Module function:
rem / [%]
Return the remain of a division operation:
pow
Power function:
cbrt
Cube root function:
sqrt
Square root function:
ceil
floor
round
signum

Statis­tical and specia­lised statis­tical functions

estimation
pack
unpack­hllpp

Network functions

ispublic
isprivate
ipip4
ipprotocol
purpose
host
routing
httpst­atu­sde­scr­iption
httpst­atu­stype
reputation
score
sbl

Conversion functions

int
str
bool
float
image
ip4
net4
ip6
net6
mac
to16
from16
to64
from64
toutf8
fromutf8
toz85
fromz85
compatible
mapped
translated
template
timestamp
duration
re
parsedate
formatdate
humansize
mkboxar

Special comparison functions

matches
Matches function finds occurr­ences in a column using a regular expression:
matche­s(c­olumn, re(string) or regexp value)
column ~ re(string) or regexp value
anymatches
Find occurr­ences in a set of names type column against a namepattern:
anymat­che­s(s­etO­fNames, namegl­ob(­string) or namepattern)
anymat­che­s(t­ables, namegl­ob(­"­my.a­pp.*.*­"))
nameglob
Return a formmated string as a namepa­ttern to use with anymatches:
namegl­ob(­string)

Date and time functions

day
dayofweek
dayofyear
month
year
epoch
hour
minute
second
millis­econd
today
tomorrow
yesterday
period

Packet functions

hasio4
hastcp
hasudp
hasether
ip4proto
ip4src
ip4dst
ip4status
ip4ttl
ip4len
ip4payload
ip4flags
ip4fra­gment
ip4cs
ip4hl
ip4ds
ip4ecn
ip4tos
etherdst
ethersrc
etherp­ayload
ethers­tatus
ethertag
ethertype
tcpdst
tcpsrc
tcpstatus
tcpflags
tcppack
tcpcs
tcpseq
tcphl
tcppayload
tcpurg
tcpwin
udpsrc
udpport
udpstatus
udpcs
udplen
udppayload

Badges

Downloaded from Unusual Locations × 1

Comments (1)

Balan275 12:59 2 Jan 2023

Hi Can u help me to organize blocks in proper manner. It aligns itself and not utilizing the page fully.

Reply

Cheatography n. The art of mapping complex subjects into clear, instantly navigable reference guides.