在PostgresSQL中排除周末

Question

我正在尝试查找基于日期的幻灯片数量，但只想包括工作日(不包括周末)。

以下是目前为止我的标准:

1天内到期:

总和(案例当订单。duedate:日期比;Current_date和orders。Duedate::date - 1 = current_date然后nullif (orders.slides, 0)

结束)作为"幻灯片(1天)"，

总和(案例当订单。duedate:日期比;Current_date和orders。Duedate::date - 2 = current_date然后nullif (orders.slides, 0)

结束)作为"幻灯片(2天)">

等等

在星期一，这段代码完成了我想要的，因为它显示了在未来几天内到期的内容，但在星期五，对于1天内到期的幻灯片，我希望它显示周一到期的幻灯片，而对于2天内到期的幻灯片，我希望它显示周二，而不是周六和周日。

任何帮助都将非常感激!

Answer 1

使用CTE。

CURRENT_DATE示例:

CREATE TABLE public.slide_test (
    id integer,
    slides integer,
    order_date date
);

INSERT INTO
   slide_test
VALUES 
    (1,2,'2022-11-07'), (2,3,'2022-11-08'),(3,1,'2022-11-09'),
    (4,5,'2022-11-10'), (5,3,'2022-11-11'),(6,8, '2022-11-14'),
    (7,7, '2022-11-15');
select * from slide_test ;
 id | slides | order_date 
----+--------+------------
  1 |      2 | 11/07/2022
  2 |      3 | 11/08/2022
  3 |      1 | 11/09/2022
  4 |      5 | 11/10/2022
  5 |      3 | 11/11/2022
  6 |      8 | 11/14/2022
  7 |      7 | 11/15/2022
SELECT CURRENT_DATE;
current_date 
--------------
 11/07/2022
WITH not_fri AS (
    SELECT
        CASE WHEN order_date - '1 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) != 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '2 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) != 5  THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
),
fri AS (
    SELECT
        CASE WHEN order_date - '3 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) = 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '4 day'::interval = CURRENT_DATE AND
            extract(dow from CURRENT_DATE) = 5 THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
)
SELECT
    *
FROM
   not_fri
UNION
SELECT
    *
FROM
    fri;

 1 Day | 2 Day 
-------+-------
  NULL |  NULL
  NULL |     1
     3 |  NULL

使用星期五日期:

WITH not_fri AS (
    SELECT
        CASE WHEN order_date - '1 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) != 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '2 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) != 5  THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
),
fri AS (
    SELECT
        CASE WHEN order_date - '3 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) = 5 THEN
            sum(nullif (slides, 0))
        END AS "1 Day",
        CASE WHEN order_date - '4 day'::interval = '11/11/2022'::date AND
            extract(dow from '11/11/2022'::date) = 5 THEN
            sum(nullif (slides, 0))
        END AS "2 Day"
    FROM
        slide_test
    GROUP BY
        order_date
)
SELECT
    *
FROM
   not_fri
UNION
SELECT
    *
FROM
    fri;
 1 Day | 2 Day 
-------+-------
  NULL |  NULL
     8 |  NULL
  NULL |     7