当前位置： 文江博客话题详情

在雪花中的条款中

发布于 2025-02-08 16:07:27 字数 402 浏览 2 评论 0原文

我正在尝试在Snowflake中的Where子句中做一个案例语句，但我不太确定该怎么做。

我要做的是，如果我当前的一个月是1月，那么日期的哪个子句是上一年和今天的开始。如果不是，那么日期的where子句将在当年年初到今天。

WHERE 
CASE MONTH(CURRENT_DATE()) = 1 THEN DATE BETWEEN DATE_TRUNC(‘YEAR’, DATEADD(YEAR, -1, CURRENT_DATE())) AND CURRENT_DATE()
CASE MONTH(CURRENT_DATE()) != 1 THEN DATE BETWEEN DATE_TRUNC(‘YEAR’, CURRENT_DATE()) AND CURRENT_DATE()
END

感谢任何帮助！

原文

I am trying to do a case statement within the where clause in snowflake but I’m not quite sure how should I go about doing it.

What I’m trying to do is, if my current month is Jan, then the where clause for date is between start of previous year and today. If not, the where clause for date would be between start of current year and today.

WHERE 
CASE MONTH(CURRENT_DATE()) = 1 THEN DATE BETWEEN DATE_TRUNC(‘YEAR’, DATEADD(YEAR, -1, CURRENT_DATE())) AND CURRENT_DATE()
CASE MONTH(CURRENT_DATE()) != 1 THEN DATE BETWEEN DATE_TRUNC(‘YEAR’, CURRENT_DATE()) AND CURRENT_DATE()
END

Appreciate any help on this!

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

留蓝 2025-02-15 16:07:27

使用案例返回-1的表达> dateadd（）在date_trunc（）：

WHERE DATE BETWEEN 
        DATE_TRUNC('YEAR', DATEADD(YEAR, CASE WHEN MONTH(CURRENT_DATE()) = 1 THEN -1 ELSE 0 END, CURRENT_DATE())) 
        AND 
        CURRENT_DATE()

Use a CASE expression that returns -1 if the current month is January or 0 for any other month, so that you can get with DATEADD() a date of the previous or the current year to use in DATE_TRUNC():

WHERE DATE BETWEEN 
        DATE_TRUNC('YEAR', DATEADD(YEAR, CASE WHEN MONTH(CURRENT_DATE()) = 1 THEN -1 ELSE 0 END, CURRENT_DATE())) 
        AND 
        CURRENT_DATE()
回复 原文
断桥再见 2025-02-15 16:07:27

我怀疑您甚至不需要在此处使用案例：

WHERE
    (MONTH(CURRENT_DATE()) = 1 AND
     DATE BETWEEN DATE_TRUNC(‘YEAR’, DATEADD(YEAR, -1, CURRENT_DATE())) AND
                  CURRENT_DATE()) OR
    (MONTH(CURRENT_DATE()) != 1 AND
     DATE BETWEEN DATE_TRUNC(‘YEAR’, CURRENT_DATE()) AND CURRENT_DATE())

I suspect that you don't even need to use CASE here:

WHERE
    (MONTH(CURRENT_DATE()) = 1 AND
     DATE BETWEEN DATE_TRUNC(‘YEAR’, DATEADD(YEAR, -1, CURRENT_DATE())) AND
                  CURRENT_DATE()) OR
    (MONTH(CURRENT_DATE()) != 1 AND
     DATE BETWEEN DATE_TRUNC(‘YEAR’, CURRENT_DATE()) AND CURRENT_DATE())
回复 原文
兔小萌 2025-02-15 16:07:27

因此，其他答案还不错，但是...答案可以更简单地

制作一个小桌子来制动正在发生的事情。

select 
    row_number() over (order by null) - 1 as rn, 
    dateadd('day', rn * 5, date_trunc('year',current_date())) as pretend_current_date,
    DATEADD(YEAR, -1, pretend_current_date) as pcd_sub1,
    month(pretend_current_date) as pcd_month,
    DATE_TRUNC(year, iff(pcd_month = 1, pcd_sub1, pretend_current_date)) as _from,
    pretend_current_date as _to
from table(generator(ROWCOUNT => 30))
order by rn;

this shows:

RNPRETEND_CURRENT_DATEPCD_SUB1PCD_MONTH_FROM_TO
02022-01-012021-01-0112021-01-012022-01-01
12022-01-062021-01-0612021-01-012022- 01-06
22022-01-112021-01-1112021-01-012022-01-11
32022-01-162021-01-1612021-01-01-01 2022-012022-01-16 4 2022--01-16
42022-- 01-212021-01-2112021-01-012022-01-21
52022-01-262021-01-2612021-01-01-01-012022-01-26
62022-01-01-312021-31 2021-01-01 -3112021-01-012022-01-31
72022-02-052021-02-0522022-01-012022-02-02-05
82022-02-02-02-02-102021-021-02-02-202-1022022-10 -012022-02-10
92022-02-152021-02-1522022-01-012022-02-15
102022-02-02-02-202021-02-02-2022022-01 2022-012022-02-02-02--02--02- 20
112022-02-252021-02-2522022-01-012022-02-25
122022-03-022021-03-0232022-02-132022-03--03-03--03--03--03--03--03--03--03--
2022-01 2022-03-02 132022-01-01-01 072021-03-0732022-01-012022-03-07
142022-03-122021-03-1232022-01-01-012022-03-03-12
152022-2022-03 2022-03-172021-03 2021-03-1732022-01-012022-03-17
162022-03-222021-03-2232022-01-012022-03-22
172022-03-272021-03-2732022-01-01-01-01-01-01-01-01-01-01-01-01-01-01-012022-03-27
182022-04-012021-04-0142022-01-012022-04-01
192022-04-06-062021-04-04-0642022-01-01 2022-012022-04-04-06-04-06
206 202022-04-112021-04-1142022-01-012022-04-11
212022-04-162021-04-1642022-01-01-012022-04-16
2220222-20222-04-212021 2021 2021 2021 -04-2142022-01-012022-04-21
232022-04-262021-04-2642022-01-012022-04-26
242022-05-01 20212021-05-05-01520222222 -01-012022-05-01
252022-05-062021-05-0652022-01-012022-05-06
262022-05-05-112021-05-11520222-01 2022-01 2022-01 2022--01 2022--01 2022--012022--01 05-11
272022-05-162021-05-1652022-01-012022-05-16
282022-05-212021-05-2152022-01-01-01 2022-012022-05-21
292022-29 2022- 05-262021-05-2652022-01-012022-05-26

您的逻辑要求“是1月份的当前日期”，此时为上一年，然后日期截断为年度，否则，将当前日期截断为年。作为两次测试的开始。

这与当前日期减去一个月相同，然后将其截断为年。

因此，无需任何IFF或案例

WHERE date BETWEEN DATE_TRUNC(year, DATEADD(month,-1, CURRENT_DATE())) AND CURRENT_DATE()

，如果您想放弃某些paren，current_date如果将其留在上情况下，则可以使用它，因此它甚至可以较小：

WHERE date BETWEEN DATE_TRUNC(year, DATEADD(month,-1, CURRENT_DATE)) AND CURRENT_DATE

So the other answers are quite good, but... the answer can be even simpler

Making a little table to brake down what is happening.

select 
    row_number() over (order by null) - 1 as rn, 
    dateadd('day', rn * 5, date_trunc('year',current_date())) as pretend_current_date,
    DATEADD(YEAR, -1, pretend_current_date) as pcd_sub1,
    month(pretend_current_date) as pcd_month,
    DATE_TRUNC(year, iff(pcd_month = 1, pcd_sub1, pretend_current_date)) as _from,
    pretend_current_date as _to
from table(generator(ROWCOUNT => 30))
order by rn;

this shows:

RNPRETEND_CURRENT_DATEPCD_SUB1PCD_MONTH_FROM_TO
02022-01-012021-01-0112021-01-012022-01-01
12022-01-062021-01-0612021-01-012022-01-06
22022-01-112021-01-1112021-01-012022-01-11
32022-01-162021-01-1612021-01-012022-01-16
42022-01-212021-01-2112021-01-012022-01-21
52022-01-262021-01-2612021-01-012022-01-26
62022-01-312021-01-3112021-01-012022-01-31
72022-02-052021-02-0522022-01-012022-02-05
82022-02-102021-02-1022022-01-012022-02-10
92022-02-152021-02-1522022-01-012022-02-15
102022-02-202021-02-2022022-01-012022-02-20
112022-02-252021-02-2522022-01-012022-02-25
122022-03-022021-03-0232022-01-012022-03-02
132022-03-072021-03-0732022-01-012022-03-07
142022-03-122021-03-1232022-01-012022-03-12
152022-03-172021-03-1732022-01-012022-03-17
162022-03-222021-03-2232022-01-012022-03-22
172022-03-272021-03-2732022-01-012022-03-27
182022-04-012021-04-0142022-01-012022-04-01
192022-04-062021-04-0642022-01-012022-04-06
202022-04-112021-04-1142022-01-012022-04-11
212022-04-162021-04-1642022-01-012022-04-16
222022-04-212021-04-2142022-01-012022-04-21
232022-04-262021-04-2642022-01-012022-04-26
242022-05-012021-05-0152022-01-012022-05-01
252022-05-062021-05-0652022-01-012022-05-06
262022-05-112021-05-1152022-01-012022-05-11
272022-05-162021-05-1652022-01-012022-05-16
282022-05-212021-05-2152022-01-012022-05-21
292022-05-262021-05-2652022-01-012022-05-26

Your logic is asking "is the current date in the month of January", at which point take the prior year, and then date truncate to the year, otherwise take the current date and truncate to the year. As the start of a BETWEEN test.

This is the same as getting the current date subtracting one month, and truncating this to year.

Thus there is no need for any IFF or CASE

WHERE date BETWEEN DATE_TRUNC(year, DATEADD(month,-1, CURRENT_DATE())) AND CURRENT_DATE()

and if you like to drop some paren's, CURRENT_DATE can be used if you leave it in upper case, thus it can even be smaller:

WHERE date BETWEEN DATE_TRUNC(year, DATEADD(month,-1, CURRENT_DATE)) AND CURRENT_DATE
回复 原文
~没有更多了~

关于作者

动次打次papapa

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

牛↙奶布丁

文章 0 评论 0

COSO

文章 0 评论 0

落叶

文章 0 评论 0

暗地喜欢

文章 0 评论 0

qq_i8qOEG

文章 0 评论 0

qq_Wl4Sbi

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文