I need to create incremental materialized view for reporting purposes having the following source table:
| product | date | subproduct | sector |
|---|---|---|---|
| A | 2024-01-01 | AA | X |
| A | 2024-01-01 | AA | X |
| A | 2024-01-01 | AB | Y |
| A | 2024-01-02 | AA | X |
| B | 2024-01-01 | AA | X |
| B | 2024-01-01 | AB | X |
| B | 2024-01-01 | AB | Y |
| B | 2024-01-01 | AB | Y |
| B | 2024-01-02 | AA | X |
| B | 2024-01-02 | AA | Y |
| C | 2024-01-02 | AA | X |
| C | 2024-01-02 | AA | X |
The aggregated result should be the following:
| product | date | subproduct | count |
|---|---|---|---|
| A | 2024-01-01 | AA | 2 |
| A | 2024-01-01 | AB | 1 |
| A | 2024-01-02 | AA | 1 |
| B | 2024-01-01 | AA | 1 |
| B | 2024-01-01 | AB | 3 |
| B - target | 2024-01-01 | AB | 2 |
| B | 2024-01-02 | AA | 2 |
| B - target | 2024-01-02 | AA | 1 |
| C | 2024-01-02 | AA | 2 |
In other words, some of the product B (these in sector = Y) need to be counted twice - first time as part of B products and one more time as “B - target”, because they are part of sector = Y
I tried to create the view using the following query:
SELECT
product,
date,
subproduct,
count(*)
FROM table a
GROUP BY product, date, subproduct
UNION ALL
SELECT
'B - target' as product,
date,
subproduct,
count(*)
FROM table a
WHERE product = 'B' and sector = 'Y'
GROUP BY product, date, subproduct
But I receive the the message that in BigQuery I can’t target the same table twice in incremental materialized view.
I tried with CASE
CASE
WHEN product = 'B' and sector = 'Y' THEN 'B -target'
ELSE product
END AS product
But this time they are not included in the total B count.
Then I tried to use CTE and then use it along with another table that I need to use for other portion of the data:
CREATE MATERIALIZED VIEW 'test_view'
AS
WITH cte as (
select * from table a
),
aggregations(
SELECT
product,
date,
subproduct,
count(*)
FROM cte a
GROUP BY product, date, subproduct
UNION ALL
SELECT
'B - target' as product,
date,
subproduct,
count(*)
FROM cte a
WHERE product = 'B' and sector = 'Y'
GROUP BY product, date, subproduct
UNION ALL
SELECT
product,
date,
subproduct,
count(*)
FROM table_2 b
GROUP BY product, date, subproduct)
SELECT * FROM aggregations
But getting the following warning - Incremental materialized view query contains unsupported features.
Any ideas what can I do?