英文:
The First Wednesday and Last Thursday of EVERY month in BigQuery
问题
我想要提取每个月的第一个星期三，从每个月的第一天开始。
SELECT
CASE
WHEN latestRefreshTime => 每个月的第一个星期三 THEN '最新'
WHEN latestRefreshTime2 => 每个月的最后一个星期四 THEN '最新'
ELSE '不最新'
END AS 最新状态
我尝试使用DATE_TRUNC(CURRENT_DATE(), MONTH)来获取每个月的第一天，但是我很难将其进一步细化为每个月的第一个星期三，并且使其具有动态性，以便每个月都会变化。对于最后一个星期四也是一样的情况。
英文:
I am looking to pull the first Wednesday of EVERY month starting for the first day of the month.
SELECT
CASE
WHEN latestRefreshTime => ***first wednesday of every month*** THEN 'Up to Date'
WHEN latestRefreshTime2 => ***last thursday of every month*** THEN 'Up to Date'
ELSE ' Not Up To Date'
END AS upToDate
I have tried using DATE_TRUNC(CURRENT_DATE(), MONTH) to get the first day of the month, but I am having a hard time drilling it to down to the first wednesday and for it to be dynamic so it changes every month. The same thing for the last Thursday
答案1
得分: 1
以下是翻译好的部分：
希望下面的BigQuery示例对您有所帮助
WITH days AS (
SELECT CURRENT_DATE AS dt UNION ALL
SELECT '2023-05-22' UNION ALL
SELECT '2023-04-22' UNION ALL
SELECT '2023-03-22'
)
SELECT dt,
IF(EXTRACT(MONTH FROM x) = EXTRACT(MONTH FROM DATE(dt)), x, x - 7) as last_thursday,
IF(EXTRACT(MONTH FROM y) = EXTRACT(MONTH FROM DATE(dt)), y, y + 7) as first_wednesday
FROM(
SELECT dt,
LAST_DAY(LAST_DAY(DATE(dt), MONTH), WEEK(FRIDAY)) x,
LAST_DAY(LAST_DAY(DATE_TRUNC(dt, MONTH) - 1, MONTH), WEEK(THURSDAY)) y
FROM days
)
with output
英文:
Hope below BigQuery example will help you
WITH days AS (
SELECT CURRENT_DATE AS dt UNION ALL
SELECT '2023-05-22' UNION ALL
SELECT '2023-04-22' UNION ALL
SELECT '2023-03-22'
)
SELECT dt,
IF(EXTRACT(MONTH FROM x) = EXTRACT(MONTH FROM DATE(dt)), x, x - 7) as last_thursday,
IF(EXTRACT(MONTH FROM y) = EXTRACT(MONTH FROM DATE(dt)), y, y + 7) as first_wednesday
FROM(
SELECT dt,
LAST_DAY(LAST_DAY(DATE(dt), MONTH), WEEK(FRIDAY)) x,
LAST_DAY(LAST_DAY(DATE_TRUNC(dt, MONTH) - 1, MONTH), WEEK(THURSDAY)) y
FROM days
)
with output
答案2
得分: 0
你可以创建一个中间表，该表包含了每个日期对应月份的第一个星期三和最后一个星期四，然后应用规则。
DECLARE arbitrary_date DATE DEFAULT '2023-06-21';
WITH
-- 包含每个日期对应月份的第一天和最后一天的表格
first_last_day_table AS (
SELECT
arbitrary_date,
DATE_TRUNC(arbitrary_date, MONTH) AS first_day_month,
DATE_SUB(DATE_TRUNC(DATE_ADD(arbitrary_date, INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY) AS last_day_month,
)
-- 包含每个日期对应月份的第一个星期三和最后一个星期四的表格
SELECT
arbitrary_date,
first_day_month + MOD(11 - EXTRACT(DAYOFWEEK FROM first_day_month), 7) AS first_wednesday,
last_day_month - MOD(2 + EXTRACT(DAYOFWEEK FROM last_day_month), 7) AS last_thursday
FROM first_last_day_table
英文:
You can create an intermediate table with the first Wednesday and last Thursday of the month of the respective date and then aplly the rule.
DECLARE arbitrary_date DATE DEFAULT'2023-06-21';
WITH
-- Table with first and last day of the month for each date
first_last_day_table AS (
SELECT
arbitrary_date,
DATE_TRUNC(arbitrary_date, MONTH) AS first_day_month,
DATE_SUB(DATE_TRUNC(DATE_ADD(arbitrary_date, INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY) AS last_day_month,
)
-- Table with first wednesday and last thursday for each date
SELECT
arbitrary_date,
first_day_month + MOD(11 - EXTRACT(DAYOFWEEK FROM first_day_month), 7) AS first_wednesday,
last_day_month - MOD(2 + EXTRACT(DAYOFWEEK FROM last_day_month), 7) AS last_thursday
FROM first_last_day_table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论