我有一张这样的桌子:

首先,我想创建两个按1:Activity和2:` `Model分组的表,如下所示:

然后:

现在,我想从这两个表中创建第三个表,该表共有4列:
表1(活动表)的
对前两个表的查询是清楚的:按活动从Table.Table组中选择活动、和(分数)、和(分数);从Table.Table组按模型选择模型、和(分数)、和(点数);但我不知道如何处理这个表。有没有人知道如何建造这个复杂的桌子。
最终结果:

在这里,您只看到那些分数和分数的总和,它们至少占总分和总分的0.3%:这包括:

以下是文本格式的数据:
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发布于 2020-11-06 08:08:18
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发布于 2020-11-06 19:21:39
下面是用于BigQuery标准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有最终结果/输出

https://stackoverflow.com/questions/64710597
复制相似问题