英文:
window function identifying the 1st, 2nd, etc. day of week of the month
问题
我有一个实用的日历表格，看起来像这样：
| 日期 | 星期几 | 日期 | 月份 | 年份 |
|---|---|---|---|---|
| 2023-01-01 | 星期日 | 1 | 1 | 2023 |
| 2023-01-02 | 星期一 | 2 | 1 | 2023 |
| 2023-01-03 | 星期二 | 3 | 1 | 2023 |
| 2023-01-04 | 星期三 | 4 | 1 | 2023 |
| 2023-01-05 | 星期四 | 5 | 1 | 2023 |
| 2023-01-06 | 星期五 | 6 | 1 | 2023 |
| 2023-01-07 | 星期六 | 7 | 1 | 2023 |
| 2023-01-08 | 星期日 | 8 | 1 | 2023 |
| 2023-01-09 | 星期一 | 9 | 1 | 2023 |
| 2023-01-10 | 星期二 | 10 | 1 | 2023 |
| 2023-01-11 | 星期三 | 11 | 1 | 2023 |
| 2023-01-12 | 星期四 | 12 | 1 | 2023 |
| 2023-01-13 | 星期五 | 13 | 1 | 2023 |
| 2023-01-14 | 星期六 | 14 | 1 | 2023 |
| 2023-01-15 | 星期日 | 15 | 1 | 2023 |
我试图添加一列，表示每月第一周的第一个工作日。在这一列中，第1-7行应该显示1，第8-14行应该显示2，以此类推，直到2023年2月，然后模式重置。
在我看来，这里需要使用窗口函数，但是RANK()只是在按月分区时计算月份的天数，或者在按提取的字段（即一年中的星期）分区时计算一周中的天数。我也不清楚其他窗口函数如何用来生成正确的结果。
英文:
I have a utility calendar table which looks like this:
| DATE | DAY_OF_WEEK | DAY_OF_MONTH | MONTH | YEAR |
|---|---|---|---|---|
| 2023-01-01 | Sunday | 1 | 1 | 2023 |
| 2023-01-02 | Monday | 2 | 1 | 2023 |
| 2023-01-03 | Tuesday | 3 | 1 | 2023 |
| 2023-01-04 | Wednesday | 4 | 1 | 2023 |
| 2023-01-05 | Thursday | 5 | 1 | 2023 |
| 2023-01-06 | Friday | 6 | 1 | 2023 |
| 2023-01-07 | Saturday | 7 | 1 | 2023 |
| 2023-01-08 | Sunday | 8 | 1 | 2023 |
| 2023-01-09 | Monday | 9 | 1 | 2023 |
| 2023-01-10 | Tuesday | 10 | 1 | 2023 |
| 2023-01-11 | Wednesday | 11 | 1 | 2023 |
| 2023-01-12 | Thursday | 12 | 1 | 2023 |
| 2023-01-13 | Friday | 13 | 1 | 2023 |
| 2023-01-14 | Saturday | 14 | 1 | 2023 |
| 2023-01-15 | Sunday | 15 | 1 | 2023 |
I am trying to add a column which signifies the first weekday of its kind in a month. In this column, rows 1-7 should say 1, rows 8-14 should say 2, and so on until February 2023, and the pattern resets.
It seems to me that a window function is necessary here, however RANK() just counts the days of the month when partitioned by month, or just counts the days in the week when partitioned by an extracted field which is the week of the year. I don't see how the other window functions could be used to generate the correct results, either.
答案1
得分: 1
尝试使用以下的 dense_rank 函数：
select *,
dense_rank() over (partition by year, month, day_of_week order by date) as rnk
from table_name
order by date
或者使用 ((day_of_month-1) /7)+1。 （基于 @NickW 的评论）
英文:
Try to use the dense_rank function as the following:
select *,
dense_rank() over (partition by year, month, day_of_week order by date) as rnk
from table_name
order by date
Or use ((day_of_month-1) /7)+1. (based on a comment from @NickW)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论