SQL 根据 ISO 标准，2015 年和 2016 年每个月有多少周？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25659156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How many weeks are in each month of the year 2015 and 2016 based on ISO standard?
提问by Mark Jason Bagsic
This year 2014 has:
今年 2014 年有：
Jan-4
Feb-4
Mar-5
Apr-4
May-4
Jun-5
Jul-4
Aug-4
Sep-5
Oct-4
Nov-4
Dec-5
How to calculate this for any given year?
如何计算任何给定年份的这个？
回答by Erwin Brandstetter
There are multiple ways to define "weeks in a month" exactly. Assuming your count is defined (as your numbers indicate):
有多种方法可以准确定义“一个月中的几周”。假设您的计数已定义（如您的数字所示）：
How many Mondays lie in each month of the year?
You can generate it like that:
你可以这样生成它：
Simple:
简单的：
SELECT EXTRACT(month FROM d) AS mon, COUNT(*) AS weeks
FROM generate_series('2014-01-01'::date
, '2014-12-31'::date
, interval '1 day') d
WHERE EXTRACT(isodow FROM d) = 1 -- only Mondays
GROUP BY 1
ORDER BY 1;
Fast:
快速地：
SELECT EXTRACT(month FROM d) AS mon, COUNT(*) AS weeks
FROM generate_series ('2014-01-01'::date -- offset to first Monday
+ (8 - EXTRACT(isodow FROM '2014-01-01'::date)::int)%7
, '2014-12-31'::date
, interval '7 days') d
GROUP BY 1
ORDER BY 1;
Either way you get:
无论哪种方式，您都会得到：
mon weeks
1 4
2 4
3 5
4 4
5 4
6 5
7 4
8 4
9 5
10 4
11 4
12 5
Just replace 2014with the year of interest in each query.
只需2014在每个查询中替换为感兴趣的年份。
回答by Clodoaldo Neto
Applying the ISO 8601 to a month as suggested here
按照此处的建议将 ISO 8601 应用于一个月
select
to_char(d, 'YYYY Mon') as "Month",
case when
extract(dow from d) in (2,3,4)
and
extract(day from (d + interval '1 month')::date - 1) + extract(dow from d) >= 33
then 5
else 4
end as weeks
from generate_series(
'2014-01-01'::date, '2014-12-31', '1 month'
) g (d)
;
Month | weeks
----------+-------
2014 Jan | 5
2014 Feb | 4
2014 Mar | 4
2014 Apr | 4
2014 May | 5
2014 Jun | 4
2014 Jul | 5
2014 Aug | 4
2014 Sep | 4
2014 Oct | 5
2014 Nov | 4
2014 Dec | 4
