CODEWITHSUNDEEP
sqlgoogle-bigquery
justnewbie89
justnewbie89

Reputation: 57

Transforming rows into columns and columns into rows to perform calculations in BigQuery

so i have this table in google-bigquery as shown below

initial table

month      | gender        |   sales   |   quantity   |
Jan-2022   | male.         |   2000.   |   50.        |
Jan-2022   | female.       |   3000.   |   100.       |

and this is my desired table

month |metrics.  |overall | male |composition (%)|female|composition (%)|
Jan-22|sales.    | 5000.  | 2000.|     40%       |3000  |    60%        |  
Jan-22|quantity. |  150.  |   50.|     33%       | 100  |    66%        |

so what query to use to get the desired result?

Upvotes: 0

Views: 74

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below approach

select * from (
  select *, 
    sum(col) over month_metric overall,
    round(100 * (sum(col) over month_metric_gender) / (sum(col) over month_metric), 0) composition
  from your_table
  unpivot (col FOR metrics IN (sales, quantity))
  window 
    month_metric as (partition by month, metrics),
    month_metric_gender as (partition by month, metrics, gender))
pivot (sum(col) gender, any_value(composition) composition for gender in ('male', 'female'))

if applied to sample data in your question - output is

enter image description here

The reason I would recommend above approach over previous (first answer you've got) - is that it is more generic and does not have dependency on explicitly calling out columns like male, femail (with except of in pivot statement of course)

Upvotes: 1

dnnshssm
dnnshssm

Reputation: 1305

You basically want to transform columns into rows and transform rows into columns. For that, you can use the UNPIVOT and PIVOT operators.

This gives you the desired output:

WITH your_table AS 
(
  SELECT 'Jan-2022' AS month, 'male' AS gender, 2000 AS sales, 50 AS quantity
  UNION ALL 
  SELECT 'Jan-2022', 'female', 3000, 100
)

SELECT
  month,
  metrics,
  (male + female) AS overall,
  male,
  male / (male + female) AS male_composition,
  female,
  female / (male + female) AS female_composition
FROM
(
  SELECT *
  FROM 
    your_table 
    UNPIVOT(vals FOR metrics IN (sales, quantity))
) PIVOT(SUM(vals) FOR gender IN ('male', 'female'))

Upvotes: 3

Related Questions