如何在Snowflake中构建从(年，周，周)开始的日期

Question

我有一种情况，使用Snowflake，我需要从这些部分构建日期(年、周、周(。ie(2022年，第13周，周三为3(将相当于今天的日期2022-03-30。

虽然这在Pandas中是可能的，但Snowflake的day_from_parts()似乎不支持此功能。有没有其他方法可以根据这三条信息构建日期？

就上下文而言，我最终试图在我的dimDate中为"weekToDate"one_answers"weektoDatePreview year"创建标志(上一年的比较是实际上不是同一日期，而是Mon-上一年同一周的星期一(。旗帜还必须说明跨越两年的周数，以及53周的闰年(在这种情况下，它将与前一年的第52周进行比较(。我需要构建的日期是前一年的比较日期到今天，来自部分：

(year(current_date())-1, min(weekOfYear(current_date()), 52), dayOfWeek(current_date()))

提前感谢您的任何建议！

Answer 1

记住设置以下参数以确保，一年中的第一周是包含该年1月1日的一周。

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

生成全年日历。使用了来自@Simeon Pilgrim 的子查询

select * from
(
select row_number() over (order by null)-1 as rn
,dateadd('day', rn, '2022-01-01'::date) as date_dt,WEEKOFYEAR(dateadd('day', rn, '2022-01-01'::date)
) as woy, DAYOFWEEK(dateadd('day', rn, '2022-01-01'::date)) dow
from table(generator(rowcount=>365))
)
limit 10;
+----+------------+-----+-----+
| RN | DATE_DT    | WOY | DOW |
|----+------------+-----+-----|
|  0 | 2022-01-01 |   1 |   6 |
|  1 | 2022-01-02 |   1 |   0 |
|  2 | 2022-01-03 |   2 |   1 |
|  3 | 2022-01-04 |   2 |   2 |
|  4 | 2022-01-05 |   2 |   3 |
|  5 | 2022-01-06 |   2 |   4 |
|  6 | 2022-01-07 |   2 |   5 |
|  7 | 2022-01-08 |   2 |   6 |
|  8 | 2022-01-09 |   2 |   0 |
|  9 | 2022-01-10 |   3 |   1 |
+----+------------+-----+-----+

根据星期几和一年中的星期获取所需日期

select date_dt from
(
select row_number() over (order by null)-1 as rn
,dateadd('day', rn, '2022-01-01'::date) as date_dt,WEEKOFYEAR(dateadd('day', rn, '2022-01-01'::date)
) as woy, DAYOFWEEK(dateadd('day', rn, '2022-01-01'::date)) dow
from table(generator(rowcount=>365))
)
where woy=13 and dow=4;
+------------+
| DATE_DT    |
|------------|
| 2022-03-24 |
+------------+

有关提取日期部分的更多信息，请参阅此处。

Answer 2

@Panjak我无法让你的代码工作。。。这篇"；基于星期几和一年中的星期获得期望的日期">刚给我上一年的今天的日期。

然而，我找到了另一个使用CTE的解决方案！这是我使用WTD和WTD_PREV_YR标志的最后一个视图。注意，它还包含MTD、MTD_PREV_YR、YTD、YTD_PREV_Y。

create or replace view DIM_DATE as (
with comparison_date as (
select
case
when weekofyear(current_date()) = 53
then 52
else weekofyear(current_date())
end as comp_week
, date as comp_date
from <TABLE>
where year(comp_date) = year(current_date()) - 1
and weekofyear(comp_date) = comp_week
and dayofweek(comp_date) = dayofweek(current_date())
)
select distinct
d.date
, case
when weekofyear(date) = weekofyear(current_date())
and date - current_date() <= 0
and date - current_date() >= -6
then 1
else 0
end as WTD
, case
when weekofyear(date) = weekofyear(c.comp_date)
and date - c.comp_date <= 0
and date - c.comp_date >= -6
then 1
else 0
end as WTD_PREV_YR
, CASE
WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
AND YEAR(DATE) = YEAR(CURRENT_DATE())
THEN 1
ELSE 0
END AS MTD
, CASE
WHEN MONTH(DATE) = MONTH(CURRENT_DATE())
AND DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
THEN 1
ELSE 0
END AS MTD_PREV_YR
, CASE
WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
AND YEAR(DATE) = YEAR(CURRENT_DATE())
THEN 1
ELSE 0
END AS YTD
, CASE
WHEN DAYOFYEAR(DATE) <= DAYOFYEAR(CURRENT_DATE())
AND YEAR(DATE) = YEAR(CURRENT_DATE()) - 1
THEN 1
ELSE 0
END AS YTD_PREV_YR
from <TABLE> as d
join comparison_date as c on 1=1
order by date desc
);