sql - 在 PostgreSQL 中直到当前日期的每月工作日

Question

我想在 PostgreSQL 中获取截至当前日期为止的一个月的工作日数。

例如，本月到今天，工作日为 17。我如何从下面的查询或通用解决方案中获取此信息？

SELECT 
    TO_CHAR(i, 'YYYY-MM') as year_month,
    i::date as date,
    EXTRACT(DOW FROM i) as number_weekday,
    CASE WHEN EXTRACT(DOW FROM i) IN ('1', '2', '3', '4', '5') 
                  AND i NOT IN ('2018-01-01', '2018-03-30', '2018-04-02', '2018-05-01', '2018-05-10', '2018-05-21', '2018-10-03', '2018-12-25', '2018-12-26',
                                    '2019-01-01', '2019-04-19', '2019-04-22', '2019-05-01', '2019-05-30', '2019-06-10', '2019-10-03', '2019-12-25', '2019-12-26',
                                    '2020-01-01', '2020-04-10', '2020-04-13', '2020-05-01', '2020-05-21', '2020-06-01', '2020-10-03', '2020-12-25', '2020-12-26',
                                    '2021-01-01', '2021-04-02', '2021-04-05', '2021-05-01', '2021-05-13', '2021-05-24', '2021-10-03', '2021-12-25', '2021-12-26')
              THEN 'Working Day' ELSE 'Non Working Day' END as day_type

    FROM 
        generate_series('2017-12-31', '2022-01-01', '1 day'::interval) i
   WHERE 
    i::date < CURRENT_DATE
    ORDER BY 
        date DESC

Answer 1

作为一个通用的解决方案，我认为最好的是 GMB 的答案。

如果你想从你提供的查询中做同样的事情，你可以这样做:

WITH days AS
(
    SELECT 
    TO_CHAR(i, 'YYYY-MM') as year_month,
    i::date as date,
    EXTRACT(DOW FROM i) as number_weekday,
    CASE WHEN EXTRACT(DOW FROM i) IN ('1', '2', '3', '4', '5') 
                  AND i NOT IN ('2018-01-01', '2018-03-30', '2018-04-02', '2018-05-01', '2018-05-10', '2018-05-21', '2018-10-03', '2018-12-25', '2018-12-26',
                                    '2019-01-01', '2019-04-19', '2019-04-22', '2019-05-01', '2019-05-30', '2019-06-10', '2019-10-03', '2019-12-25', '2019-12-26',
                                    '2020-01-01', '2020-04-10', '2020-04-13', '2020-05-01', '2020-05-21', '2020-06-01', '2020-10-03', '2020-12-25', '2020-12-26',
                                    '2021-01-01', '2021-04-02', '2021-04-05', '2021-05-01', '2021-05-13', '2021-05-24', '2021-10-03', '2021-12-25', '2021-12-26')
              THEN 'Working Day' ELSE 'Non Working Day' END as day_type

    FROM 
        generate_series('2017-12-31', '2022-01-01', '1 day'::interval) i
   WHERE 
    i::date <= CURRENT_DATE
    ORDER BY 
        date DESC
)
SELECT year_month, SUM((day_type = 'Working Day'):: int) 
FROM days
WHERE 
     date >= date_trunc('month', now())
GROUP BY year_month;

DEMO HERE