Compare BQL and SQL commands
Although BQL is similar to existing database query languages like SQL, its custom design for Scuba queries means that it differs in a few key points.
The following table compares and identifies differences between BQL and SQL clauses.
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 |
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 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, | 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. |