时间戳

首页 > 解决方案 > 获取一周中接下来 n 天的列表？

sql - 获取一周中接下来 n 天的列表？

问题描述

我有两列工作日和这些工作日之间的天数，如下所示：

Monday  | Saturday    | 13
Tuesday | Friday      | 54
Friday  | Wednesday   | 10

等我需要使用 SQL 来创建另一个包含两个工作日之间所有 13 或 54 个工作日的字符串的列。所以像“星期一，星期二，星期三，......星期一，星期二......星期五，星期六”

如何实现？谢谢。

标签： sqlstringparsinggoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT start, duration,
  REGEXP_EXTRACT(
    REPEAT('Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,',CAST(duration/7+1 AS INT64)),
    CONCAT(r'', start, r'(?:(?:,\w+){', CAST(duration - 1 AS STRING), '})')
  ) days_list
FROM `project.dataset.table`

您可以使用来自您问题的虚拟数据来测试/玩它，如下所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Monday' start, 'Saturday' finish, 13 duration UNION ALL
  SELECT 'Tuesday', 'Friday', 54 UNION ALL
  SELECT 'Friday', 'Wednesday', 10 
)
SELECT start, duration,
  REGEXP_EXTRACT(
    REPEAT('Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,', CAST(duration / 7 + 1 AS INT64) ), 
    CONCAT(r'', start, r'(?:(?:,\w+){', CAST(duration - 1 AS STRING), '})')
  ) days_list
FROM `project.dataset.table`

结果为 

Row start   duration    days_list    
1   Monday  13  Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday   
2   Tuesday 54  Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday    
3   Friday  10  Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday

推荐阅读