google-bigquery - 查询执行期间资源超出。大查询-6ren

google-bigquery - 查询执行期间资源超出。大查询

转载作者：行者123 更新时间：2023-12-04 15:31:13

全部，

我一直在尝试通过 BigQuery 运行一些更大的分析代码，但我继续遇到许多状态和存在的数据量的问题。我们谈论的是多年的数据。可能是我的查询不符合标准，但是我需要尝试根据特定组来获取总和。

我需要在查询中更改什么以防止资源超出错误？

SELECT
  COMPANY_NAME,
  RATING_CLASS,
  COMPANY_KEY,
  -- State Info & Calculations
  -- Over is used as a WINDOW function to SUM ALL results within the given query 
  SUM(ZIP5_MED_SUPP_TOOL_NUM_QUOTE) OVER () AS STATE_MED_SUPP_TOOL_NUM_QUOTE,
  -- ZIP3 Info & Calculations
  ZIP3,
  ZIP3_MED_SUPP_TOOL_NUM_QUOTE AS ZIP3_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP3_TOTAL_RESULT_APPEARANCE,
  ZIP3_LOWEST_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_LOWEST,
  ZIP3_AVG_RATIO_TO_LOWEST AS ZIP3_AVG_RATIO_TO_LOWEST,
  ZIP3_AVG_RANK AS ZIP3_AVG_RANK,
  ZIP3_TOP5_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_TOP5,
  ZIP3_TOP10_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_TOP10,
  ZIP3_AVG_CENT_DIFF AS ZIP3_AVG_CENT_DIFF,
  ZIP3_DISCOUNTED_LOWEST_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_LOWEST,
  ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST AS ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP3_DISCOUNTED_AVG_RANK AS ZIP3_DISCOUNTED_AVG_RANK,
  ZIP3_DISCOUNTED_TOP5_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_TOP5,
  ZIP3_DISCOUNTED_TOP10_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_TOP10,
  ZIP3_DISCOUNTED_AVG_CENT_DIFF AS ZIP3_DISCOUNTED_AVG_CENT_DIFF,
  -- ZIP5 Info & Calculations
  ZIP5,
  ZIP5_MED_SUPP_TOOL_NUM_QUOTE AS ZIP5_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP5_TOTAL_RESULT_APPEARANCE,
  ZIP5_LOWEST_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_LOWEST,
  ZIP5_AVG_RATIO_TO_LOWEST AS ZIP5_AVG_RATIO_TO_LOWEST,
  ZIP5_AVG_RANK AS ZIP5_AVG_RANK,
  ZIP5_TOP5_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_TOP5,
  ZIP5_TOP10_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_TOP10,
  ZIP5_AVG_CENT_DIFF AS ZIP5_AVG_CENT_DIFF,
  ZIP5_DISCOUNTED_LOWEST_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_LOWEST,
  ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST AS ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP5_DISCOUNTED_AVG_RANK AS ZIP5_DISCOUNTED_AVG_RANK,
  ZIP5_DISCOUNTED_TOP5_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_TOP5,
  ZIP5_DISCOUNTED_TOP10_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_TOP10,
  ZIP5_DISCOUNTED_AVG_CENT_DIFF AS ZIP5_DISCOUNTED_AVG_CENT_DIFF,
FROM (
  SELECT
    COMPANY_NAME,
    COMPANY_KEY,
    RATING_CLASS,
    -- ZIP3
    ZIP3,
    COUNT(DISTINCT logging_key) OVER (PARTITION BY ZIP3) AS ZIP3_MED_SUPP_TOOL_NUM_QUOTE,
    COUNT(*) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_TOTAL_RESULT_APPEARANCE,
    SUM(CASE
        WHEN lowest = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_LOWEST_COUNT,
    AVG(discounted_ratio_to_min) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_AVG_RANK,
    SUM(CASE
        WHEN top5 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_TOP5_COUNT,
    SUM(CASE
        WHEN top10 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_TOP10_COUNT,
    AVG(discounted_cent_diff) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_AVG_CENT_DIFF,
    SUM(CASE
        WHEN DISCOUNTED_lowest = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_DISCOUNTED_LOWEST_COUNT,
    AVG(discounted_ratio_to_min) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_DISCOUNTED_AVG_RANK,
    SUM(CASE
        WHEN DISCOUNTED_top5 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_DISCOUNTED_TOP5_COUNT,
    SUM(CASE
        WHEN DISCOUNTED_top10 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_DISCOUNTED_TOP10_COUNT,
    AVG(discounted_cent_diff) OVER (PARTITION BY ZIP3, company_key, rating_class) AS ZIP3_DISCOUNTED_AVG_CENT_DIFF,
    -- ZIP5
    ZIP5,
    COUNT(DISTINCT logging_key) OVER (PARTITION BY ZIP5) AS ZIP5_MED_SUPP_TOOL_NUM_QUOTE,
    COUNT(*) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_TOTAL_RESULT_APPEARANCE,
    SUM(CASE
        WHEN lowest = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_LOWEST_COUNT,
    AVG(discounted_ratio_to_min) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_AVG_RANK,
    SUM(CASE
        WHEN top5 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_TOP5_COUNT,
    SUM(CASE
        WHEN top10 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_TOP10_COUNT,
    AVG(discounted_cent_diff) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_AVG_CENT_DIFF,
    SUM(CASE
        WHEN DISCOUNTED_lowest = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_DISCOUNTED_LOWEST_COUNT,
    AVG(discounted_ratio_to_min) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_DISCOUNTED_AVG_RANK,
    SUM(CASE
        WHEN DISCOUNTED_top5 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_DISCOUNTED_TOP5_COUNT,
    SUM(CASE
        WHEN DISCOUNTED_top10 = TRUE THEN 1
        ELSE 0 END) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_DISCOUNTED_TOP10_COUNT,
    AVG(discounted_cent_diff) OVER (PARTITION BY ZIP5, company_key, rating_class) AS ZIP5_DISCOUNTED_AVG_CENT_DIFF,
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T00:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T00:00:00.000Z')
    AND user_key NOT IN ("6522869941010432", "6277136540237824")
  GROUP BY
    COMPANY_NAME,
    COMPANY_KEY,
    RATING_CLASS,
    ZIP3,
    ZIP5,
    LOWEST,
    RATIO_TO_MIN,
    RATE_ORDER,
    TOP5,
    TOP10,
    CENT_DIFF,
    DISCOUNTED_LOWEST,
    DISCOUNTED_RATIO_TO_MIN,
    DISCOUNTED_RATE_ORDER,
    DISCOUNTED_TOP5,
    DISCOUNTED_TOP10,
    DISCOUNTED_CENT_DIFF,
    LOGGING_KEY )
GROUP BY
  COMPANY_NAME,
  COMPANY_KEY,
  RATING_CLASS,
  -- ZIP3 General
  ZIP3,
  ZIP3_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP3_TOTAL_RESULT_APPEARANCE,
  ZIP3_LOWEST,
  ZIP3_AVG_RATIO_TO_LOWEST,
  ZIP3_AVG_RANK,
  ZIP3_TOP5,
  ZIP3_TOP10,
  ZIP3_AVG_CENT_DIFF,
  ZIP3_DISCOUNTED_LOWEST,
  ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP3_DISCOUNTED_AVG_RANK,
  ZIP3_DISCOUNTED_TOP5,
  ZIP3_DISCOUNTED_TOP10,
  ZIP3_DISCOUNTED_AVG_CENT_DIFF,
  -- ZIP5 General
  ZIP5,
  ZIP5_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP5_TOTAL_RESULT_APPEARANCE,
  ZIP5_LOWEST,
  ZIP5_AVG_RATIO_TO_LOWEST,
  ZIP5_AVG_RANK,
  ZIP5_TOP5,
  ZIP5_TOP10,
  ZIP5_AVG_CENT_DIFF,
  ZIP5_DISCOUNTED_LOWEST,
  ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP5_DISCOUNTED_AVG_RANK,
  ZIP5_DISCOUNTED_TOP5,
  ZIP5_DISCOUNTED_TOP10,
  ZIP5_DISCOUNTED_AVG_CENT_DIFF

更新了带有建议修复的查询:

SELECT
  main.COMPANY_NAME AS COMPANY_NAME,
  main.COMPANY_KEY AS COMPANY_KEY,
  main.RATING_CLASS AS RATING_CLASS,
  state_count.STATE_MED_SUPP_TOOL_NUM_QUOTE AS STATE_MED_SUPP_TOOL_NUM_QUOTE,
  -- ZIP3
  main.ZIP3 AS ZIP3,
  ZIP3_COUNT.ZIP3_MED_SUPP_TOOL_NUM_QUOTE AS ZIP3_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE,
  ZIP3_SUB.ZIP3_AVG_RATIO_TO_LOWEST AS ZIP3_AVG_RATIO_TO_LOWEST,
  ZIP3_SUB.ZIP3_TOP5_COUNT/ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_TOP5,
  ZIP3_SUB.ZIP3_LOWEST_COUNT/ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_LOWEST,
  ZIP3_SUB.ZIP3_TOP10_COUNT/ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_TOP10,
  ZIP3_SUB.ZIP3_AVG_RANK AS ZIP3_AVG_RANK,
  ZIP3_SUB.ZIP3_AVG_CENT_DIFF AS ZIP3_AVG_CENT_DIFF,
  ZIP3_SUB.ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST AS ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP3_SUB.ZIP3_DISCOUNTED_TOP5_COUNT/ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_TOP5,
  ZIP3_SUB.ZIP3_DISCOUNTED_LOWEST_COUNT/ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_LOWEST,
  ZIP3_SUB.ZIP3_DISCOUNTED_TOP10_COUNT/ZIP3_SUB.ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_TOP10,
  ZIP3_SUB.ZIP3_DISCOUNTED_AVG_RANK AS ZIP3_DISCOUNTED_AVG_RANK,
  ZIP3_SUB.ZIP3_DISCOUNTED_AVG_CENT_DIFF AS ZIP3_DISCOUNTED_AVG_CENT_DIFF,
  -- ZIP5
  main.ZIP5 AS ZIP5,
  ZIP5_COUNT.ZIP5_MED_SUPP_TOOL_NUM_QUOTE AS ZIP5_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE,
  ZIP5_SUB.ZIP5_AVG_RATIO_TO_LOWEST AS ZIP5_AVG_RATIO_TO_LOWEST,
  ZIP5_SUB.ZIP5_TOP5_COUNT/ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_TOP5,
  ZIP5_SUB.ZIP5_LOWEST_COUNT/ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_LOWEST,
  ZIP5_SUB.ZIP5_TOP10_COUNT/ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_TOP10,
  ZIP5_SUB.ZIP5_AVG_RANK AS ZIP5_AVG_RANK,
  ZIP5_SUB.ZIP5_AVG_CENT_DIFF AS ZIP5_AVG_CENT_DIFF,
  ZIP5_SUB.ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST AS ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP5_SUB.ZIP5_DISCOUNTED_TOP5_COUNT/ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_TOP5,
  ZIP5_SUB.ZIP5_DISCOUNTED_LOWEST_COUNT/ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_LOWEST,
  ZIP5_SUB.ZIP5_DISCOUNTED_TOP10_COUNT/ZIP5_SUB.ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_TOP10,
  ZIP5_SUB.ZIP5_DISCOUNTED_AVG_RANK AS ZIP5_DISCOUNTED_AVG_RANK,
  ZIP5_SUB.ZIP5_DISCOUNTED_AVG_CENT_DIFF AS ZIP5_DISCOUNTED_AVG_CENT_DIFF,
FROM (
  SELECT
    COMPANY_NAME,
    COMPANY_KEY,
    RATING_CLASS,
    ZIP3,
    ZIP5,
    STATE,
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T06:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T05:00:00.000Z')
    AND user_key NOT IN ("6522869941010432",
      "6277136540237824",
      "4872666167115776",
      "6396348765044736",
      "6139303562313728",
      "4988973881491456")
    AND portal_key NOT IN ("5878607637381120")
  GROUP BY
    COMPANY_NAME,
    COMPANY_KEY,
    RATING_CLASS,
    ZIP3,
    ZIP5,
    STATE,
    ) AS MAIN
LEFT JOIN (
  SELECT
    ZIP3,
    COUNT(*) AS ZIP3_TOTAL_RESULT_APPEARANCE,
    COMPANY_KEY,
    RATING_CLASS,
    AVG(discounted_ratio_to_min) AS ZIP3_AVG_RATIO_TO_LOWEST,
    SUM(CASE
        WHEN TOP5 = TRUE THEN 1
        ELSE 0 END) AS ZIP3_TOP5_COUNT,
    SUM(CASE
        WHEN LOWEST = TRUE THEN 1
        ELSE 0 END) AS ZIP3_LOWEST_COUNT,
    SUM(CASE
        WHEN TOP10 = TRUE THEN 1
        ELSE 0 END) AS ZIP3_TOP10_COUNT,
    AVG(discounted_rate_order) AS ZIP3_AVG_RANK,
    AVG(discounted_cent_diff) AS ZIP3_AVG_CENT_DIFF,
    AVG(discounted_ratio_to_min) AS ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
    SUM(CASE
        WHEN DISCOUNTED_TOP5 = TRUE THEN 1
        ELSE 0 END) AS ZIP3_DISCOUNTED_TOP5_COUNT,
    SUM(CASE
        WHEN DISCOUNTED_LOWEST = TRUE THEN 1
        ELSE 0 END) AS ZIP3_DISCOUNTED_LOWEST_COUNT,
    SUM(CASE
        WHEN DISCOUNTED_TOP10 = TRUE THEN 1
        ELSE 0 END) AS ZIP3_DISCOUNTED_TOP10_COUNT,
    AVG(discounted_rate_order) AS ZIP3_DISCOUNTED_AVG_RANK,
    AVG(discounted_cent_diff) AS ZIP3_DISCOUNTED_AVG_CENT_DIFF,
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T06:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T05:00:00.000Z')
    AND user_key NOT IN ("6522869941010432",
      "6277136540237824")
  GROUP BY
    ZIP3,
    COMPANY_NAME,
    COMPANY_KEY,
    RATING_CLASS,
    ) AS ZIP3_SUB
ON
  main.ZIP3 = ZIP3_SUB.ZIP3
  AND main.COMPANY_KEY = ZIP3_SUB.COMPANY_KEY
  AND main.RATING_CLASS = ZIP3_SUB.RATING_CLASS
LEFT JOIN (
  SELECT
    ZIP3,
    EXACT_COUNT_DISTINCT(logging_key) AS ZIP3_MED_SUPP_TOOL_NUM_QUOTE
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T06:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T05:00:00.000Z')
    AND user_key NOT IN ("6522869941010432",
      "6277136540237824")
  GROUP BY
    ZIP3 ) AS ZIP3_COUNT
ON
  main.ZIP3 = ZIP3_COUNT.ZIP3
LEFT JOIN (
  SELECT
    ZIP5,
    COUNT(*) AS ZIP5_TOTAL_RESULT_APPEARANCE,
    COMPANY_KEY,
    RATING_CLASS,
    AVG(discounted_ratio_to_min) AS ZIP5_AVG_RATIO_TO_LOWEST,
    SUM(CASE
        WHEN TOP5 = TRUE THEN 1
        ELSE 0 END) AS ZIP5_TOP5_COUNT,
    SUM(CASE
        WHEN LOWEST = TRUE THEN 1
        ELSE 0 END) AS ZIP5_LOWEST_COUNT,
    SUM(CASE
        WHEN TOP10 = TRUE THEN 1
        ELSE 0 END) AS ZIP5_TOP10_COUNT,
    AVG(discounted_rate_order) AS ZIP5_AVG_RANK,
    AVG(discounted_cent_diff) AS ZIP5_AVG_CENT_DIFF,
    AVG(discounted_ratio_to_min) AS ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
    SUM(CASE
        WHEN DISCOUNTED_TOP5 = TRUE THEN 1
        ELSE 0 END) AS ZIP5_DISCOUNTED_TOP5_COUNT,
    SUM(CASE
        WHEN DISCOUNTED_LOWEST = TRUE THEN 1
        ELSE 0 END) AS ZIP5_DISCOUNTED_LOWEST_COUNT,
    SUM(CASE
        WHEN DISCOUNTED_TOP10 = TRUE THEN 1
        ELSE 0 END) AS ZIP5_DISCOUNTED_TOP10_COUNT,
    AVG(discounted_rate_order) AS ZIP5_DISCOUNTED_AVG_RANK,
    AVG(discounted_cent_diff) AS ZIP5_DISCOUNTED_AVG_CENT_DIFF,
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T06:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T05:00:00.000Z')
    AND user_key NOT IN ("6522869941010432",
      "6277136540237824")
  GROUP BY
    ZIP5,
    COMPANY_NAME,
    COMPANY_KEY,
    RATING_CLASS,
    ) AS ZIP5_SUB
ON
  main.ZIP5 = ZIP5_SUB.ZIP5
  AND main.COMPANY_KEY = ZIP5_SUB.COMPANY_KEY
  AND main.RATING_CLASS = ZIP5_SUB.RATING_CLASS
LEFT JOIN (
  SELECT
    ZIP5,
    EXACT_COUNT_DISTINCT(logging_key) AS ZIP5_MED_SUPP_TOOL_NUM_QUOTE
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T06:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T05:00:00.000Z')
    AND user_key NOT IN ("6522869941010432",
      "6277136540237824")
  GROUP BY
    ZIP5 ) AS ZIP5_COUNT
ON
  main.ZIP5 = ZIP5_COUNT.ZIP5
LEFT JOIN (
  SELECT
    STATE,
    EXACT_COUNT_DISTINCT(logging_key) AS STATE_MED_SUPP_TOOL_NUM_QUOTE
  FROM
    [csgapi:qh_med_supp_tool.v2_TX]
  WHERE
    SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T06:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T05:00:00.000Z')
    AND user_key NOT IN ("6522869941010432",
      "6277136540237824")
  GROUP BY
    STATE ) AS STATE_COUNT
ON
  main.STATE = STATE_COUNT.STATE

解释:
由于 BigQuery 是共享资源，BigQuery 会估算查询所需的资源量并相应地分配资源。通过将查询拆分为同一个表上的多个连接，可以使用比原先预期更多的资源来执行计算。有关为何限制查询效果良好的技术性解释，请参阅 Jordan Tigani 上的堆栈溢出响应 here 。

最佳答案

不知何故，我觉得下面是你需要的。我可能是错的，因为它实际上只是相对盲目地尝试对您的逻辑进行逆向工程，所以不要在这里判断我是否错了
无法测试，但我觉得实际成功的成本会很低

SELECT
  main.COMPANY_NAME AS COMPANY_NAME,
  main.COMPANY_KEY AS COMPANY_KEY,
  main.RATING_CLASS AS RATING_CLASS,
  SUM(ZIP5_MED_SUPP_TOOL_NUM_QUOTE) OVER () AS STATE_MED_SUPP_TOOL_NUM_QUOTE,
  -- ZIP3
  main.ZIP3 AS ZIP3,
  ZIP3_MED_SUPP_TOOL_NUM_QUOTE AS ZIP3_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP3_TOTAL_RESULT_APPEARANCE,
  ZIP3_LOWEST_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_LOWEST,
  ZIP3_AVG_RATIO_TO_LOWEST AS ZIP3_AVG_RATIO_TO_LOWEST,
  ZIP3_AVG_RANK AS ZIP3_AVG_RANK,
  ZIP3_TOP5_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_TOP5,
  ZIP3_TOP10_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_TOP10,
  ZIP3_AVG_CENT_DIFF AS ZIP3_AVG_CENT_DIFF,
  ZIP3_DISCOUNTED_LOWEST_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_LOWEST,
  ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST AS ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP3_DISCOUNTED_AVG_RANK AS ZIP3_DISCOUNTED_AVG_RANK,
  ZIP3_DISCOUNTED_TOP5_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_TOP5,
  ZIP3_DISCOUNTED_TOP10_COUNT/ZIP3_TOTAL_RESULT_APPEARANCE AS ZIP3_DISCOUNTED_TOP10,
  ZIP3_DISCOUNTED_AVG_CENT_DIFF AS ZIP3_DISCOUNTED_AVG_CENT_DIFF,
  -- ZIP5
  main.ZIP5 AS ZIP5,
  ZIP5_MED_SUPP_TOOL_NUM_QUOTE AS ZIP5_MED_SUPP_TOOL_NUM_QUOTE,
  ZIP5_TOTAL_RESULT_APPEARANCE,
  ZIP5_LOWEST_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_LOWEST,
  ZIP5_AVG_RATIO_TO_LOWEST AS ZIP5_AVG_RATIO_TO_LOWEST,
  ZIP5_AVG_RANK AS ZIP5_AVG_RANK,
  ZIP5_TOP5_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_TOP5,
  ZIP5_TOP10_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_TOP10,
  ZIP5_AVG_CENT_DIFF AS ZIP5_AVG_CENT_DIFF,
  ZIP5_DISCOUNTED_LOWEST_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_LOWEST,
  ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST AS ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
  ZIP5_DISCOUNTED_AVG_RANK AS ZIP5_DISCOUNTED_AVG_RANK,
  ZIP5_DISCOUNTED_TOP5_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_TOP5,
  ZIP5_DISCOUNTED_TOP10_COUNT/ZIP5_TOTAL_RESULT_APPEARANCE AS ZIP5_DISCOUNTED_TOP10,
  ZIP5_DISCOUNTED_AVG_CENT_DIFF AS ZIP5_DISCOUNTED_AVG_CENT_DIFF
FROM (
  SELECT COMPANY_NAME, COMPANY_KEY, RATING_CLASS, ZIP3, ZIP5
  FROM [csgapi:qh_med_supp_tool.v2_TX]
  WHERE SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T00:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T00:00:00.000Z')
    AND user_key NOT IN ("6522869941010432", "6277136540237824")
  GROUP BY COMPANY_NAME, COMPANY_KEY, RATING_CLASS, ZIP3, ZIP5  
) AS main
LEFT JOIN (
  SELECT 
    ZIP3, company_key, rating_class,
    COUNT(*)                                                  AS ZIP3_TOTAL_RESULT_APPEARANCE,
    SUM(CASE WHEN lowest = TRUE THEN 1 ELSE 0 END)            AS ZIP3_LOWEST_COUNT,
    AVG(discounted_ratio_to_min)                              AS ZIP3_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order)                                AS ZIP3_AVG_RANK,
    SUM(CASE WHEN top5 = TRUE THEN 1 ELSE 0 END)              AS ZIP3_TOP5_COUNT,
    SUM(CASE WHEN top10 = TRUE THEN 1 ELSE 0 END)             AS ZIP3_TOP10_COUNT,
    AVG(discounted_cent_diff)                                 AS ZIP3_AVG_CENT_DIFF,
    SUM(CASE WHEN DISCOUNTED_lowest = TRUE THEN 1 ELSE 0 END) AS ZIP3_DISCOUNTED_LOWEST_COUNT,
    AVG(discounted_ratio_to_min)                              AS ZIP3_DISCOUNTED_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order)                                AS ZIP3_DISCOUNTED_AVG_RANK,
    SUM(CASE WHEN DISCOUNTED_top5 = TRUE THEN 1 ELSE 0 END)   AS ZIP3_DISCOUNTED_TOP5_COUNT,
    SUM(CASE WHEN DISCOUNTED_top10 = TRUE THEN 1 ELSE 0 END)  AS ZIP3_DISCOUNTED_TOP10_COUNT,
    AVG(discounted_cent_diff)                                 AS ZIP3_DISCOUNTED_AVG_CENT_DIFF,
    FROM [csgapi:qh_med_supp_tool.v2_TX]
    WHERE SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T00:00:00.000Z')
      AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T00:00:00.000Z')
      AND user_key NOT IN ("6522869941010432", "6277136540237824")
    GROUP BY ZIP3, company_key, rating_class
) AS zip3_sub
ON main.ZIP3 = zip3_sub.ZIP3 AND main.company_key = zip3_sub.company_key AND main.rating_class = zip3_sub.rating_class
LEFT JOIN (  
  SELECT 
    ZIP5, company_key, rating_class,
    COUNT(*)                                                  AS ZIP5_TOTAL_RESULT_APPEARANCE,
    SUM(CASE WHEN lowest = TRUE THEN 1 ELSE 0 END)            AS ZIP5_LOWEST_COUNT,
    AVG(discounted_ratio_to_min)                              AS ZIP5_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order)                                AS ZIP5_AVG_RANK,
    SUM(CASE WHEN top5 = TRUE THEN 1 ELSE 0 END)              AS ZIP5_TOP5_COUNT,
    SUM(CASE WHEN top10 = TRUE THEN 1 ELSE 0 END)             AS ZIP5_TOP10_COUNT,
    AVG(discounted_cent_diff)                                 AS ZIP5_AVG_CENT_DIFF,
    SUM(CASE WHEN DISCOUNTED_lowest = TRUE THEN 1 ELSE 0 END) AS ZIP5_DISCOUNTED_LOWEST_COUNT,
    AVG(discounted_ratio_to_min)                              AS ZIP5_DISCOUNTED_AVG_RATIO_TO_LOWEST,
    AVG(discounted_rate_order)                                AS ZIP5_DISCOUNTED_AVG_RANK,
    SUM(CASE WHEN DISCOUNTED_top5 = TRUE THEN 1 ELSE 0 END)   AS ZIP5_DISCOUNTED_TOP5_COUNT,
    SUM(CASE WHEN DISCOUNTED_top10 = TRUE THEN 1 ELSE 0 END)  AS ZIP5_DISCOUNTED_TOP10_COUNT,
    AVG(discounted_cent_diff)                                 AS ZIP5_DISCOUNTED_AVG_CENT_DIFF,
  FROM [csgapi:qh_med_supp_tool.v2_TX]
  WHERE SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T00:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T00:00:00.000Z')
    AND user_key NOT IN ("6522869941010432", "6277136540237824")
  GROUP BY ZIP5, company_key, rating_class
) AS zip5_sub
ON main.ZIP5 = zip5_sub.ZIP5 AND main.company_key = zip5_sub.company_key AND main.rating_class = zip5_sub.rating_class
LEFT JOIN (
  SELECT ZIP3, COUNT(DISTINCT logging_key) AS ZIP3_MED_SUPP_TOOL_NUM_QUOTE
  FROM [csgapi:qh_med_supp_tool.v2_TX]
  WHERE SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T00:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T00:00:00.000Z')
    AND user_key NOT IN ("6522869941010432", "6277136540237824")
  GROUP BY ZIP3
) AS zip3_count
ON main.ZIP3 = zip3_count.ZIP3
LEFT JOIN (
  SELECT ZIP5, COUNT(DISTINCT logging_key) AS ZIP5_MED_SUPP_TOOL_NUM_QUOTE
  FROM [csgapi:qh_med_supp_tool.v2_TX]
  WHERE SEARCH_TIMESTAMP >= TIMESTAMP('2016-01-01T00:00:00.000Z')
    AND SEARCH_TIMESTAMP <= TIMESTAMP('2017-01-01T00:00:00.000Z')
    AND user_key NOT IN ("6522869941010432", "6277136540237824")
  GROUP BY ZIP5
) AS zip5_count
ON main.ZIP5 = zip5_count.ZIP5

另外，请注意:在 BigQuery Legacy SQL 中 - COUNT(DISTINCT)函数是概率性的 - 给出统计近似值，不保证准确。
您可以使用 EXACT_COUNT_DISTINCT()相反的功能 - 这个给你确切的数字，但在后端贵一点

当然，整个查询可以重写为 BigQuery Standard SQL当 COUNT(DISTINCT) 产生精确计数并且根据 BigQuery 团队的建议最好使用标准 SQL 时

关于google-bigquery - 查询执行期间资源超出。大查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41750801/

文章推荐： azure - 无法更新 Azure 应用程序 list 中的 KeyCredential 'Value'

文章推荐： asynchronous - 异步写入文件

google-apps-script - 使用 Google 应用程序脚本刷新从 Google 表格粘贴到 Google 幻灯片中的表格
这里的这个问题对 updating Google Sheets charts linked to Google slides 有一个简洁的解决方案. function onOpen() { var
google-apps-script - 用于将 Google 表单添加到 Google 类作业的 Google 脚本
我正在尝试将 Google 表单添加到 Google 类作业中，但似乎不可能。首先，它在这里 ( https://developers.google.com/classroom/reference/
google-visualization - Google 日期时间格式化程序不适用于 Google 折线图
出于某种原因，无论我做什么以及我如何尝试，这个日期格式化程序都不起作用。工具提示仍然显示错误的格式。你可以试试代码here . 在代码中我必须注释掉 formatter.format(dataTabl
google-analytics - Google Analytics - 是否必须从托管 Google Analytics 帐户的 Google 配置文件创建服务帐户？
我目前正在使用访问 token 和刷新 token 从 Google Analytics Reporting API (v4) 中提取数据。当我致力于自动从 Google Analytics 中提取数
google-sheets - Google 电子表格中 Google 驱动器中的引用文件
我已在 Google 云端硬盘中创建了一个文件夹，例如测试一下，放入3个文件 a.jpg, b.jpg, c.jpg 我希望在同一帐户下的 Google 电子表格中访问文件，例如生成图像文件的链接，可
google-apps-script - 在 Google 网站中嵌入 Google 电子表格时，Google Apps 脚本可帮助解决错误？
电子表格 A 是欢迎新移民来到我们小镇的团队的主数据源。它里面有大量非常敏感的数据，不能公开，哪怕是一点点。 (我们谈论的是 child 的姓名和出生日期以及他们在哪里上学……保证电子表格 A 的安全
google-apps-script - 使用 Google Apps 脚本从 Google 表格数据表复制到 Google 文档表
有没有办法在 Google 文档中编写 Google Apps 脚本以从 Google 表格中检索仅限于非空白行的范围并将这些行显示为表格？我正在寻找一个脚本，用于使用 Google Apps 脚本
google-apps-script - 使用 Google Apps 脚本从 Google 表格数据表复制到 Google 文档表
有没有办法在 Google 文档中编写 Google Apps 脚本以从 Google 表格中检索仅限于非空白行的范围并将这些行显示为表格？我正在寻找一个脚本，用于使用 Google Apps 脚本
google-apps-script - 使用 Google Apps 脚本从 Google Firebase 写入 Google Sheets
尝试检索存储在 google firebase 中名为条目的节点下的表单条目，并使用谷歌工作表中的脚本编辑器附加到谷歌工作表。我已将 FirebaseApp 库添加到谷歌表脚本编辑器。然后我的代码看
google-oauth - Google oauth - 限制登录到特定的 google 组
是否可以将我的 Web 应用程序的登录限制为仅限 google 组中的帐户？我不希望每个人都可以使用他们的私有(private) gmail 登录，而只能使用我的 google 组中的用户。最佳答
google-oauth - 带有 Google 自定义搜索功能的 Google 附加链接搜索框
我们想使用 Google 自定义搜索实现 Google 附加链接搜索框。在谷歌 documentation , 我发现我们需要包含以下代码来启用附加链接搜索框 { "@context"
google-trends - 我可以将 Google 趋势图添加到 Google 数据洞察吗？
我想将特定搜索词的 Google 趋势图表添加到我的 Google Data Studio 报告中，但趋势不是数据源列表中的选项。我也找不到嵌入 JavaScript 的选项。是否可以将趋势图表添加到
google-drive-api - 将文件从 Google Drive 复制到 Google 内的 Google Cloud Storage
是否可以将文件从 Google Drive 复制到 Google Cloud Storage？我想它会非常快，因为两者都在类似的存储系统上。我还没有看到有关无缝执行此操作的任何方法的任何信息，而无需
google-analytics - Google Universal Analytics Google-自定义维度
之间有什么区别 ga('send', 'pageview', { 'dimension1': 'data goes here' }); 和 ga('set', 'dimension1', 'da
google-analytics - Google Universal Analytics Google-浏览量
我正在尝试记录每个博客站点作者的点击率。 ga('send', 'pageview'); (in the header with the ga code to track each page) ga(
google-analytics - 自定义变量值未从 Google 跟踪代码管理器传递到 Google Analytics
我设置了 Google Tag Manager 和 2 个数据层变量:一个用于跟踪用户 ID，传递给 Google Analytics 以同步用户 session ，另一个用于跟踪访问者类型。在使用
google-search - Google for Jobs 显示的工作位置不正确(Google 使用的是我们公司的总部)
我在我们的网站上遇到多个职位发布的问题。我们在加拿大多个地点提供工作机会。所有职位页面都包含一个“LD+JSON ”职位发布的结构化数据，基于 Google 的职位发布文档: https://dev
google-analytics - 无需 Google 帐户即可访问 Google Analytics
公司未使用 Google 套件，使用个人(消费者)帐户(甚至是 Google 帐户)违反公司政策。需要访问 Google Analytics - 没有 Google 帐户是否可能？谢谢最佳答案
google-analytics - Google Play 应用页面的 Google Analytics
我想分析人们使用哪些搜索关键字在 Play 商店中找到我的应用。那可能吗？我怎么能这样做？最佳答案自 2013 年 10 月起，您可以关联您的 Google Analytics(分析)和 Goo
google-api - 是否有用于访问 Google Now 或 Google Keep 中设置的提醒的公共(public) Google API？
Google Now 和 Google Keep 中基于时间和位置的提醒与 Google Calendar 事件提醒不同。是否有公共(public) API 可以访问 Now 和 Keep 中的这些事

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

google-bigquery - 查询执行期间资源超出。大查询