如何在不拆分不同单词的情况下将BigQuery中的字符串分成多列？

Question

我尝试将一个字符串分成两列，但前提是字符串的总长度大于25个字符。如果它少于25个字符，那么我希望它只出现在第二列。如果长度大于25，那么我希望字符串的第一部分在第一列，字符串的第二部分在第二列。关键是..。我不希望文字被拆散。因此，如果字符串的总长度是26，我知道我需要两列，但我需要弄清楚在哪里拼接字符串，以便在每一列中只表示完整的单词。

例如，字符串为"Transportation Manager“。因为它有超过25个字符，我希望第一列说“运输项目”，第二列说“经理”。“运输项目”少于25个字符，但我希望它到此为止，因为没有另一个完整的单词可以在25个字符的限制内。

另一个例子--字符串是"Caseworker I“。因为它少于25个字符，所以我希望在第2列中表示整个字符串。

谢谢您抽时间见我!

Answer 1

哇，这是一个很棒的面试问题！这是我想出来的：

WITH sample_data AS 
(
    SELECT 'Transportation Project Manager' AS phrase
    UNION ALL
    SELECT 'Caseworker I' AS phrase
    UNION ALL
    SELECT "This's 25 characters long" AS phrase
    UNION ALL
    SELECT "This's 25 characters long (not!)" AS phrase
    UNION ALL
    SELECT 'Antidisestablishmentarianist' AS phrase
),
unnested_words AS --Make a dataset with one row per "word" per phrase
(
    SELECT 
        *,
        --To preserve the spaces for character counts, prepend one to every word but the first
        CASE WHEN i = 0 THEN '' ELSE ' ' END || word AS word_with_space
    FROM
        sample_data
    CROSS JOIN 
        UNNEST(SPLIT(phrase, ' ')) AS word WITH OFFSET AS i
),
with_word_length AS 
(
    SELECT 
        *,
        --This doesn't need its own CTE, but done here for clarity
        LENGTH(word_with_space) AS word_length
    FROM
        unnested_words
),
running_sum AS --Mark when the total character length exceeds 25
(
    SELECT 
        *,
        SUM(word_length) OVER (PARTITION BY phrase ORDER BY i) <= 25 AS is_first_25
    FROM
        with_word_length
),
by_subphrase AS --Make a subphrase of words in the first 25, and one for any others
(
    SELECT 
        phrase,
        ARRAY_TO_STRING(ARRAY_AGG(word), '') AS subphrase
    FROM
        running_sum
    GROUP BY 
        phrase, is_first_25
),
by_phrase AS --Put subphrases into an array (back to one row per phrase)
(
    SELECT 
        phrase, ARRAY_AGG(subphrase) AS subphrases 
    FROM 
        by_subphrase 
    GROUP BY 
        1
)
SELECT 
    phrase,
    --Break the array of subphrases into columns per your rules
    CASE WHEN ARRAY_LENGTH(subphrases) = 1 THEN subphrases[OFFSET(0)] ELSE subphrases[OFFSET(1)] END, 
    CASE WHEN ARRAY_LENGTH(subphrases) = 1 THEN NULL ELSE subphrases[OFFSET(0)] END
FROM 
    by_phrase

虽然不是很漂亮，但还是能搞定。

Answer 2

为了根据定义的最大长度(遵循您描述的逻辑)将一个字符串拆分为两列，我们将结合使用内置函数LENGTH和JavaScript User Defined Function in BigQuery (UDF)。

首先，将对字符串进行分析。如果最大阈值之后的字符是空格，那么它将以给定的最大字符串长度拆分。但是，如果不是这样，将检查每个字符，向后计数，直到找到空格并拆分字符串。有了这个过程，避免了拆分单词的功能，它将始终根据最大允许长度进行拆分。

下面是带有一些示例数据的查询，

CREATE TEMP FUNCTION split_str_1(s string,len int64)
RETURNS string
LANGUAGE js AS """
var len_aux = len, prev = 0;

//first part of the string within the threshold 
output = [];

//the rest of the string wihtout the first part
output2 = [];

//if the next character in the string is a whitespace, them split the string  
if(s[len_aux++] == ' ') {

    output.push(s.substring(prev,len_aux));
    output2.push(s.substring(prev,s.length));

}
else{
      do {
          if(s.substring(len_aux - 1, len_aux) == ' ')
            {
                 output.push(s.substring(prev,len_aux));
                 prev = len_aux;
                 output2.push(s.substring(prev,s.length));  
                 break;
           }len_aux--;
       } while(len_aux > prev)     
      }
      
//outputting the first part of the string      
return output[0];
""";

CREATE TEMP FUNCTION split_str_2(s string,len int64)
RETURNS string
LANGUAGE js AS """
var len_aux = len, prev = 0;

//first part of the string within the threshold 
output = [];

//the rest of the string wihtout the first part
output2 = [];

//if the next character in the string is a whitespace, them split the string  
if(s[len_aux++] == ' ') {

    output.push(s.substring(prev,len_aux));
    output2.push(s.substring(prev,s.length));

}
else{
      do {
          if(s.substring(len_aux - 1, len_aux) == ' ')
            {
                 output.push(s.substring(prev,len_aux));
                 prev = len_aux;
                 output2.push(s.substring(prev,s.length));  
                 break;
           }len_aux--;
       } while(len_aux > prev)     
      }
      
//outputting the first part of the string      
return output2[0];
""";

WITH data AS (
SELECT "Trying to split a string with more than 25 characters length" AS str UNION ALL
SELECT "Trying to split"  AS str
)
SELECT  str,
        IF(LENGTH(str)>25, split_str_1(str,25), null) as column_1,
        CASE WHEN LENGTH(str)>25 THEN split_str_2(str,25) ELSE str END AS column_2
FROM data

以及输出，

​

​

注意，有2个JavaScript UDF，这是因为当字符串长度超过25个字符时，第一个返回字符串的第一部分，第二个返回第二部分。此外，允许的最大长度作为参数传递，但它可以在UDF中静态定义为len=25。

Answer 3

我认为你的攻角应该是找到第25个字符之前的第一个空格，然后在此基础上拆分。

使用其他提交的答案短语作为样本数据：

 with sample_data as(
  select 'Transportation Project Manager' as phrase union all
  select 'Caseworker I'as phrase union all
  select "This's 25 characters long" as phrase union all
  select "This's 25 characters long (not!)" as phrase union all
  select 'Antidisestablishmentarianist' as phrase union all
  select 'Trying to split a string with more than 25 characters in length' as phrase union all
  select 'Trying to split' as phrase
),
temp as (
  select 
    phrase,
    length(phrase) as phrase_len,
    -- Find the first space before the 25th character
    -- by reversing the first 25 characters
    25-strpos(reverse(substr(phrase,1,25)),' ') as first_space_before_25 
  from sample_data
)
select 
  phrase,
  phrase_len,
  first_space_before_25,
  case when phrase_len <= 25 or first_space_before_25 = 25 then null
       when phrase_len > 25 then substr(phrase,1,first_space_before_25)
       else null
  end as col1,
  case when phrase_len <= 25 or first_space_before_25 = 25 then phrase
       when phrase_len > 25 then substr(phrase,first_space_before_25+1, phrase_len)
       else null
  end as col2
from temp

​

​

我认为，使用基本的sql字符串操作，这将使您非常接近。您可能需要/想要根据您希望col2从空格开始还是被修剪，以及您的截止点(您提到的小于25和大于25，但不完全是25)来清理这一点。