时间戳

首页 > 解决方案 > 如何在 BQ 中显示以特定字符开头的名称

google-bigquery - 如何在 BQ 中显示以特定字符开头的名称

问题描述

如何显示以 A、G 和 E 开头的国家名称？

#standardSQL
with table1 as(
select "America" as country_name union all
select "Germany" as country_name union all
select "England" as country_name union all
select "Nauru" as country_name union all
select "Brunei" as country_name union all
select "Kiribati" as country_name union all
select "Djibouti" as country_name union all
select "Malta" as country_name
)
select * from table1

标签： google-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
WITH table1 AS(
  SELECT "America" AS country_name UNION ALL
  SELECT "Germany" AS country_name UNION ALL
  SELECT "England" AS country_name UNION ALL
  SELECT "Nauru" AS country_name UNION ALL
  SELECT "Brunei" AS country_name UNION ALL
  SELECT "Kiribati" AS country_name UNION ALL
  SELECT "Djibouti" AS country_name UNION ALL
  SELECT "Malta" AS country_name
)
SELECT *
FROM table1
ORDER BY CASE 
  WHEN LOWER(SUBSTR(country_name, 1, 1)) IN ('a', 'g', 'e') 
  THEN CONCAT(' ', country_name) ELSE country_name 
END

预期输出是

Row country_name     
1   America  
2   England  
3   Germany  
4   Brunei   
5   Djibouti     
6   Kiribati     
7   Malta    
8   Nauru

如果您只需要那些国家 

#standardSQL
WITH table1 AS(
  SELECT "America" AS country_name UNION ALL
  SELECT "Germany" AS country_name UNION ALL
  SELECT "England" AS country_name UNION ALL
  SELECT "Nauru" AS country_name UNION ALL
  SELECT "Brunei" AS country_name UNION ALL
  SELECT "Kiribati" AS country_name UNION ALL
  SELECT "Djibouti" AS country_name UNION ALL
  SELECT "Malta" AS country_name
)
SELECT *
FROM table1
WHERE LOWER(SUBSTR(country_name, 1, 1)) IN ('a', 'g', 'e')

或者

#standardSQL
WITH table1 AS(
  SELECT "America" AS country_name UNION ALL
  SELECT "Germany" AS country_name UNION ALL
  SELECT "England" AS country_name UNION ALL
  SELECT "Nauru" AS country_name UNION ALL
  SELECT "Brunei" AS country_name UNION ALL
  SELECT "Kiribati" AS country_name UNION ALL
  SELECT "Djibouti" AS country_name UNION ALL
  SELECT "Malta" AS country_name
)
SELECT * 
FROM table1
WHERE REGEXP_CONTAINS(country_name, r'(?i)^(a|g|e)')

以上均返回： 

Row country_name     
1   America  
2   Germany  
3   England

推荐阅读