SQL BigQuery在使用case Then获取列时花费了太多时间

Question

当前的问题是我们需要"动态地"；使用SQL检索表列。

现在，当我们设置ID和readStatus值时，查询的运行时间远远超过3分钟。

这个想法是，我们得到所有其他列，而那些动态的列是动态的，只有当变量调用它时才会检索到

declare var1 String;
declare var2 String;
declare var3 String;
declare var4 String;
declare var5 String;
declare cpeValue String;
declare readStatusValue String;
set var1 = "1_15";
set var2 = "2_3";
set var3 = "3_45";
set var4 = "4_6";
set var5 = "5_75";
set IDValue = "PT0002000003259606FA";
set readStatusValue = "null";
SELECT 
ID,
Read_Status,
CASE 
WHEN var1='1_15' THEN DYNAMIC_COLUMN_1_15
WHEN var1='2_3' THEN DYNAMIC_COLUMN_2_3
WHEN var1='3_45' THEN DYNAMIC_COLUMN_3_45
WHEN var1='4_6' THEN DYNAMIC_COLUMN_4_6
WHEN var1='5_75' THEN DYNAMIC_COLUMN_5_75
WHEN var1='6_9' THEN DYNAMIC_COLUMN_6_9
WHEN var1='10_35' THEN DYNAMIC_COLUMN_10_35
WHEN var1='13_8' THEN DYNAMIC_COLUMN_13_8
WHEN var1='17_25' THEN DYNAMIC_COLUMN_17_25
WHEN var1='20_7' THEN DYNAMIC_COLUMN_20_7
WHEN var1='27_6' THEN DYNAMIC_COLUMN_27_6
WHEN var1='34_5' THEN DYNAMIC_COLUMN_34_5
WHEN var1='41_4' THEN DYNAMIC_COLUMN_41_4
ELSE 0
END AS DYNAMIC_COLUMN_1,
CASE 
WHEN var2='1_15' THEN DYNAMIC_COLUMN_1_15
WHEN var2='2_3' THEN DYNAMIC_COLUMN_2_3
WHEN var2='3_45' THEN DYNAMIC_COLUMN_3_45
WHEN var2='4_6' THEN DYNAMIC_COLUMN_4_6
WHEN var2='5_75' THEN DYNAMIC_COLUMN_5_75
WHEN var2='6_9' THEN DYNAMIC_COLUMN_6_9
WHEN var2='10_35' THEN DYNAMIC_COLUMN_10_35
WHEN var2='13_8' THEN DYNAMIC_COLUMN_13_8
WHEN var2='17_25' THEN DYNAMIC_COLUMN_17_25
WHEN var2='20_7' THEN DYNAMIC_COLUMN_20_7
WHEN var2='27_6' THEN DYNAMIC_COLUMN_27_6
WHEN var2='34_5' THEN DYNAMIC_COLUMN_34_5
WHEN var2='41_4' THEN DYNAMIC_COLUMN_41_4
ELSE 0
END AS DYNAMIC_COLUMN_2,
CASE 
WHEN var3='1_15' THEN DYNAMIC_COLUMN_1_15
WHEN var3='2_3' THEN DYNAMIC_COLUMN_2_3
WHEN var3='3_45' THEN DYNAMIC_COLUMN_3_45
WHEN var3='4_6' THEN DYNAMIC_COLUMN_4_6
WHEN var3='5_75' THEN DYNAMIC_COLUMN_5_75
WHEN var3='6_9' THEN DYNAMIC_COLUMN_6_9
WHEN var3='10_35' THEN DYNAMIC_COLUMN_10_35
WHEN var3='13_8' THEN DYNAMIC_COLUMN_13_8
WHEN var3='17_25' THEN DYNAMIC_COLUMN_17_25
WHEN var3='20_7' THEN DYNAMIC_COLUMN_20_7
WHEN var3='27_6' THEN DYNAMIC_COLUMN_27_6
WHEN var3='34_5' THEN DYNAMIC_COLUMN_34_5
WHEN var3='41_4' THEN DYNAMIC_COLUMN_41_4
ELSE 0
END AS DYNAMIC_COLUMN_3,
CASE 
WHEN var4='1_15' THEN DYNAMIC_COLUMN_1_15
WHEN var4='2_3' THEN DYNAMIC_COLUMN_2_3
WHEN var4='3_45' THEN DYNAMIC_COLUMN_3_45
WHEN var4='4_6' THEN DYNAMIC_COLUMN_4_6
WHEN var4='5_75' THEN DYNAMIC_COLUMN_5_75
WHEN var4='6_9' THEN DYNAMIC_COLUMN_6_9
WHEN var4='10_35' THEN DYNAMIC_COLUMN_10_35
WHEN var4='13_8' THEN DYNAMIC_COLUMN_13_8
WHEN var4='17_25' THEN DYNAMIC_COLUMN_17_25
WHEN var4='20_7' THEN DYNAMIC_COLUMN_20_7
WHEN var4='27_6' THEN DYNAMIC_COLUMN_27_6
WHEN var4='34_5' THEN DYNAMIC_COLUMN_34_5
WHEN var4='41_4' THEN DYNAMIC_COLUMN_41_4
ELSE 0
END AS DYNAMIC_COLUMN_4,
CASE 
WHEN var5='1_15' THEN DYNAMIC_COLUMN_1_15
WHEN var5='2_3' THEN DYNAMIC_COLUMN_2_3
WHEN var5='3_45' THEN DYNAMIC_COLUMN_3_45
WHEN var5='4_6' THEN DYNAMIC_COLUMN_4_6
WHEN var5='5_75' THEN DYNAMIC_COLUMN_5_75
WHEN var5='6_9' THEN DYNAMIC_COLUMN_6_9
WHEN var5='10_35' THEN DYNAMIC_COLUMN_10_35
WHEN var5='13_8' THEN DYNAMIC_COLUMN_13_8
WHEN var5='17_25' THEN DYNAMIC_COLUMN_17_25
WHEN var5='20_7' THEN DYNAMIC_COLUMN_20_7
WHEN var5='27_6' THEN DYNAMIC_COLUMN_27_6
WHEN var5='34_5' THEN DYNAMIC_COLUMN_34_5
WHEN var5='41_4' THEN DYNAMIC_COLUMN_41_4
ELSE 0
END AS DYNAMIC_COLUMN_5
FROM `Very.Big.Table`
WHERE ID = cpeValue
AND (readStatusValue = "null" or Read_Status = readStatusValue)

现在的readStatus，当它有一个不同于null的值时，它需要比只有id和null更长的时间。

Answer 1

试试下面的(BigQuery标准SQL(

declare var1 String;
declare var2 String;
declare var3 String;
declare var4 String;
declare var5 String;
declare cpeValue String;
declare readStatusValue String;
set var1 = "1_15";
set var2 = "2_3";
set var3 = "3_45";
set var4 = "4_6";
set var5 = "5_75";
set cpeValue = "PT0002000003259606FA";
set readStatusValue = "null";
EXECUTE IMMEDIATE '''
SELECT 
ID,
Read_Status,
DYNAMIC_COLUMN_''' || var1 || ''' DYNAMIC_COLUMN_1,
DYNAMIC_COLUMN_''' || var2 || ''' DYNAMIC_COLUMN_2,
DYNAMIC_COLUMN_''' || var3 || ''' DYNAMIC_COLUMN_3,
DYNAMIC_COLUMN_''' || var4 || ''' DYNAMIC_COLUMN_4,
DYNAMIC_COLUMN_''' || var5 || ''' DYNAMIC_COLUMN_5
FROM `Very.Big.Table`
WHERE ID = "''' || cpeValue || '''" ''' || 
CASE 
WHEN readStatusValue != "null" 
THEN ''' AND Read_Status = "''' || readStatusValue || '''" '''
ELSE ''' '''
END;