Joining 2 tables on Grouping Setrollup total row on BigQuery - Stack Overflow

admin管理员组

文章数量:1397110

I have created 2 temporary tables that aggregate data. One is payment data where I have created a sum by month_year, and one is savings made aggregated in the same way.

Query:

WITH PAYMENT
(SELECT
year_month
,SUM(AMOUNT) PAYMENT_AMOUNT
FROM PAYMENTS
GROUP BY ROLLUP (year_month))

,SAVING
(SELECT
year_month
,SUM(AMOUNT) SAVING_AMOUNT
FROM SAVING
GROUP BY ROLLUP (year_month))

SELECT payment.year_month, PAYMENT_AMOUNT, SAVING_AMOUNT,SAVING_AMOUNT/PAYMENT_AMOUNT PERC_SAVED FROM PAYMENT
LEFT JOIN SAVINGS ON PAYMENT.YEAR_MONTH=SAVINGS.YEAR_MONTH

The issue that I have is that the savings amount for the total created from the rollup does not join. I have used an IFNULL(year_month, 'Total') on both tables to create an alias to join, and coalesce(year_month, 'Total'), but I only get;

enter image description here

Whereas the result that I want is;

enter image description here

I have created 2 temporary tables that aggregate data. One is payment data where I have created a sum by month_year, and one is savings made aggregated in the same way.

Query:

WITH PAYMENT
(SELECT
year_month
,SUM(AMOUNT) PAYMENT_AMOUNT
FROM PAYMENTS
GROUP BY ROLLUP (year_month))

,SAVING
(SELECT
year_month
,SUM(AMOUNT) SAVING_AMOUNT
FROM SAVING
GROUP BY ROLLUP (year_month))

SELECT payment.year_month, PAYMENT_AMOUNT, SAVING_AMOUNT,SAVING_AMOUNT/PAYMENT_AMOUNT PERC_SAVED FROM PAYMENT
LEFT JOIN SAVINGS ON PAYMENT.YEAR_MONTH=SAVINGS.YEAR_MONTH

The issue that I have is that the savings amount for the total created from the rollup does not join. I have used an IFNULL(year_month, 'Total') on both tables to create an alias to join, and coalesce(year_month, 'Total'), but I only get;

enter image description here

Whereas the result that I want is;

enter image description here

• google-bigquery

Share Improve this question Follow edited Mar 26 at 12:05 Paul T. 4,9521212 gold badges4747 silver badges6363 bronze badges asked Mar 26 at 11:54 David ManDavid Man 122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

rollup will produce a null year_month for the total, which will not match, however you can use coalesce to match with the following BigQuery SQL

WITH PAYMENT
(
    SELECT    year_month
              ,SUM(AMOUNT) PAYMENT_AMOUNT
    FROM      PAYMENTS
    GROUP BY  ROLLUP (year_month)
)
,SAVING
(
    SELECT    year_month
              ,SUM(AMOUNT) SAVING_AMOUNT
    FROM      SAVING
    GROUP BY  ROLLUP (year_month)
)

SELECT  COALESCE(payment.year_month, savings.year_month, 'TOTAL') year_month, 
        PAYMENT_AMOUNT, 
        SAVING_AMOUNT,
        SAVING_AMOUNT/PAYMENT_AMOUNT PERC_SAVED 
FROM    PAYMENT
LEFT JOIN SAVINGS ON COALESCE(payment.year_month,  'TOTAL') = COALESCE(savings.year_month, 'TOTAL');

producing this

本文标签： Joining 2 tables on Grouping Setrollup total row on BigQueryStack Overflow