每个月的第一个星期三和最后一个星期四在BigQuery中

huangapple go评论98阅读模式
英文:

The First Wednesday and Last Thursday of EVERY month in BigQuery

问题

  1. 我想要提取每个月的第一个星期三,从每个月的第一天开始。

SELECT
CASE
WHEN latestRefreshTime => 每个月的第一个星期三 THEN '最新'
WHEN latestRefreshTime2 => 每个月的最后一个星期四 THEN '最新'
ELSE '不最新'
END AS 最新状态

  1. 我尝试使用DATE_TRUNC(CURRENT_DATE(), MONTH)来获取每个月的第一天,但是我很难将其进一步细化为每个月的第一个星期三,并且使其具有动态性,以便每个月都会变化。对于最后一个星期四也是一样的情况。
英文:

I am looking to pull the first Wednesday of EVERY month starting for the first day of the month.

  1. SELECT
  2. CASE
  3. WHEN latestRefreshTime => ***first wednesday of every month*** THEN 'Up to Date'
  4. WHEN latestRefreshTime2 => ***last thursday of every month*** THEN 'Up to Date'
  5. ELSE ' Not Up To Date'
  6. 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

每个月的第一个星期三和最后一个星期四在BigQuery中

英文:

Hope below BigQuery example will help you

  1. WITH days AS (
  2. SELECT CURRENT_DATE AS dt UNION ALL
  3. SELECT '2023-05-22' UNION ALL
  4. SELECT '2023-04-22' UNION ALL
  5. SELECT '2023-03-22'
  6. )
  7. SELECT dt,
  8. IF(EXTRACT(MONTH FROM x) = EXTRACT(MONTH FROM DATE(dt)), x, x - 7) as last_thursday,
  9. IF(EXTRACT(MONTH FROM y) = EXTRACT(MONTH FROM DATE(dt)), y, y + 7) as first_wednesday
  10. FROM(
  11. SELECT dt,
  12. LAST_DAY(LAST_DAY(DATE(dt), MONTH), WEEK(FRIDAY)) x,
  13. LAST_DAY(LAST_DAY(DATE_TRUNC(dt, MONTH) - 1, MONTH), WEEK(THURSDAY)) y
  14. FROM days
  15. )

with output

每个月的第一个星期三和最后一个星期四在BigQuery中

答案2

得分: 0

你可以创建一个中间表,该表包含了每个日期对应月份的第一个星期三和最后一个星期四,然后应用规则。

  1. DECLARE arbitrary_date DATE DEFAULT '2023-06-21';
  2. WITH
  3. -- 包含每个日期对应月份的第一天和最后一天的表格
  4. first_last_day_table AS (
  5. SELECT
  6. arbitrary_date,
  7. DATE_TRUNC(arbitrary_date, MONTH) AS first_day_month,
  8. DATE_SUB(DATE_TRUNC(DATE_ADD(arbitrary_date, INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY) AS last_day_month,
  9. )
  10. -- 包含每个日期对应月份的第一个星期三和最后一个星期四的表格
  11. SELECT
  12. arbitrary_date,
  13. first_day_month + MOD(11 - EXTRACT(DAYOFWEEK FROM first_day_month), 7) AS first_wednesday,
  14. last_day_month - MOD(2 + EXTRACT(DAYOFWEEK FROM last_day_month), 7) AS last_thursday
  15. 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.

  1. DECLARE arbitrary_date DATE DEFAULT'2023-06-21';
  2. WITH
  3. -- Table with first and last day of the month for each date
  4. first_last_day_table AS (
  5. SELECT
  6. arbitrary_date,
  7. DATE_TRUNC(arbitrary_date, MONTH) AS first_day_month,
  8. DATE_SUB(DATE_TRUNC(DATE_ADD(arbitrary_date, INTERVAL 1 MONTH), MONTH), INTERVAL 1 DAY) AS last_day_month,
  9. )
  10. -- Table with first wednesday and last thursday for each date
  11. SELECT
  12. arbitrary_date,
  13. first_day_month + MOD(11 - EXTRACT(DAYOFWEEK FROM first_day_month), 7) AS first_wednesday,
  14. last_day_month - MOD(2 + EXTRACT(DAYOFWEEK FROM last_day_month), 7) AS last_thursday
  15. FROM first_last_day_table

huangapple
  • 本文由 发表于 2023年6月22日 02:46:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76526262.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定