google-bigquery - Bigquery 中的两位数 ISO 周格式 (ww)

Question

我想要 Bigquery 中的两位数 ISO 周格式 (ww)

"select week(date)..." //statement results in a one digit week format.

提前感谢您的帮助

圭多

Answer 1

取决于您的业务逻辑 - 您可能还想考虑 %W除了%V
试试看下面的区别

#standardSQL
WITH SampleDates AS (
  SELECT DATE '2017-01-01' AS day UNION ALL
  SELECT DATE '2017-02-28' UNION ALL
  SELECT DATE '2016-12-15' 
)
SELECT
  day,
  FORMAT_DATE('%a', day) AS weekday,
  FORMAT_DATE('%V', day) AS weeknumber1,
  FORMAT_DATE('%W', day) AS weeknumber2
FROM SampleDates
ORDER BY day

day         weekday weeknumber1 weeknumber2  
2016-12-15  Thu     50          50   
2017-01-01  Sun     52          00   
2017-02-28  Tue     09          09    

查看 2017-01-01的区别
这是因为

%V  The week number of the year (Monday as the first day of the week) 
    as a decimal number (01-53).   
    If the week containing January 1 has four or more days in the new year,  
    then it is week 1; otherwise it is week 53 of the previous year,   
    and the next week is week 1.  

%W  The week number of the year (Monday as the first day of the week)   
    as a decimal number (00-53).

同时，您仍然可以在旧版 SQL 中使用两位数。下图

#legacySQL
SELECT 
  day,
  RIGHT(STRING(100 + WEEK(day)), 2) as weeknumber
FROM (SELECT '2017-01-01' AS day),
     (SELECT '2017-02-28' AS day),
     (SELECT '2016-12-15' AS day)
ORDER BY day