Extra superior customers of SQL know the facility of analytic capabilities (aka window capabilities). These capabilities compute values over a bunch of rows, returning a single consequence for every row. For instance, clients use analytic capabilities to compute a grand whole, subtotal, shifting common, rank, and extra. With the announcement of assist for QUALIFY, BigQuery customers can now filter on the outcomes of analytic capabilities through the use of the QUALIFY clause.
QUALIFY belongs within the household of question clauses used for filtering together with WHERE and HAVING. The WHERE clause is used to filter particular person rows in a question. The HAVING clause is used to filter mixture rows in a consequence set after mixture capabilities and GROUP BY clauses. The QUALIFY clause is used to filter outcomes of analytic capabilities.
To point out the utility of QUALIFY, the instance beneath makes use of QUALIFY to return the highest three feminine names from annually within the final decade utilizing from bigquery-public-data.usa_names.usa_1910_current.