大查询 - 拆分功能

Question

INSERT INTO
"Table Name" (
CurrentMonthlastDate, 
MonthLastDate,
yearmonth)
SELECT DISTINCT 
(SELECT 
DATE_SUB(DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY) AS CurrentMonthlastDate),
MonthLastDate, -- Get last day of month based on year.month
yearmonth
FROM
"Table Name"

如果年月值为"2018.04"，如何拆分并找出该月的最后一天？ 是否可以在插入绳索时拆分值并在"MonthLastDate"字段上插入月份的最后一天。

Answer 1

#standardSQL
SELECT
DATE_SUB(DATE_TRUNC(DATE_ADD(PARSE_DATE('%Y.%m',
'2018.04'), INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY)

Answer 2

(SELECT DATE_SUB(DATE_TRUNC(DATE_ADD(DATE(CAST(REGEXP_EXTRACT(CAST(yearmonth AS STRING(，'(.(...'( 作为 INT64(， 铸件(REGEXP_EXTRACT(投(年月为字符串(，'.....(.)')如INT64(，01(，间隔1个月(，月(，间隔1天(作为月上一个日期(

我自己得到了。谢谢！！

Answer 3

下面是 BigQuery Standard SQL

#standardSQL
SELECT 
DATE_SUB(
DATE_ADD(PARSE_DATE('%Y.%m', yearmonth), INTERVAL 1 MONTH), 
INTERVAL 1 DAY
) MonthLastDate

您可以使用以下虚拟数据测试/播放上述内容

#standardSQL
WITH `project.dataset.table` AS (
SELECT '2018.04' AS yearmonth  UNION ALL
SELECT '2018.02'
)
SELECT 
yearmonth, 
DATE_SUB(
DATE_ADD(PARSE_DATE('%Y.%m', yearmonth), INTERVAL 1 MONTH), 
INTERVAL 1 DAY
) MonthLastDate
FROM `project.dataset.table`  

结果是

Row yearmonth   MonthLastDate    
1   2018.04     2018-04-30   
2   2018.02     2018-02-28