首页 文章

BigQuery - 将日期数组转换为字符串

提问于
浏览
2

我在 Big Query 中有一个表,其中一些字段是日期数组(类型为Date Repeated) . 我需要将它们转换为字符串 . 对于字符串字段数组我正在执行如下,它工作正常:

ARRAY_TO_STRING(national_id, "|", "") AS national_id

但是当该字段是一系列日期时我会低于错误 .

参数类型的函数ARRAY_TO_STRING没有匹配的签名:ARRAY,STRING,STRING . 支持的签名:ARRAY_TO_STRING(ARRAY,STRING,[STRING]); ARRAY_TO_STRING(ARRAY,BYTES,[BYTES])在[41:1]

我也尝试将日期转换为字符串,如下所示:

ARRAY_TO_STRING(cast(natural_person_date_of_birth_list as string), "|", "") AS natural_person_date_of_birth_list,

但我得到以下错误:

在[41:22]从ARRAY到STRING的无效演员表 .

任何人都可以帮我解决这个问题吗?

非常感谢你

3 回答

  • 0

    您可以使用选择列表中的子查询将日期转换为字符串 . 例如,

    SELECT
      ARRAY_TO_STRING(national_id, "|", "") AS national_id,
      (SELECT STRING_AGG(date, "|") FROM UNNEST(natural_person_date_of_birth_list)) AS dates_of_birth
    FROM YourTable;
    

    这种方法的优点是你可以获得每行的字符串,这听起来像你想要的 .

  • 0

    您可以使用 unnest() 执行此操作:

    select string_agg(cast(dte as string), '|')
    FROM (select [date('2017-01-01'), date('2018-01-01')] as aofd
         ) d,
         unnest(aofd) as dte
    
  • 2

    请参阅以下两个选项 . 两者都适用于BigQuery Standard SQL

    #standardSQL
    SELECT 
      REPLACE(REGEXP_REPLACE(TO_JSON_STRING(natural_person_date_of_birth_list), r'\[|\]|"', ''), ',', '|') dates_string_1,
      (SELECT STRING_AGG(FORMAT_DATE('%Y-%m-%d', d), "|") 
        FROM UNNEST(natural_person_date_of_birth_list) d) AS dates_string_2
    FROM `project.dataset.table`
    

    以下是您的快速测试和玩法:

    #standardSQL
    WITH `project.dataset.table` AS (
      SELECT [CURRENT_DATE(), DATE '2011-10-15'] natural_person_date_of_birth_list
    )
    SELECT 
      REPLACE(REGEXP_REPLACE(TO_JSON_STRING(natural_person_date_of_birth_list), r'\[|\]|"', ''), ',', '|') dates_string_1,
      (SELECT STRING_AGG(FORMAT_DATE('%Y-%m-%d', d), "|") 
        FROM UNNEST(natural_person_date_of_birth_list) d) AS dates_string_2
    FROM `project.dataset.table`
    

    两种选择都回归了我的预期:

    dates_string_1           dates_string_2  
    2017-11-01|2011-10-15    2017-11-01|2011-10-15
    

    注意:第二个选项的好处是您可以控制列表中日期的顺序,例如

    #standardSQL
    WITH `project.dataset.table` AS (
      SELECT [CURRENT_DATE(), DATE '2011-10-15'] natural_person_date_of_birth_list
    )
    SELECT 
      REPLACE(REGEXP_REPLACE(TO_JSON_STRING(natural_person_date_of_birth_list), r'\[|\]|"', ''), ',', '|') dates_string_1,
      (SELECT STRING_AGG(FORMAT_DATE('%Y-%m-%d', d), "|" ORDER BY d) 
        FROM UNNEST(natural_person_date_of_birth_list) d
      ) AS dates_string_2
    FROM `project.dataset.table`
    

    这里输出为(注意在dates_string_2中的变化)

    dates_string_1          dates_string_2   
    2017-11-01|2011-10-15   2011-10-15|2017-11-01
    

相关问题