我正在尝试创建一个where语句，该语句考虑一周中的哪一天，然后根据它进行筛选。例如，如果我提取数据的日期是周三，我只想提取该周的周一至周三的数据。如果是星期五，那么星期一-星期五，以此类推
我试着在where子句中使用一个case来做这件事，但我无法使它发挥作用。
select
uuid,
acquisition_campaign,
REFERRAL_PROMO,
channel,
partner,
created_at::date as created_date
from CONSUMER_TMP
where created_date between case DAYOFWEEK(getdate())
when 0 then (getdate()::date) and (getdate()::date)
when 1 then (DATEADD('day', -1, getdate())) and (getdate())
when 2 then (DATEADD('day', -2, getdate())) and (getdate())
when 3 then (DATEADD('day', -3, getdate())) and (getdate())
when 4 then (DATEADD('day', -4, getdate())) and (getdate())
when 5 then (DATEADD('day', -5, getdate())) and (getdate())
when 6 then (DATEADD('day', -6, getdate())) and (getdate())
else (DATEADD('day', -7, getdate())) and (getdate())
end
我没有测试它，但我认为这会起作用。
一个问题是不能在where子句中使用自定义列名，所以首先必须将其更改为实际列名。其次，您需要更改查询中的and，如下所示：
where created_at::date between case DAYOFWEEK(getdate())
when 0 then (getdate()::date)
when 1 then (DATEADD('day', -1, getdate()))
when 2 then (DATEADD('day', -2, getdate()))
when 3 then (DATEADD('day', -3, getdate()))
when 4 then (DATEADD('day', -4, getdate()))
when 5 then (DATEADD('day', -5, getdate()))
when 6 then (DATEADD('day', -6, getdate()))
else (DATEADD('day', -7, getdate()))
end and (getdate())
因为您的案例中的and需要像between something and other_thing一样位于case之外，例如，当DAYOFWEEK(getdate())为0时，它会检查created_date between (getdate()::date) and (getdate())是否存在，依此类推
编辑：
正如@Simeon Pilgrim在评论中所说，您可以在雪花中使用select中的列名。
我认为你甚至可以进一步简化你的查询，你根本不需要case，你可以这样做：
where created_date between (DATEADD('day',-(DAYOFWEEK(getdate())),getdate())) and (getdate())
因此，如果星期几是0，它将返回between getdate() and getdate()，因为加上-0天会增加0天，如果是1，它将恢复between getdate() - 1 and getdate()，依此类推
查询的另一个问题是else (DATEADD('day', -7, getdate()))，我相信你可以删除这个else并将其添加到不同的日期，因为dayofweek()只能返回7个值，(0-6)，所以你可以为其他任何一天添加其他值，而不是为当天添加when then，所以如果我以前的建议不起作用(我不知道为什么不应该)，你可以用这个建议来简化它。
要做的第一点是，您的case语句正在截断到本周的开始，所以您应该只执行DATE_TRUNC
此代码获取一些天数，并与它们一起显示自联接天数的输入或输出，即显示您的current_date()与d1如何相同
with data(date) as (
select
to_date(column1, 'mm-dd-yyyy')
from values
('07-10-2022'),
('07-11-2022'),
('07-12-2022'),
('07-13-2022'),
('07-14-2022'),
('07-15-2022')
)
select d1.date as d1
,d2.date as d2
,dayname(d1) as day_name
,DAYOFWEEK(d1) as day_of_week
,date_trunc('week', d1) as trunc_d1
,d2 between trunc_d1 and d1 as in_range
from data as d1
cross join data as d2
order by 1;
| D1 | D2 | DAY_NAME | >DAY_OF_WEEKTRUNC_D1IN_RANGE|||
|---|---|---|---|---|---|
| 2022-07-10 | 2022-07-11 | 太阳 | 02022:07-04 | 错误 | |
| 2022-07-10 | 2022-07-12 | 太阳 | 02022:07-04错误|||
| 2022-07-10 | 2022-07-13 | 周日0 | 2022-07-04错误 | ||
| 2022-07-10 | 2022-07-14 | 周日0 | 2022-07-04错误|||
| 2022-07-10 | 2022-07-15 | 周日0 | 2022-07-04错误 | ||
| 2022-07-10 | 2022-07-10 | 太阳0 | 2022-07-04真|||
| 2022-07-11 | 2022-07-14 | 周一 | 1 | <2022-07-11>错误 | |
| 2022-07-11 | 2022-07-15 | 周一 | 1 | <2022-07-11>错误 | |
| 2022-07-11 | 2022-07-13 | 周一 | 1 | <2022-07-11>错误 | |
| 2022-07-11 | 2022-07-12 | 周一 | 202207-11 | 错误 | |
| 2022-07-11 | 2022-07-11 | 周一1 | 22022-07-11 | 真 | |
| 2022-07-11 | 2022-07-10 | 周一1 | 22022-07-11 | 错误 | |
| 2022-07-12 | 2022-07-10 | 周二22022-07-11 | 错误|||
| 2022-07-12 | 2022-07-11 | 周二<2022-07-11>真 | //tr>|||
| 2022-07-12 | 2022-07-12 | 周二<2022-07-11>真 | //tr>|||
| 2022-07-12 | 2022-07-13 | 周二<2022-07-11>错误 | //tr>|||
| 2022-07-12 | 2022-07-14 | 周二<2022-07-11>错误 | //tr>|||
| 2022-07-12 | 2022-07-15 | 周二<2022-07-11>错误 | //tr>|||
| 2022-07-13 | 2022-07-10 | 周三2022-07-11 | 错误|||
| 2022-07-13 | 2022-07-15 | 周三2022-07-11 | 错误|||
| 2022-07-13 | 2022-07-14 | 周三2022-07-11 | 错误|||
| 2022-07-13 | 2022-07-13 | 周三2022-07-11 | 真 | ||
| 2022-07-13 | 2022-07-12 | 周三2022-07-11 | 真 | ||
| 2022-07-13 | 2022-07-11 | 周三 | <2022-07-11>真 | ||
| 2022-07-14 | 2022-07-10 | 周四 | <2022-07-11>错误 | //tr>||
| 2022-07-14 | 2022-07-11 | 周四 | 2022-07-11 | 真 | |
| 2022-07-14 | 2022-07-12 | 周四 | 202207-11 | 真 | //tr>|
| 2022-07-14 | 2022-07-13 | 周四 | 202207-11 | 真 | //tr>|
| 2022-07-14 | 2022-07-14 | 周四 | <2022-07-11>真 | ||
| 2022-07-14 | 2022-07-15 | 周四 | <2022-07-11>错误 | ||
| 2022-07-15 | 2022-07-10 | 周五 | <2022-07-11>错误 | //tr>||
| 2022-07-15 | 2022-07-11 | 周五 | 5 | 2022:07-11TRUE | |
| 2022-07-15 | 2022-07-12 | 周五 | 5 | <2022-07-11>真||
| 2022-07-15 | 2022-07-13 | 周五 | 5 | <2022-07-11>真||
| 2022-07-15 | 2022-07-14 | 周五 | 5 | <2022-07-11>真||
| 2022-07-15 | 2022-07-15 | 周五 | 5 | 202207-11 | 真
上面的更正将修复与BETWEEN一起使用的case语句，但在遵循所需结果时，我认为星期几的逻辑可能存在问题，特别是如果打算在星期一使用的话->只要是一周内的今天。
值得注意的是，请检查您的周初设置，因为它可能不会在此处为周初返回0。https://docs.snowflake.com/en/sql-reference/functions-date-time.html#first-
我创建了一个测试用例，并对其进行了测试——它似乎按照上面描述的所需逻辑工作：
create
or replace table testdata (
create_date date,
c2 varchar,
c1 int default dayofweekiso(create_date)
);
insert into
testdata (create_date, c2)
values
(to_date('07-10-2022', 'mm-dd-yyyy'),'Sunday'),
(to_date('07-11-2022', 'mm-dd-yyyy'),'Monday'),
(to_date('07-12-2022', 'mm-dd-yyyy'), 'Tuesday'),
(to_date('07-13-2022', 'mm-dd-yyyy'), 'Wednesday'),
(to_date('07-14-2022', 'mm-dd-yyyy'),'Thursday'),
(to_date('07-15-2022', 'mm-dd-yyyy'),'Friday');
select
current_date(),
dayofweek(current_date()),
dayofweekiso(current_date),
create_date
from
testdata
where
(
dayofweekiso(create_date) = dayofweekiso(current_date())
and create_date = current_date()
)
or create_date between case
dayofweekiso(current_date())
when 2 then (DATEADD('day', -1, current_date()))
when 3 then (DATEADD('day', -2, current_date()))
when 4 then (DATEADD('day', -3, current_date()))
when 5 then (DATEADD('day', -4, current_date()))
when 6 then (DATEADD('day', -5, current_date()))
when 7 then (DATEADD('day', -6, current_date()))
end
and current_date();
这只是周二，我预计只会在周一和周二的结果中看到数据：
| CURRENT_DATE | ||||
|---|---|---|---|---|
| 2022-07-12 | 22022-07-11 | |||
| 2022-07-12 | 2 | 2 | 22022-07-12 |