Compare BQL and SQL commands

Concept

SQL

BQL

Notes

select *

select * from my_table

Not supported

select count(*)

select count(*) from my_table

select count(*) from my_table

select distinct

select count(distinct user) from my_table

select count_unique(user) from my_table 

Use “select count_unique” instead of “select count distinct”

where

select count(*) from my_table where user = “jack” 

select count(* where user=”jack”) from my_table

In BQL, “where” used as a filter is contained within an aggregation. This is because you can have multiple aggregations, each with its own set of filters.

and, or, not

select count(*) from my_table where not(user = “jack” or user = “jill”) 

select count(* where not(user=”jack” or user=”jill”)) from my_table 

order by

select count(distinct purchase) from my_table order by user asc

select count_unique(purchase) as purchases, user from my_table order by purchases asc

min, max, avg, count, sum

select min(time_in_app) from my_table

select min(time_in_app) from my_table

in

select count(*) from my_table where user in (“jack”,”jill”)

select count(* where user in (“jack”,”jill”)) from my_table

between

select count(*) from my_table where user_id between 1 and 100

select count(*) from my_table between 2019-2-19 and now

BQL between is specifically a Scuba timespec syntax. BQL does not support “between” as a regular operator, and SQL lacks the notion of timespec.

group by

select count(*),country from my_table group by country

select count(*) from my_table group by user between 2014-2-19 4pm and now

"group by" corresponds to "split by" in the Scuba UI

limit

select count(*) from my_table limit 5

select count(*) from my_table group by user limit 5 between 2014-2-19 4pm and now

select count(*) from my_table group by user, country limit 5 and by platform limit 3
 

BQL can use hierarchical "group by", as shown in the second example here. The syntax, though, is the same from SQL to BQL.

joins

select my_table.user, lookup_table.age from inner join my_table.user = lookup_table.user

Not supported

BQL does not support "joins" because they are implied depending on the property combinations.

having

select count(user), country from my_table group by country having count(user) > 5

Not supported

on scope

Not supported

select count(* on event) from my_table group by Actor<user>(max(age))

select count(* on Actor<user>) from my_table group by Actor<user>(max(age))

Explicitly specify the scope of an aggregation.

The first query counts the number of events grouped by the user’s age.

The second query counts the number of users grouped by the user’s age. This is the same as:

select count(*) from my_table group by Actor<user>(max(age))

math (round, sqrt, etc.)

select count(user) from my_table where sqrt(age)>5

select count_unique(user where sqrt(age) > 5) from my_table

percentile

select distinct month, P90 = percentile_disc(0.9) within group (order by score) over (partition by [month]) from my_table  

select percentile(user_high_score, 90) from my_table

percentile is similar to percentile_desc in relational databases, but the two functions are not exactly the same, hence the different name

beginning_of_time,
for every

Not Supported

select count(*) from my_table for every week over 7 days between beginning_of_time and now

Some powerful time operators are present in BQL but not in SQL. This example counts the number of events that happened in a 7 day period, and performs this calculation every week from the beginning of time to now.

like

select count(*) from my_table where name like ‘%jack%’

select count(* where name like ‘.*jack.*’) from my_table

BQL "like" searches for text contained within a column. It is similar to mysql "rlike". It accepts regular expressions, unlike SQL "like", which accepts %foo% syntax.