英文:
Find the n-th occurance of a day in a month in SQL
问题
我正在尝试从日期中仅仅计算一个月中某个工作日的数量。我的目标是"Expectation"列。您有关于如何使用SQL脚本来实现这个目标的想法吗？
英文:
i am trying to count the number of a weekday in a month just from a date.
My goal is the expectation column. Any idea how I can achieve it with a SQL script?
答案1
得分: 0
代码部分不翻译，以下是翻译好的内容：
"Weekday"列是否属于你的输入还是仅仅属于你的输出尚不清楚。但是你确实说了“只从日期中获取”，所以我会假设它是输出（为什么需要重复我不知道）。你可以使用to_char()函数来获取日期名称，然后在窗口函数row_number() over()中使用它（参见这里、这里和这里）。
with test(dt) as
( select dt::date
from generate_series( date '2020-01-01'
, date '2020-01-22'
, interval '1 day'
) gs(dt)
)
-- 你的查询从这里开始。
select dt "Date"
, wd "Weekday"
, (row_number(*) over(partition by wd order by dt))::text || '.' || wd "Expected"
from (select dt, to_char(dt, 'Day') wd
from test
) sq
order by dt;
这个公共表达式(CTE)严格用作数据生成器。请参考demo。
顺便提一下，对于国际受众来说，最好使用ISO 8601日期格式yyyy-mm-dd。不管当地的惯例如何，它都是明确的。如果你的日期没有超过12，我们无法确定你使用的是哪种格式（mm-dd-yyyy还是dd-mm-yyyy）。
英文:
It is unclear whether the Weekday column is part your input or just part of your output. But you did say just from a date so I will assume it is output (Why id needs repeating I do not know). You can get the day name from the to_char() function. Then use that in the Window function row_number() over(). (see here, here and here)
with test(dt) as
( select dt::date
from generate_series( date '2020-01-01'
, date '2020-01-22'
, interval '1 day'
) gs(dt)
)
-- your query starts here.
select dt "Date"
, wd "Weekday"
, (row_number(*) over(partition by wd order by dt))::text || '.' || wd "Expected"
from (select dt, to_char(dt, 'Day') wd
from test
) sq
order by dt;
The CTE is used strictly as a data generator. See demo
FYI. It is best with an international audience to use the ISO 8601 date format yyyy-mm-dd. It is unambiguous regardless of local conventions. If your days had not exceeded 12 we could not know which format (mm-dd-yyyy or dd-mm-yyyy) you used.
答案2
得分: 0
(extract(day from dt)::int - 1) / 7 + 1
完整示例:
with test(dt) as
(select dt::date
from generate_series(date '2020-01-01'
, date '2020-01-22'
, interval '1 day'
) gs(dt))
select dt, (extract(day from dt)::int - 1) / 7 + 1 || '. ' || to_char(dt, 'Day')
from test
order by dt
英文:
You can calculate how many full weeks has been passed since the start of the month and add 1 to the result. This is the index number of day of week.
(extract(day from dt)::int - 1) / 7 + 1
Full example:
with test(dt) as
(select dt::date
from generate_series(date '2020-01-01'
, date '2020-01-22'
, interval '1 day'
) gs(dt))
select dt, (extract(day from dt)::int - 1) / 7 + 1 || '. ' || to_char(dt, 'Day')
from test
order by dt
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论