Оставшиеся рабочие дни в этом месяце в Amazon Redshift PostgreSQL

Я хотел бы найти способ определить оставшееся количество рабочих дней в этом календарном месяце с помощью Redshift PostgreSQL. В настоящее время у меня есть версия MySQL, написанная другом. Я не знаю достаточно о том, как это было написано даже для перевода на другой диалект. Но если бы кто-нибудь мог помочь с переводом, это был бы очень полезный инструмент!

Вывод должен функционировать так же, как networkdays() функция от Excel. В этой функции begin_date и end_date предоставляются в качестве аргументов для функции. Он рассчитывает количество рабочих дней (не выходных календарных дней) между начальной и конечной датами включительно.

Вот текущий MySQL:

SELECT 1 AS pk ,COUNT(*) AS remaining
FROM (
    SELECT WEEKDAY(DATE(DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'PST8PDT'), '%Y-%m-01') + INTERVAL (a.num-1) DAY)) AS weekdays
        FROM (
        SELECT @row := @row + 1 AS num
        FROM schema.table t, (SELECT @row := 0) r
    ) a
        WHERE a.num >= DAY(CONVERT_TZ(NOW(), 'UTC', 'PST8PDT'))
        AND a.num <= DAY((DATE_FORMAT(CONVERT_TZ(NOW(), 'UTC', 'PST8PDT'), '%Y-%m-01') + INTERVAL 1 MONTH) - INTERVAL 1 DAY)
) b
WHERE b.weekdays NOT IN (0,6)

Любая помощь будет отличной!

Источник

4 ответа

Решение

Популярной идеей для хранилищ данных является создание calendar таблица, которая содержит все даты (или, по крайней мере, даты, относящиеся к компании), а также такие флаги, как:

  • Праздничные дни
  • Рабочие дни
  • Первый (рабочий) день месяца
  • Последний (рабочий) день месяца
  • Номер месяца
  • Номер недели
  • Номер дня

Хотя многие из этих значений могут быть рассчитаны с помощью функций даты, часто проще присоединиться к calendar таблица для выполнения некоторых функций даты.

В случае расчета оставшихся рабочих дней, это будет просто подсчет количества строк в calendar таблицы, которые находятся в желаемом диапазоне, где is_work_day флаг установлен. Это может быть сделано через JOIN или подзапрос.

Не так необычно, как некоторые из этих запросов, но часто гораздо проще в обслуживании. Кроме того, Amazon Redshift не поддерживает generate_series функция, так что это часто все, что возможно.

Смотрите также:

Источник

Никакая функция не нужна, вы можете сделать это с помощью одного оператора SQL:

SELECT count(*)
FROM generate_series(CURRENT_TIME,
                     date_trunc('month', CURRENT_TIME) + interval '1 month - 1 day',
                     interval '1 day') days(d)
WHERE extract(dow from d) NOT IN (0, 6);

Конечно, вы можете обернуть это в функцию SQL, если это необходимо. Учитывая ваше требование оставшихся рабочих дней в текущем месяце, вам не нужно указывать какие-либо параметры.

Источник

Мой ответ заключается в том, что у вас есть таблица с огромным количеством транзакций, по крайней мере, один раз в день в месяц, в который вы заботитесь. К счастью для меня, наша система позволяет пользователям планировать транзакции в будущем, поэтому я могу просто пропинговать ее для некоторой простой логики дат.

SELECT
count(CASE WHEN business_day < date(getdate()) THEN 1 END) as passed
,count(business_day) as total_business_days
FROM
  (SELECT distinct
   date(o.appointment_full_time) as business_day
  FROM
   orders o
  WHERE
   date_trunc('month', o.appointment_full_time) = date_trunc('month', getdate())
--this month
    AND extract(dow from o.appointment_full_time) not in (0,6)
--exclude weekends
    AND date(o.appointment_full_time) 
       not in ('2017-1-1', '2017-1-2', '2017-1-16', '2017-5-29', '2017-7-4', '2017-9-4',
          '2017-11-23', '2017-11-24', '2017-12-25', '2017-12-24', '2017-12-31')
--manually enter the holidays in once per year
 ) a
Источник

Чтобы подсчитать количество рабочих дней, вам нужно знать дату 1-го дня (start_date) месяца с использованием date_trunc() функция. После этого вам нужно получить количество дней для конкретного месяца (month_last_day) с помощью extract() функция, есть вики-страница по этому поводу. И, наконец, вы можете generate_series() дней использования start_date дата и month_last_day число, исключая выходные дни, используя date_part() функция.

CREATE OR REPLACE FUNCTION extract_month_business_days(d DATE, count_remaining BOOLEAN)
  RETURNS INTEGER AS $$
DECLARE
  start_date DATE;
  month_last_day INTEGER;
  result INTEGER;
BEGIN
  IF count_remaining THEN
    start_date = d;
  ELSE
    start_date = date_trunc('month',d);
  END IF;
  month_last_day = extract(DAY FROM date_trunc('month',d) + INTERVAL '1 MONTH - 1 day');
  SELECT count(*) INTO result FROM generate_series(0,(month_last_day - extract(DAY FROM start_date))::INTEGER) day
    WHERE date_part('dow', start_date + day) NOT IN (0,6);
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Результат:

WITH t(dates) AS ( VALUES
  ('2016-02-18'::DATE),
  ('2016-03-18'::DATE),
  ('2016-04-18'::DATE),
  ('2016-05-18'::DATE)
)
SELECT
  to_char(dates,'Month YY') AS month,
  extract_month_business_days(dates,FALSE) AS number_business_days,
  extract_month_business_days(dates,TRUE) AS remaining_business_days
FROM t;

    month     | number_business_days | remaining_business_days 
--------------+----------------------+-------------------------
 February  16 |                   21 |                       8
 March     16 |                   23 |                      10
 April     16 |                   21 |                      10
 May       16 |                   22 |                      10
(4 rows)

ОБНОВЛЕНИЕ - КРАСНОЕ ИЗДАНИЕ

Как отметил @Джон, generate_series() недоступно в AWS Redshift, определение функции будет следующим:

CREATE OR REPLACE FUNCTION extract_month_business_days(d DATE, count_remaining BOOLEAN)
  RETURNS INTEGER AS $$
DECLARE
  start_date DATE;
  month_last_day INTEGER;
  result INTEGER;
  i INTEGER;
BEGIN
  result = 0;
  IF count_remaining THEN
    start_date = d;
  ELSE
    start_date = date_trunc('month',d);
  END IF;
  month_last_day = extract(DAY FROM date_trunc('month',d) + INTERVAL '1 MONTH - 1 day');
  result = 0;
  FOR i IN 0..(month_last_day - extract(DAY FROM start_date))::INTEGER LOOP
    IF (date_part('dow', start_date + i) NOT IN (0,6)) THEN
      result = result + 1;
    END IF;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;
Источник
Другие вопросы по тегам