我需要构建分析 SQL 查询,其中客户端应指定任何指标(特定列中的值总和(或维度(按特定列分组(。
假设我有一个表,其中包含列hour
、dim_a
、dim_b
、metric_a
、metric_b
、metric_c
列,其值以 csv 显示
hour,dim_a,dim_b,metric_a,metric_b
0,A,X,4,4
0,A,Y,4,24
0,B,Y,20,24
1,B,Y,21,35
1,A,Y,4,35
1,C,Y,10,35
2,B,Y,21,30
2,C,Y,3,30
2,A,Y,6,30
看看metric_b
.如果无论dim_a
的值如何,hour
和dim_b
的值都相同,则此指标始终相同。例如:
1,B,Y,21,35
1,A,Y,4,35
1,C,Y,10,35
如果我们选择列hour
,dim_b
,metric_b
并取非重复值表将如下所示:
hour,dim_b,metric_b
0,X,4
0,Y,24
1,Y,35
2,Y,30
通过这个值,应该完成针对metric_b
的所有聚合
我想按特定维度对这些数据进行分组运行分析查询,并在涉及metric_b
时使用特殊聚合进行指标聚合。
- 当我想按
hour
、dim_a
、dim_b
和 查看指标metric_a
和metric_b
时。预期结果为
hour,dim_a,dim_b,metric_a,metric_b
0,A,X,4,4
0,A,Y,4,24
0,B,Y,20,24
1,B,Y,21,35
1,A,Y,4,35
1,C,Y,10,35
2,B,Y,21,30
2,C,Y,3,30
2,A,Y,6,30
- 当我想按
dim_a
、dim_b
和metric_b
查看metric_a
指标时。预期结果为
dim_a,dim_b,metric_a,metric_b
A,X,4,4
A,Y,14,89
B,Y,62,89
C,Y,13,89
metric_b
的值计算公式为 89 = 24 + 35 + 30;4 = 4
- 当我想按
dim_b
分组时,请参阅指标。metric_a
和metric_b
.预期结果为:
dim_b,metric_a,metric_b
X,4,4
Y,89,89
metric_b
的值计算公式为 89 = 24 + 35 + 30; 4 = 4
- 最后,当我想按
dim_a
分组时,并查看度量。metric_a
和metric_b
.预期结果是:
dim_a,metric_a,metric_b
A,18,93
B,62,93
C,13,93
metric_b
的值计算公式为 93 = 24 + 35 + 30 + 4
因此,metric_b
的聚合应该是metric_b
的总和,但不会将帐户dim_a
作为分组列,而是将其他所有内容都纳入其中。是否有SQL语法可以帮助我做到这一点?
更重要的是,我想说的是,这些查询将在AWS Redshift上运行,并且有20个指标和维度16,因此有36列。那里将有多达 1000 亿行。
对于数字 2:
SELECT *
FROM (
SELECT dim_a
,dim_b
,sum(metric_a) a
FROM dbo.Table_2 t
GROUP BY dim_a
,dim_b
) a
CROSS APPLY (
SELECT sum(metric_b) b
FROM (
SELECT DISTINCT metric_b
,hour
,dim_b
FROM dbo.Table_2
) t2
WHERE t2.dim_b = a.dim_b
) c
对于数字 3 :
SELECT *
FROM (
SELECT dim_b
,sum(metric_a) a
FROM dbo.Table_2 t
GROUP BY dim_b
) a
CROSS APPLY (
SELECT sum(metric_b) b
FROM (
SELECT DISTINCT metric_b
,hour
,dim_b
FROM dbo.Table_2
) t2
WHERE t2.dim_b = a.dim_b
) c
对于数字 4:
SELECT *
FROM (
SELECT dim_a
,sum(metric_a) a
FROM dbo.Table_2 t
GROUP BY dim_a
) a
CROSS APPLY (
SELECT sum(metric_b) b
FROM (
SELECT DISTINCT metric_b
,hour
,dim_b
FROM dbo.Table_2
) t2
) c