我在bigquery中有一个表，我想聚合多个不同的类别（表中的列）来生成数据摘要，这是一个简单的count和groupby语句。然而，对于这个项目，我想产生一个“完整”的计数，每一个类别的排列（类别的可能值是已知的提前时间）与0总组合没有任何项目的排列。
下面是简单的初始计数和组语句：

SELECT
  COUNT(item.id) as total,
  TIMESTAMP_TRUNC(item.date, WEEK) as week,
  item.attribute_1,
  item.attribute_2,
FROM item
GROUP BY week, attribute_1, attribute_2
ORDER BY week DESC, attribute_1, attribute_2

哪里 attribute_1 有可能的值 ('A', 'B', 'C') 以及 attribute_2 有可能的值 ('X', 'Y', 'Z') 会产生这样的结果：

Row | total | week                    | attribute_1 | attribute_2
-----------------------------------------------------------------
1   | 1     | 2020-07-12 00:00:00 UTC | A           | X
2   | 2     | 2020-07-12 00:00:00 UTC | A           | Z
3   | 1     | 2020-07-12 00:00:00 UTC | B           | X
4   | 1     | 2020-07-12 00:00:00 UTC | B           | Y
5   | 3     | 2020-07-12 00:00:00 UTC | B           | Z
6   | 1     | 2020-07-12 00:00:00 UTC | C           | Y
7   | 1     | 2020-07-12 00:00:00 UTC | C           | Z
etc.

然而，我的目标是最终得到这样的结果：

Row | total | week                    | attribute_1 | attribute_2
-----------------------------------------------------------------
1   | 1     | 2020-07-12 00:00:00 UTC | A           | X
2   | 0     | 2020-07-12 00:00:00 UTC | A           | Y
3   | 2     | 2020-07-12 00:00:00 UTC | A           | Z
4   | 1     | 2020-07-12 00:00:00 UTC | B           | X
5   | 1     | 2020-07-12 00:00:00 UTC | B           | Y
6   | 3     | 2020-07-12 00:00:00 UTC | B           | Z
7   | 0     | 2020-07-12 00:00:00 UTC | C           | X
8   | 1     | 2020-07-12 00:00:00 UTC | C           | Y
9   | 1     | 2020-07-12 00:00:00 UTC | C           | Z
etc.

对于单个类别，我发现了类似的问题，因此我可以通过以下语句保持其他属性不变来解决该问题：

WITH data AS (
  SELECT
    item.id as item_id,
    TIMESTAMP_TRUNC(item.date, WEEK) as week,
    item.attribute_1,
    item.attribute_2,
  FROM item
  GROUP BY week, attribute_1, attribute_2
  ORDER BY week DESC, attribute_1, attribute_2
),
category_1 as (
 select 'A' as name union all
 select 'B' union all
 select 'C'
)
SELECT
  category_1.name,
  (
    SELECT COUNT(item_id) as total
    FROM data
    WHERE data.attribute_1 = category_1.name
    AND data.attribute_2 = 'X'
    AND data.week > TIMESTAMP('2020-07-10')
  ) as total
FROM category_1

产生：

Row | name | total
------------------
1   | A    | 1
2   | B    | 1
3   | C    | 0

我感兴趣的一般情况能解决吗？