获取 BQ 公共数据集中的顶级专利国家/地区和代码

我正在尝试使用分析函数来获取专利申请量排名前 2 的国家/地区，并在这两个排名前 2 的国家/地区中获取排名前 2 的申请类型。例如，答案将如下所示：

country  -   code 
US           P
US           A
GB           X
GB           P

这是我用来获取此信息的查询：

SELECT
  country_code,
  MIN(count_country_code) count_country_code,
  application_kind
FROM (
  WITH
    A AS (
    SELECT
      country_code,
      COUNT(country_code) OVER (PARTITION BY country_code) AS count_country_code,
      application_kind
    FROM
      `patents-public-data.patents.publications`),
    B AS (
    SELECT
      country_code,
      count_country_code,
      DENSE_RANK() OVER(ORDER BY count_country_code DESC) AS country_code_num,
      application_kind,
      DENSE_RANK() OVER(PARTITION BY country_code ORDER BY count_country_code DESC) AS application_kind_num
    FROM
      A)
  SELECT
    country_code,
    count_country_code,
    application_kind
  FROM
    B
  WHERE
    country_code_num <= 2
    AND application_kind_num <= 2) x
GROUP BY
  country_code,
  application_kind
ORDER BY
  count_country_code DESC

但是，不幸的是，由于过度/顺序/分区，我收到“内存超出”错误。这是消息：

查询执行期间超出资源：无法在分配的内存中执行查询。峰值使用量：限制的 112%。主要内存消耗：用于分析 OVER() 子句的排序操作：98% 其他/未归因：2%

我如何在不遇到这些内存错误的情况下执行上述查询（或其他类似的查询）？这可以在公共数据集上进行测试.

一种粗略的方法（仅当字段具有半低基数时才有效）是将其作为简单的聚合操作来执行，并对数据库外部内存中的结果进行排序。例如：

以下是 BigQuery 标准 SQL

#standardSQL
WITH A AS (
  SELECT country_code
  FROM `patents-public-data.patents.publications`
  GROUP BY country_code
  ORDER BY COUNT(1) DESC
  LIMIT 2
), B AS (
  SELECT
    country_code,
    application_kind,
    COUNT(1) application_kind_count
  FROM `patents-public-data.patents.publications`
  WHERE country_code IN (SELECT country_code FROM A)
  GROUP BY country_code, application_kind
), C AS (
  SELECT
    country_code,
    application_kind,
    application_kind_count,
    DENSE_RANK() OVER(PARTITION BY country_code ORDER BY application_kind_count DESC) AS application_kind_rank
  FROM B
)
SELECT
  country_code,
  application_kind,
  application_kind_count
FROM C
WHERE application_kind_rank <= 2  

有结果