SQL filter query results based on analytic function

Question

I'd like to find an efficient way to filter my RANK() OVER function in SQL.

I have the following query:

SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
FROM
    `my_table` base
GROUP BY
    1

Which returns this result set:

Now I'd like to filter for items where the SLS_rank is < 10 OR the txn_rank is < 10. Ideally I'd like to do this in the HAVING clause, like this:

SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
FROM
    `my_table` base
GROUP BY
    1
HAVING   
    SLS_rank < 10 OR txn_rank < 10

But bigquery throws an error:

Column SLS_rank contains an analytic function, which is not allowed in HAVING clause at [9:8]

The only option I can think of is to create this as a separate table and selecting from there, but that doesn't seem very pretty. Any other ideas on how to do this?

score 6 · Accepted Answer · answered Jun 30 '21 at 21:44

Update June 2021.

BigQuery announced support for the QUALIFY clause on the 10th of May, 2021.

The QUALIFY clause filters the results of analytic functions. An analytic function is required to be present in the QUALIFY clause or the SELECT list.

What you need can be achieved with QUALIFY in the following way:

SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
FROM `my_table` base
GROUP BY 1
QUALIFY SLS_rank < 10 OR txn_rank < 10

Find more examples in the documentation.

score 2 · Answer 2 · edited Oct 10 '20 at 15:30

2

SELECT * FROM (
  SELECT
    base.ITEM_SKU_NBR,
    RANK() OVER (ORDER BY SUM(base.NET_SLS_AMT) DESC) AS SLS_rank,
    RANK() OVER (ORDER BY COUNT(DISTINCT base.txn_id) DESC) AS txn_rank
  FROM `my_table` base
  GROUP BY 1
)
WHERE SLS_rank < 300 OR txn_rank < 300

edited Oct 10 '20 at 15:30

Muriel

449
4
20

answered Dec 18 '19 at 18:07

Mikhail Berlyant

165,386
8
154
230

Thanks @Mikhail! Yeah, that's what I figured. Just would have liked it to be able to do it without the sub-SELECT. – Muriel Dec 18 '19 at 18:45
unfortunately - subquery is the way here :o) – Mikhail Berlyant Dec 18 '19 at 18:51

SQL filter query results based on analytic function

2 Answers2