将行分为2列 [英] Splitting row in 2 columns

问题描述

尝试使用此查询在存在'/'分隔符的语言中进行数据拆分，但是它可以工作，但是当不存在'/'时，该行转到法语列，我希望如果不存在该行，则法语列为空"/"，数据应在英语"列中显示.它是根据网站ID排序的，因此只有ID 412中包含法语.

Trying to get data split by language where '/' delimiter is present used this query it works but when there is no '/' it the row goes to the french column i want the french column to be null if there is no '/' and the data should go in English column. It is sorted by site id so only id 412 has the french in it.

SELECT
s.siteid,
s.notes, --This is the column that CSI uses for the description.
split(s.notes,'/') [safe_OFFSET(0)] French,
split(s.notes,'/') [safe_OFFSET(1)] english
FROM AloomaTestBeta.SCSERVICES s

siteid  notes                           French                  english
412     Le cardio-/ Cardio Tennis .     Le cardio-tennis        Cardio Tennis 
412     Le cardio-/Cardio Tennis        Le cardio-tennis        Cardio Tennis 
412     La ligue de / Drop-In Tennis    La ligue de tennis      Drop-In Tennis 
411     An extended duration            An extended duration    null                    
411     Increase flexibility            Increase flexibility    Null    

也尝试使用case语句，但是当没有'/'分隔符时，它开始为我提供空值.

Also tried using the case statement but it starts giving me null when there is no '/' delimiter.

SELECT
s.siteid,
s.notes, --This is the column that CSI uses for the description.
case when  s.siteid = 412 then split(s.notes,'/') [safe_OFFSET(0)] else null end as French,
split(s.notes,'/') [safe_OFFSET(1)] english
FROM AloomaTestBeta.SCSERVICES s

siteid  notes                           French                  english
412     Le cardio-/ Cardio Tennis .     Le cardio-tennis        Cardio Tennis 
412     Le cardio-/Cardio Tennis        Le cardio-tennis        Cardio Tennis 
412     La ligue de / Drop-In Tennis    La ligue de tennis      Drop-In Tennis 
411     An extended duration            null                     null                   
411     Increase flexibility            null                      Null                  

这就是我想要的结果

siteid  notes                           French                  english
412     Le cardio-/ Cardio Tennis .     Le cardio-tennis        Cardio Tennis 
412     Le cardio-/Cardio Tennis        Le cardio-tennis        Cardio Tennis 
412     La ligue de / Drop-In Tennis    La ligue de tennis      Drop-In Tennis 
411     An extended duration            null                    An extended duration 
411     Increase flexibility            Null                    Increase flexibility

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT
  s.siteid,
  s.notes, --This is the column that CSI uses for the description.
  IF(v, SPLIT(s.notes,'/')[OFFSET(0)], NULL) French,
  IF(v, SPLIT(s.notes,'/')[SAFE_OFFSET(1)], SPLIT(s.notes,'/')[OFFSET(0)]) English
FROM `AloomaTestBeta.SCSERVICES` s, UNNEST([s.notes LIKE '%/%']) v

您可以使用问题中的示例数据来测试，玩转上面的示例

You can test, play with above using sample data from your question as in example below

#standardSQL
WITH `AloomaTestBeta.SCSERVICES` AS (
  SELECT 412 siteid, 'Le cardio-/ Cardio Tennis' notes UNION ALL
  SELECT 412, 'Le cardio-/Cardio Tennis' UNION ALL
  SELECT 412, 'La ligue de / Drop-In Tennis' UNION ALL
  SELECT 411, 'An extended duration' UNION ALL
  SELECT 411, 'Increase flexibility' 
)
SELECT
  s.siteid,
  s.notes, --This is the column that CSI uses for the description.
  IF(v, SPLIT(s.notes,'/')[OFFSET(0)], NULL) French,
  IF(v, SPLIT(s.notes,'/')[SAFE_OFFSET(1)], SPLIT(s.notes,'/')[OFFSET(0)]) English
FROM `AloomaTestBeta.SCSERVICES` s, UNNEST([s.notes LIKE '%/%']) v  

有结果

Row siteid  notes                           French          English  
1   412     Le cardio-/ Cardio Tennis       Le cardio-      Cardio Tennis    
2   412     Le cardio-/Cardio Tennis        Le cardio-      Cardio Tennis    
3   412     La ligue de / Drop-In Tennis    La ligue de     Drop-In Tennis   
4   411     An extended duration            null            An extended duration     
5   411     Increase flexibility            null            Increase flexibility       

如果您了解上述方法的工作原理，那么您就可以准备更优雅的解决方案了

If you got how above works - you are ready for more elegant solution

#standardSQL
SELECT
  s.siteid,
  s.notes, --This is the column that CSI uses for the description.
  SPLIT(s.notes,'/')[SAFE_OFFSET(v)] French,
  SPLIT(s.notes,'/')[SAFE_OFFSET(1 - v)] English
FROM `AloomaTestBeta.SCSERVICES` s, UNNEST([IF(s.notes LIKE '%/%', 0, 1)]) v

这篇关于将行分为2列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！