CODEWITHSUNDEEP
google-bigquery
Shivam Sahil
Shivam Sahil

Reputation: 4925

How to use hybrid (Metric and SUM(Metric) ) Condition in BigQuery Sql?

I have a table like this: enter image description here

First I want to create 2 tables that are grouped by 1: Activity and 2: `Model as shown below: enter image description here and then: enter image description here

Now I want to create a third table from these two tables which has total 4 columns:

  1. Score from Table 1 (Activity table) where Score >= 0.3*Sum(Score) => Here Score is total Score.
  2. Score from Table 2 (Model table) where Score >= 0.3*Sum(Score) => Here Score is total Score.
  3. Points from Table 1 (Activity table) where Score >= 0.3*Sum(Score) => Here Score is total Score.
  4. Points from Table 2 (Model table) where Score >= 0.3*Sum(Score) => Here Score is total Score.

Query for the first two table are clear to me: Select Activity, SUM(Score),SUM(Points) FROM Table.Table GROUP BY Activity; Select Model, SUM(Score),SUM(Points) FROM Table.Table GROUP BY Model; But I don't know how to proceed after this one. Does anyone know how to go about building this complex table.

Final Result: enter image description here

Here you see sums of only those Scores and points that are atleast 0.3% of the total sum of Score and points: That includes: enter image description here

Here is the data in the text format:

Date        Activity    Model       Score   Points

01-Dec-20   Football    T2SSHEDF    20       0

02-Dec-20   Cricket     R45SFDVG    60       10

03-Dec-20   Badminton   W348DVG     80       2

04-Dec-20   BasketBall  GH43ERH     90       40

05-Dec-20   Cricket     T2SSHEDF    10       10

06-Dec-20   Football    R45SFDVG    40       10

07-Dec-20   BasketBall  W348DVG     32       30

08-Dec-20   Badminton   GH43ERH     28       20

Upvotes: 1

Views: 338

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below is for BigQuery Standard SQL

#standardSQL
with `project.dataset.table` as (
  select '01-Dec-20' Date, 'Football' Activity, 'T2SSHEDF' Model, 20 Score, 0 Points union all
  select '02-Dec-20', 'Cricket', 'R45SFDVG', 60, 10 union all
  select '03-Dec-20', 'Badminton', 'W348DVG', 80, 2 union all
  select '04-Dec-20', 'BasketBall', 'GH43ERH', 90, 40 union all
  select '05-Dec-20', 'Cricket', 'T2SSHEDF', 10, 10 union all
  select '06-Dec-20', 'Football', 'R45SFDVG', 40, 10 union all
  select '07-Dec-20', 'BasketBall', 'W348DVG', 32, 30 union all
  select '08-Dec-20', 'Badminton', 'GH43ERH', 28, 20 
), table1 as (
  select Activity, sum(Score) Score, sum(Points) Points
  from `project.dataset.table`
  group by Activity
), table2 as (
  select Model, sum(Score) Score, sum(Points) Points
  from `project.dataset.table`
  group by Model
)
select Dimention, sum(Score) as Score, sum(Points) as Points
from (
  select 'Activity' as Dimention, Score, Points, Score >= 0.3 * sum(Score) over() as qualified
  from table1 union all
  select 'Row Labels', Score, Points, Score >= 0.3 * sum(Score) over()
  from table2
)
where qualified
group by Dimention

with final result / output

enter image description here

Upvotes: 0

Sergey Geron
Sergey Geron

Reputation: 10222

WITH TestData AS (
  SELECT 'Football' as activity, 'T2SSHEDF' as model, 20 as score, 0 as points UNION ALL
  SELECT 'Cricket', 'R45SFDVG', 60, 10 UNION ALL
  SELECT 'Badminton', 'W348DVG', 80, 2 UNION ALL
  SELECT 'BasketBall', 'GH43ERH', 90, 40 UNION ALL
  SELECT 'Cricket', 'T2SSHEDF', 10, 10 UNION ALL
  SELECT 'Football', 'R45SFDVG', 40, 10 UNION ALL
  SELECT 'BasketBall', 'W348DVG', 32, 30 UNION ALL
  SELECT 'Badminton', 'GH43ERH', 28, 20
),
Summed_Data AS (
  SELECT 
    activity,
    SUM(score) OVER (PARTITION BY activity) as activity_score_sum,
    SUM(points) OVER (PARTITION BY activity) as activity_points_sum,
    model,
    SUM(score) OVER (PARTITION BY model) as model_score_sum,
    SUM(points) OVER (PARTITION BY model) as model_points_sum,
    SUM(score) OVER () as total_score
  FROM TestData
)
SELECT 
  activity, 
  model,
  IF(activity_score_sum > 0.3 * total_score, activity_score_sum, NULL) as activity_score,
  IF(model_score_sum > 0.3 * total_score, model_score_sum, NULL) as model_score,
  IF(activity_score_sum > 0.3 * total_score, activity_points_sum, NULL) as activity_ponts,
  IF(model_score_sum > 0.3 * total_score, model_points_sum, NULL) as model_points
FROM Summed_Data

Upvotes: 0

Related Questions